概述

本文将演示如何在Spring Data JPA中使用@Query注解来执行JPQL(Java Persistence Query Languag)和原生SQL查询。

查询语言

• JPQL

默认情况下，@Query查询定义使用JPQL。比如从数据库中返回活动用户实体：

@Query("SELECT u FROM User u WHERE u.status = 1")
Collection<User> findAllActiveUsers();

• Native SQL

还可以使用原生SQL来定义查询。将nativeQuery属性的值设置为true，并在注释的value属性中定义SQL查询：

@Query(
  value = "SELECT * FROM USERS u WHERE u.status = 1", 
  nativeQuery = true)
Collection<User> findAllActiveUsersNative();

排序查询

可以将Sort类型的参数传递给具有@Query注解的Spring Data方法，它将被转换为传递给数据库的ORDER BY子句。

• JPA提供的排序方法

比如findAll(Sort)或包含OrderBy方法，只能使用对象的属性来定义排序：

userRepository.findAll(Sort.by(Sort.Direction.ASC, "name"));

如果要按name属性的长度排序：

userRepository.findAll(Sort.by("LENGTH(name)"));

执行上述代码时，会收到一个异常：

org.springframework.data.mapping.PropertyReferenceException: No property LENGTH(name) found for type User!

• JPQL

使用JPQL定义查询时，添加一个Sort类型的方法参数：

@Query(value = "SELECT u FROM User u")
List<User> findAllUsers(Sort sort);

调用此方法并传递一个Sort参数，该参数将根据User对象的name属性对查询结果进行排序：

userRepository.findAllUsers(Sort.by("name"));

根据name属性的长度进行排序返回的用户列表：

userRepository.findAllUsers(JpaSort.unsafe("LENGTH(name)"));

使用JpaSort.unsafe()创建一个Sort对象实例是关键点：跳过检查要排序的属性是否属于实体域。

分页查询

• JPQL

@Query(value = "SELECT u FROM User u ORDER BY id")
Page<User> findAllUsersWithPagination(Pageable pageable);

可以传递PageRequest参数来获取一页数据。

• Native SQL

可以通过声明一个额外的属性countQuery来启用分页查询。

@Query(
  value = "SELECT * FROM Users ORDER BY id", 
  countQuery = "SELECT count(*) FROM Users", 
  nativeQuery = true)
Page<User> findAllUsersWithPagination(Pageable pageable);

索引参数查询

有两种方法可以将参数传递给查询语句：索引参数和命名参数。先介绍下索引参数：

• JPQL

对于JPQL中的索引参数，Spring Data将按照方法声明中出现的相同顺序向查询语句传递方法参数：

@Query("SELECT u FROM User u WHERE u.status = ?1")
User findUserByStatus(Integer status);

@Query("SELECT u FROM User u WHERE u.status = ?1 and u.name = ?2")
User findUserByStatusAndName(Integer status, String name);

• Native SQL

索引参数的传递方式与与JPQL完全相同：

@Query(
  value = "SELECT * FROM Users u WHERE u.status = ?1", 
  nativeQuery = true)
User findUserByStatusNative(Integer status);

命名参数查询

用@Param注解的每个参数都必须有一个与相应的JPQL或SQL查询参数名称匹配。

• JPQL

@Query("SELECT u FROM User u WHERE u.status = :status and u.name = :name")
User findUserByStatusAndNameNamedParams(
  @Param("status") Integer userStatus, 
  @Param("name") String userName);

• Native SQL

与JPQL相比传递参数的方式没有区别：

@Query(value = "SELECT * FROM Users u WHERE u.status = :status and u.name = :name", 
  nativeQuery = true)
User findUserByStatusAndNameNamedParamsNative(
  @Param("status") Integer status, @Param("name") String name);

集合参数

JPQL或Native SQL的where子句包含IN（或NOT IN）关键字的情况：

SELECT u FROM User u WHERE u.name IN :names

可以定义一个以Collection作为参数的查询方法：

@Query(value = "SELECT u FROM User u WHERE u.name IN :names")
List<User> findUserByNameList(@Param("names") Collection<String> names);

该参数是一个Collection，可以是List、HashSet等。

使用@Modifying更新查询

可以使用@Query注解来执行SQL修改，需要同时添加@Modifying注解。

• JPQL

返回值表示执行更新的行数。索引参数和命名参数都可以在更新查询中使用。

@Modifying
@Query("update User u set u.status = :status where u.name = :name")
int updateUserSetStatusForName(@Param("status") Integer status, 
  @Param("name") String name);

• Native SQL

@Modifying
@Query(value = "update Users u set u.status = ? where u.name = ?", 
  nativeQuery = true)
int updateUserSetStatusForNameNative(Integer status, String name);

• 执行插入新增操作

@Modifying
@Query(
  value = 
    "insert into Users (name, age, email, status) values (:name, :age, :email, :status)",
  nativeQuery = true)
void insertUser(@Param("name") String name, @Param("age") Integer age, 
  @Param("status") Integer status, @Param("email") String email);

动态查询

通常，我们会遇到需要基于在运行时才知道其值的条件或数据集构建SQL语句的情况。在这种情况无法使用静态查询。

• 动态查询示例

例如，我们需要从运行时定义的集合中选择电子邮件为模糊匹配email1，email2，…，emailn的所有用户：

SELECT u FROM User u WHERE u.email LIKE '%email1%' 
    or  u.email LIKE '%email2%'
    ... 
    or  u.email LIKE '%emailn%'

由于集合是动态构造的，无法在编译时知道要添加多少LIKE子句。通过实现自定义扩展基本JpaRepository功能，构建动态查询逻辑。

• 使用JPA标准API

创建自定义储存库接口：

public interface UserRepositoryCustom {
    List<User> findUserByEmails(Set<String> emails);
}

需要确保自定义存储库类名包含Impl后缀，Spring将根据UserRepositoryCustomj接口搜索UserRepositoryCustomImpl实现类。

public class UserRepositoryCustomImpl implements UserRepositoryCustom {

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public List<User> findUserByEmails(Set<String> emails) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<User> query = cb.createQuery(User.class);
        Root<User> user = query.from(User.class);

        Path<String> emailPath = user.get("email");

        List<Predicate> predicates = new ArrayList<>();
        for (String email : emails) {
            predicates.add(cb.like(emailPath, email));
        }
        query.select(user)
            .where(cb.or(predicates.toArray(new Predicate[predicates.size()])));

        return entityManager.createQuery(query)
            .getResultList();
    }
}

• 扩展存储库接口

使用自定义扩展库接口扩展UserRepository定义：

public interface UserRepository extends JpaRepository<User, Integer>, UserRepositoryCustom {
    //  query methods from section 2 - section 7
}

• 使用自定义存储库

最后就可以调用动态查询方法：

Set<String> emails = new HashSet<>();
// filling the set with any number of items

userRepository.findUserByEmails(emails);

结论

@Query注解一般用于比较简单的数据库SQL操作场景，涉及复杂操作一般采用类似QueryDSL，MyBatis等扩展实现库。