526互联

Mybatis-plus

发布时间 2023-04-12 18:21:42作者: lvye1221

工具

根据 SQL 语句 来生成 类文件的 在线生成器

http://java.bejson.com/generator/

资料

已解决的问题列表

https://gitee.com/baomidou/mybatis-plus/issues?assignee_id=&author_id=&branch=&issue_search=&label_name=&milestone_id=&scope=&sort=&state=closed

知识点

自定义sql，包含参数判断 与 时间的比较。时间参数采用 new Date() 的形式自动创建即可。

 

    <select id="selectArrangeListPage" resultType="io.renren.modules.esc.model.ESCExamarrangeModel">
        SELECT
            M.SimpleName, 
           
            A.ExamState,A.ExamArrangeID,A.ExamDate,
            A.BeginTime,A.EndTime,A.InvigilatorTeacher,
            
            T.ExamObject,T.ExamAddress,
            T.ExaminApplicationCode,T.ExamCount,T.Phone,
            
            R.TrainOrgName
        
        FROM
            ExamArrange A,ExamApplication T,TrainOrg R ,ManageUnit M
        
        WHERE 1=1
        	And A.ExamState=10
        	And T.ManageUnit=M.ManageUnitID 
        	And A.ExaminApplicationID=T.ExaminApplicationID 
        	And T.TrainOrgId=R.TrainOrgId
        	        	
            <if test="arrange.keyWord&nbsp;!= null and arrange.keyWord&nbsp;!= ''">
                and T.ExamAddress like '%'+#{arrange.keyWord}+'%' 
            </if>
            
            <if test="arrange.beginTime&nbsp;!= null">
                and A.BeginTime  &gt;= #{arrange.beginTime}
            </if>
            
            <if test="arrange.endTime&nbsp;!= null">
                and A.EndTime &lt;= #{arrange.endTime}
            </if>

        ORDER BY
        	A.BeginTime desc
    </select>

 

Dao层接口的设置

List<ESCExamarrangeModel> selectArrangeListPage(Pagination page, @Param("arrange") ArrangeListModel arrange);

Service层的处理

ArrangeListModel arrange = new ArrangeListModel();

arrange.setKeyWord((String) params.get("keyWord"));
String strBeginTime = (String)params.get("beginTime");
if (StringUtils.isNotBlank(strBeginTime)) {
	Long beginTime = Long.valueOf(strBeginTime);
	arrange.setBeginTime(new Date(beginTime));
}

String strEndTime = (String)params.get("endTime");
if (StringUtils.isNotBlank(strEndTime)) {
	Long endTime = Long.valueOf(strEndTime);
	arrange.setEndTime(new Date(endTime));
}

Page<ESCExamarrangeModel> page = new Query<ESCExamarrangeModel>(params).getPage();
page.setRecords(this.baseMapper.selectArrangeListPage(page, arrange));        

return new PageUtils(page);

 

联合查询

        List<ExamstudentresitEntity> examStudentResitList = examstudentresitService.selectList(
                Condition.create()
                .setSqlSelect("*")
                .andNew()
                .eq("status", Constant.ExamStudentStatus.UPDATED.getValue())
                .or()
                .eq("status", Constant.ExamStudentStatus.FINISHABLE.getValue())

 

集合数据遍历

&nbsp; &nbsp; <select id="selectAll" resultType="io.renren.modules.dss.entity.AreaEntity">
&nbsp; &nbsp; &nbsp; select * from area
&nbsp;&nbsp; &nbsp; &nbsp;<if test="null!=roleIds">&nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; where id in
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;(select area_id from ds_role_area where role_id in
&nbsp; &nbsp; &nbsp; &nbsp; <foreach item="roleId" collection="roleIds" open="(" separator="," close=")">
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; #{roleId}
&nbsp; &nbsp; &nbsp; &nbsp; </foreach>
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;)
&nbsp;&nbsp; &nbsp; &nbsp;</if>
&nbsp; &nbsp; </select>

Dao 层接口测试

List<AreaEntity> selectAll(@Param("roleIds") Long[] roleIds);

模糊匹配

   <select id="getList" resultType="io.renren.modules.report.entity.ReportDriverManagerEntity">
   select t0.*, t1.username, t1.mobile, (select t2.name from sys_dept t2 where t2.id=t1.dept_id) deptName,
   (select t2.name from sys_dept t2 where t2.id=t1.super_dept_id) superDeptName,
   (select t2.name from tb_dept_post t2 where t2.id=t1.postid) postName
   from tb_report_driver_manager t0
   left join sys_user t1
   on t0.user_id = t1.id
   where 1=1
   <if test="username != null and username.trim() != ''">
      and t1.username like concat('%',#{username},'%')
   </if>
   ORDER BY create_date DESC
</select>

问题及解决

SQLServer2012版本，不支持批量插入 insertBatch的问题

MybatisPlusException: Error: Cannot execute insertBatch Method. Cause

Caused by: org.apache.ibatis.executor.ExecutorException: Error getting generated key or setting result to parameter object. 
Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 必须执行该语句才能获得结果。


 同样遇到了这个问题，insertbatch 无法作用于 sqlserver 2012

https://gitee.com/baomidou/mybatis-plus/issues/IJXDF
【解决办法】 将 insertBatch 用 insert 来替换掉。