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

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

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

@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
@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注解，注明当前方法是修改操作。

如下代码：

@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解决了这个问题。如下代码：

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。看代码：

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

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

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

Pageable+Sort分页排序

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

看下面的代码：

//省略部分……
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构造方法源码如下:

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来补充，代码优雅最大化，切库改动也不大。