2021/9/26Mybatis入门
随着中秋节的结束,我们终于迎来了大三学期的第一堂课。与上学期不同,我们开始重新回顾Java,学习后端框架。我以为,大数据可以说是后端的延申,是大后端但是想要发展到大数据,就需要有后端的敲门砖,否则就会产生有劲使不上的感觉。
今天学的是Mybatis入门,先是回顾了一下mysql操作
# 创建数据库
CREATE DATABASE ssm_db1;
# 使用数据库
USE ssm_db1;
# 1.1 创建用户表
CREATE TABLE `user` (
`uid` VARCHAR(32) NOT NULL,
`username` VARCHAR(20) DEFAULT NULL, #用户名
`password` VARCHAR(32) DEFAULT NULL, #密码
`name` VARCHAR(20) DEFAULT NULL, #昵称
`email` VARCHAR(30) DEFAULT NULL, #电子邮箱
`telephone` VARCHAR(20) DEFAULT NULL, #电话
`birthday` DATE DEFAULT NULL, #生日
`sex` VARCHAR(10) DEFAULT NULL, #性别
`state` INT(11) DEFAULT 0, #状态:0=未激活,1=已激活
`code` VARCHAR(64) DEFAULT NULL, #激活码
PRIMARY KEY (`uid`)
) ;
# 1.2 初始化用户默认数据
INSERT INTO `user` VALUES ('u001','jack','1234','杰克','jack@czxy.com','13612345678','2015-11-04','男',0,NULL);
INSERT INTO `user` VALUES ('u002','rose','1234','肉丝','rose@czxy.com','13612345679','2015-11-05','女',0,NULL);
INSERT INTO `user` VALUES ('373eb242933b4f5ca3bd43503c34668b','ccc','ccc','aaa','bbb@store.com','15723689921','2015-11-04','男',0,'9782f3e837ff422b9aee8b6381ccf927bdd9d2ced10d48f4ba4b9f187edf7738')
,('3ca76a75e4f64db2bacd0974acc7c897','bb','bb','张三','bbb@store.com','15723689921','1990-02-01','男',0,'1258e96181a9457987928954825189000bae305094a042d6bd9d2d35674684e6')
,('62145f6e66ea4f5cbe7b6f6b954917d3','cc','cc','张三','bbb@store.com','15723689921','2015-11-03','男',0,'19f100aa81184c03951c4b840a725b6a98097aa1106a4a38ba1c29f1a496c231'),
('c95b15a864334adab3d5bb6604c6e1fc','bbb','bbb','老王','bbb@store.com','15712344823','2000-02-01','男',0,'71a3a933353347a4bcacff699e6baa9c950a02f6b84e4f6fb8404ca06febfd6f'),
('f55b7d3a352a4f0782c910b2c70f1ea4','aaa','aaa','小王','aaa@store.com','15712344823','2000-02-01','男',1,NULL);
# 创建数据库
CREATE DATABASE 数据库名;
# 使用数据库
USE 数据库名;
# 创建表
CREATE TABLE 表名(
#字段名1 类型 [约束],
uid VARCHAR(50) PRIMARY KEY,
username VARCHAR(50),
`password` VARCHAR(32)
);
# 数据的增删改查
## 1) 添加 insert into 表(字段1,字段2,...) values(值1,值2,...);
INSERT INTO `user`(uid,username,`password`) VALUES('u003','张三','1234');
## 2) 修改 update 表名 set 字段1=值1, 字段2=值2, ... where 条件
UPDATE `user` SET `password`='6666', `name`='小三' WHERE uid = 'u003';
## 3) 删除 delete from 表名 where 条件;
DELETE FROM `user` WHERE uid='u003';
## 4) 查询
SELECT * | 字段1,字段2,...
FROM 表名
WHERE 条件
GROUP BY 分组字段
HAVING 分组条件
ORDER BY 排序字段
LIMIT 开始索引,每页个数;
SELECT * FROM `user`;
## 5) 条件查询
SELECT * FROM `user` WHERE uid='u001' OR uid='u002';
简单回顾了一下,不过还可以抽时间练练多表查询,一对多,多对一这些感觉就有点不太熟了
进入正题,进入Mybatis入门
1.4 入门案例:搭建环境
1.4.1 构建项目
- 1)创建新项目:Java --> Java EE --> Web Application
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jO4xCuD4-1632616431295)(assets/image-20210720101402733.png)]
- 2)创建项目名:mybatis-demo01
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BDnotKrO-1632616431297)(assets/image-20210720102340299.png)]
- 3)添加jar包
WEB-INF/lib
目录下(目录不存在,需手动创建)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eW3Ijux6-1632616431298)(assets/image-20210720102938138.png)]
- 4)添加jar
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-szllmP4u-1632616431300)(assets/image-20210720103009314.png)]
1.5 入门案例:查询所有
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1OglX5o7-1632616376844)(assets/image-20210820211045795.png)]
1.5.1 JavaBean:User
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4CGjCcwK-1632616376847)(assets/image-20210720104018907.png)]
package com.czxy.ssm.domain;
import java.util.Date;
/**
*
Create Table
CREATE TABLE `user` (
`uid` varchar(32) NOT NULL,
`username` varchar(20) DEFAULT NULL,
`password` varchar(20) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`email` varchar(30) DEFAULT NULL,
`birthday` date DEFAULT NULL,
`sex` varchar(10) DEFAULT NULL,
`state` int(11) DEFAULT NULL,
`code` varchar(64) DEFAULT NULL,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*/
public class User {
private String uid;
private String username;
private String password;
private String name;
private String email;
private Date birthday;
private String sex;
private Integer state;
private String code;
@Override
public String toString() {
return "User{" +
"uid='" + uid + '\'' +
", username='" + username + '\'' +
", password='" + password + '\'' +
", name='" + name + '\'' +
", email='" + email + '\'' +
", birthday=" + birthday +
", sex='" + sex + '\'' +
", state=" + state +
", code='" + code + '\'' +
'}';
}
public String getUid() {
return uid;
}
public void setUid(String uid) {
this.uid = uid;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Integer getState() {
return state;
}
public void setState(Integer state) {
this.state = state;
}
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
public User(String uid, String username, String password, String name, String email, Date birthday, String sex, Integer state, String code) {
this.uid = uid;
this.username = username;
this.password = password;
this.name = name;
this.email = email;
this.birthday = birthday;
this.sex = sex;
this.state = state;
this.code = code;
}
public User() {
}
}
1.5.2 编写Dao:UserMapper
之前的开发中我们编写的都是UserDao,在MyBatis将dao称为Mapper。
所以此后所有dao接口统一命名成Mapper。
- 在MyBatis只需要编写接口即可,实现类由MyBatis自动生成,并在测试程序时自动执行。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-c9gPzpMq-1632616376849)(assets/image-20210720104245488.png)]
package com.czxy.ssm.mapper;
import com.czxy.ssm.domain.User;
import org.apache.ibatis.annotations.Select;
import java.util.List;
/**
* @author 桐叔
* @email liangtong@itcast.cn
*/
public interface UserMapper {
/**
* 查询所有
* @return
*/
@Select("select * from user")
public List<User> selectAll();
}
1.5.3 编写核心配置文件:SqlMapConfig.xml
-
配置文件名称:SqlMapConfig.xml
-
配置文件位置:src
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tza7AvDj-1632616376850)(assets/image-20210720104501947.png)]
- 配置文件内容:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 环境: default的值引用什么id,就表示使用什么数据库了-->
<environments default="db1">
<!-- 配置数据库连接信息及事务 -->
<environment id="db1">
<!-- 表示使用事务:默认是自动开启事务 -->
<transactionManager type="JDBC" />
<!-- 使用连接池 -->
<dataSource type="POOLED">
<!-- 数据库连接基本信息 -->
<property name="driver" value="com.mysql.cj.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/ssm_db1" />
<property name="username" value="root" />
<property name="password" value="1234" />
</dataSource>
</environment>
</environments>
<mappers>
<!-- 表示加载此包下的所有dao接口-->
<package name="com.czxy.ssm.mapper"/>
</mappers>
</configuration>
1.5.4 测试类
package com.czxy.ssm.test;
import com.czxy.ssm.domain.User;
import com.czxy.ssm.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
/**
* @author 桐叔
* @email liangtong@itcast.cn
*/
public class Test01_SelectAll {
public static void main(String[] args) throws IOException {
//1 加载配置文件
// 1.1 获得资源流
InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
// 1.2 获得工厂
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
//2 获得会话(连接)
SqlSession session = factory.openSession();
//3获得功能接口
UserMapper userMapper = session.getMapper(UserMapper.class);
//4 调用功能
List<User> userList = userMapper.selectAll();
//5 打印查询结果
for (User user : userList) {
System.out.println(user);
}
//6 释放资源
session.close();
}
}
1.6 总结
- 到这里MyBatis的入门案例已经完成。我们总结一下:
- 编写SqlMapConfig.xml,用于配置数据源和需要加载的Mapper
- 编写UserMapper.java接口,用于执行方法与SQL语句的绑定
- 基本API使用,流程是:加载资源、获得工厂、获得会话、获得Mapper。
2. 基本操作:增删改查
2.1 模糊查询
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mhxpLGy4-1632616471344)(assets/image-20210720110922348.png)]
-
功能接口中的方法
-
如果参数简单类型,sql语句需要使用value [不推荐]
@Select("select * from user where name like '%${value}%'") public List<User> selectByName(String name);
-
如果使用@Param,可以进行相应的命名 【推荐】
@Select("select * from user where name like '%${name}%'") public List<User> selectByName(@Param("name") String name);
-
参数替换总结
- #{} ,全部替换,例如:where uid = #{uid}
- , 部 分 替 换 , 例 如 : w h e r e n a m e l i k e ′ {} ,部分替换,例如:where name like '% ,部分替换,例如:wherenamelike′{name}%’
-
-
测试类
package com.czxy.ssm.test;
import com.czxy.ssm.domain.User;
import com.czxy.ssm.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
/**
* @author 桐叔
* @email liangtong@itcast.cn
*/
public class Test02_Like {
public static void main(String[] args) throws IOException {
//1 加载配置文件
// 1.1 获得资源流
InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
// 1.2 获得工厂
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
//2 获得会话(连接)
SqlSession session = factory.openSession();
//3获得功能接口
UserMapper userMapper = session.getMapper(UserMapper.class);
//4 调用功能
List<User> userList = userMapper.selectByName("王");
//5 打印查询结果
for (User user : userList) {
System.out.println(user);
}
}
}
2.2 插入数据
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aYaS2b4t-1632616471346)(assets/image-20210720112245191.png)]
- 功能接口中的方法
/**
* 插入数据
* @param user
*/
@Insert("insert into user(uid, username, password, name, email, birthday, sex, state) values(#{uid},#{username},#{password},#{name},#{email},#{birthday},#{sex},#{state})")
public Integer insert(User user);
- 测试类(注意:需要提交事务)
package com.czxy.ssm.test;
import com.czxy.ssm.domain.User;
import com.czxy.ssm.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.List;
/**
* @author 桐叔
* @email liangtong@itcast.cn
*/
public class Test03_Insert {
public static void main(String[] args) throws IOException {
//1 加载配置文件
// 1.1 获得资源流
InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
// 1.2 获得工厂
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
//2 获得会话(连接)
SqlSession session = factory.openSession();
//3获得功能接口
UserMapper userMapper = session.getMapper(UserMapper.class);
//4 调用功能
User user = new User();
user.setUid("1");
user.setUsername("jack");
user.setPassword("1234");
user.setName("杰克");
user.setEmail("itcast_lt@163.com");
user.setBirthday(new Date());
user.setSex("男");
user.setSex("0");
Integer result = userMapper.insert(user);
System.out.println(result);
//5 提交资源
session.commit();
//6 释放资源
session.close();
}
}
2.3 更新数据
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4XV2CvcA-1632616471347)(assets/image-20210720113540494.png)]
- 功能接口中的方法
/**
* 插入数据
* @param user
*/
@Insert("update user set username=#{username}, password=#{password}, name=#{name}, email=#{email},birthday=#{birthday},sex=#{sex}, state=#{state} where uid=#{uid}")
public Integer update(User user);
- 测试类
package com.czxy.ssm.test;
import com.czxy.ssm.domain.User;
import com.czxy.ssm.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
/**
* @author 桐叔
* @email liangtong@itcast.cn
*/
public class Test04_Update {
public static void main(String[] args) throws IOException {
//1 加载配置文件
// 1.1 获得资源流
InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
// 1.2 获得工厂
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
//2 获得会话(连接)
SqlSession session = factory.openSession();
//3获得功能接口
UserMapper userMapper = session.getMapper(UserMapper.class);
//4 调用功能
User user = new User();
user.setUid("1");
user.setUsername("jack1");
user.setPassword("12341");
user.setName("杰克");
user.setEmail("itcast_lt@163.com");
user.setBirthday(new Date());
user.setSex("男");
user.setSex("0");
Integer result = userMapper.update(user);
System.out.println(result);
//5 提交资源
session.commit();
//6 释放资源
session.close();
}
}
2.4 删除数据
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KsG38Ots-1632616471349)(assets/image-20210720113835328.png)]
- 功能接口中的方法
/**
* 通过id删除
* @param uid
*/
@Delete("delete from user where uid = #{uid}")
public Integer deleteByPrimaryKey(@Param("uid") Integer uid);
- 测试类
package com.czxy.ssm.test;
import com.czxy.ssm.domain.User;
import com.czxy.ssm.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
/**
* @author 桐叔
* @email liangtong@itcast.cn
*/
public class Test05_Delete {
public static void main(String[] args) throws IOException {
//1 加载配置文件
// 1.1 获得资源流
InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
// 1.2 获得工厂
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
//2 获得会话(连接)
SqlSession session = factory.openSession();
//3获得功能接口
UserMapper userMapper = session.getMapper(UserMapper.class);
//4 调用功能
Integer result = userMapper.deleteByPrimaryKey(1);
System.out.println(result);
//5 提交资源
session.commit();
//6 释放资源
session.close();
}
}
3.日志与工具类
3.1 日志
3.1.1 什么是日志
- 记录程序运行的过程细节。例如:我们在控制台可以看到SQL语句
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bPAWJpix-1632616471350)(assets/image-20210720115723948.png)]
3.1.2 整合日志
-
1)添加jar包 (已添加)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-M6uoDHrJ-1632616471353)(assets/image-20210720115823376.png)]
-
2)添加配置文件
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-E3tfIiwg-1632616471354)(assets/image-20210720153406467.png)]
# 2. 输出格式
## log4j.appender.stdout=输出位置(固定值,由log4j提供)
## log4j.appender.stdout.Target=方式
## log4j.appender.stdout.layout=布局(固定值)
## log4j.appender.stdout.layout.ConversionPattern=格式
# 2.1 将日志输出到控制台
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c:%L - %m%n
# 2.2 将日志输出到文件
log4j.appender.file=org.apache.log4j.FileAppender
log4j.appender.file.File=d:/file.log
log4j.appender.file.Append=false
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{ABSOLUTE} %5p %c:%L - %m%n
#1.log4j.rootLogger=日志级别, 输出方式1, 输出方式2, ...
## 日志级别:debug、info、warn、error
log4j.rootLogger=debug, stdout, file
# 3 自定义日志级别
## log4j.logger.包=日志级别
#log4j.logger.com.ibatis = debug
#log4j.logger.com.ibatis.common.jdbc.SimpleDataSource = debug
#log4j.logger.com.ibatis.common.jdbc.ScriptRunner = debug
#log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientDelegate = debug
#log4j.logger.java.sql.Connection = debug
#log4j.logger.java.sql.Statement = debug
#log4j.logger.java.sql.PreparedStatement = debug
#log4j.logger.java.sql.ResultSet =debug
log4j.logger.org.apache.ibatis.transaction = info
log4j.logger.org.apache.ibatis.io = info
log4j.logger.org.apache.ibatis.datasource = info
log4j.logger.org.apache.ibatis.logging = info
3.2 工具类
3.2.1 拷贝工具类
-
完成增删改查的时候,我们发现有一些代码大量重复,我们将提供工具类,对重复代码进行简化。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SfJcCfP8-1632616471354)(assets/image-20210720174410909.png)]
package com.czxy.ssm.utils;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
/**
* @author 桐叔
* @email liangtong@itcast.cn
*/
public class MyBatisUtils {
// 会话工厂
private static SqlSessionFactory factory;
static{
try {
// 1.1 加载核心配置文件
InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
// 1.2 获得工厂
factory = new SqlSessionFactoryBuilder().build(is);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
private static ThreadLocal<SqlSession> local = new ThreadLocal<>();
/**
* 获得新会话
* @return
*/
private static SqlSession openSession(){
SqlSession sqlSession = local.get();
if(sqlSession == null){
sqlSession = factory.openSession();
local.set(sqlSession);
}
return sqlSession;
}
/**
* 获得mapper
* @param clazz
* @return
*/
public static <T> T getMapper(Class<T> clazz){
return openSession().getMapper(clazz);
}
/**
* 释放资源
*/
public static void close() {
SqlSession sqlSession = openSession();
if(sqlSession != null){
sqlSession.close();
}
}
/**
* 提交并释放资源
*/
public static void commitAndClose() {
SqlSession sqlSession = openSession();
if(sqlSession != null){
sqlSession.commit();
close();
}
}
/**
* 回滚并释放资源
*/
public static void rollbackAndClose() {
SqlSession sqlSession = openSession();
if(sqlSession != null){
sqlSession.rollback();
close();
}
}
}
3.2.2 工具类原理分析
- 工具类实现原理分析
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5AIaMNbX-1632616471355)(assets/image-20210720174052080.png)]
3.2.3 测试类
package com.czxy.ssm.test;
import com.czxy.ssm.domain.User;
import com.czxy.ssm.mapper.UserMapper;
import com.czxy.ssm.utils.MyBatisUtils;
import java.util.Date;
/**
* @author 桐叔
* @email liangtong@itcast.cn
*/
public class Test06_Utils {
public static void main(String[] args) {
UserMapper userMapper = MyBatisUtils.getMapper(UserMapper.class);
User user = new User();
user.setUid("1");
user.setUsername("jack");
user.setPassword("1234");
user.setName("杰克");
user.setEmail("itcast_lt@163.com");
user.setBirthday(new Date());
user.setSex("男");
user.setSex("0");
Integer result = userMapper.insert(user);
System.out.println(result);
MyBatisUtils.commitAndClose();
}
}
今天就说了这么多,总结就是不是很难,而且过几天会被替代,但是属于偏底层代码需要多练,加油,离工作有进一步!!!!!!!!!