MyBatis-Plus学习(2)

持久层

发布日期: 2021-06-09

MybatisPlus常用操作的熟悉了解。快速迭代开发技巧熟悉，相关知识点总结记录。后续加入复杂查询及分页等。

MyBatis-Plus常用操作

1、主键查询返回实体类

School school = schoolMapper.selectById(schoolId);


// 相应的控制台打印
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@52ebdf07] was not registered for synchronization because synchronization is not active
JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@4ecf9] will not be managed by Spring
==>  Preparing: SELECT id,school_name,school_address,school_icon,school_establish,create_date,create_user,update_date,update_user,is_delete FROM sys_school WHERE id=? AND is_delete=1 
==> Parameters: 1(Long)
<==      Total: 0
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@52ebdf07]

2、非主键查询默认返回List

QueryWrapper<School> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("school_address", "44");
List<School> schoolList = schoolMapper.selectList(queryWrapper);

// 相应的控制台打印
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@506c3bdd] was not registered for synchronization because synchronization is not active
JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@6ab856dd] will not be managed by Spring
==>  Preparing: SELECT id,school_name,school_address,school_icon,school_establish,create_date,create_user,update_date,update_user,is_delete FROM sys_school WHERE is_delete=1 AND (school_address = ?) 
==> Parameters: 44(String)
<==    Columns: id, school_name, school_address, school_icon, school_establish, create_date, create_user, update_date, update_user, is_delete
<==        Row: 4, 44, 44, 55, 2020-09-08 03:11:11, 2021-05-26 11:43:11, 1, 2021-05-26 11:43:11, 1, 1
<==        Row: 6, 55555, 44, 55, 2021-05-28 11:35:56, 2021-05-28 03:35:57, 1, 2021-05-28 03:35:57, 1, 1
<==        Row: 7, 8888889, 44, 55, 2021-05-28 15:34:01, 2021-05-28 07:34:01, 1, 2021-05-28 07:34:01, 1, 1
<==      Total: 3
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@506c3bdd]

3、非主键唯一查询

QueryWrapper<School> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("school_address", "44");
School schoolList = schoolMapper.selectOne(queryWrapper);


//当根据查询条件返回多个结果时,系统会报异常
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 3

    
//如果我们想要根据查询结果取一条的话相应的QueryWrapper拼接如下：
QueryWrapper<School> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("school_address", "44").last(" limit 1");
School schoolList = schoolMapper.selectOne(queryWrapper);


// 相应的控制台打印
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@25ba7e55] was not registered for synchronization because synchronization is not active
JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@7cb406f8] will not be managed by Spring
==>  Preparing: SELECT id,school_name,school_address,school_icon,school_establish,create_date,create_user,update_date,update_user,is_delete FROM sys_school WHERE is_delete=1 AND (school_address = ?) limit 1 
==> Parameters: 44(String)
<==    Columns: id, school_name, school_address, school_icon, school_establish, create_date, create_user, update_date, update_user, is_delete
<==        Row: 4, 44, 44, 55, 2020-09-08 03:11:11, 2021-05-26 11:43:11, 1, 2021-05-26 11:43:11, 1, 1
<==      Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@25ba7e55]

4、查询结果返回指定列

QueryWrapper<School> queryWrapper = new QueryWrapper<>();
queryWrapper.s elect("id, school_name, school_address").eq("school_address", "44").last(" limit 1");
School schoolList = schoolMapper.selectOne(queryWrapper);


SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7526927e] was not registered for synchronization because synchronization is not active
JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@1afc5a20] will not be managed by Spring
==>  Preparing: SELECT id, school_name, school_address FROM sys_school WHERE is_delete=1 AND (school_address = ?) limit 1 
==> Parameters: 44(String)
<==    Columns: id, school_name, school_address
<==        Row: 4, 44, 44
<==      Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7526927e]

5、查询返回自定义实体

// List查询结果使用convert函数进行转换
Page<School> listPage = schoolMapper.selectPage(page, queryWrapper);
		
        return  listPage.convert(obj -> SchoolDto.builder()
                .id(obj.getId())
                .schoolName(obj.getSchoolName())
                .schoolAddress(obj.getSchoolAddress())
                .schoolIcon(obj.getSchoolIcon())
                .schoolEstablish(obj.getSchoolEstablish())
                .build());

// 使用自定义SQL来返回自定义实体
@Select("select * from sys_school ${ew.customSqlSegment}")
SchoolDto selectCustomById(@Param(Constants.WRAPPER) Wrapper wrapper);

6、Query封装

// lamdaQuery
QueryWrapper<School> queryWrapper = new QueryWrapper<School>();
if (Optional.ofNullable(schoolVo).isPresent()) {
    queryWrapper.lambda()
    .eq(StringUtils.isNotBlank(schoolVo.getSchoolName()), School::getSchoolName, schoolVo.getSchoolName())
    .eq(StringUtils.isNotBlank(schoolVo.getSchoolAddress()), School::getSchoolAddress, schoolVo.getSchoolAddress())
    .eq(StringUtils.isNotBlank(schoolVo.getSchoolIcon()), School::getSchoolIcon, schoolVo.getSchoolIcon())
    .eq(Objects.nonNull(schoolVo.getSchoolEstablish()), School::getSchoolEstablish, schoolVo.getSchoolEstablish());
}

// query有以下两种写法
QueryWrapper<School> queryWrapper = new QueryWrapper<School>();
queryWrapper.eq(StringUtils.isNotBlank(schoolVo.getSchoolName()), "school_name", schoolVo.getSchoolName());

if (StringUtils.isNotBlank(schoolVo.getSchoolName())) {
	queryWrapper.eq("school_name", schoolVo.getSchoolName());    
}