08月
15

JPA-Criteria-Querydsl

发表于 • 分类于

JPA criteria 查询: 类型安全与面向对象

Criteria Query

实体定义： 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
@Entity
public class Advisor extends AbstractAuditingEntity implements Serializable { 
   
	private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "sequenceGenerator")
    @SequenceGenerator(name = "sequenceGenerator")
    private Long id;
         
	@NotNull
    @Column(name = "name", nullable = false)
    private String name;
    
    @NotNull
    @Column(name = "activated", nullable = false)
    private Boolean activated;
    
  	@OneToMany(mappedBy = "advisor", fetch = FetchType.LAZY)
    private Set<Product> products = new HashSet<>();
    
    @OneToMany(mappedBy = "advisor", fetch = FetchType.LAZY)
    @OrderBy("lastModifiedDate desc")
    private Set<Manager> managers = new HashSet<>();
      
    //...
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
@Entity
public class Product extends AbstractAuditingEntity implements Serializable {
  	   
  	private static final long serialVersionUID = 1L;
    
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "sequenceGenerator")
    @SequenceGenerator(name = "sequenceGenerator")
    private Long id;
      
    @NotNull
    @Column(name = "name", nullable = false)
    private String name;
      
    @NotNull
    @Column(name = "rate", nullable = false)
    private Double rate;
      
    @ManyToOne(fetch = FetchType.LAZY)
    private Advisor advisor;
      
    //...
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
@Entity
public class Manager extends AbstractAuditingEntity implements Serializable {
  	  
  	private static final long serialVersionUID = 1L;
      
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "sequenceGenerator")
    @SequenceGenerator(name = "sequenceGenerator")
    private Long id;
      
    @NotNull
    @Column(name = "name", nullable = false)
    private String name;
      
    @ManyToOne(fetch = FetchType.LAZY)
    private Advisor advisor;
      
    //...
}

元模型（Metamodel）

Hibernate JPA 2 Metamodel Generator

Criteria Query 完成 multi select 查询

现在要查询所有 Advisor 对象,并且查出 Advisor 对象中 managers 集合的个数和 products 集合中 rate 属性的最大值:

定义一个 DTO 对象,保存查询结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
public class AdvisorDto implements Serializable {
  private Long id;
  private String name;
  private Long managerNum;
  private Double maxRate;
  
  //multiselect查询语句返回值需要参数个数和类型都匹配的一个构造方法
  public AdvisorDto(Long id, String name, Long managerNum, Double maxRate) {
    this.id = id;
    this.name = name;
    this.managerNum = managerNum;
    this.maxRate = maxRate;
  }
  //...
}

定义查询接口和实现类

1
2
3
public interface AdvisorDtoRepository {
  Page<AdvisorDto> findAll(Boolean activated, String name, Pageable pageable);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
public class AdvisorDtoRepositoryImpl implements AdvisorDtoRepository {

	@PersistenceContext
    private EntityManager em;
    
