通用Mapper的接口用法和CRUD用例

我们在使用Mybatis时,经常会写大量的SQL语句在xml文件中,怎么去解决这种困境呢?然后通用Mapper便应运而生了.

添加maven依赖
在对应的pom.xml文件中添加

<dependency>
    <groupId>javax.persistence</groupId>
    <artifactId>persistence-api</artifactId>
    <version>1.0</version>
</dependency>
<dependency>
    <groupId>tk.mybatis</groupId>
    <artifactId>mapper</artifactId>
    <version>3.1.2</version>
</dependency>

一.通用Mapper的api

api	详解
int countByExample(UserExample example) thorws SQLException	按条件计数
int deleteByPrimaryKey(Integer id) thorws SQLException	按主键删除
int deleteByExample(UserExample example) thorws SQLException	按条件查询
String/Integer insert(User record) thorws SQLException	插入数据（返回值为ID）
User selectByPrimaryKey(Integer id) thorws SQLException	按主键查询
List selectByExample(UserExample example) thorws SQLException	按条件查询
List selectByExampleWithBLOGs(UserExample example) thorws SQLException	按条件查询（包括BLOB字段）
int updateByPrimaryKey(User record) thorws SQLException	按主键更新
int updateByPrimaryKeySelective(User record) thorws SQLException	按主键更新值不为null的字段
int updateByExample(User record, UserExample example) thorws SQLException	按条件更新
int updateByExampleSelective(User record, UserExample example) thorws SQLException	按条件更新值不为null的字段

二、Example接口

Example example = new Example(JavaBean.class);
Example.Criteria criteria = example.createCriteria();

api	详解
example.setOrderByClause(“字段名 ASC”);	添加升序排列条件，DESC为降序
example.setDistinct(false)	去除重复，boolean型，true为选择不重复的记录。
criteria.andXxxIsNull	添加字段xxx为null的条件
criteria.andXxxIsNotNull	添加字段xxx不为null的条件
criteria.andXxxEqualTo(value)	添加xxx字段等于value条件
criteria.andXxxNotEqualTo(value)	添加xxx字段不等于value条件
criteria.andXxxGreaterThan(value)	添加xxx字段大于value条件
criteria.andXxxGreaterThanOrEqualTo(value)	添加xxx字段大于等于value条件
criteria.andXxxLessThan(value)	添加xxx字段小于value条件
criteria.andXxxLessThanOrEqualTo(value)	添加xxx字段小于等于value条件
criteria.andXxxIn(List<？>)	添加xxx字段值在List<？>条件
criteria.andXxxNotIn(List<？>)	添加xxx字段值不在List<？>条件
criteria.andXxxLike(“%”+value+”%”)	添加xxx字段值为value的模糊查询条件
criteria.andXxxNotLike(“%”+value+”%”)	添加xxx字段值不为value的模糊查询条件
criteria.andXxxBetween(value1,value2)	添加xxx字段值在value1和value2之间条件
criteria.andXxxNotBetween(value1,value2)	添加xxx字段值不在value1和value2之间条件

三、使用案例
1.查询
selectByPrimaryKey() 按主键查询

//相当于：select * from user where id = 100;
User user = UserMapper.selectByPrimaryKey(100); 

selectByExample() 和 selectByExampleWithBLOGs()

//相当于：select * from user where username = 'wyw' and  username is null order by username asc,email desc
UserExample example = new UserExample();
Criteria criteria = example.createCriteria();
criteria.andUsernameEqualTo("wyw");
criteria.andUsernameIsNull();
example.setOrderByClause("username asc,email desc");
List<?>list = XxxMapper.selectByExample(example);

2.插入数据
insert()

//相当于：insert into user(ID,username,password,email) values ('dsfgsdfgdsfgds','jack','1234','hello@126.com');
User user = new User();
user.setId("dsfgsdfgdsfgds");
user.setUsername("jack");
user.setPassword("1234")
user.setEmail("hello@163.com");
XxxMapper.insert(user);

3.更新数据
updateByPrimaryKey()

//相当于：update user set username='rose', password='5678', email='hello@163.com' where id='a01'
User user =new User();
user.setId("a01");
user.setUsername("rose");
user.setPassword("5678");
user.setEmail("hello@163.com");
XxxMapper.updateByPrimaryKey(user);

updateByPrimaryKeySelective()

//相当于：update user set password='7890' where id='a01'
User user = new User();
user.setId("a01");
user.setPassword("7890");
XxxMapper.updateByPrimaryKey(user);

updateByExample() 和 updateByExampleSelective()

//相当于：update user set password='6666' where username='jack'
UserExample example = new UserExample();
Criteria criteria = example.createCriteria();
criteria.andUsernameEqualTo("jack");
User user = new User();
user.setPassword("6666");
XxxMapper.updateByPrimaryKeySelective(user,example);
//updateByExample()更新所有的字段，包括字段为null的也更新
建议使用 updateByExampleSelective()更新想更新的字段

4.删除数据
deleteByPrimaryKey()

//相当于：delete from user where id=1
XxxMapper.deleteByPrimaryKey(1);  

deleteByExample()

//相当于：delete from user where username='jack'
UserExample example = new UserExample();
Criteria criteria = example.createCriteria();
criteria.andUsernameEqualTo("jack");
XxxMapper.deleteByExample(example);

5.查询数据数量
countByExample()

//相当于：select count(*) from user where username='jack'
UserExample example = new UserExample();
Criteria criteria = example.createCriteria();
criteria.andUsernameEqualTo("jack");
int count = XxxMapper.countByExample(example);