一. 概述

简单的增删改查使用 JPA 非常方便，复杂的 Where 条件我们也在 Specification 和 CriteriaBuilder 的基础上封装了 WhereBuilder，实现了类似 C# 的 LINQ 的写法，但是在很多情况下是需要使用 SQL 的，而且很多情况下原生 SQL 效率高很多。
JPA 支持二种最基本的 SQL 方式，Native SQL 和 JPQL，JPQL 语法非常类似于SQL语法，但是是针对 Java 对象和实体。我更倾向原生 SQL，毕竟 SQL 是通用的标准，开发人员或多或少都是掌握 SQL 的。
以下是在 JPA 里使用 SQL 的一些总结，不同的情况下有不同的应对方式。

二. @Query注解的方式

我们可以直接在 DAO 的接口里定义 SQL，以下列出了7种方式:

@Repository
public interface UserDao extends JpaRepository<UserEntity, String>, JpaSpecificationExecutor<UserEntity> {
    @Query(nativeQuery = true, value = "select * from d1_user where name like %?1%")
    Optional<UserEntity> test1(String name);

    @Query(nativeQuery = true, value = "select * from d1_user where name like %?1%")
    Optional<UserVm> test2(String name);

    @Query(nativeQuery = true, value = "select * from d1_user where name like %?1%")
    Optional<IUserVm> test3(String name);

    @Query("select new d1.dxdevices.iot.user.model.UserVm( u.name,u.id,u.remark) from UserEntity u where u.name like %?1%")
    Optional<UserVm> test4(String name);

    @Query(nativeQuery = true, value = "select * from d1_user where name like %?1%")
    Optional<Object> test5(String name);

    @Query(nativeQuery = true, value = "select * from d1_user where callback_url is null")
    Page<UserEntity> test6(Pageable pageable);

    @Query(nativeQuery = true, value = "select * from d1_user where callback_url is null",
            countQuery = "select count(*) from d1_user where callback_url is null")
    Page<UserEntity> test7(Pageable pageable);
}

1. test1

@Query(nativeQuery = true, value = "select * from d1_user where name like %?1%")
Optional<UserEntity> test1(String name);

使用原生 SQL 一定需要加上 nativeQuery = true, 动态参数使用 ?1这种问号加数字的方式，比较特殊的是字段和表名一定得用上数据库真实的值，比如 UserEntity 对应的表名是 d1_user,原生的 SQL 是不可以 select * from UserEntity。
还有一个就是驼峰的双词字段，需要换成下划线的方式，比如

/**
  * API回调地址
*/
@Column(length = 255)
@Comment("API回调地址")
private String callbackUrl;

UserEntity 下的字段 callbackUrl 映射到表里面字段名就变成 callback_url了，原生的 SQL 是不可以 select callbackUrl from d1_user,而应该是select callback_url from d1_user,总之原生 SQL 是可以直接拷贝到 Navicat 等 SQL 工具里直接执行成功的。

2. test2

@Query(nativeQuery = true, value = "select * from d1_user where name like %?1%")
Optional<UserVm> test2(String name);

和 test1 的差别是test1返回的就是 UserEntity ，但是 test2 返回的是 UserVm，是我们自己定义的一个实体对象，它的字段和 UserEntity 部分字段一样。这样的情况很常见，我们需要查询出数据转换成我们定义的实体对象，而不是数据库对应的 Entity。
实际上这个 test2 是执行失败的，这样是不行的，会报 Converter 错误，因为我们没有定义映射关系，即使 UserVm 和 UserEntity 字段完全一样也不行。

3. test3

@Query(nativeQuery = true, value = "select * from d1_user where name like %?1%")
Optional<IUserVm> test3(String name);

为了解决 test2 不行的问题，我们对应有几种解决方式，最简单的就是把实体类换成 interface 就可以了。

public interface IUserVm {
    String getId();
    LocalDateTime getCreateTime();
    String getName();
    String getCallbackUrl();
    String getCallback_Url();
}

但是注意因为是原生 SQL ，IUserVm 里的 get 方法也必须和表里面的字段保持一致，参考上面，其中 getCallbackUrl() 就不可以，而 getCallback_Url() 才能获取到值

4. test4

@Query("select new d1.dxdevices.iot.user.model.UserVm( u.name,u.id,u.remark) from UserEntity u where u.name like %?1%")
Optional<UserVm> test4(String name);

为了解决 test2 不行的问题,还有一种办法，就是不用原生 SQL，使用 JPQL ，可以直接在 select 里面加上自定义实体类的构造函数，注意必须把实体类的完整 package 写全。这个时候表名和字段名又不能按数据库表的来了，得按 Entity 对象定义的来了。

5. test5

@Query(nativeQuery = true, value = "select * from d1_user where name like %?1%")
Optional<Object> test5(String name);

更通用的方式，或者说最原始的方式，类似我们早期用 JDBC 的方式，返回的是 ResultSet，是一个 Object 的数组，然后由调用者自己去做映射，以下是调用 test5 的代码

public void test5(){
  Optional<Object> optionalUserEntity = userDao.test5("ad");
  if (optionalUserEntity.isEmpty()) {
      return;
  }
  Object obj = optionalUserEntity.get();
  UserVm userVm = new UserVm(obj);
  System.out.println(userVm.getName());
}

