Spring Data JPA使用必备(三):Spring Data JPA自定义SQL写法

Spring Data JPA的前两篇已经写了通过方法名格式自动生成SQL,也简单的提到了@Query注解。但是往往真正的业务逻辑里面,这些是完全不够用的,涉及到一些稍微复杂一点的查询就会有点问题,如根据一组条件中的某几个条件查询(条件不固定),然后再加上分页、排序,这个时候只是使用之前的方法就有点捉襟见肘啦。

这篇博客的篇幅不会很长,主要是讲两个点,一个是在Spring Data JPA系列的第一篇博客中提到的@Query注解,一个就是通过Specification组合动态条件以及PageableSort实现分页和排序。

@Query注解

@Query注解使用起来很简单,默认的属性是value,就是当前写的SQL语句,有时会用到nativeQuery属性,这个属性是用来标记当前的SQL是本地SQL,还是符合JPA语法规范的SQL。这里需要解释一下本地SQL和JPA语法规范的SQL区别。

  • 本地SQL,是根据实际使用的数据库类型写的SQL,这种SQL中使用到的一些语法格式不能被JPA解析以及可能不兼容其他数据库,这种SQL称为本地SQL,此时需要将nativeQuery属性设置为true,否则会报错。

  • JPA语法规范的SQL,往往这种SQL本身是不适用于任何数据库的,需要JPA将这种SQL转换成真正当前数据库所需要的SQL语法格式。

注意:JPA很好的一个特性就是用JPA语法规范写的SQL,会根据当前系统使用的数据库类型改变生成的SQL语法,兼容数据库类型的切换,如之前使用的是MySQL,现在换成Oracle,由于不同类型的数据库,SQL语法会有区别,如果使用的是mybatis,就需要手动去改SQL兼容Oracle,而JPA就不用啦,无缝对接。

说明:很大的时候使用JPA感觉都是为了兼容后期可能会有数据库切换的问题,所以在使用JPA的时候,不要去使用本地SQL,这就违背了使用JPA的初衷,让nativeQuery属性保持默认值就可以啦!(切记切记)

举个栗子

根据这个栗子再引出一些常用的东西,代码如下:

1
2
3
4
5
6
7
8
9
//示例1
@Query("select t from Device t where t.deviceSn=:deviceSn and t.deleteFlag=1")
Device findExistDevice(@Param("deviceSn") String deviceSn);
//示例2
@Query("select t from Device t where t.deviceSn=:deviceSn and t.deviceType =:deviceType and t.deleteFlag=1")
Device findExistDevice(@Param("deviceSn") String deviceSn,@Param("deviceType")Integer deviceType);
//示例3
@Query("select t from Device t where t.deviceSn=?1 and t.deviceType = ?2 and t.deleteFlag=1")
Device findDevice(String deviceSn,Integer deviceType);
  • 在SQL上使用占位符的两种方式,第一种是使用”:”后加变量的名称,第二种是使用”?”后加方法参数的位置。如果使用”:”的话,需要使用@Param注解来指定变量名;如果使用”?”就需要注意参数的位置。
  • SQL语句中直接用实体类代表表名,因为在实体类中使用了@Table注解,将该实体类和表进行了关联。

还有其他有使用SpEL表达式等等就不多说了,基本没什么用,也是一些不常用的东西(可以了解了解去装X)。

@Modifying注解

相信在正常的项目开发中都会涉及到修改数据信息的操作,如逻辑删除、封号、解封、修改用户名、头像等等。在使用JPA的时候,如果@Query涉及到update就必须同时加上@Modifying注解,注明当前方法是修改操作。

如下代码:

1
2
3
@Modifying
@Query("update Device t set t.userName =:userName where t.id =:userId")
User updateUserName(@Param("userId") Long userId,@Param("userName") String userName);

到这里就写完了@Query的基本用法,很多复杂的用法就不多说了,用不上。

Specification+Pageable+Sort组合复杂SQL

在查询列表数据的时候,这三个基本都是可以用上的,如果不涉及到排序、分页,只是组合动态的查询条件,Specification就够用了,下面来依次说一下。

Specification动态组合查询

组合条件查询很常见,使用@Query或者根据方法名自动生成SQL实现都不是很方便,JPA提供了Specification解决了这个问题。如下代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
package com.itcrud.jpa;

import com.google.common.collect.Lists;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.domain.Specification;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.Predicate;
import java.util.List;

/**
* @Author: IT-CRUD
*/
public class ArticleService {

@Autowired
private ArticleRepository repository;

public void articleList(final ArticleReqDTO reqDTO) {
Specification<Article> spec = (root, query, builder) -> {
List<Predicate> predicates = Lists.newArrayList();
//等于,根据ID精确查询
if (reqDTO.getId() != null) {
predicates.add(builder.equal(root.get("id"), reqDTO.getId()));
}
//模糊,关键字匹配文章摘要+标题
if (StringUtils.isNotBlank(reqDTO.getKeywords())) {
predicates.add(builder.or(
builder.like(root.get("title"), "%" + reqDTO.getKeywords() + "%"),
builder.like(root.get("abstract"), "%" + reqDTO.getKeywords() + "%")
));
}
//in范围,根据文章分类查询
if (CollectionUtils.isNotEmpty(reqDTO.getCategories())) {
CriteriaBuilder.In<Object> builderIn = builder.in(root.get("category"));
for (Integer category : reqDTO.getCategories()) {
builderIn = builderIn.value(category);
}
predicates.add(builderIn);
}
return builder.and(predicates.toArray(new Predicate[predicates.size()]));
};
List<Article> articles = repository.findAll(spec);
}
}

