Spring Data JPA @Query查询

1.声明@Query

public interface UserRepository extends JpaRepository<User, Long> {
  @Query("select u from User u where u.emailAddress = ?1")
  User findByEmailAddress(String emailAddress);
}

2.like查询

public interface UserRepository extends JpaRepository<User, Long> {
  @Query("select u from User u where u.firstname like %?1")
  List<User> findByFirstnameEndsWith(String firstname);
}

3.本地查询 : nativeQuery = true 原始 SQL

public interface UserRepository extends JpaRepository<User, Long> {
  @Query(value = "SELECT * FROM USERS WHERE EMAIL_ADDRESS = ?1", nativeQuery = true)
  User findByEmailAddress(String emailAddress);
}

4.排序

1. @Query(value = "select * from user_info where first_name=?1 order by ?2",nativeQuery = true)
List<UserInfoEntity> findByFirstName(String firstName,String sort);

2. @Query("select u from User u where u.lastname like ?1%")
   List<User> findByAndSort(String lastname, Sort sort);

5.分页
1.    
 public interface UserRepository extends JpaRepository<User, Long> {
      @Query(value = "select u from User u where u.lastname = ?1")
      Page<User> findByLastname(String lastname, Pageable pageable);
    }
//调用者的写法
repository.findByFirstName("jackzhang",new PageRequest(1,10));

2.
public interface UserRepository extends JpaRepository<User, Long> {
  @Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1",
    countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
    nativeQuery = true)
  Page<User> findByLastname(String lastname, Pageable pageable);
}
不写countQuery 我试着也是OK的

6.@Param命名参数

public interface UserRepository extends JpaRepository<User, Long> {
  @Query("select u from User u where u.firstname = :firstname or u.lastname = :lastname")
  User findByLastnameOrFirstname(@Param("lastname") String lastname,
                                 @Param("firstname") String firstname);
}
7.动态查询
1. 原生
@Query(value = "SELECT * FROM user u"+
            " where 1=1 "+
            " and if (:name!= '', u.name=:name, 1 = 1 )",
            nativeQuery = true)
List<User> findUsers(String name);

2. JPQL
@Query("select u.name as name,u.email as email from User u where (:name is null or u.name =:name) and (:email is null or u.email =:email)")
   UserOnlyName findByUser(@Param("name") String name,@Param("email") String email);

总结:别人的观点
1.能用方法名表示的，尽量用方法名表示，因为这样语义清晰、简单快速，基本上只要编译通过，一定不会有问题；

2.能用 @Query 里面的 JPQL 表示的，就用 JPQL，这样与 SQL 无关，万一哪天换数据库了，基本上代码不用改变；

3.最后实在没有办法了，可以选择 nativeQuery 写原始 SQL，特别是一开始从 MyBatis 转过来的同学，选择写 SQL 会更容易一些。