MyBatis Plus之注解实现动态SQL

[TOC]

使用MyBatis，所有的Dao层方法继承基类BaseMapper<T>

一、使用<script></script>脚本包裹

第一种方式：使用<script></script>进行包裹，像在xml中写sql语句一样实现动态SQL

1、使用<if></if>标签，实现关键词模糊查找

@Mapper
public interface CompanyMapper extends BaseMapper<CompanyEntity> {

    // 分页查询
    @Select("<script>" +
            " select t.*,a.name_cn as company_name" +
            " from t_company t " +
            " join t_customer_company a on t.company_id=a.id" +
            " where <![CDATA[t.status <> 2]]>" +
            " <if test='nameCn != null and nameCn.trim() != &quot;&quot;'>" +
            " AND t.name_cn like CONCAT('%',#{nameCn},'%')" +
            " </if>" +
            " </script>")
    IPage<CompanyEntity> selectCompanybyPage(Page<CompanyEntity> page,
                                       @Param("nameCn") String nameCn);
}
  • 1、如果涉及不等于等操作，可以使用<![CDATA[要包裹的内容]]>将其包裹。
  • 2、对字符串进行null判断与空串的判断可参照<if test='nameCn != null and nameCn.trim() != &quot;&quot;'>方式，具体可参考动态SQL之、条件判断。

1.1、使用<where></where>标签，实现关键词模糊查询进阶

由<where></where>包裹的<if></if>标签中的SQl语句，除第一个and可省略不写外，其他均需要写。

@Select("<script>" +
            " select t.* from t_log t" +
            " <where>" +
            " <if test='typeName!= null'>" +
            " and t.type_name like CONCAT('%',#{typeName},'%')" +
            " </if>" +
            " <if test='typeCode!= null'>" +
            " and t.type_code like CONCAT('%',#{typeCode},'%')" +
            " </if>" +
            " </where>" +
            " </script>")
    IPage<LogEntity> selectLogByPage(Page<LogEntity> page,
                                           @Param("typeName") String typeName,
                                           @Param("typeCode") String typeCode);

1.2、查询语句中出现大于小于的另一种方式写法

当注解SQL语句中出现日期的大于等于和小于等于判断时，如果未使用<![CDATA[内容]]>进行包裹，则将小于等于转译为lt;=（或者将大于等于转义为：&gt;=）
原则是：SQL注解语句中只能出现同方向的大于或者我小于。

@Select("<script>" +
            " select t.* from t_user_plan t" +
            " where t.type=0" +
            " <if test='startTime != null'>" +
            " AND t.effective_date >= #{startTime} " +
            " </if>" +
            " <if test='endTime != null'>" +
            " AND t.effective_date &lt;= #{endTime} " +
            " </if>" +
            " </script>"
    )
    IPage<UserPlanEntity> selectUserPlanByPage(Page<UserPlanEntity> page,
                                               @Param("startTime") LocalDate startTime,
                                               @Param("endTime") LocalDate endTime);

2、使用<foreach></foreach>标签，实现id列表查询

// 根据id集合查询所有对象
@Select("<script>" +
        " select t.* from t_user t " +
        " where t.id in" +
        " <foreach collection='ids' item='id' open='(' separator=',' close=')'>" +
        " #{id}" +
        " </foreach>" +
        " </script>")
List<UserEntity> selectAllUserbyIds(@Param("ids") List<String> ids);

3、使用<foreach></foreach>标签，实现批量插入

    /**
     * 批量插入
     * @param list
     * @return
     */
    @Insert("<script>" +
            " INSERT INTO t_position(sate,type,zone_id,time,lon,lat,height)VALUES" +
            " <foreach collection='list' item='entity' separator=','> " +
            " (#{entity.sate},#{entity.type},#{entity.zoneId},#{entity.time},#{entity.lon},#{entity.lat},#{entity.height})" +
            " </foreach> " +
            " </script>")
    boolean insertBatch(@Param("list") List<PositionEntity> list);

附上yml中需要配置项特别说明：需要在url中添加rewriteBatchedStatements=true，否则批量插入不生效！

spring:
  config:
    activate:
      on-profile: dev
  datasource:
    driverClassName: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/mysql_name?zeroDateTimeBehavior=convertToNull&autoReconnect=true&tinyInt1isBit=false&useSSL=false&allowMultiQueries=true&useUnicode=true&characterEncoding=utf8&allowPublicKeyRetrieval=true&rewriteBatchedStatements=true
    username: root
    password: 123456

