MyBatisPlus自定义sql

MyBatisPlus自定义sql

在说怎么实现之前我们要先明白一个概念，就是mybatis-plus是在mybatis的基础上进行增强，并不做改变，所以mybatis的操作在mybatis-plus中也是一样可以使用的，咱们直接上代码.

1、单纯地使用注解自定义SQL

数据库数据：

执行测试方法后结果如下：

注意：这里我们把MybatisPlusConfig这个类注释掉，持久层的类使用@Mapper注解，也是一样可以运行的哈。

结果：

2、使用注解加标签做自定义sql

展示1：使用if标签

结果：

展示2：使用foreach标签

结果：

展示3：使用foreach标签

下面我们使用id来查找一下：

结果：

扩展知识点：{ } 和 ${ } 的区别？

一、区别概述

1.1、主要区别：

1、#{ } 是预编译处理，${ } 是直接替换；

2、${ } 存在SQL注入的问题，而 #{ } 不存在；

Ps：这也是面试主要考察的部分~

1.2、细节上：

1、${ } 可以实现排序查询，#{ } 不能实现排序查询。

2、${ } 可以直接进行模糊查询（但不建议，存在 SQL 注入问题），#{ } 不可以直接进行模糊查询，但可以通过 mysql 内置函数 concat()实现模糊查询(不存在 SQL 注入问题)。

二、具体描述

2.1、预编辑处理 vs 直接替换

预编辑处理：是指 MyBatis 在处理 #{ } 时，就是把 #{ } 替换成了 ？号，使用 PreparedStatement 的 set 方法来赋值。也就是说 #{ } 会把 { } 内的整体看成 value ，最后再给 value 加上单引号，重点强调引号内部是一个整体（ #{ } 不会发生 SQL 注入的根本原因）。

直接替换：是指 MyBatis 在处理 ${ } 时，会把 ${ } 替换成变量的值（不会加引号处理）。

2.2、SQL 注入问题

2.2.1、引发 SQL 注入

例如现在有一个登陆程序，需要输入正确的账户和密码才能登录，当使用了 SQL 注入就可以在不知道密码的情况下进行登录，如下

xml 文件如下：

<selectid="login"resultType="com.example.demo.entity.Userinfo">select * from userinfo where username = '${ username}' and password = '${ password}'    select>

接口如下：

/**     * 登录逻辑     * @param username     * @param password     * @return     */Userinfologin(@Param("username")Stringusername,@Param("password")Stringpassword);

测试方法如下：

@Testvoidlogin(){ Stringusername ="admin";Stringpassword ="' or 1 = '1";Userinfouserinfo =userMapper.login(username,password);System.out.println("登录状态："+(userinfo ==null?"失败":"成功"));}

执行结果如下：

2.2.2、SQL 注入分析

可以在运行结果的日志中看到，我们最后执行的SQL语句如下：

在 MySQL 中 1 = ‘1’ 结果必然为 true，所以不难分析出，如上 SQL 一定会将这张表中的用户全部返回（即使账号都填写错了，照样返回，因为1 = ‘1’ 必然为 true ）；

使用 ${ } 的注意事项：一定是可以穷举的值，在使用之前一定要对传递的值进行合法性验证（在Controller中通过穷举的方式验证安全性）。

2.3、排序查询

使用 ${ } 可以实现排序查询，而 #{ } 不可以实现排序查询，因为使用 #{ } 查询时，如果传递的值为 String 就会加单引号，导致 sql 错误。

例如你期望的 sql 语句为：select * from userinfo order by id desc；

而 #{ sort} 中传入的是一个 String 类型的值为 “desc”；

那么最终实际的 sql 语句为：select * from userinfo order by id ‘desc’；这必然是错误的~

2.4、like 查询

2.4.1、${ } 模糊查询

方式一：直接替换 <selectid="likeSelect"resultType="com.example.demo.entity.Userinfo">select * from userinfo where username like '%${ key}%'select>方式二：使用concat进行字符串拼接<selectid="likeSelect"resultType="com.example.demo.entity.Userinfo">select * from userinfo where username like concat('%', '${ key}', '%')select>

Ps：虽然可以这样，但并不建议使用 ${ } 进行模糊查询，因为存在 SQL 注入问题。

2.4.2、#{ } 模糊查询