返回的 Object 其实是一个 Object[] 数组，我们可以给 UserVm里增加一个构造函数，参数是这个数组。

public UserVm(Object obj) {
  if (obj instanceof Object[] objs) {
      this.name = objs[0].toString();
      //......
  }
}

这里要注意的是，这个数组里对应的元素的顺序是按表里定义字段的顺序，一点儿也不能错

6. test6

@Query(nativeQuery = true, value = "select * from d1_user where callback_url is null")
Page<UserEntity> test6(Pageable pageable);

原生的 SQL 要支持分页可以通过在 sql 语句里面加 limit offset 之类的关键字，但是更简单的方式是使用 Pageable ，这个我们也一直在使用，也就是加上 Pageabele 参数后，JPA 会在 sql 后面自动加上 limit 和 offset，我相信切换一个其他类型的数据库， JPA 会自动拼接特定的关键字。
另外为了返回 Page 类型的结构体，需要查询总数，JPA 会根据你的 sql 自动拼接出查询总数的 sql 语句。

Hibernate: /* dynamic native SQL query */ select * from d1_user where callback_url is null limit ? offset ?
Hibernate: /* dynamic native SQL query */ select count(*) from d1_user

7. test7

@Query(nativeQuery = true, value = "select * from d1_user where callback_url is null",countQuery = "select count(*) from d1_user where callback_url is null")
Page<UserEntity> test7(Pageable pageable);

相比 test6 ，test7 增加了一个 countQuery,在当前的例子里，这个写不写都没有问题，但是在一些特殊的情况下，你的 sql 比较复杂的时候，有可能 JPA 无法准确正确的自动拼接出查询总数的 sql 语句，所以 JPA 提供了这个属性来让开发者自己定义查询总数的 sql。

8. 动态 sql

举一个例子，我们根据用户来查询这个用户名下的所有设备，假如传递过来的用户是admin，则返回所有设备，否则就返回这个用户下的设备。如果用原生 SQL 的方式则比较麻烦了。

@Query(nativeQuery = true, value = "select * from d1_device where name=?1")
Page<DeviceEntity> test8(String name,Pageable pageable);

如果按照以上的写法是无法实现这个需求的。JPA 提供了一个 QueryRewriter 来实现这个功能，可惜是 JPA 3.0 才有的功能，而我们现在用的是 JPA 2.6，所以我没有写相关的例子，基本用法也很简单，类似:

@Query(value = "select original_user_alias.* from SD_USER original_user_alias",nativeQuery = true, queryRewriter = MyQueryRewriter.class)
List<User> findByNativeQuery(String param);

然后定义一个 MyQueryRewriter

public class MyQueryRewriter implements QueryRewriter {
    @Override
    public String rewrite(String query, Sort sort) {
        if(query.contain("admin"){//在执行查询前根据条件修改sql
        return query.repalceAll("name=admin","1=1");
      }else{
        return query;
      }
    }
}

注意：以上代码我没有验证，因为没有 JPA3 的环境

9. 总结

最合适的方式我推荐的是 test1、test3、test6，也就是用 interface 的方式支持自定义实体类和用 Pageable 支持分页。

三. 代码的方式执行原生 SQL

这种方式繁琐一点，但是完全靠代码来实现，类似 JDBC 的方式，是最灵活的，可以实现各种复杂的需求。

我们首先在 dao（Repository）定义一个 test8() 函数

@Repository
public interface UserDao extends JpaRepository<UserEntity, String>, JpaSpecificationExecutor<UserEntity> {
   //这里要做一个关联查询，关联设备表和用户表，但是不用 @Query注解
   List test8(String name);
}

正常我们是需要编写一个类 实现这个接口，实现test8(),如下:

@Service
public class UserDaoService implements UserDao{
    @override
  List test8(String name){
    //......
  }
}

但是这样写是不合适的，因为如果这样写需要实现所有方法，包括 JpaRepository 和 JpaSpecificationExecutor 下有几十个方法，很显然这个不合适。

JPA 有一个约定，很好的解决这个问题，也就是在你定义一个实现类，类名是你定义的 Repository 类后加上后缀 Impl 就可以，而不需要显式的实现接口，也就是 ** 不需要 ** 写 implements UserDao ,如下

@Service
public class UserDaoImpl {
    private final Logger log = LoggerFactory.getLogger(this.getClass());
    private final EntityManager manager;

    public UserDaoImpl(EntityManager manager) {
        this.manager = manager;
    }

    public List test8(String name) {
        String nativeSql = "SELECT u.name,d.remark FROM d1_user u,d1_device d WHERE d.user_id=u.id and u.name like CONCAT('%', :name, '%')";
        Query query = manager.createNativeQuery(nativeSql);
        query.setParameter("name", name);
        List list = query.getResultList();
        //做一些相应的处理转换成我们需要的实体对象
        return list;
    }
}

看上去 UserDaoImpl 和 UserDao没有任何关系，但是 JPA 执行的时候会自动调用 UserDaoImpl 的 test8 方法，剩下就是开发者自己编写 SQL 语句，可以设置参数,冒号加名称表示一个参数变量。
这里弄了一个关联查询，我们返回的是一个 List ，里面是一个 Object，需要把 Object 转换成自定义的实体对象。