  	public Page<AdvisorDto> findAll(Boolean activated, String name, Pageable pageable) {
    	CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<AdvisorDto> multiselectQuery = cb.createQuery(AdvisorDto.class);
        //定义查询根的实体类型
        Root<Advisor> root = multiselectQuery.from(Advisor.class);
        
        /*创建 select 部分要查询的属性,并分别定义别名*/
        Selection idSelection = root.get("id").alias("id");
        Selection nameSelection = root.get("name").alias("name");
        Selection managerNumSelection = cb.count(root.join("managers", JoinType.LEFT)).alias("managerNum");
        Selection maxRateSelection = cb.max(root.join("products", JoinType.LEFT).get("rate")).alias("maxRate");

        multiselectQuery.multiselect(idSelection, nameSelection, managerNumSelection, maxRateSelection);

		/*创建 where 字句的查询条件*/
        List<Predicate> predicates = new ArrayList<>();

        if (activated != null) {
            predicates.add(cb.equal(root.get(Advisor_.activated), activated));
        }
        if (!StringUtils.isEmpty(name)) {
            predicates.add(cb.like(root.get(Advisor_.name), "%" + name + "%"));
        }

        Predicate wherePredicate = cb.and(predicates.toArray(new Predicate[0]));

        //(Column "Advisor_.ID" must be in the GROUP BY list)   	multiselectQuery.where(wherePredicate).groupBy(root.get(Advisor_.id)).orderBy(cb.desc(root.get(Advisor_.lastModifiedDate)));
        
        /*获取查询结果*/
        List<AdvisorDto> results = em.createQuery(multiselectQuery)
            .setFirstResult(pageable.getOffset())
            .setMaxResults(pageable.getPageSize())
            .getResultList();

		/*查询总元素的个数*/
        CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
        countQuery.select(cb.count(countQuery.from(Advisor.class)));
        countQuery.where(wherePredicate);
        Long totalElements = em.createQuery(countQuery).getSingleResult();

		//创建 Page 对象
        Page<AdvisorDto> advisors = new PageImpl<AdvisorDto>((totalElements > pageable.getOffset() ? results : Collections.<AdvisorDto>emptyList()), pageable, totalElements);

        return advisors;
  	}
}
``` 

## Spring Data Jpa  

> [Spring Data JPA with QueryDSL: Repositories made easy](http://dontpanic.42.nl/2011/06/spring-data-jpa-with-querydsl.html)

Spring Data JPA can be used to dramatically minimize, and often even eliminate, a repository implementation. Imagine having the following domain class:




@Entity
public class User {

 @Id
 @GeneratedValue(strategy = GenerationType.AUTO)
 private Long id;

 private String username;
 private String firstname;
 private String lastname;

 ...
}


With plain Spring and JPA we would build the following repository implementation:




@Repository
public class JpaUserRepository implements UserRepository {

 @PersistenceContext
 private EntityManager em;
 
 public Iterable<User> findAll() {
  return em.createQuery(
    "from User", User.class
   ).getResultList();
 }

 public User findByUsername(String username) {
  return em.createQuery(
    "from User where username = :username", User.class
   ).setParameter("username", username).getSingleResult();
 }

 public User save(User user) {
  if(user.getId() == null) {
   em.persist(user);
  } else {
   user = em.merge(user);
  }
  return user;
 }
}


Much of the above repository implementation is boilerplate and can be generalized. This is where Spring Data JPA kicks in.




When using Spring Data JPA, we only need to define a repository interface, and the implementation will be provided automatically. So we define the repository interface:




public interface UserRepository
 extends CrudRepository<User, Long> {
 User findByUsername(String username);
}


And activate the automatic repository implementations in our application context:




<jpa:repositories base-package="com.myproject.repository" />



Now whenever we wire the UserRepository, we will recieve a fully functional repository implementation automatically. The repository will have full CRUD functionality, due to extending the CrudRepository interface, but also custom find behaviour.


Inside the repository interface we defined a findByUsername method, which basically finds a single user with the provided username. These type of findX methods are called “dynamic finder methods”, and will be resolved automatically whenever the naming convention is applied. Spring Data JPA offers quite extensive finder method support, which is more than enough for most repositories. For more information about dynamic finder methods, look at the reference documentation.




So we basically just created a full blown repository bean, having defined only three lines of code. Which is great! But what happens if queries get more complex and the dynamic finder behaviour won’t suffice?

### Named queries 
One of the easiest techniques is using JPA named queries. We simply define a @NamedQuery on our entity:




@Entity
@NamedQuery(
 name = "User.findByTheUsersName",
 query = "from User u where u.username = ?"
)
public class User { }


And create a corresponding finder method in our repository interface:




public interface UserRepository {
 User findByTheUsersName(String username);
}


So whenever the finder method is invoked, Spring Data JPA will automatically perform our named query and return its results.



### @Query 
使用注解直接定义一个查询语句在 repository 接口上： 
``` 
public interface UserRepository {
    @Query("select u from User u where u.firstname = ?")
    List<User> findByFirstname(String firstname);
}
``` 

### Custom implementation 
We could even create a custom implementation for some repository method(s). To provide a custom implementation we first define a UserRepositoryCustom interface, with all custom repository methods, and an implementation UserRepositoryImpl. Then we let our UserRepository extend from the custom interface, and Spring Data JPA will automatically wire our custom implementation into the repository bean.

A custom implementation is defined as follows:




public interface UserRepository extends
 UserRepositoryCustom, CrudRepository<User, Long> {
 // Generated finder method
 User findByUsername(String username);
}



public interface UserRepositoryCustom {
 // Custom method
 List<User> findInactiveUsers();
}



public class UserRepositoryImpl
 implements UserRepositoryCustom {

 @PersistenceContext
 private EntityManager em;

 @Override
 public List<User> findInactiveUsers() {
  return em.createQuery(
   "from User where active = false", User.class
  ).getResultList();
 }
}


So we can use Spring Data JPA to create our boilerplate implementations, and yet it is still possible to create custom methods as desired.

## Spring Data Jpa 的 Specification 查询 

> [SpringDataJpa 的 Specification 查询](http://blog.csdn.net/baijunzhijiang_01/article/details/51557125)  

Spring Data JPA 支持 JPA2.0 的 Criteria Query，相应的接口是 JpaSpecificationExecutor。Criteria 查询：是一种类型安全和更面向对象的查询 。

这个接口基本是围绕着 Specification 接口来定义的， Specification 接口中只定义了如下一个方法：

``` 
Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb);

