Spring-Data-JPA @Query annotation Sort sorting

When it is difficult to use method names to achieve the desired query results, we can use @Query to query.@QueryIt is a convenient way to add custom queries.

Using @QueryAnnotation, there are two ways to use annotation, one is JPQL SQL language way, one is native SQL language.

1.User.java Entity class

1. package com.niugang.entity;
2. import javax.persistence.Column;
3. import javax.persistence.Entity;
4. import javax.persistence.GeneratedValue;
5. import javax.persistence.GenerationType;
6. import javax.persistence.Id;
7. import javax.persistence.Table;
8. //javax.persistence.Entity
9. @Entity
10. @Table(name=“user”)
11. public class User {
12. /**
13. *mysql Self generated primary key
14. *Indicates that the persistence provider must allocate the primary key of the entity using the database identification column.
15. */
16. @Id
17. @GeneratedValue(strategy = GenerationType.IDENTITY)  
18. @Column(name=“id” ,insertable=false,updatable=false)
19. private Integer id;
20. /**
21. * @ColumnThe name in the default is the same as the default field name.
22. */
23. @Column(name = “name”)
24. private String name;
25. @Column(name = “age”)
26. private Integer age;
27. @Column(name = “phone”)
28. private String phone;
29. @Column(name = “password”)
31. private String password;
33.        public String getPassword() {
34. return password;
35. }
36. public void setPassword(String password) {
37. this.password = password;
38. }
39. public Integer getId() {
40. return id;
41. }
42. public void setId(Integer id) {
43. this.id = id;
44. }
45. public String getName() {
46. return name;
47. }
48. public void setName(String name) {
49. this.name = name;
50. }
51. public Integer getAge() {
52. return age;
53. }
54. public void setAge(Integer age) {
55. this.age = age;
56. }
57. public String getPhone() {
58. return phone;
59. }
60. public void setPhone(String phone) {
61. this.phone = phone;
62. }
63. @Override
64. public String toString() {
65. return “User [id=” + id + “, name=” + name + “, age=” + age + “, phone=” + phone + “, password=” + password
66. + “]”;
67. }
69. }

2.UserDao.java

1. package com.niugang.dao;
2. import java.util.List;
3. import org.springframework.data.domain.Page;
4. import org.springframework.data.domain.Pageable;
5. import org.springframework.data.jpa.repository.JpaRepository;
6. import org.springframework.data.jpa.repository.Query;
7. import org.springframework.data.repository.query.Param;
8. import org.springframework.stereotype.Repository;
9. import com.niugang.entity.User;
10. /**
11.  * JpaRepository springbootIt has been configured automatically, and has also been injected into the data source.
12.  * 
13.  * @author niugang
14.  *
15.  */
16. @Repository
17. public interface UserDao extends JpaRepository<User, Integer> {
18. /**
19. * ？Add numbers to indicate placeholders? 1 represents the first parameter in the method parameter, which is different from other index, starting from 1.
20. */
21. // select * from User where name = ?1 Be careful not to write *
22. @Query(value = “select u from  User u where u.name = ?1”)
23. User findUserByName1(String name);
26. /**
27. * =:Add the variable name, which is in the method parameter.@ParamThe value matching is not the same as the actual one, but is similar to the first one above.
28. */
29. @Query(value = “select u from  User u where u.name = :name”)
31. User findUserByName2(@Param(“name”) String username);
33.       /**
34. * Query through native SQL
35. * To open nativeQuery=true, you can use the native SQL statement to complete the query in value.
36. */
37. @Query(nativeQuery = true, value = “select * from  user u where u.name = :name”)
38. User findUserByNativeSQL(@Param(“name”) String username);
41. /**
42. * The number of fuzzy queries here can only be placed in front of the placeholder.
43. */
44. @Query(value = “select u from  User u where u.name like  %?1% “)
45. List<User> findUserLike(String name);
48. /**
49. * Conditional paging query Pageable:spring-data-jpa self contained interface
50. * 
51. * @param name
52. *            :Query condition name
53. * @param page:Paging object
54. * @return
55. */
56. @Query(value = “select u from  User u where u.name like  %?1% “)
58. Page<User> findUserLikeByPage(String name, Pageable page);
60.      /**
61. * @QueryQuery is not supported, sort and pagination.
62. * Source code analysis
63. * if (hasParameterOfType(method, Pageable.class)) {
66. if (!isStreamQuery()) {
67. assertReturnTypeAssignable(method, Slice.class, Page.class, List.class);
68. }
71. if (hasParameterOfType(method, Sort.class)) {
72. throw new IllegalStateException(String.format(“Method must not have Pageable *and* Sort parameter. “
73. + “Use sorting capabilities on Pageble instead! Offending method: %s”, method.toString()));
74. }
75. }
76. * 
77. * @param sort ：Sorting object org.springframework.data.domain.Sort
78. * @return
79. */
80. @Query(value = “select u from  User u”)
81. List<User> findUserLikeBySort(Sort sort);
83. }

3.   findUserLikeByPage(String name, Pageable page)Interface explanation:

1.  public Page<User> findUserByPage(String name) {
2.   PageRequest pageRequest = new PageRequest(0, 4);
3.   return userDao.findUserLikeByPage(name, pageRequest);
5. }

4.Sorting test code

1. /**
2. * Single conditional sort
3. */
4. public List<User> findListSortSingleCondition(){
5. //idAscending query
6. Sort sort = new Sort(Sort.Direction.ASC,“id”);
7. return userDao.findUserLikeBySort(sort);
8. }
9. /**
10. * Multi condition ranking
11. */
12. public List<User> findListSortMultiCondition(){
13. List<Order> orders=new ArrayList<Order>();
14. Order orderId = new Sort.Order(Sort.Direction.DESC,“id”);
15. Order orderAge = new Sort.Order(Sort.Direction.DESC,“age”);
16. orders.add(orderId);
17. orders.add(orderAge);
18. Sort sort = new Sort(orders);
19. return userDao.findUserLikeBySort(sort);
20. }