以上on-profile写法，使用MP版本为：3.4.3.4

4、使用<set></set>标签，实现字段更新

    @Update("<script>" +
            " update radius.t_user_plan" +
            " <set>" +
            " <if test='plan.state != null'>" +
            "  state = #{plan.state}," +
            " </if>" +
            " <if test='plan.effectiveDate != null'>" +
            "  effective_date=#{plan.effectiveDate}," +
            " </if>" +
            " <if test='plan.expireDate != null'>" +
            "  expire_date=#{plan.expireDate}," +
            " </if>" +
            " <if test='plan.dataLimit != null'>" +
            "  data_limit=#{plan.dataLimit}," +
            " </if>" +
            " </set>" +
            " where user_plan_id in" +
            " <foreach collection='ids' item='id' open='(' separator=',' close=')'>" +
            " #{id}" +
            " </foreach>" +
            " and state = 0" +
            " and (YEAR(effective_date)=YEAR(CURTIME()) and MONTH(effective_date)=MONTH(CURTIME()))" +
            " </script>")
    int updateUserByIds(@Param("ids") List<Long> ids,
                        @Param("plan") Plan plan);

5、使用对象集合来进行更新

方式一：更新同一字段

@Update("<script>" +
            " update t_user" +
            " set balance = #{balance}" +
            " where id in" +
            " <foreach collection='list' item='one' open='(' separator=',' close=')'>" +
            " #{one.id}" +
            " </foreach>" +
            " </script>"
    )
    int updateBillPubAccountByIds(@Param("list") List<User> list,
                                  @Param("balance") Integer balance);

方式二：根据ID更新各自对象属性字段

特别说明：经过测试，在数据库连接中添加useAffectedRows=true参数并不能实现返回实际受影响的行数（如使用Mybatis Plus中的com.baomidou.mybatisplus.extension.service.updateBatchById方法进行更新，则返回的永远为true）

 /**
     * 根据ID批量更新设备状态（纯测试）
     *
     * @param list 设备状态列表
     * @return 返回影响的行数（需要在数据库连接参数中添加useAffectedRows=true，否则不生效？）
     */
    @Update("<script>" +
            " <foreach collection='list' item='one' separator=';'>" +
            " update t_device_status" +
            " <set>" +
            " <if test='one.cpuRate != null'>cpu_rate=#{one.cpuRate},</if>" +
            " <if test='one.memeryTotal != null'>memery_total=#{one.memeryTotal},</if>" +
            " <if test='one.memeryUsed != null'>memery_used=#{one.memeryUsed},</if>" +
            " <if test='one.memeryRate != null'>memery_rate=#{one.memeryRate},</if>" +
            " <if test='one.diskTotal != null'>disk_total=#{one.diskTotal},</if>" +
            " <if test='one.diskUsed != null'>disk_used=#{one.diskUsed},</if>" +
            " <if test='one.diskRate != null'>disk_rate=#{one.diskRate},</if>" +
            " <if test='one.gpuTemperature != null'>gpu_temperature=#{one.gpuTemperature},</if>" +
            " <if test='one.gpuLoad != null'>gpu_load=#{one.gpuLoad},</if>" +
            " <if test='one.gpuFrequencyCurrent != null'>gpu_frequency_current=#{one.gpuFrequencyCurrent},</if>" +
            " <if test='one.gpuPowerControlStatus != null'>gpu_power_control_status=#{one.gpuPowerControlStatus},</if>" +
            " <if test='one.gpuRaligateStatus != null'>gpu_raligate_status=#{one.gpuRaligateStatus},</if>" +
            " <if test='one.gpuTpcPgMaskStatus != null'>gpu_tpc_pg_mask_status=#{one.gpuTpcPgMaskStatus},</if>" +
            " <if test='one.gpu3dScalingStatus != null'>gpu3d_scaling_status=#{one.gpu3dScalingStatus},</if>" +
            " </set>" +
            " where id = #{one.id}" +
            " </foreach>" +
            " </script>"
    )
    int updateBatchById(@Param("list") List<DeviceStatus> list);

6、插入或更新(单条记录)

由于MyBatis Plus升级到3.5.7后，继承自BaseMapper后，其父类中存在insertOrUpdate更新单条或多条记录的重载方法，经测试，其更新或插入依赖的是主键id，不能很好的符合要求，以下重写了insertOrUpdate实现根据自定义唯一性索引进行更新

SQL详情

