第三章 CRUD操作
1. 实体类
1.1 定义实体类
public class Person {
private Integer id;
private String lastName;
private Integer age;
private Integer gender;
private String email;
//get,set方法省略
}
1.2 实体类上使用的注解
1.2.1 @TableName
描述:表名注解
属性 | 类型 | 必须指定 | 默认值 | 描述 |
---|---|---|---|---|
value | String | 否 | “” | 表名 |
resultMap | String | 否 | “” | xml中resultMap的id |
schema | String | 否 | “” | schema(@since 3.1.1) |
keepGlobalPrefix | boolean | 否 | false | 是否保持使用全局的tablePrefix的值(如果设置了全局 tablePrefix且自行设置了value的值)(@since 3.1.1) |
1.2.2 @TableId
描述:主键注解
属性 | 类型 | 必须指定 | 默认值 | 描述 |
---|---|---|---|---|
value | String | 否 | “” | 主键字段名 |
type | Enum | 否 | IdType.NONE | 主键类型 |
IdType
值 | 描述 |
---|---|
AUTO | 数据库自增 |
INPUT | 自行输入 |
ID_WORKER | 分布式全局唯一ID 长整型类型 |
UUID | 32位UUID字符串 |
NONE | 无状态 |
ID_WORKER_STR | 分布式全局唯一ID 字符串类型 |
注:实体类中主键对应的属性名称不为id时,此注解必须使用
1.2.3 @TableField
描述:字段注解(非主键)
属性 | 类型 | 必须指定 | 默认值 | 描述 |
---|---|---|---|---|
value | String | 否 | “” | 字段名 |
el | String | 否 | “” | 映射为原生#{…}逻辑,相当于写在 xml里的#{…}部分 |
exist | boolean | 否 | true | 是否为数据库表字段 |
condition | String | 否 | “” | 字段where实体查询比较条件,有值设置则按设置的值为准,没有则为默认全局的%s=#{%s},参考 |
update | String | 否 | “” | 字段update set部分注入(该属性优先级高于el属性) |
strategy | Enum | 否 | FieldStrategy.DEFAULT | 字段验证策略 |
fill | Enum | 否 | FieldFill.DEFAULT | 字段自动填充策略 |
select | boolean | 否 | true | 是否进行select查询 |
keepGlobalFormat | boolean | 否 | false | 是否保持使用全局的format进行处理(@since 3.1.1) |
2. BaseMapper接口
2.1 说明
- 通用CRUD封装BaseMapper接口,为Mybatis-Plus启动时自动解析实体表关系映射转换为Mybatis内部对象注入容器
- 泛型T为任意实体对象
- 参数Serializable为任意类型主键Mybatis-Plus。不推荐使用复合主键,约定每一张表都有自己的唯一id主键
- 对象Wrapper为条件构造器
public interface PersonMapper extends BaseMapper<Person> {
}
2.2 插入操作
2.2.1 insert
int insert(T entity)
private PersonMapper personMapper = ac.getBean("personMapper", PersonMapper.class);
@Test
public void testInsert(){
Person person = new Person();
person.setLastName("张三");
person.setAge(21);
person.setGender(1);
person.setEmail("zhangsan@qq.com");
personMapper.insert(person);
}
问题1:
没有指定主键类型
解析1:
1.在主键属性上添加@TableId注解
@TableId(type = IdType.AUTO)
private Integer id;
2.在applciationContext.xml进行全局配置
<bean id="sqlSessionFactory" class="com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean">
<property name="dataSource" ref="dataSource"/>
<property name="typeAliasesPackage" value="com.mp.entity"/>
<property name="globalConfig" ref="globalConfig"/>
</bean>
<!--定义 MP 全局策略-->
<bean id="globalConfig" class="com.baomidou.mybatisplus.core.config.GlobalConfig">
<property name="dbConfig" ref="dbConfig"/>
</bean>
<bean id="dbConfig" class="com.baomidou.mybatisplus.core.config.GlobalConfig.DbConfig">
<property name="idType" value="AUTO"/>
</bean>
问题2:
实体类名称与表名不符
解析2:
1.在实体类上添加@TableName注解
@TableName("tmp_person")
public class Person {
//...
}
2.在applciationContext.xml进行全局配置
<bean id="dbConfig" class="com.baomidou.mybatisplus.core.config.GlobalConfig.DbConfig">
<!-- 设置全局主键类型 -->
<property name="idType" value="AUTO"/>
<!-- 设置全局表名前缀 -->
<property name="tablePrefix" value="tmp_"/>
</bean>
问题3:
在实体类中姓名属性采用驼峰式写法String lastName,但是插入时字段名称转化为下划线间隔last_name。
解析3:
在applciationContext.xml的全局配置中带有
<bean id="sqlSessionFactory" class="com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean">
<property name="dataSource" ref="dataSource"/>
<property name="typeAliasesPackage" value="com.mp.entity"/>
<property name="globalConfig" ref="globalConfig"/>
<property name="configuration" ref="configuration"/>
</bean>
<bean id="configuration" class="com.baomidou.mybatisplus.core.MybatisConfiguration">
<!-- 驼峰式与下划线的转化 -->
<property name="mapUnderscoreToCamelCase" value="true"/>
</bean>
mapUnderscoreToCamelCase配置默认值为true,无需转换时可设置为false关闭
问题4:
实体类中的属性名称与表中字段名称不一致
解析4:
在实体类的属性上添加@TableId(主键)@TableField(非主键)注解,实现属性名称与字段名称的映射
@TableId(value = "id")
private Integer personId;
@TableField(value = "gender")
private Integer sex;
问题5:
能否实现插入后获取主键值?
解析5:
MP中已经默认配置了此项功能,无需任何自定义配置,在插入方法后,可获取实体类中的主键属性
2.3 更新操作
2.3.1 updateById
根据ID更新:
int updateById(@Param(Constants.ENTITY) T entity);
@Test
public void testUpdateById(){
Person person = new Person();
person.setId(6);
person.setLastName("李四");
person.setAge(25);
person.setGender(1);
person.setEmail("lisi@qq.com");
personMapper.updateById(person);
}
2.3.2 update
根据updateWrapper条件更新:
int update(@Param(Constants.ENTITY) T entity, @Param(Constants.WRAPPER) Wrapper<T> updateWrapper);
2.4 删除操作
2.4.1 deleteById
根据ID删除:
int deleteById(Serializable id);
@Test
public void testDeleteById(){
personMapper.deleteById(7);
}
2.4.2 deleteByMap
根据columnMap条件删除:
int deleteByMap(@Param(Constants.COLUMN_MAP) Map<String, Object> columnMap);
@Test
public void testDeleteByMap(){
Map<String, Object> columnMap = new HashMap<>();
columnMap.put("last_name", "tom");
columnMap.put("age", 20);
personMapper.deleteByMap(columnMap);
}
注意:columnMap中的key为字段名称
2.4.3 deleteBatchIds
根据ID批量删除:
int deleteBatchIds(@Param(Constants.COLLECTION) Collection<? extends Serializable> idList);
@Test
public void testDeleteBatchIds(){
List<Integer> idList = new ArrayList<>();
idList.add(11);
idList.add(12);
idList.add(13);
personMapper.deleteBatchIds(idList);
}
2.4.4 delete
根据wrapper条件删除:
int delete(@Param(Constants.WRAPPER) Wrapper<T> wrapper);
2.5 查询操作
2.5.1 selectById
根据ID查询:
T selectById(Serializable id);
@Test
public void testSelectById(){
Person person = personMapper.selectById(1);
System.out.println(person);
}
2.5.2 selectBatchIds
根据ID批量查询:
List<T> selectBatchIds(@Param(Constants.COLLECTION) Collection<? extends Serializable> idList);
@Test
public void testSelectBatchIds(){
List<Integer> idList = new ArrayList<>();
idList.add(1);
idList.add(2);
idList.add(3);
List<Person> list = personMapper.selectBatchIds(idList);
System.out.println(list);
}
2.5.3 selectByMap
根据columnMap条件查询:
List<T> selectByMap(@Param(Constants.COLUMN_MAP) Map<String, Object> columnMap);
@Test
public void testSelectByMap(){
Map<String, Object> columnMap = new HashMap<>();
columnMap.put("last_name", "tom");
columnMap.put("age", 22);
List<Person> list = personMapper.selectByMap(columnMap);
System.out.println(list);
}
2.5.4 selectOne
根据queryWrapper条件查询一条记录:
T selectOne(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
2.5.5 selectCount
根据queryWrapper条件查询总记录数:
Integer selectCount(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
@Test
public void testSelectCount(){
int count = personMapper.selectCount(null);
System.out.println(count);
}
注:当方法参数为null时,查询表中的总记录数
2.5.6 selectList
根据queryWrapper条件查询全部记录:
List<T> selectList(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
@Test
public void testSelectList(){
List<Person> list = personMapper.selectList(null);
System.out.println(list);
}
注:当方法参数为null时,查询表中的所有记录
2.5.7 selectMaps
根据queryWrapper条件查询全部记录
List<Map<String, Object>> selectMaps(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
@Test
public void testSelectMaps(){
List<Map<String, Object>> list = personMapper.selectMaps(null);
System.out.println(list);
}
注:与selectList()方法不同的是查询到的数据被封住在Map<String, Object>集合中,而并非实体类中
2.5.8 selectObjs
根据queryWrapper条件查询全部记录:
List<Object> selectObjs(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
@Test
public void testSelectObjs(){
List<Object> list = personMapper.selectObjs(null);
System.out.println(list);
}
注:只返回第一个字段的值
2.5.9 selectPage
根据queryWrapper条件查询分页记录:
IPage<T> selectPage(IPage<T> page, @Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
注:根据分页查询条件,查询返回结果为实体分页对象
2.5.10 selectMapsPage
根据queryWrapper条件查询分页记录:
IPage<Map<String, Object>> selectMapsPage(IPage<T> page, @Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
注:与selectPage()方法不同的是查询到的数据被封住在Map<String, Object>集合中,而并非实体类中
3. IService接口
3.1 说明
- 通用ServiceCRUD封装IService接口,进一步封装CRUD采用get查询单行、remove删除、list查询集合、page分页,前缀命名方式区分Mapper层避免混淆
- 泛型T为任意实体对象
- 建议如果存在自定义通用Service方法的可能,请创建自己的IBaseService继承 Mybatis-Plus提供的基类
- 对象Wrapper为条件构造器
//接口
public interface PersonService extends IService<Person> {
}
//实现类
@Service
public class PersonServiceImpl extends ServiceImpl<PersonMapper, Person> implements PersonService {
}
3.2 插入操作
3.2.1 save
插入一条记录:
boolean save(T entity);
private PersonService personService = ac.getBean("personService", PersonService.class);
@Test
public void testSave(){
Person person = new Person();
person.setLastName("刘备");
person.setAge(30);
person.setGender(1);
person.setEmail("liubei@qq.com");
personService.save(person);
}
3.2.2 saveBatch
批量插入:
boolean saveBatch(Collection<T> entityList);
@Test
public void testSaveBatch(){
Person person1 = new Person();
person1.setLastName("关羽");
person1.setAge(28);
person1.setGender(1);
person1.setEmail("guanyu@qq.com");
Person person2 = new Person();
person2.setLastName("张飞");
person2.setAge(26);
person2.setGender(1);
person2.setEmail("zhangfei@qq.com");
Person person3 = new Person();
person3.setLastName("赵云");
person3.setAge(20);
person3.setGender(1);
person3.setEmail("zhaoyun@qq.com");
List<Person> list = new ArrayList<>();
list.add(person1);
list.add(person2);
list.add(person3);
personService.saveBatch(list);
}
3.3 更新操作
3.3.1 updateById
根据ID更新:
boolean updateById(T entity);
@Test
public void testUpdateById(){
Person person = new Person();
person.setId(16);
person.setLastName("诸葛亮");
person.setAge(21);
person.setGender(1);
person.setEmail("zgl@qq.com");
personService.updateById(person);
}
3.3.2 update
根据updateWrapper条件更新:
boolean update(T entity, Wrapper<T> updateWrapper);
3.3.3 updateBatchById
根据ID批量更新:
boolean updateBatchById(Collection<T> entityList);
@Test
public void testUpdateBatchById(){
Person person1 = new Person();
person1.setId(17);
person1.setLastName("曹操");
person1.setAge(33);
person1.setGender(1);
person1.setEmail("cc@qq.com");
Person person2 = new Person();
person2.setId(18);
person2.setLastName("郭嘉");
person2.setAge(25);
person2.setGender(1);
person2.setEmail("gj@qq.com");
Person person3 = new Person();
person3.setId(19);
person3.setLastName("典韦");
person3.setAge(29);
person3.setGender(1);
person3.setEmail("dw@qq.com");
List<Person> list = new ArrayList<>();
list.add(person1);
list.add(person2);
list.add(person3);
personService.updateBatchById(list);
}
3.3.4 saveOrUpdate
id存在更新记录,否插入一条记录:
boolean saveOrUpdate(T entity);
@Test
public void testSaveOrUpdate(){
Person person = new Person();
//设置id属性值为更新,不设置id属性值为插入
person.setId(22);
person.setLastName("aa");
person.setAge(20);
person.setGender(1);
person.setEmail("aa@qq.com");
personService.saveOrUpdate(person);
}
3.3.5 saveOrUpdateBatch
id存在批量更新记录,否批量插入记录:
boolean saveOrUpdateBatch(Collection<T> entityList);
3.4 删除操作
3.4.1 removeById
根据ID删除:
boolean removeById(Serializable id);
@Test
public void testRemoveById(){
personService.removeById(22);
}
3.4.2 removeByMap
根据columnMap条件删除:
boolean removeByMap(Map<String, Object> columnMap);
@Test
public void testRemoveByMap(){
Map<String, Object> columnMap = new HashMap<>();
columnMap.put("last_name", "bb");
columnMap.put("age", 20);
personService.removeByMap(columnMap);
}
注意:columnMap中的key为字段名称
3.4.3 removeByIds
根据ID批量删除:
boolean removeByIds(Collection<? extends Serializable> idList);
@Test
public void testRemoveByIds(){
List<Integer> idList = new ArrayList<>();
idList.add(17);
idList.add(18);
idList.add(19);
personService.removeByIds(idList);
}
3.4.4 remove
根据queryWrapper条件删除:
boolean remove(Wrapper<T> queryWrapper);
3.5 查询操作
3.5.1 getById
根据ID查询:
T getById(Serializable id);
@Test
public void testGetById(){
Person person = personService.getById(16);
System.out.println(person);
}
3.5.2 listByIds
根据ID批量查询:
Collection<T> listByIds(Collection<? extends Serializable> idList);
@Test
public void testListByIds(){
List<Integer> idList = new ArrayList<>();
idList.add(1);
idList.add(2);
idList.add(3);
Collection<Person> list = personService.listByIds(idList);
System.out.println(list);
}
2.5.3 listByMap
根据columnMap条件查询:
Collection<T> listByMap(Map<String, Object> columnMap);
@Test
public void testListByMap(){
Map<String, Object> columnMap = new HashMap<>();
columnMap.put("last_name", "tom");
columnMap.put("age", 22);
Collection<Person> list = personService.listByMap(columnMap);
System.out.println(list);
}
3.5.4 getOne
根据queryWrapper条件查询一条记录:
T getOne(Wrapper<T> queryWrapper);
3.5.5 getMap
根据queryWrapper条件查询一条记录:
Map<String, Object> getMap(Wrapper<T> queryWrapper);
注:与getOne()方法不同的是查询到的数据被封住在Map<String, Object>集合中,而并非实体类中
3.5.6 getObj
根据queryWrapper条件查询一条记录:
<V> V getObj(Wrapper<T> queryWrapper, Function<? super Object, V> mapper);
注:只返回第一个字段的值
3.5.7 count
查询总记录数:
int count();
根据queryWrapper条件查询总记录数:
int count(Wrapper<T> queryWrapper);
@Test
public void testCount(){
int count = personService.count();
System.out.println(count);
}
3.5.8 list
查询所有记录:
List<T> list();
根据queryWrapper条件查询记录:
List<T> list(Wrapper<T> queryWrapper);
@Test
public void testList(){
List<Person> list = personService.list();
System.out.println(list);
}
3.5.9 page
查询分页记录:
IPage<T> page(IPage<T> page);
根据queryWrapper条件查询分页记录:
IPage<T> page(IPage<T> page, Wrapper<T> queryWrapper);
注:根据分页查询条件,查询返回结果为实体分页对象
3.5.10 listMaps
查询全部记录:
List<Map<String, Object>> listMaps();
根据queryWrapper条件查询全部记录:
List<Map<String, Object>> listMaps(Wrapper<T> queryWrapper);
注:与list()方法不同的是查询到的数据被封住在Map<String, Object>集合中,而并非实体类中
3.5.11 listObjs
查询全部记录:
List<Object> listObjs();
根据queryWrapper条件查询全部记录:
List<Object> listObjs(Wrapper<T> queryWrapper);
注:只返回第一个字段的值
3.5.12 pageMaps
查询分页记录:
IPage<Map<String, Object>> pageMaps(IPage<T> page);
根据queryWrapper条件查询分页记录:
IPage<Map<String, Object>> pageMaps(IPage<T> page, Wrapper<T> queryWrapper);
注:与page()方法不同的是查询到的数据被封住在Map<String, Object>集合中,而并非实体类中
4. 关联查询
MyBatisPlus中没有提供关联查询,可使用原生MyBatis中关联查询
4.1 一对一关联
4.1.1 实体类
public class Dept {
private Integer deptno;
private String dname;
private String loc;
}
public class Emp {
private Integer empno;
private String ename;
private String job;
private Integer mgr;
private Date hiredate;
private Double sal;
private Double comm;
private Dept dept;
}
4.1.2 Mapper接口
在Mapper接口中定义关联查询的方法
public interface DeptMapper extends BaseMapper<Dept> {
}
public interface EmpMapper extends BaseMapper<Emp> {
List<Emp> selectEmpAndDept();
}
4.1.3 Service
在Service接口中定义关联查询的方法
//接口
public interface DeptService extends IService<Dept> {
}
public interface EmpService extends IService<Emp> {
List<Emp> selectEmpAndDept();
}
//实现类
@Service("deptService")
public class DeptServiceImpl extends ServiceImpl<DeptMapper, Dept> implements DeptService {
}
@Service
public class EmpServiceImpl extends ServiceImpl<EmpMapper, Emp> implements EmpService {
@Resource
private EmpMapper empMapper;
@Override
public List<Emp> selectEmpAndDept() {
return empMapper.selectEmpAndDept();
}
}
4.1.4 mapper.xml
<resultMap id="empRM" type="Emp">
<id column="empno" property="empno"/>
<result column="ename" property="ename"/>
<result column="job" property="ename"/>
<result column="mgr" property="mgr"/>
<result column="hiredate" property="hiredate"/>
<result column="sal" property="sal"/>
<result column="comm" property="comm"/>
<association property="dept" javaType="Dept">
<id column="deptno" property="deptno"/>
<result column="dname" property="dname"/>
</association>
</resultMap>
<select id="selectEmpAndDept" resultMap="empRM">
select empno,ename,job,mgr,hiredate,sal,comm,d.deptno,dname from tmp_emp e,tmp_dept d where e.deptno=d.deptno
</select>
4.1.5 注解
在Mapper接口的方法中添加注解
public interface EmpMapper extends BaseMapper<Emp> {
@Results(@Result(property = "dept", column = "deptno",
one = @One(select = "com.mp.dao.DeptMapper.selectById")))
@Select("select empno,ename,job,mgr,hiredate,sal,comm,deptno from tmp_emp")
List<Emp> selectEmpAndDept();
}
4.1.6 测试
private EmpService empService = ac.getBean("empService", EmpService.class);
@Test
public void testOne2One(){
List<Emp> list = empService.selectEmpAndDept();
System.out.println(list);
}
4.2 一对多关联
4.2.1 实体类
public class Orders {
private Integer id;
private String orderNumber;
private Double totalPrice;
private String status;
}
public class Users {
@TableId
private Integer userid;
private String username;
private String realname;
private String password;
private List<Orders> ordersList;
}
4.2.2 Mapper接口
在Mapper接口中定义关联查询的方法
public interface OrdersMapper extends BaseMapper<Orders> {
}
public interface UsersMapper extends BaseMapper<Users> {
Users selectOneUsersAndOrders(Integer userid);
}
4.2.3 Service
在Service接口中定义关联查询的方法
//接口
public interface OrdersService extends IService<Orders> {
}
public interface UsersService extends IService<Users> {
Users selectOneUsersAndOrders(Integer userid);
}
//实现类
@Service("ordersService")
public class OrdersServiceImpl extends ServiceImpl<OrdersMapper, Orders> implements OrdersService {
}
@Service("usersService")
public class UsersServiceImpl extends ServiceImpl<UsersMapper, Users> implements UsersService {
@Resource
private UsersMapper usersMapper;
@Override
public Users selectOneUsersAndOrders(Integer userid) {
return usersMapper.selectOneUsersAndOrders(userid);
}
}
4.2.4 mapper.xml
<resultMap id="usersRM" type="Users">
<id column="userid" property="userid"/>
<result column="username" property="username"/>
<result column="realname" property="realname"/>
<result column="password" property="password"/>
<collection property="ordersList" ofType="Orders">
<id column="order_id" property="id"/>
<result column="order_number" property="orderNumber"/>
<result column="total_price" property="totalPrice"/>
<result column="status" property="status"/>
</collection>
</resultMap>
<select id="selectOneUsersAndOrders" parameterType="int" resultMap="usersRM">
select
u.userid,username,password,realname,id order_id,order_number,total_price,status
from tmp_users u left join tmp_orders o on u.userid=o.userid
where u.userid=#{userid}
</select>
4.2.5 注解
在Mapper接口的方法中添加注解
public interface OrdersMapper extends BaseMapper<Orders> {
@Select("select id,order_number,total_price,status from tmp_orders where userid=#{userid}")
List<Orders> selectOrdersByUserid(Integer userid);
}
public interface UsersMapper extends BaseMapper<Users> {
@Results({@Result(property = "userid",column = "userid",id = true),
@Result(property = "ordersList", column = "userid",
many = @Many(select = "com.mp.dao.OrdersMapper.selectOrdersByUserid"))})
@Select("select userid,username,password,realname from tmp_users where userid=#{userid}")
Users selectOneUsersAndOrders(Integer userid);
}
4.2.6 测试
private UsersService usersService = ac.getBean("usersService", UsersService.class);
@Test
public void testOne2Many(){
Users users = usersService.selectOneUsersAndOrders(1);
System.out.println(users);
List<Orders> ordersList = users.getOrdersList();
for (Orders orders : ordersList) {
System.out.println(orders);
}
}
elect("select id,order_number,total_price,status from tmp_orders where userid=#{userid}")
List<Orders> selectOrdersByUserid(Integer userid);
}
public interface UsersMapper extends BaseMapper<Users> {
@Results({@Result(property = "userid",column = "userid",id = true),
@Result(property = "ordersList", column = "userid",
many = @Many(select = "com.mp.dao.OrdersMapper.selectOrdersByUserid"))})
@Select("select userid,username,password,realname from tmp_users where userid=#{userid}")
Users selectOneUsersAndOrders(Integer userid);
}
4.2.6 测试
private UsersService usersService = ac.getBean("usersService", UsersService.class);
@Test
public void testOne2Many(){
Users users = usersService.selectOneUsersAndOrders(1);
System.out.println(users);
List<Orders> ordersList = users.getOrdersList();
for (Orders orders : ordersList) {
System.out.println(orders);
}
}