Specification 查询: 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
public class AdvisorSpecs {
  public static Specification<Advisor> findAdvisors( final Boolean activated, final String name, final Long managerNum) {
    return (root, query, cb) -> {
    	List<Predicate> predicates = new ArrayList<>(); 
    
    	if (managerNum != null) {   		query.groupBy(root.get(Advisor_.id)).orderBy(cb.desc(root.get(Advisor_.lastModifiedDate))).having(cb.greaterThan((cb.countDistinct(root.join(Advisor_.managers, JoinType.LEFT))), managerNum));
        }
        if (activated != null) {
        	predicates.add(cb.equal(root.get(Advisor_.activated), activated));
        }
    	if (!StringUtils.isEmpty(name){
    		predicates.add(cb.like(root.get(Advisor_.name), "%" + name + "%"));
    	}
  
  		return cb.and(predicates.toArray(new Predicate[0]));
  	}
  }
}

Spring Data Jpa 查询: 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
@GetMapping("/advisors")
public String index(@RequestParam(value = "activated", required = false, defaultValue = "") Boolean activated,
@RequestParam(value = "name", required = false, defaultValue = "") String name,
@RequestParam(value = "managerNum", required = false, defaultValue = "") Long managerNum,
@PageableDefault(sort = "lastModifiedDate", direction = Sort.Direction.DESC) Pageable pageable, Model model) {
	Specification<Advisor> specification = 	Specifications.where(AdvisorSpecs.findAdvisors(activated, name, managerNum));

	Page<Advisor> advisors = advisorRepository.findAll(specification, pageable);

	model.addAttribute("activated", activated);
	model.addAttribute("name", name);
	model.addAttribute("managerNum", managerNum);

	return "advisors/list";
}

Querydsl

Querydsl 是一个类型安全的 Java 查询框架，支持 JPA, JDO, JDBC, Lucene, Hibernate Search 等标准。类型安全（Type safety）和一致性（Consistency）是它设计的两大准则。在 Spring Boot 中可以很好的弥补 JPA 的不灵活，实现更强大的逻辑。
Criteria Query 也是类型安全的，但它要求 CriteriaQuery, CriteriaBuilder 和 Root 对象，而 Querydsl 只需要一个生成的元数据模型（以大写字母 Q 开头加上 java 实体类型名称的类）。
而且 BooleanExpression 不和查询实例耦合，当和 JPA 联合使用时，我们可以在不同的表达式中使用它。

配置

QueryDSL+gradle+idea

build.gradle 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
dependencies {
    ... 
    compile "com.querydsl:querydsl-jpa:4.1.4"
    compile "com.querydsl:querydsl-apt:4.1.4:jpa"
    ...
}
``` 
启动项目后，会将项目中所有添加了 `@Entity` 注解的实体类会在 `src/main/generated/` 目录下生成对应的查询类型。每个查询类型都以大写字母Q开头。

修改默认生成路径，在 build.gradle 中添加： 
``` 
apply plugin: 'idea'
idea {
    module {
        sourceDirs += file('generated/')
        generatedSourceDirs += file('generated/')
    }
}
``` 

然后在 idea 中设置：
![](/images/2017-12-06-store-generated-sources-relative-to.png)   


### QueryDslPredicateExecutor 接口  
继承了 QueryDslPredicateExecutor 接口和继承了 JpaRepository 接口一样，可以直接使用其自带的方法： 
``` 
public interface QueryDslPredicateExecutor<T> {  
  
    T findOne(Predicate predicate);  
  
    Iterable<T> findAll(Predicate predicate);  
  
    Iterable<T> findAll(Predicate predicate, Sort sort);  
  
    Iterable<T> findAll(Predicate predicate, OrderSpecifier<?>... orders);  
  
    Iterable<T> findAll(OrderSpecifier<?>... orders);  
  
    Page<T> findAll(Predicate predicate, Pageable pageable);  
  
    long count(Predicate predicate);  
  
