本文深入探讨了SQL与JPA在实际应用中的高级技巧，包括MySQL中的DISTINCT使用细节、EXISTS关键字的工作原理、常见SQL优化技巧以及JPA中的save()与saveAll()的区别等内容。
MySQL
DISTINCT
老生常谈的问题了，去重的方式是根据 distinct后的所有字段决定，在查表中的一个字段时还能用，想查去重后的这一行数据，不可避免的要用查出来的主键，再进行一遍查询
EXISTS关键字用法
and exists + (子查询) 返回的是true | false
EXISTS执行顺序：
- 首先执行一次外部查询，并缓存结果集，如 SELECT * FROM A
- 遍历外部查询结果集的每一行记录R，代入子查询中作为条件进行查询，如 SELECT 1 FROM B WHERE B.id = A.id
- 如果子查询有返回结果，则EXISTS子句返回TRUE，这一行R可作为外部查询的结果行，否则不能作为结果
指定字段的排序方式
指定按status字段1,2,0的顺序排序
SELECT * FROM table ORDER BY FIELD(status,1,2,0);
对时间类型字段的判断
- SELECT * FROM 表名 WHERE 字段名 > NOW() - INTERVAL 1 HOUR;
- SELECT * FROM 表名 WHERE 字段名 > DATE_SUB(NOW(), INTERVAL 60 MINUTE);
AND、OR优先级问题
MySQL中，AND的执行优先级高于OR。也就是说，在没有小括号()的干预下，总是先执行AND语句，再执行OR语句
SELECT * FROM `TABLE1` WHERE 条件1 AND 条件2 OR 条件3
SELECT * FROM `TABLE1` WHERE (条件1 AND 条件2 ) OR 条件3
表连接过程理解
先将两个表所有数据连接出来得到m*n（笛卡尔积）条数据，在根据on条件筛选出满足条件的数据，如果是left join 则同时会保留主表不满足的数据，反之
UNION、UNION ALL
union自带去重效果。使用时需保证联合的多结果集字段一致，必要时可用常量填补查询不出的字段
我们相对结果集进行排序时，不要在两块union的sql中进行排序，将多块union sql合并为一个子查询，在子查询外进行排序
ISNULL 与 != xxx
目的想检索城市不为青岛、济南的用户：
SELECT * FROM `user` WHERE city != "青岛" AND city != "济南"
上述sql不会检索出city字段为空的数据，如果条件列允许为空记得加上AND ISNULL(列名)条件
实现升序排列时实现null的排序字段靠后排序
可以使用排序ORDER BY 排序字段 IS NULL, 排序字段这种方式，可以理解为字段非空的行是0、字段为空的行是1,形成了排序
MyBatis
XML文件中WHERE条件写法
详细内容可以参照这篇文章，结论就是使用<where></where>标签可以代替WHERE 1 = 1的写法，同时也能规避标签内有多余AND关键字的情况
<![CDATA[]]>的含义
CDATA标签中字符文本是不作为html被再次转义的。比如 将不像其他地方那样被转换成空格。通常CDATA里面放一些一字不改的数据，比如源代码。菜鸟教程中的详细描述
SELECT i.*
FROM tv_ad i
LEFT JOIN tv_ad_cust custb_ ON i.id=custb_.ad_id AND custb_.type='B'
LEFT JOIN tv_ad_cust custc_ ON i.id=custc_.ad_id AND custc_.type='C'
LEFT JOIN tv_ad_cust custa_ ON i.id=custa_.ad_id AND custa_.type='A'
LEFT JOIN tv_delivery_terminal_relation ter_ ON i.id=ter_.ref_id AND ter_.ref_type='E'
WHERE 1=1
AND i.is_del = 'N'
<if test="dto.adStatus != null and dto.adStatus == 'inactive'">
<![CDATA[ AND now() < i.start_time]]>
</if>
<if test="dto.adStatus != null and dto.adStatus == 'expired'">
<![CDATA[ AND now() > i.end_time]]>
</if>
范围查询中<foreach></foreach>与集合的正确用法
要注意非空判断，mapper内不加检查的话，要在调用mapper的地方加上校验，不然会出现异常
<if test="list != null and list.size > 0">
AND item IN
<foreach collection="list" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</if>
<if test="list != null and list.size == 0">
and 1 = 0
</if>
其中第二组<if>标签是为了规避传入集合为空时，未过滤结果集的情况
Mapper中Integer类型的判断＜if test="status != null and status != ‘’ "＞问题
当status为0时 status != ‘’ 会被判断为false,即传入整形0数据时，逻辑等同于空串
Mapper中使用resultMap导致分页结果的不正确
业务场景是两个表A、B,一对多的对应关系，分页想以A表中数据条数进行分页，pageHelper本质上是在sql里拼接了 limit 0,10 这种的语句，但是resultMap的对象形成是在分页之后，这样会导致分页结果不正确。
目前只能通过规避resultMap和分页同时使用解决问题
模糊查询的写法
下面写法能有效避免sql注入问题，同时不需要传入参数提前拼接%
p.project_code LIKE CONCAT('%', #{params.projectCodeOrName}, '%')
PageHelper分页方式
项目里没有显示调用PageHelper.startPage()方法，仅能mapper接口中通过下述方式传参就实现了分页
List<SearchProjectListResultDto> searchProjectList(
@Param("params") SearchProjectListParamsDto paramsDto,
@Param("pageNum") Integer pageNum,
@Param("pageSize") Integer pageSize);
原因：mybatis相关配置文件中开启了supportMethodsArguments这个配置项
下面是关键代码：
com.github.pagehelper.page.PageParams
//是否支持接口参数来传递分页参数，默认false
protected boolean supportMethodsArguments = false;
com.github.pagehelper.util.PageObjectUtil
JPA
save()传入对象属性为null时，会将DB表中对应字段值改为null
调用save()方式时，首先要构造一个对应的pojo对象传入，但在更新操作时我们一般只会更新对象的某个属性，例如User user = new User();user.setName("xxx")后，user对象其他属性都为空，我们把这个对象传入，jpa会将DB表中对应字段都置为空。
解决方式：先执行jpa接口的findById()，查出对象，再调用这个对象的set方法，最后将这个对象传入到save()中。
批量插入使用saveAll()，并不会对性能有提升
如果数据量真的很大并且想通过jpa去实现批量插入操作，可以参照下面这几个方案
https://blog.youkuaiyun.com/zwang60/article/details/106080270/
save()什么时候执行update逻辑，什么时候执行insert逻辑
之前对这块有个误解就是传入pojo对象带着id属性不为空就是update否则是insert，具体看下面源码
为什么使用BeanUtils.copyProperties拷贝新的数据对象时，新对象save后id字段为null
场景：先查一条数据，用工具方法把旧数据属性赋值到新对象上，再将拷贝后的对象id赋值成null，调用save方法，getId()为null，但是实际表中新的数据已保存成功了，并且主键是自增的
原因：跟上面那个问题答案类似，因为Jpa判断是否是对象依赖version字段，需要把version字段也赋值成null，save后getId才会得到自增的id
save()逻辑是先查再更吗？进行大数据量更新时是否会影响性能？
是先查询再更新，因为jpa保存实体对象过程中会比较这个实体对象是否已在DB中存在，如果存在不会进行更新。
也就说如果这个实体对象的值未发生变化，即使调用save()方法，对应的DB记录里的update_time字段也不会发生变化。
JpaRepository接口定义的findByIdNotAndName()
这种接not接口定义方法，传入id都不能为空。其实按理说如果想做非空判断应该用isNull、isNotNull、NotNull这种写法，因为有多个业务场景我想共用这一个sql，实际测试结果是不行的，会抛出异常。
调用自定义Jpa接口异常：org.springframework.dao.InvalidDataAccessApiUsageException: Parameter value [0] did not match expected type [java.lang.Integer]
造成异常的原因是，jpa接口中实体类memberId属性是Integer类型的，但是我定义的接口方法，入参是Long类型的导致出错。
错误例子：List<TvScreenSettingDetail> findByMemberIdAndType(Long memberId, Integer type);
org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.ObjectDeletedException: deleted instance passed to merge
使用jpa保存已经删除过的对象会抛出这个异常。
使用场景是，我先查了一遍数据，再将数据删除掉，再将数据中的某个属性值改掉，重新存储出错。
解决方案是重新new对象，或者将查出的数据对象id属性置null。
参考资料
https://segmentfault.com/a/1190000039999504?utm_source=sf-similar-article
Jpa对updateTime和createTime字段强制更新的手段
updateTime直接更新是可以生效的
Jpa AuditorAware、Auditable
Jpa saveAndFlush() 和 save()
https://www.jianshu.com/p/717b681d04f3
saveAndFlush()会将结果刷新到数据库中，但是没有commit()，如果你用Jpa和Mybatis同时操作一条数据，这种场景需要使用saveAndFlush()
扫一扫
3424
到【灌水乐园】发言
