1. 通用 Mapper 介绍
通用 Mapper 是一个辅助 MyBatis 单表开发的组件,它使我们能极其方便的使用 MyBatis 单表的增删改查。
2. 快速入门
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');
-
创建工程
- 打开 IDEA --> Create New Project --> Empty Project --> 填写项目名 --> Finish
- New Module --> Maven --> Next --> 填写项目信息 --> Finish
-
引入依赖
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <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> </project>
-
配置 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
-
创建引导类
@SpringBootApplication public class MapperApplication { public static void main(String[] args) { SpringApplication.run(MapperApplication.class,args); } }
-
创建测试类,测试能否获取数据库连接
@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); } }
运行结果:
HikariProxyConnection@737756161 wrapping com.mysql.cj.jdbc.ConnectionImpl@2532b351
2.2 集成通用 Mapper
-
引入依赖
<!--通用 Mapper--> <dependency> <groupId>tk.mybatis</groupId> <artifactId>mapper-spring-boot-starter</artifactId> <version>2.1.5</version> </dependency>
-
创建实体类 User
/** * 建立数据库与实体类之间的关系 */ @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 + '}'; } }
-
创建持久层 UserMapper
/** * 具体操作数据库的 Mapper 接口,需要继承通用 mapper 提供的核心接口:Mapper<User> * 泛型类型就是实体类的类型 */ @org.apache.ibatis.annotations.Mapper public interface UserMapper extends Mapper<User> { }
-
创建 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)); } }
运行结果:
User{userId=1, userName='张三', userGender='男', userAddress='北京', userBirthday=Wed Jan 15 00:00:00 CST 2020}
3. 常用注解
3.1 @Table
-
作用:建立实体类和数据库表之间的对应关系。
-
默认规则:实体类类名首字母小写作为表名。User 类 --> user 表。
-
实例:
@Table(name = "table_user") public class User implements Serializable {
3.2 @Column
-
作用:建立实体类字段和数据库表字段之间的对应关系。
-
默认规则:
实体类字段:驼峰式命名
数据库表字段:使用 “_” 区分各个单词
-
实例:
@Column(name = "user_id") private Integer id;
3.3 @Id
-
作用:建立实体类主键字段和数据库表主键字段之间的对应关系。
-
实例:
@Id private Integer userId;
3.4 @GeneratedValue
-
作用:让通用 Mapper 在执行 insert 操作之后将数据库自动生成的主键值回写到实体类对象中。
-
实例:
@Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Integer userId;
@Test public void testInsert() { User user = new User(null, "小红", "女", "杭州", new java.util.Date()); userMapper.insert(user); System.out.println(user); }
运行结果:
User{userId=10, userName='小红', userGender='女', userAddress='杭州', userBirthday=Sat Jan 18 14:52:38 CST 2020}
3.5 @Transient
-
作用:用于标记不与数据库表字段对应的实体类字段。
-
实例:
@Transient private String otherThings; //非数据库表中字段
4. 基础方法
4.1 selectOne 方法
-
说明:根据实体中的属性进行查询,只能有一个返回值,有多个结果是抛出异常,查询条件使用等号
-
实例:
@Test public void testSelectOne() { User user = new User(null, "张三", null, null, null); System.out.println(userMapper.selectOne(user)); }
运行结果:
User{id=1, userName='张三', userGender='男', userAddress='北京', userBirthday=Wed Jan 15 00:00:00 CST 2020}
4.2 select 方法
-
说明:根据实体中的属性值进行查询,查询条件使用等号
-
实例:
@Test public void testSelect() { User user = new User(null, null, "男", null, null); List<User> users = userMapper.select(user); for (User user1 : users) { System.out.println(user1); } }
运行结果:
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}
4.3 selectAll 方法
-
说明:查询全部结果,select(null)方法能达到同样的效果
-
实例:
@Test public void testSelectAll() { List<User> users = userMapper.selectAll(); for (User user : users) { System.out.println(user); } }
运行结果:
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}
4.4 selectByPrimaryKey 方法
-
说明:根据主键字段进行查询,方法参数必须包含完整的主键属性,查询条件使用等号
-
注意:该方法需要使用 @Id 指定实体类中的主键,否则通用 Mapper 会默认将所有实体类字段作为联合主键
-
实例:
@Test public void testSelectByPrimaryKey() { System.out.println(userMapper.selectByPrimaryKey(3)); }
运行结果:
User{userId=3, userName='王五', userGender='女', userAddress='广州', userBirthday=Sun Jan 12 00:00:00 CST 2020}
4.5 selectCount 方法
-
说明:根据实体中的属性查询总数,查询条件使用等号
-
实例:
@Test public void testSelectCount() { User user = new User(null, null, "男", null, null); System.out.println(userMapper.selectCount(user)); }
运行结果:
3
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))); }
运行结果:
User{userId=14, userName='小红', userGender='女', userAddress='杭州', userBirthday=Sat Jan 18 00:00:00 CST 2020}
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))); }
运行结果:
User{userId=15, userName='小美', userGender='未知', userAddress='南京', userBirthday=Sat Jan 18 00:00:00 CST 2020}
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))); }
运行结果:
User{userId=3, userName='建国', userGender='null', userAddress='null', userBirthday=null}
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))); }
运行结果:
User{userId=2, userName='建军', userGender='男', userAddress='上海', userBirthday=Tue Jan 14 00:00:00 CST 2020}
4.10 delete 方法
-
说明:根据实体属性作为条件进行删除,查询条件使用等号
-
注意:如果所有实体属性都为空,会删除表中所有记录
-
实例:
@Test public void testDelete() { User user = new User(null, "小美", null, null, null); userMapper.delete(user); }
4.11 deleteByPrimaryKey 方法
-
说明:根据主键字段进行删除,方法参数必须包含完整的主键属性
-
实例:
@Test public void testDeleteByPrimaryKey() { userMapper.deleteByPrimaryKey(14); }
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<User> users = userMapper.selectByExample(example); for (User user : users) { System.out.println(user); } }
运行结果:
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}
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)); }
运行结果:
3
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)); }
运行结果:
User{userId=7, userName='小红', userGender='null', userAddress='null', userBirthday=null}
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)); }
运行结果:
User{userId=3, userName='小花', userGender='女', userAddress='广州', userBirthday=Sun Jan 12 00:00:00 CST 2020}
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); }