<selectid="likeSelect"resultType="com.example.demo.entity.Userinfo">select * from userinfo where username like concat('%', #{ key}, '%')select>

2.4.3、#{ } 模糊查询问题分析

你期望的 sql 语句：select * from user where username = ‘%abc%’;

然而当你使用 #{ } 传入的参数会自带引号，于是就变成了：select * from user where username = ‘%’ abc ‘%’;

注意：

注意一点：接口里面的形参如果没有使用@Param注解，那么你sql里面就不能直接用${ }

1）使用@Param注解 当以下面的方式进行写SQL语句时：     @Select("select column from table where userid = #{ userid} ")publicintselectColumn(intuserid);当你使用了使用@Param注解来声明参数时，如果使用 #{ }或 ${ }的方式都可以。     @Select("select column from table where userid = ${ userid} ")publicintselectColumn(@Param("userid")intuserid);当你不使用@Param注解来声明参数时，必须使用使用 #{ }方式。如果使用 ${ }的方式，会报错。     @Select("select column from table where userid = ${ userid} ")publicintselectColumn(@Param("userid")intuserid);2）不使用@Param注解 不使用@Param注解时，参数只能有一个，并且是Javabean。在SQL语句里可以引用JavaBean的属性，而且只能引用JavaBean的属性。     // 这里id是user的属性@Select("SELECT * from Table where id = ${ id}")EnchashmentselectUserById(Useruser);

3、注解使用条件构造器来做查询条件

扩展知识点Wrapper中的方法

关于Wrapper中的一些方法，介绍如下：

1. eq：等于，ne：不等于

2. gt：大于，ge：大于等于，lt：小于，le：小于等于

3. between：在值1和值2之间，notBetween：不在值1和值2之间

4. like：’%值%’，notLike：’%值%’，likeLeft：’%值’，likeRight：‘值%’

5. isNull：字段 IS NULL，isNotNull：字段 IS NOT NULL

6. in：字段 IN (v0, v1, …)，notIn：字段 NOT IN (value.get(0), value.get(1), …)

7. or：拼接 OR，AND 嵌套

   注意事项:

主动调用or表示紧接着下一个方法不是用and连接!(不调用or则默认为使用and连接)

8. exists：拼接 EXISTS ( sql语句 )，notExists：拼接 NOT EXISTS ( sql语句 )

9. orderByAsc：排序：ORDER BY 字段, … ASC，orderByDesc：排序：ORDER BY 字段, … DESC

想要了解详细点可以看：https://blog.csdn.net/llllllkkkkkooooo/article/details/108216957

使用QueryWrapper

演示如下：

效果：

使用LambdaQueryWrapper

上面的这个条件构造器也可以使用LambdaQueryWrapper来做。

比如：

效果如下：

$ew.sqlSegment相当于是拿到条件构造器里面的查询条件们，但是注意：条件构造器中，其实，不止有${ ew.sqlSegment}可以使用。还有$ew.sqlSelect，${ ew.customSqlSegment}，$ew.sqlSet等等。他们都相当于是ew对象中的属性，${ }只是用来取那些属性的。

关于ew

具体的如下：

1. ew.customSqlSegment相当于是ew中定义的所有查询条件，并且会直接在sql中会在使用的时候先添加 where，即，你sql中可以不用写那个where了

   使用例子如下：

   @Select(select *from sys_user ${ ew.customSqlSegment}）List<SysUser>listPage(@Param(Constants.WRAPPER)QueryWrapperqueryWrapper)

2. ew.sqlSegment属性相当于是ew中定义的所有的查询条件，但是不会加where

   使用例子如下：

   @Select(select *from sys_user  where ${ ew.sqlSegment}） List<SysUser>listPage@Param(Constants.WRAPPER)QueryWrapperqueryWrapper)

3. ew.sqlSelect属性相当于是ew中所有你通过queryWrapper.select(……) 所定义查询的字段

   @Select(select ${ ew.sqlSelect}from sys_user  ）List<SysUser>listPage(@Param(Constants.WRAPPER)QueryWrapperqueryWrapper)

   <selectid="selectUser"resultType="com.example.demo.entity.User">select            <iftest="ew != null and ew.sqlSelect != null and ew.sqlSelect != ''">${ ew.sqlSelect}            if>from            sys_user        where is_deleted != 1        <iftest="ew != null"><iftest="ew.nonEmptyOfWhere">AND            if>${ ew.sqlSegment}        if>select>

4. ew.sqlSet属性用于update语句

   例子：

   Mapper接口：

   @Mapper@RepositorypublicinterfaceUserMapperextendsBaseMapper<User>{ List<User>queryAll(@Param("tableName")StringtableName,@Param(Constants.WRAPPER)Wrapperwrapper);booleanupdateById(@Param("tableName")StringtableName,@Param("id")intid,@Param(Constants.WRAPPER)Wrapperwrapper);//若变量名为ew则无需注解}

   XML：

   <selectid="queryAll"resultType="cn.alan.mybatis.POJO.User">select ${ ew.sqlSelect} from ${ tableName} ${ ew.customSqlSegment};select><updateid="updateById">update ${ tableName} set ${ ew.sqlSet} ${ ew.customSqlSegment};update>

   等效select SQL：select * from user where age = 10；

   等效update SQL：update user set name = 5 where id = 5；

   Controller（或Test）:

   @AutowiredUserServiceImpluserService;@RequestMapping("/query")publicList<User>queryAll(){ QueryWrapper<User>wrapper =newQueryWrapper<>();wrapper.select("*").eq("age","10");returnuserService.queryAll("user",wrapper);//return userService.queryAll("user", Wrappers.query().select("*").eq("age","10"));}@RequestMapping("/update")publicbooleanupDateById(){ UpdateWrapper<User>wrapper =newUpdateWrapper<>();wrapper.set("name","5").eq("id","5");returnuserService.updateById("user",5,wrapper);//return userService.updateById("user",5,Wrappers.update().set("name","5").eq("id",5));}}

5. ew.nonEmptyOfNormal这个属性定义在Wrapper类中，用于判断查询条件是否不为空

6. ew.nonEmptyOfWhere这个属性是用于判断你的这个ew中查询条件是否不为空的。

7. ew.emptyOfWhere这个属性定义在Wrapper类中，用于判断where条件是否为空的。

下面具体演示一些上面这些ew属性的使用：

结果：

使用条件构造器多表联查

注意：

使用${ ew.sqlSegment}的话，如果是多表查询且查询条件是多个表的字段，那么则需在service层拼接查询条件时字段前指定别名，而且不能用lambda的条件构造器来做查询了。因为多表查询的话，如果你两个表里面有需要区分的字段，比如两个表里面都有name，你sql里面的where后面一般写为where user.name=‘张三’ and ……这种的，这个“表名.字段名”这种东西用lambda的条件构造器是写不出来的。但是如果直接用字段名就可以区分的情况，就可以用lambda的条件构造器，没有关系的。

直接用字段名就可以区分的情况，例子：

数据库里面的edu_course如下：

运行，但是结果如下：

解决方案一：修改数据源的版本

解决方案二：你新建一个实体类来接收返回的数据，不用List>类型来接收了，但是注意你新建的这个实体类要是包含两个表的字段了，因为是多表查询的结果对应的实体类嘛，然后我们在这个新建的实体类里面我们让create_time这个字段被Date类型的实体类属性来接收就行了，就像是单表查询里面我们写的实体类那样。

我们之前单表对应的实体类的接收create_time字段的属性的类型是Date类型的，所以之前没有出现这个问题，如下：

这里我们采用第一种解决方法，修改druid的版本号为1.1.21或者1.1.21以上的版本就行了。

修改后，运行结果如下：

就相当于是数据库里面是这样的sql：

可以看到sql里面没有需要区分的字段，所以可以用lambda的写法。

但是如果我们把id也作为查询条件呢？

因为两个表都有id，所以会查询失败。上面这里就相当于这样的sql执行：

看到错误一样。

解决方法就是用“表名.字段名”来表示查询使用哪个表的id作为查询条件。

但是LambdaQueryWrapper是根据字段来映射的，是自动的，所以你不好改变查询条件里面的字段名。而且，上面LambdaQueryWrapper这个例子里面，这里你查询结果是有两个id字段的，那么查询结果将会用哪个id放在我们返回对象里面呢？我们看到，上面的例子里面，返回结果里面就封装了edu_teacher表里面的id，而edu_course里面的id是被舍弃了，反正，不管取哪一个，都会有一个id消失了。即，查询结果中名字重复的字段只会保留一个。这样不好。

所以，建议还是用QueryWrapper来做多表的查询条件，不用LambdaQueryWrapper做多表的查询条件。

例子：

结果：

对于上面这个QueryWrapper，我们其实都没有用到这个EduTeacher，所以我们这样写也行：

但是这样，我们不能用链式编程了：

但是我们可以这样写：

执行结果：

其实相当于下面这样的sql执行：

当然哈，你也可以自己新建一个实体类，然后用这个新建的实体类接收查询的结果。但是你要是嫌麻烦的话，还是用上面这个List>来接收吧。

例子1：

结果：

4、自定义查询加上分页

其实就是先写好分页插件的配置，然后dao层接口方法的形参上面写一个IPage的参数，返回值改为IPage，到时候调用的时候把设置好参数后的IPage传过来就可以了，不用在dao层的方法上面写什么其他东西的。

具体如下：

结果：

要给自定义的多表查询加上分页也一样哈，就写好分页插件的配置，然后dao层接口分页方法的形参上面写一个IPage的参数，返回值改为IPage，到时候调用的时候把设置好参数后的IPage传过来就可以了，不用在dao层的方法上面写什么其他东西。

例子如下：

结果：

数据查询结果不是封装到一个实体类里面的，而是放在一个Map集合里面的，那么我们得怎么加上分页呢？也是和前面一样的，就是接口的那个方法里面的第一个参数写的IPage中泛型改为Map就行了。

例子：

结果：

5、使用第三方工具做多表查询

上面我们做多表查询都是要自己写sql的，还是比较麻烦的，下面介绍一种不用自己写sql的方式来完成多表查询。

这个第三方工具是一个大佬封装的一个jar包，即mybatis-plus-join架包，这个架包可以支持MyBatis-Plus的多表联查。

官网如下：https://mybatisplusjoin.com/

或者你看这个博主写的也行：https://blog.csdn.net/weixin_39555954/article/details/128217887

快速入门的使用

一、引依赖

注意:要求mybatis plus version >= 3.4.0

<dependency><groupId>com.github.yulichanggroupId><artifactId>mybatis-plus-joinartifactId><version>1.2.4version>dependency>

二、使用方法

mapper继承MPJBaseMapper (必选)
service继承MPJBaseService (可选)
serviceImpl继承MPJBaseServiceImpl (可选)

三、（实战）多表查询

MPJLambdaWrapper<Map>mpjLambdaWrapper =newMPJLambdaWrapper();mpjLambdaWrapper.select(ChatRecord::getId,ChatRecord::getRedMoney).select(OfShopMembers::getUsablePoint).select(ChatMultiList::getName).leftJoin(OfShopMembers.class,OfShopMembers::getId,ChatRecord::getId).leftJoin(ChatMultiList.class,ChatMultiList::getId,ChatRecord::getMultiId).eq(ChatRecord::getMemberId,3213);Listlist =chatRecordMybatisJoinMapper.selectJoinList(Map.class,mpjLambdaWrapper);

对应查询语句

SELECTt.id,t.red_money,t1.username,t2.name FROMchat_record t LEFTJOINof_shop_members t1 ON(t1.id =t.id)LEFTJOINchat_multi_list t2 ON(t2.id =t.multi_id)WHERE(t.member_id =3213)

参数说明
1、select：表示查询的指定字段，一个select只能查一个表的
2、leftJoin：
第一个参数: 参与连表的实体类class
第二个参数: 连表的ON字段,这个属性必须是第一个参数实体类的属性
第三个参数: 参与连表的ON的另一个实体类属性
3、默认主表别名是t，其他的表别名以先后调用的顺序使用t1,t2,t3…

四、（实战）多表分页查询

MPJLambdaWrapper<Map>mpjLambdaWrapper =newMPJLambdaWrapper();mpjLambdaWrapper.select(ChatRecord::getId,ChatRecord::getRedMoney).select(OfShopMembers::getUsablePoint).select(ChatMultiList::getName).leftJoin(OfShopMembers.class,OfShopMembers::getId,ChatRecord::getId).leftJoin(ChatMultiList.class,ChatMultiList::getId,ChatRecord::getMultiId).eq(ChatRecord::getMemberId,3213).orderByDesc(ChatRecord::getAddTime);Pagepage =newPage(1,2);IPage<Map>mapIPage =chatRecordMybatisJoinMapper.selectJoinPage(page,Map.class,mpjLambdaWrapper);

对应查询语句

SELECTt.id,t.red_money,t1.usable_point,t2.name FROMchat_record t LEFTJOINof_shop_members t1 ON(t1.id =t.id)LEFTJOINchat_multi_list t2 ON(t2.id =t.multi_id)WHERE(t.member_id =3213)ORDERBYt.add_time DESCLIMIT2

我的测试：

一、引入依赖

这里我们测试的项目使用的是3.4.1的mybatis-plus-boot-starter

我们进去看看里面指定的mybatis-plus的版本：

看到是3.4.1版本的mybatis-plus，所以可以放心引入依赖了。

引入好后如下：

二、建表

我们新建三个表用来演示：

这三个表如下：

三、新建实体类

这个DTO里面写你要多表查询的全部数据。

四、新建dao层的接口

五、我们直接测试

测试一：多表查询

你可以把下面的测试代码当作service层的代码。

结果：

我们修改一下查询条件，看看结果：

结果：

测试二：多表分页查询

结果：

数据库我们执行sql的结果如下（下面的这个sql执行的时候是没有带分页的limit的）：

看到，两个一组，第一组里面就是id为4和id为5的数据。所以测试完全正确。

但是要注意：这里之前是配置了分页插件的，要是没有配置分页插件，上面的执行结果不会有分页效果的。

测试三：多表分页查询且自定义别名

结果：

测试四：多表查询分页且不把数据封装到实体类里面

结果：

测试五：多表分页查询，不封装到实体类，自定义别名

结果：