这里具体的Article实体类、ArticleReqDTO实体类就不展示出来了,太占地方,意会即可。但是ArticleRepository还是要看一下,里面不仅要继承JpaRepository,还要继承另一个接口JpaSpecificationExecutor。看代码:

1
2
3
//泛型的Article是代表数据库的映射的类,Long代表主键的类型
public interface ArticleRepository extends JpaRepository<Article, Long>, JpaSpecificationExecutor<Article> {
}

上面在ArticleService类中使用到了findAll方法,但是在ArticleRepository类里面没有这个方法,那是因为在JpaRepositoryJpaSpecificationExecutor里面有很多内置的方法,这里使用的是Specification,很容易可以理解到使用的都是JpaSpecificationExecutor内的方法,这个接口里面的方法都很简单。(这些内置的方法也挺好用,方法很多)

组合条件的代码不是很难,就是从写SQL转换成用代码写有一个适应的过程,另外用SQL的话会更直观一点,这个代码写了以后需要在日志里面打印出SQL语句,检查SQL的正确性。代码中builder内还有很多可用的API,比如大于、小于、等于等操作,就不一一的列举啦。

Pageable+Sort分页排序

分页排序往往都是一起用的,Pageable的实现类PageResult本身的构造方法里面就支持自动构建Sort对象。

看下面的代码:

1
2
3
4
5
6
7
8
9
10
11
//省略部分……
public void articleList(final ArticleReqDTO reqDTO) {
Specification<Article> spec = (root, query, builder) -> {
List<Predicate> predicates = Lists.newArrayList();
//省略部分……
return builder.and(predicates.toArray(new Predicate[predicates.size()]));
};
Pageable pageable = new PageRequest(reqDTO.getPageNo() - 1
, reqDTO.getPageSize(), Sort.Direction.ASC, "createTime");//按照createTime升序
Page<Article> pageInfo = repository.findAll(spec, pageable);
}

这里代码的省略部分和上面介绍Specification的代码一样,可以参考,这里就不写出来啦。重点代码是最后两行。需要注意的就是pageNo参数,JPA是从0开始,如果外部传入的页码是从1开始,就需要做减1的操作;另外就是这里看似没有涉及到Sort,但是实际是用到的,看PageResult构造方法源码如下:

1
2
3
4
public PageRequest(int page, int size, Direction direction, String... properties) {
//使用后两个参数,构建了Sort对象,排序支持多个字段
this(page, size, new Sort(direction, properties));
}

其他都是常规操作啦。具体查到的数据,已经和分页相关的参数都封装在pageInfo里面。

还有一种组合是Specification+Sort对查询的数据按照指定格式排序,Sort的创建可以参考一下上面的代码,也可以直接去看源码,比较简单,不赘述。

总结

草草的写了三篇关于JPA的博客,写了基本的用法,从起初第一篇的Repository接口的创建、实体类的注解说明,基本的操作,然后第二篇写了JPA的特性之根据方法自动生成SQL,给出了一些常用的模板。最后就是这一篇,涉及到手动写SQL和一些单表的复杂SQL编写。基本的使用已经完全没有问题了,但是这些都是单表操作,如果涉及多表联查,就会不太好使,为了暂时的方便,在项目开发的过程中都是直接写native SQL,放在@Query注解里面。虽然个人是极其不推荐的,可是真是JPA对连表查询不友好,操作也比较麻烦。

以后如果很闲的话,可能会更新写JPA连表查的相关博客。之所以不写有下面几个原因:

  • 如果只是简单表联查,完全可以拆成多次来查,有时候多次查比连表查更方便快捷,效率上往往单表操作比联表查更高
  • 如果涉及到非常复杂的连表查询,即使你会用JPA连表查的操作,你也不会去做,涉及的代码量很大,而且不方便,这个时候不如写native SQL来的快,效率高
  • 虽然不推荐写native SQL,但是项目中需要使用到很复杂SQL查询的地方一般都是很少的,对这些复杂查询使用了复杂SQL,以后切换数据库改动量也不大

总结:单表操作直接怼,简单联查拆开搞,复杂SQL不用怕,本地SQL来补充,代码优雅最大化,切库改动也不大。


本文作者:IT-CRUD
原文地址:http://blog.itcrud.com/blogs/2019/05/spring-jpa-base-customize-sql-original
版权归作者所有,转载请注明出处

支付宝 微信

如果文章对你有帮助,欢迎点击上方按钮打赏作者