    boolean exists(Predicate predicate);  
}  
```  
示例： 
``` 
@GetMapping(value = {"", "/", "home", "/index", "/index.html"})
public String index(@PageableDefault Pageable pageable, Model model) {
    QBlog qBlog = QBlog.blog;
    Predicate predicate = qBlog.id.gt(2);
    // likeIgnoreCase() 方法实参前后要加上 % 
    predicate = qBlog.title.containsIgnoreCase("spring").and(predicate);
    // 搜索id大于2并且标题忽略大小写匹配“spring”字符串
    Page<Blog> blogs = blogRepository.findAll(predicate, pageable);
    model.addAttribute("articles", blogs);
    return "blogs/index";
}
``` 
然后请求： 
``` 
http://localhost:8008/blog

QueryDslPredicateExecutor 接口

Spring 参数支持解析 com.querydsl.core.types.Predicate，根据用户请求的参数自动生成 Predicate，这样搜索方法不用自己写啦，比如：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
@GetMapping(value = {"", "/", "home", "/index", "/index.html"})
public String index(@QuerydslPredicate(root = Blog.class) Predicate predicate, 
                    @PageableDefault Pageable pageable, Model model) {
    Page<Blog> blogs = blogRepository.findAll(predicate, pageable);
    model.addAttribute("articles", blogs);
    return "blogs/index";
}
``` 
然后请求：
``` 
http://localhost:8080/blog?id=1 //列表页面仅显示id为1的博客
```  
> 字符串会自动忽略大小写，但是不能模糊匹配，必须要全文匹配。

还可以自定义 Predicate，继承 QueryDslPredicateExecutor 接口： 
``` 
@GetMapping(value = {"", "/", "home", "/index", "/index.html"})
public String index(@QuerydslPredicate(root = Blog.class) Predicate predicate,
                    @PageableDefault Pageable pageable, Model model) {
    Page<Blog> blogs = blogRepository.findAll(predicate, pageable);
    model.addAttribute("articles", blogs);
    return "blogs/index";
}
``` 

``` 
public interface BlogRepository extends JpaRepository<Blog, Long>, QueryDslPredicateExecutor<Blog>, QuerydslBinderCustomizer<QBlog> {

    default void customize(QuerydslBindings bindings, QBlog root) {
        bindings.bind(root.id).first((path, value) -> path.gt(value));
        bindings.bind(root.title).first((StringPath path, String value) -> path.containsIgnoreCase(value));
    }
}

然后请求： 

1
http://localhost:8008/blog?id=2&title=xx // 列表页面仅显示id大于2且标题忽略大小写匹配xx字符串的博客

类 SQL 查询

spring boot-jpa整合QueryDSL来简化复杂操作

Spring Boot JPA - Querydsl

QueryDSL JPA 查询示例

配置 JPAQueryFactory： 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
import com.querydsl.jpa.HQLTemplates;
import com.querydsl.jpa.impl.JPAQueryFactory;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.persistence.EntityManager;

@Configuration
public class JpaConfiguration {

    @Bean
    public JPAQueryFactory jpaQueryFactory(EntityManager entityManager) {
        return new JPAQueryFactory(new HQLTemplates(), entityManager);
    }

}
``` 

查询： 
``` 
@Autowired
private JPAQueryFactory queryFactory;

@GetMapping(value = {"", "/", "home", "/index", "/index.html"})
public String index(@PageableDefault Pageable pageable, Model model) {
    QBlog qBlog = QBlog.blog;

//        String title = queryFactory.select(qBlog.title).from(qBlog).where(qBlog.id.eq(Long.valueOf(1))).fetchFirst();
//        System.out.println("title: " + title);

    List<Blog> blogList = queryFactory.selectFrom(qBlog)
            .where(qBlog.title.containsIgnoreCase("spring"))
            .offset(pageable.getOffset())
            .limit(pageable.getPageSize())
            .orderBy(qBlog.createdDate.desc())
            .fetch();
    Long totalElements = queryFactory.selectFrom(qBlog).where(qBlog.title.containsIgnoreCase("spring")).fetchCount();
    Page<Blog> blogs = new PageImpl<Blog>(blogList, pageable, totalElements);

    model.addAttribute("articles", blogs);

    return "blogs/index";
}
``` 

结合 @QuerydslPredicate 查询： 
``` 
@Autowired
private JPAQueryFactory queryFactory; 

@GetMapping(value = {"", "/", "home", "/index", "/index.html"})
public String index(@QuerydslPredicate(root = Blog.class) Predicate predicate,
                    @PageableDefault Pageable pageable, Model model) {
    QBlog qBlog = QBlog.blog;

    List<Blog> blogList = queryFactory.selectFrom(qBlog)
            .where(predicate)
            .offset(pageable.getOffset())
            .limit(pageable.getPageSize())
            .orderBy(qBlog.createdDate.desc())
            .fetch();

    Long totalElements = queryFactory.selectFrom(qBlog).where(predicate).fetchCount();
    Page<Blog> blogs = new PageImpl<Blog>(blogList, pageable, totalElements);

    model.addAttribute("articles", blogs);

    return "blogs/index";
}
``` 

然后请求： 

``` 
http://localhost:8008/blog?title=spring // 列表页显示忽略大小写模糊匹配”spring“字符串的博客

JPAExpressions

分享到：
Disqus 加载中...

如果长时间无法加载，请针对 disq.us | disquscdn.com | disqus.com 启用代理