Spring Boot JPA – Querydsl

Querydsl is a type-safe Java query framework that supports JPA, JDO, JDBC, Lucene, Hibernate Search and other standards. Type safety and Consistency are the two principles of its design. Spring Boot can make up for the inflexibility of JPA and achieve more powerful logic.

Rely on

<dependency> <groupId>com.querydsl</groupId> <artifactId>querydsl-apt</artifactId> <scope>provided</scope> </dependency>  <dependency> <groupId>com.querydsl</groupId> <artifactId>querydsl-jpa</artifactId> </dependency>Copy the code

The Maven APT plugin is used to automatically generate classes:

<project> <build> <plugins> ... < plugin > < groupId > com. Mysema. Maven < / groupId > < artifactId > apt - maven - plugin < / artifactId > < version > 1.1.3 < / version > <executions> <execution> <phase>generate-sources</phase> <goals> <goal>process</goal> </goals> <configuration> <outputDirectory>target/generated-sources</outputDirectory> <processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor> </configuration> </execution> </executions> </plugin> . </plugins> </build> </project>Copy the code

The basic concept

For each Model (annotated with @javax.persistence.Entity), Querydsl generates a class starting with Q (default, configurable) in the same package to facilitate query operations. Such as:

JPAQueryFactory queryFactory = new JPAQueryFactory(entityManager); List<Person> persons = queryfactory.selectfrom (Person). Where (person.firstname.eq ("John"), person.lastName.eq("Doe")) .fetch(); List<Person> persons = queryfactory.selectFrom (Person).orderby (person.lastname.asc (), person.firstName.desc()) .fetch(); List<Person> persons = queryfactory.selectFrom (Person). Where (person.children.size().eq() JPAExpressions.select(parent.children.size().max()) .from(parent))) .fetch(); List<Tuple> tuples = queryFactory.select(person.lastname, person.firstname, person.yearOfBirth) .from(person) .fetch();Copy the code

Is it powerful? . Let’s look at a more concrete example:

The instance

Following a few tables from the previous chapter, do a little more advanced query:

Spring provides a convenient way to use Querydsl, you just need to inherit QueryDslPredicateExecutor in the Repository:

@Repository
public interface UserRepository extends JpaRepository<User, Long>, QueryDslPredicateExecutor<User> {
}Copy the code

You can then seamlessly connect to Querydsl using UserRepository:

userRepository.findAll(user.name.eq("lufifcc")); FindAll (user.email.endswith ("@gmail.com"). And (user.name.startswith ("lu"))) .and(user.id.in(Arrays.asList(520L, 1314L, 1L, 2L, 12L))) ); // All Gmail users with names starting with lu, And ID in 520L, 1314L, 1L, 2L, 12 l userRepository. Count (user. Email. EndsWith (" @ outlook.com "). And (user) name) containsIgnoreCase (" a "))); // Outlook user, Userrepository.findall (user.email.endswith ("@outlook.com"). And (user.posts.size().goe(5))); // Outlook user, Userrepository.findall (user.posts.size().eq(JPAExpressions. Select (user.posts.size().max()).from(user))); // The user with the most articlesCopy the code

In addition, Querydsl can also use SQL mode query, adding dependencies:

<dependency>
    <groupId>com.querydsl</groupId>
    <artifactId>querydsl-sql</artifactId>
</dependency>Copy the code

1. Then, get all user email addresses:

@GetMapping("users/emails") public Object userEmails() { QUser user = QUser.user; return queryFactory.selectFrom(user) .select(user.email) .fetch(); } / / return [" [email protected] ", "[email protected]", "[email protected]", "[email protected]"]Copy the code

2. Obtain all user names and email addresses:

@GetMapping("users/names-emails") public Object userNamesEmails() { QUser user = QUser.user; return queryFactory.selectFrom(user) .select(user.email, user.name) .fetch() .stream() .map(tuple -> { Map<String, String> map = new LinkedHashMap<>(); map.put("name", tuple.get(user.name)); map.put("email", tuple.get(user.email)); return map; }).collect(Collectors.toList()); } / / return [{" name ":" Lufficc ", "email" : "[email protected]"}, {" name ":" Allen ", "email" : "[email protected]"}, {" name ": "Mike", "email": "[email protected]" }, { "name": "Lucy", "email": "[email protected]" } ]Copy the code

Note that we can use the same expression used in the query to get a type-safe value, such as name: tupl.get (user.name), which returns a String value.

3. Get all user ids, names, and the number of posts they have:

@GetMapping("users/posts-count") public Object postCount() { QUser user = QUser.user; QPost post = QPost.post; return queryFactory.selectFrom(user) .leftJoin(user.posts, post) .select(user.id, user.name, post.count()) .groupBy(user.id) .fetch() .stream() .map(tuple -> { Map<String, Object> map = new LinkedHashMap<>(); map.put("id", tuple.get(user.id)); map.put("name", tuple.get(user.name)); map.put("posts_count", tuple.get(post.count())); return map; }).collect(Collectors.toList()); } / / return [{" id ": 1," name ":" Lufficc ", "posts_count" : 9}, {" id ": 2," name ":" Allen ", "posts_count" : 6}, {" id ": 3, "name": "Mike", "posts_count": 6 }, { "id": 4, "name": "Lucy", "posts_count": 6 } ]Copy the code

