Mybatis之通用Mappper
-
通用 Mapper 介绍
通用 Mapper 是一个辅助 MyBatis 单表开发的组件,它使我们能极其方便的使用 MyBatis 单表的增删改查。 -
快速入门
2.1 搭建环境
创建数据库 mapper,和 table_user 表
DROP TABLE IF EXISTS table_user
;
CREATE TABLE table_user
(
user_id
int(11) NOT NULL AUTO_INCREMENT,
user_name
varchar(20) DEFAULT NULL,
user_gender
char(2) DEFAULT ‘未知’,
user_address
varchar(30) DEFAULT NULL,
user_birthday
date DEFAULT NULL,
PRIMARY KEY (user_id
)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
– Records of table_user
INSERT INTO table_user
VALUES (‘1’, ‘张三’, ‘男’, ‘北京’, ‘2020-01-15’);
INSERT INTO table_user
VALUES (‘2’, ‘李四’, ‘男’, ‘上海’, ‘2020-01-14’);
INSERT INTO table_user
VALUES (‘3’, ‘王五’, ‘女’, ‘广州’, ‘2020-01-12’);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
创建工程
打开 IDEA --> Create New Project --> Empty Project --> 填写项目名 --> Finish
New Module --> Maven --> Next --> 填写项目信息 --> Finish
引入依赖
4.0.0
<groupId>com.zt</groupId>
<artifactId>mapper_demo1</artifactId>
<version>1.0-SNAPSHOT</version>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.2.1.RELEASE</version>
</parent>
<dependencies>
<!--jdbc的启动器,默认使用 HikariCP 连接池-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!--mysql驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!--mybatis-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<!--SpringBoot-test-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 配置 application.properties
连接四大参数
spring.datasource.url=jdbc:mysql://localhost:3306/mapper?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8
spring.datasource.username=root
spring.datasource.password=123456
1
2
3
4
5
创建引导类
@SpringBootApplication
public class MapperApplication {
public static void main(String[] args) {
SpringApplication.run(MapperApplication.class,args);
}
}
1
2
3
4
5
6
7
创建测试类,测试能否获取数据库连接
@RunWith(SpringRunner.class)
@SpringBootTest(classes = MapperApplication.class)
public class ConnectionTest {
@Autowired
private DataSource dataSource;
@Test
public void testGetConnection() throws SQLException {
Connection connection = dataSource.getConnection();
System.out.println(connection);
}
}
1
2
3
4
5
6
7
8
9
10
11
12
运行结果:
HikariProxyConnection@737756161 wrapping com.mysql.cj.jdbc.ConnectionImpl@2532b351
1
2.2 集成通用 Mapper
引入依赖
/**
-
建立数据库与实体类之间的关系
*/
@Table(name = “table_user”)
public class User implements Serializable {
private Integer userId;
private String userName;
private String userGender;
private String userAddress;
private Date userBirthday;public User() {
}public User(Integer userId, String userName, String userGender, String userAddress, Date userBirthday) {
this.userId = userId;
this.userName = userName;
this.userGender = userGender;
this.userAddress = userAddress;
this.userBirthday = userBirthday;
}public Integer getUserId() {
return userId;
}public void setUserId(Integer userId) {
this.userId = userId;
}public String getUserName() {
return userName;
}public void setUserName(String userName) {
this.userName = userName;
}public String getUserGender() {
return userGender;
}public void setUserGender(String userGender) {
this.userGender = userGender;
}public String getUserAddress() {
return userAddress;
}public void setUserAddress(String userAddress) {
this.userAddress = userAddress;
}public Date getUserBirthday() {
return userBirthday;
}public void setUserBirthday(Date userBirthday) {
this.userBirthday = userBirthday;
}@Override
public String toString() {
return “User{” +
“userId=” + userId +
“, userName=’” + userName + ‘’’ +
“, userGender=’” + userGender + ‘’’ +
“, userAddress=’” + userAddress + ‘’’ +
“, userBirthday=” + userBirthday +
‘}’;
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
创建持久层 UserMapper
/**
- 具体操作数据库的 Mapper 接口,需要继承通用 mapper 提供的核心接口:Mapper
- 泛型类型就是实体类的类型
*/
@org.apache.ibatis.annotations.Mapper
public interface UserMapper extends Mapper {
}
1
2
3
4
5
6
7
8
9
创建 UserMapper 测试类
@RunWith(SpringRunner.class)
@SpringBootTest(classes = MapperApplication.class)
public class MapperTest {
@Autowired
private UserMapper userMapper;
@Test
public void testSelectOne() {
User user = new User(null, "张三", null, null, null);
System.out.println(userMapper.selectOne(user));
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
运行结果:
User{userId=1, userName=‘张三’, userGender=‘男’, userAddress=‘北京’, userBirthday=Wed Jan 15 00:00:00 CST 2020}
1
3. 常用注解
3.1 @Table
作用:建立实体类和数据库表之间的对应关系。
默认规则:实体类类名首字母小写作为表名。User 类 --> user 表。
实例:
@Table(name = “table_user”)
public class User implements Serializable {
1
2
3.2 @Column
作用:建立实体类字段和数据库表字段之间的对应关系。
默认规则:
实体类字段:驼峰式命名
数据库表字段:使用 “_” 区分各个单词
实例:
@Column(name = “user_id”)
private Integer id;
1
2
3.3 @Id
作用:建立实体类主键字段和数据库表主键字段之间的对应关系。
实例:
@Id
private Integer userId;
1
2
3.4 @GeneratedValue
作用:让通用 Mapper 在执行 insert 操作之后将数据库自动生成的主键值回写到实体类对象中。
实例:
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer userId;
1
2
3
@Test
public void testInsert() {
User user = new User(null, “小红”, “女”, “杭州”, new java.util.Date());
userMapper.insert(user);
System.out.println(user);
}
1
2
3
4
5
6
7
运行结果:
User{userId=10, userName=‘小红’, userGender=‘女’, userAddress=‘杭州’, userBirthday=Sat Jan 18 14:52:38 CST 2020}
1
3.5 @Transient
作用:用于标记不与数据库表字段对应的实体类字段。
实例:
@Transient
private String otherThings; //非数据库表中字段
1
2
4. 基础方法
4.1 selectOne 方法
说明:根据实体中的属性进行查询,只能有一个返回值,有多个结果是抛出异常,查询条件使用等号
实例:
@Test
public void testSelectOne() {
User user = new User(null, “张三”, null, null, null);
System.out.println(userMapper.selectOne(user));
}
1
2
3
4
5
运行结果:
User{id=1, userName=‘张三’, userGender=‘男’, userAddress=‘北京’, userBirthday=Wed Jan 15 00:00:00 CST 2020}
1
4.2 select 方法
说明:根据实体中的属性值进行查询,查询条件使用等号
实例:
@Test
public void testSelect() {
User user = new User(null, null, “男”, null, null);
List users = userMapper.select(user);
for (User user1 : users) {
System.out.println(user1);
}
}
1
2
3
4
5
6
7
8
运行结果:
User{userId=1, userName=‘张三’, userGender=‘男’, userAddress=‘北京’, userBirthday=Wed Jan 15 00:00:00 CST 2020}
User{userId=2, userName=‘李四’, userGender=‘男’, userAddress=‘上海’, userBirthday=Tue Jan 14 00:00:00 CST 2020}
User{userId=7, userName=‘小明’, userGender=‘男’, userAddress=‘深圳’, userBirthday=Sat Jan 18 00:00:00 CST 2020}
1
2
3
4
4.3 selectAll 方法
说明:查询全部结果,select(null)方法能达到同样的效果
实例:
@Test
public void testSelectAll() {
List users = userMapper.selectAll();
for (User user : users) {
System.out.println(user);
}
}
1
2
3
4
5
6
7
运行结果:
User{userId=1, userName=‘张三’, userGender=‘男’, userAddress=‘北京’, userBirthday=Wed Jan 15 00:00:00 CST 2020}
User{userId=2, userName=‘李四’, userGender=‘男’, userAddress=‘上海’, userBirthday=Tue Jan 14 00:00:00 CST 2020}
User{userId=3, userName=‘王五’, userGender=‘女’, userAddress=‘广州’, userBirthday=Sun Jan 12 00:00:00 CST 2020}
User{userId=7, userName=‘小明’, userGender=‘男’, userAddress=‘深圳’, userBirthday=Sat Jan 18 00:00:00 CST 2020}
1
2
3
4
5
4.4 selectByPrimaryKey 方法
说明:根据主键字段进行查询,方法参数必须包含完整的主键属性,查询条件使用等号
注意:该方法需要使用 @Id 指定实体类中的主键,否则通用 Mapper 会默认将所有实体类字段作为联合主键
实例:
@Test
public void testSelectByPrimaryKey() {
System.out.println(userMapper.selectByPrimaryKey(3));
}
1
2
3
4
运行结果:
User{userId=3, userName=‘王五’, userGender=‘女’, userAddress=‘广州’, userBirthday=Sun Jan 12 00:00:00 CST 2020}
1
4.5 selectCount 方法
说明:根据实体中的属性查询总数,查询条件使用等号
实例:
@Test
public void testSelectCount() {
User user = new User(null, null, “男”, null, null);
System.out.println(userMapper.selectCount(user));
}
1
2
3
4
5
运行结果:
3
1
4.6 insert 方法
说明:保存一个实体,null 的属性也会保存,不会使用数据库默认值
实例:
@Test
public void testInsert() {
User user = new User(null, “小红”, “女”, “杭州”, new java.util.Date());
userMapper.insert(user);
System.out.println(userMapper.selectOne(new User(null,“小红”,null,null,null)));
}
1
2
3
4
5
6
运行结果:
User{userId=14, userName=‘小红’, userGender=‘女’, userAddress=‘杭州’, userBirthday=Sat Jan 18 00:00:00 CST 2020}
1
4.7 insertSelective 方法
说明:保存一个实体,null 的属性不会保存,会使用数据库默认值
实例:
@Test
public void testInsertSelective() {
User user = new User(null, “小美”, null, “南京”, new java.util.Date());
userMapper.insertSelective(user);
System.out.println(userMapper.selectOne(new User(null,“小美”,null,null,null)));
}
1
2
3
4
5
6
运行结果:
User{userId=15, userName=‘小美’, userGender=‘未知’, userAddress=‘南京’, userBirthday=Sat Jan 18 00:00:00 CST 2020}
1
4.8 updateByPrimaryKey 方法
说明:根据主键更新实体全部字段,null 值会被更新
实例:
@Test
public void testUpdateByPrimaryKey() {
User user = new User(3, “建国”, null, null, null);
userMapper.updateByPrimaryKey(user);
System.out.println(userMapper.selectOne(new User(3, null, null, null, null)));
}
1
2
3
4
5
6
运行结果:
User{userId=3, userName=‘建国’, userGender=‘null’, userAddress=‘null’, userBirthday=null}
1
2
4.9 updateByPrimaryKeySelective 方法
说明:根据主键更新属性不为 null 的值
实例:
@Test
public void testUpdateByPrimaryKeySelective() {
User user = new User(2, “建军”, null, null, null);
userMapper.updateByPrimaryKeySelective(user);
System.out.println(userMapper.selectOne(new User(2, null, null, null, null)));
}
1
2
3
4
5
6
运行结果:
User{userId=2, userName=‘建军’, userGender=‘男’, userAddress=‘上海’, userBirthday=Tue Jan 14 00:00:00 CST 2020}
1
4.10 delete 方法
说明:根据实体属性作为条件进行删除,查询条件使用等号
注意:如果所有实体属性都为空,会删除表中所有记录
实例:
@Test
public void testDelete() {
User user = new User(null, “小美”, null, null, null);
userMapper.delete(user);
}
1
2
3
4
5
4.11 deleteByPrimaryKey 方法
说明:根据主键字段进行删除,方法参数必须包含完整的主键属性
实例:
@Test
public void testDeleteByPrimaryKey() {
userMapper.deleteByPrimaryKey(14);
}
1
2
3
4
5. Example 方法
Mybatis 的逆向工程中会生成实例及实例对应的 example,example 用于添加条件,相当 where 后面的部分
5.1 selectByExample 方法
说明:根据 Example 条件进行查询
实例:
/**
-
相当于:select * from table_user where (user_address = “北京”) or (user_name like “%王%”);
*/
@Test
public void testSelectByExample() {
// 1. 创建 Example 对象
Example example = new Example(User.class);
// 2. 通过 Example 对象创建 Criteria 对象
Example.Criteria criteria1 = example.createCriteria();
Example.Criteria criteria2 = example.createCriteria();// 3. 在两个 Criteria 对象中分别设置查询条件
criteria1.andEqualTo(“userAddress”,“北京”);
criteria2.andLike(“userName”,"%王%");// 4. 使用 or 关键词组装两个 Criteria 对象
example.or(criteria2);// 5. 执行查询
List users = userMapper.selectByExample(example);
for (User user : users) {
System.out.println(user);
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
运行结果:
User{userId=1, userName=‘张三’, userGender=‘男’, userAddress=‘北京’, userBirthday=Wed Jan 15 00:00:00 CST 2020}
User{userId=3, userName=‘王五’, userGender=‘女’, userAddress=‘广州’, userBirthday=Sun Jan 12 00:00:00 CST 2020}
1
2
5.2 selectCountByExample 方法
说明:根据 Example 条件进行查询总数
实例:
/**
-
相当于:select COUNT(*) from table_user where user_gender = “男”;
*/
@Test
public void testSelectCountByExample() {
Example example = new Example(User.class);
Example.Criteria criteria = example.createCriteria();criteria.andEqualTo(“userGender”, “男”);
System.out.println(userMapper.selectCountByExample(example));
}
1
2
3
4
5
6
7
8
9
10
11
12
运行结果:
3
1
5.3 updateByExample 方法
说明:根据 Example 条件更新实体包含的全部属性,null 值会被更新
实例:
/**
-
相当于:update table_user set user_name = “小红”,user_gender = null,user_address = null,user_birthday = null where user_id = 7;
*/
@Test
public void testUpdateByExample() {
User user = new User(null, “小红”, null, null, null);
Example example = new Example(User.class);
Example.Criteria criteria = example.createCriteria();
criteria.andEqualTo(“userId”, 7);userMapper.updateByExample(user, example);
System.out.println(userMapper.selectOne(user));
}
1
2
3
4
5
6
7
8
9
10
11
12
13
运行结果:
User{userId=7, userName=‘小红’, userGender=‘null’, userAddress=‘null’, userBirthday=null}
1
5.4 updateByExampleSelective 方法
说明:根据 Example 条件更新实体包含的不是 null 的属性值
实例:
/**
-
相当于:update table_user set user_name = “小花” where user_id = 3;
*/
@Test
public void testUpdateByExampleSelective() {
User user = new User(null, “小花”, null, null, null);
Example example = new Example(User.class);
Example.Criteria criteria = example.createCriteria();
criteria.andEqualTo(“userId”, 3);userMapper.updateByExampleSelective(user,example);
System.out.println(userMapper.selectOne(user));
}
1
2
3
4
5
6
7
8
9
10
11
12
13
运行结果:
User{userId=3, userName=‘小花’, userGender=‘女’, userAddress=‘广州’, userBirthday=Sun Jan 12 00:00:00 CST 2020}
1
5.5 deleteByExample 方法
说明:根据 Example 条件删除数据
实例:
/**
-
相当于:delete from table_user where user_name = “小红”;
*/
@Test
public void testDeleteByExample() {
Example example = new Example(User.class);
Example.Criteria criteria = example.createCriteria();
criteria.andEqualTo(“userName”,“小红”);userMapper.deleteByExample(example);
}