一、if-else写法foreach collectionlist indexindex itemitem separator, trim prefix( suffix) suffixOverrides, choose when testitem.id ! null and item.id !#{item.id,jdbcTypeCHAR},/when otherwise,/otherwise /choose choose when testitem.userType ! null and item.userType !#{item.userType,jdbcTypeVARCHAR},/when otherwise,/otherwise /choose /trim /foreach二、where标签用法where if testquery.username ! null and query.username ! and user.username LIKE CONCAT(%,#{query.username},%) /if /wherewhere标签可以自动去除多余的and或or三、like写法LIKE CONCAT(%,#{query.username},%)四、转义字符写法方式一![CDATA[ sql语句 ]]方式二直接转义lt;lt;gt;gt;amp;apos;quot;五、IN用法where xxx IN foreach collectionlist itemitem indexindex open( close) separator, #{item} /foreach // 默认List类型参数collection为list,Array类型为array。也可用Param(list)指定六、mybatisplus 常用查询// 去重查询 xxxService.count(Wrappers.Xxxquery().select(DISTINCT province))); // 自定义分组查询 ListXxx orderList xxxService.list(Wrappers.Xxxquery() .select(DATE_FORMAT(create_time, %Y-%m-%d) AS ym,DATE_FORMAT(create_time, %d) AS type, COUNT(id) as price) .groupBy(ym) .lambda().between(Xxx::getCreateTime, LocalDateTime.of(startDate, LocalTime.MIN), LocalDateTime.of(endDate, LocalTime.MAX)) ); // 求和查询 Xxx all xxxService.getOne(Wrappers.Xxxquery().select(sum(price) as price));七、关联查询association和collectionassociaction一对一、多对一-- 一条语句自动封装实体结构t_emp表与t_dept表左连接以t_emp的dept_id字段和t_dept的dept_id字段相同连接实体结构Emp:... Dept resultMap idempResultMapByAssociation typeEmp id columnemp_id propertyempId/id result columnemp_name propertyempName/result result columnage propertyage/result result columngender propertygender/result association propertydept javaTypeDept id columndept_id propertydeptId/id result columndept_name propertydeptName/result /association /resultMap select idselectEmpByAssociationOne resultMapempResultMapByAssociation select * from t_emp left join t_dept on t_emp.dept_idt_dept.dept_id where t_emp.emp_id#{empId} /select -- 子查询主语句的关联字段columndept_id传递到子查询中当参数 resultMap idempResultMapStepOne typeEmp id columnemp_id propertyempId/id result columnemp_name propertyempName/result result columnage propertyage/result result columngender propertygender/result association propertyDept selectcom.mybatis.mapper.DeptMapper.selectDeptStepTwo columndept_id /association /resultMap select idselectEmpByAssociationStepOne resultMapempResultMapStepOne select * from t_emp where emp_id#{empId} /select select idselectDeptStepTwo resultTypecom.mybatis.pojo.Dept select * from t_dept where dept_id#{deptId} /selectcollection一对多-- 一条语句自动封装实体数据结构Dept: ... ListEmp emps resultMap idresultMapByCollection typeEmp id columnemp_id propertyempId/id result columnemp_name propertyempName/result result columnage propertyage/result result columngender propertygender/result collection propertydepts ofTypeDept id columndept_id propertydeptId/id result columndept_name propertydeptName/result /collection /resultMap select idselectEmpByAssociationOne resultMapresultMapByCollection select * from t_emp left join t_dept on t_emp.dept_idt_dept.dept_id where t_emp.emp_id#{empId} /select -- 子查询 resultMap idselectStepOne typeEmp id columnemp_id propertyempId/id result columnemp_name propertyempName/result result columnage propertyage/result result columngender propertygender/result collection propertydepts selectcom.mybatis.mapper.EmpMapper.selectStepTwoByCollection columndept_id /collection /resultMap select idselectStepOneByCollection resultMapselectStepOne select * from t_emp where emp_id #{empId} /select select idselectStepTwoByCollection resultTypeDept select * from t_dept where dept_id #{deptId} /select子查询的方式会多次访问数据库一般使用一条语句自动封装实体的方式但是遇到分页时这种方式会导致分页是按照明细数据分页而不是封装后的数据分页分页问题可以使用先分页查主数据然后再用这种方式封装数据来解决。