4. Obtain all user names and corresponding user namesJava 、PythonNumber of articles under category:

@GetMapping("users/category-count") public Object postCategoryMax() { QUser user = QUser.user; QPost post = QPost.post; NumberExpression<Integer> java = post.category .name.lower().when("java").then(1).otherwise(0); NumberExpression<Integer> python = post.category .name.lower().when("python").then(1).otherwise(0); return queryFactory.selectFrom(user) .leftJoin(user.posts, post) .select(user.name, user.id, java.sum(), python.sum(), post.count()) .groupBy(user.id) .orderBy(user.name.desc()) .fetch() .stream() .map(tuple -> { Map<String, Object> map = new LinkedHashMap<>(); map.put("username", tuple.get(user.name)); map.put("java_count", tuple.get(java.sum())); map.put("python_count", tuple.get(python.sum())); map.put("total_count", tuple.get(post.count())); return map; }).collect(Collectors.toList()); [{} / / return "username" : "Mike", "java_count" : 3, "python_count" : 1, "total_count" : 5}, {" username ": "Lufficc", "java_count": 2, "python_count": 4, "total_count": 9 }, { "username": "Lucy", "java_count": 1, "python_count": 1, "total_count": 5 }, { "username": "Allen", "java_count": 2, "python_count": 1, "total_count": 5}]Copy the code

Notice the use of powerful Case expressions.

5. Count the number of articles published each year, includingJava 、PythonNumber of articles under category:

@GetMapping("posts-summary") public Object postsSummary() { QPost post = QPost.post; ComparableExpressionBase<? > postTimePeriodsExp = post.createdAt.year(); NumberExpression<Integer> java = post.category .name.lower().when("java").then(1).otherwise(0); NumberExpression<Integer> python = post.category .name.lower().when("python").then(1).otherwise(0); return queryFactory.selectFrom(post) .groupBy(postTimePeriodsExp) .select(postTimePeriodsExp, java.sum(), python.sum(), post.count()) .orderBy(postTimePeriodsExp.asc()) .fetch() .stream() .map(tuple -> { Map<String, Object> map = new LinkedHashMap<>(); map.put("time_period", tuple.get(postTimePeriodsExp)); map.put("java_count", tuple.get(java.sum())); map.put("python_count", tuple.get(python.sum())); map.put("total_count", tuple.get(post.count())); return map; }).collect(Collectors.toList()); } // Return [{"time_period": 2015, "javA_count ": 1," python_COUNT ": 3, "total_count": 6}, {"time_period": 2016, "java_count": 4, "python_count": 2, "total_count": 14 }, { "time_period": 2017, "java_count": 3, "python_count": 2, "total_count": 7 } ]Copy the code

add

Spring parameters analytical com support. Querydsl. Core. Types. The Predicate, according to user’s request parameters automatically generate Predicate, so don’t have to write their own search method, such as:

@GetMapping("posts") public Object posts(@QuerydslPredicate(root = Post.class) Predicate predicate) { return postRepository.findAll(predicate); @getMapping ("posts") public Object posts(@querydslpredicate (root = post.class) Predicate, Pageable pageable) { return postRepository.findAll(predicate, pageable); }Copy the code

Then request:

/posts? Title =title01 // Return title to title01 /posts? Id =2 / * * * * * * * * * * * * * * * * * * * Category. Name =Python // Returns articles classified as Python (you read that right, can be nested, < posts? User.id =2&category. Name =Java > < posts? User.id =2&categoryCopy the code

Note that this does not cause SQL injection problems, because nonexistent properties are not valid to write, and Spring has already determined that. Add again, you can also modify the default behavior, inheritance QueryDslPredicateExecutor interface:

@Repository public interface PostRepository extends JpaRepository<Post, Long>, QueryDslPredicateExecutor<Post>, QuerydslBinderCustomizer<QPost> { default void customize(QuerydslBindings bindings, QPost post) { bindings.bind(post.title).first(StringExpression::containsIgnoreCase); }}Copy the code

So you can visit /posts again? Title =title01 returns all articles whose title contains title01, not all articles whose title is equal to title01.

conclusion

In this article, the knowledge of Querydsl is not fully reflected, and The Spring Boot official also provides good support, so, master Querydsl, really do not worry about Java can not write Sql, important is type safety (no cast), Cross-database (Querydsl is still a technology like JPA, so as long as you don’t write native Sql, basic relational databases are common). By the way, the source code here is example-jpa, you can directly run ~~ we have any good experience can also be put forward in the comments.

— END

Write good, to help you, sponsor the host fee, remember to leave a message oh, I will statistics ~

Alipay
WeChat

Copyright Notice: Freely reproduced – Non-commercial – Non-derivative – Remain signed (Creative Commons 3.0 License)