/**
     * 插入或更新数据信息
     * key值：user_id(需要在数据库中将这字段标注为唯一索引)
     *
     * @param entity 待插入或更新的实体对象
     */
    @Override
    @Insert("<script>" +
            " INSERT INTO t_user_task(user_id,user_type,priority,slice_id,task_type,des_user_id,res_req,state,sid,bid,flag,sid2,bid2) VALUES " +
            " (#{entity.userId},#{entity.userType},#{entity.priority},#{entity.sliceId},#{entity.taskType},#{entity.desUserId},#{entity.resReq},#{entity.state},#{entity.sid},#{entity.bid},#{entity.flag},#{entity.sid2},#{entity.bid2})" +
            " on duplicate key update" +
            " user_id=values(user_id)," +
            " user_type=values(user_type)," +
            " priority=values(priority)," +
            " slice_id=values(slice_id)," +
            " task_type=values(task_type)," +
            " des_user_id=values(des_user_id)," +
            " res_req=values(res_req)," +
            " state=values(state)," +
            " sid=values(sid)," +
            " bid=values(bid)," +
            " flag=values(flag)," +
            " sid2=values(sid2)," +
            " bid2=values(bid2)" +
            " </script>")
    boolean insertOrUpdate(@Param("entity") UserTask entity);

设置该表中user_id为唯一索引


设置user_id为唯一索引

7、插入或更新(多条记录)

/**
     * 批量插入或更新数据信息
     * key值：user_id(需要在数据库中将这字段标注为唯一索引)
     *
     * @param list 待插入或更新的实体集合
     * @return 返回影响的记录数
     */
    @Insert("<script>" +
            " <foreach collection='list' item='entity' separator=';'>" +
            " INSERT INTO t_user_task(user_id,user_type,priority,slice_id,task_type,des_user_id,res_req,state,sid,bid,flag,sid2,bid2) VALUES " +
            " (#{entity.userId},#{entity.userType},#{entity.priority},#{entity.sliceId},#{entity.taskType},#{entity.desUserId},#{entity.resReq},#{entity.state},#{entity.sid},#{entity.bid},#{entity.flag},#{entity.sid2},#{entity.bid2})" +
            " on duplicate key update" +
            " user_id=values(user_id)," +
            " user_type=values(user_type)," +
            " priority=values(priority)," +
            " slice_id=values(slice_id)," +
            " task_type=values(task_type)," +
            " des_user_id=values(des_user_id)," +
            " res_req=values(res_req)," +
            " state=values(state)," +
            " sid=values(sid)," +
            " bid=values(bid)," +
            " flag=values(flag)," +
            " sid2=values(sid2)," +
            " bid2=values(bid2)" +
            " </foreach>" +
            " </script>")
    int insertOrUpdateBatch(@Param("list") List<UserTask> list);

注意：多条记录进行插入或更新操作时，返回的int结果，经过测试，并非是影响的记录数。

二、使用if()（类三目运算）

第二种方式：使用if(执行条件,true操作,false操作)进行。
使用此方法时，注意where条件后面根据需要加上1=1

1、实现关键词查询

@Mapper
public interface UserMapper extends BaseMapper<UserEntity> {

    // 根据id查询、根据name模糊查询
    @Select("select t.id,t.login_name,t.name,b.name as login_station_name" +
           " from t_user t" +
           " left join t_remote_station b on t.login_station_id=b.id" +
           " WHERE 1=1" +
           " and if(#{id} is null,1=1,t.id=#{id})" +
           " and if(#{name} is null,1=1,t.name like CONCAT('%',#{name},'%'))")
    List<UserEntity> selectUserListByPage(Page<UserEntity> page,
                                          @Param("id") Long id,
                                          @Param("name") String name);
}

总结

1、使用以上任意一种方式都能实现比较复杂的动态SQL，建议使用第一种方式。
2、无论使用第一种还是第二种，都存在隐患，即，当传入参数为空的时候，可能会造成全表查询。解决的办法是：在业务实现层对参数进行非空判断，如果为空，则赋null值。
3、如果涉及到在同一个数据库实例中的跨库查询时，表名前需加上数据库名称即可，如：数据库名.表名（中间有一点）。

引文

文章参考

XML转义字符

语义 字符 转义字符
小于号 < &lt;
大于号 > &gt;
和 & &amp;
单引号 ' &apos;
双引号 " &quot;
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
【社区内容提示】社区部分内容疑似由AI辅助生成，浏览时请结合常识与多方信息审慎甄别。
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

友情链接更多精彩内容