In the previous article, we introduced the basic use of JPA, Spring Boot (3) : ORM framework JPA and connection pool Hikari. In this article, we take an introductory, advanced look at QueryDSL that puts wings on JPA.

1. The introduction

There is no denying that JPA is very convenient to use, very simple configuration, only need to use annotations, no XML configuration file, semantics simple to understand, but all of the above is based on the premise of single table query, we can use the default method provided by JPA, simple and easy to complete CRUD operations.

However, when it comes to multi-table dynamic queries, JPA’s capabilities are somewhat limited, although we can use the @query annotation, in which either SQL or HQL is concatenated strings, and the concatenated strings are not very readable. Of course, JPA also provides Specification to do this. The readability is fine from my own experience, but the usage of Predicate and CriteriaBuilder will probably dissuade a lot of beginners. What type is an Object[]? What is the field name? None of these can be obtained intuitively, and we need to manually map Object[] to the Model class we need, which is undoubtedly a very bad experience.

All of this came to an end with QueryDSL. QueryDSL syntax is very similar to SQL, the code is very readable, the exception introduction is elegant, and the JPA integration is very high without unnecessary configuration, which has been great from my personal experience. Suffice it to say, if you know how to write SQL, you can get to the starting level just by looking at the sample code.

2. QueryDSL profile

QueryDSL is an active open source project with 251 releases currently available on Github. The latest Release is 4.2.1. It is supported by both the Querydsl Google group and the StackOverflow team.

QueryDSL is a framework for constructing statically typed SQL-like queries. Instead of writing the query as an inline string or externalizing it as an XML file, you can construct the query through an API such as QueryDSL.

For example, there are advantages to using an API over simple strings

  • Code completion in the IDE

  • There are almost no queries with invalid syntax

  • Field types and properties can be referenced safely

  • Better refactoring of domain type changes

3. Actual QueryDSL usage

3.1 Importing Maven dependencies

Code listing: spring-boot-jpa-queryDSL/POM.xml

<! <groupId>com.querydsl</groupId> <artifactId> querydSL-apt </artifactId> <scope>provided</scope> </dependency> <! </groupId> </artifactId> querydSL-jpa </artifactId> </dependency>Copy the code
  • You don’t need to specify the version number herespring-boot-dependenciesDefined in engineering.

3.2 Adding the Maven plug-in

This plugin is added to allow the program to automatically generate query type (query entity, named “Q”+ corresponding entity name). Querydsl-apt serves this plug-in in the dependency introduced above.

Note: In the process of using, if the query type cannot be automatically generated, use Maven to Update the project to solve (right-click project -> Maven -> Update Folders).

Code listing: spring-boot-jpa-queryDSL/POM.xml

<plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> <plugin> <groupId>com.mysema.maven</groupId> <artifactId>apt-maven-plugin</artifactId> Executions > <execution> <goal>process</goal> </goals> <configuration> <outputDirectory>target/generated-sources/java</outputDirectory> <processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor> </configuration> </execution> </executions> </plugin>  </plugins>Copy the code

3.3 Updates and Deletes

JPA already provides a very easy way to use QueryDSL updates and deleters. We don’t need to use QueryDSL updates and deleters at all, but here’s how to use them:

Code listing: spring – the boot – jpa – querydsl/SRC/main/Java/com/springboot springbootjpaquerydsl/service/impl UserServiceImpl. Java

@Override public Long update(String id, String nickName) { QUserModel userModel = QUserModel.userModel; / / updatereturnqueryFactory.update(userModel).set(userModel.nickName, nickName).where(userModel.id.eq(id)).execute(); } @Override public Long delete(String id) { QUserModel userModel = QUserModel.userModel; / / deletereturn queryFactory.delete(userModel).where(userModel.id.eq(id)).execute();
}
Copy the code

3.2 the query

QueryDSL is very easy to do with queries. For example, some common ways to write select() and fetch() are as follows:

Code listing: spring – the boot – jpa – querydsl/SRC/main/Java/com/springboot springbootjpaquerydsl/service/impl UserServiceImpl. Java

@Override
public List<String> selectAllNameList() { QUserModel userModel = QUserModel.userModel; // Query the fieldreturn queryFactory.select(userModel.nickName).from(userModel).fetch();
}

@Override
public List<UserModel> selectAllUserModelList() { QUserModel userModel = QUserModel.userModel; // Query the entityreturn queryFactory.selectFrom(userModel).fetch();
}

@Override
public List<UserDTO> selectAllUserDTOList() { QUserModel userModel = QUserModel.userModel; QLessonModel lessonModel = QLessonModel.lessonModel; // Connect the table to the query entity and encapsulate the results to the DTOreturn queryFactory
            .select(
                    Projections.bean(UserDTO.class, userModel.nickName, userModel.age, lessonModel.startDate, lessonModel.address, lessonModel.name)
            )
            .from(userModel)
            .leftJoin(lessonModel)
            .on(userModel.id.eq(lessonModel.userId))
            .fetch();
}

@Override
public List<String> selectDistinctNameList() { QUserModel userModel = QUserModel.userModel; // Delete the queryreturn queryFactory.selectDistinct(userModel.nickName).from(userModel).fetch();
}

@Override
public UserModel selectFirstUser() { QUserModel userModel = QUserModel.userModel; // Query the first entityreturnqueryFactory.selectFrom(userModel).fetchFirst(); } @Override public UserModel selectUser(String id) { QUserModel userModel = QUserModel.userModel; // Query a single entity, if there are multiple results, throw 'NonUniqueResultException'.return queryFactory.selectFrom(userModel).fetchOne();
}
Copy the code

3.4 Complex Query Operations

Simple queries are listed above, but in practice we will encounter quite complex operations, such as subquery, multi-condition query, and multi-table query. The example is as follows:

Code listing: spring – the boot – jpa – querydsl/SRC/main/Java/com/springboot springbootjpaquerydsl/service/impl LessonServiceImpl. Java

@Service
public class LessonServiceImpl implements LessonService {

    @Autowired
    JPAQueryFactory queryFactory;

    @Override
    public List<LessonModel> findLessonList(String name, Date startDate, String address, String userId) throws ParseException {
        QLessonModel lessonModel = QLessonModel.lessonModel;
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); // Example of querying multiple conditionsreturn queryFactory.selectFrom(lessonModel)
                .where(
                        lessonModel.name.like("%" + name + "%")
                        .and(lessonModel.address.contains(address))
                        .and(lessonModel.userId.eq(userId))
                        .and(lessonModel.startDate.between(simpleDateFormat.parse("The 2018-12-31 00:00:00"), new Date()))
                )
                .fetch();
    }

    @Override
    public List<LessonModel> findLessonDynaList(String name, Date startDate, String address, String userId) throws ParseException {
        QLessonModel lessonModel = QLessonModel.lessonModel;
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); // BooleanBuilder builder = new BooleanBuilder();if(! StringUtils.isEmpty(name)){ builder.and(lessonModel.name.like("%" + name + "%"));
        }

        if(startDate ! = null) { builder.and(lessonModel.startDate.between(simpleDateFormat.parse("The 2018-12-31 00:00:00"), new Date()));
        }

        if(! StringUtils.isEmpty(address)) { builder.and(lessonModel.address.contains(address)); }if(! StringUtils.isEmpty(userId)) { builder.and(lessonModel.userId.eq(userId)); }returnqueryFactory.selectFrom(lessonModel).where(builder).fetch(); } @Override public List<LessonModel> findLessonSubqueryList(String name, String address) { QLessonModel lessonModel = QLessonModel.lessonModel; // Subquery example, no practical significancereturnqueryFactory.selectFrom(lessonModel) .where(lessonModel.name.in( JPAExpressions .select(lessonModel.name) .from(lessonModel) .where(lessonModel.address.eq(address)) )) .fetch(); }}Copy the code

3.5 Mysql aggregate functions

QueryDSL already has some common Mysql aggregation functions built in. Don’t panic if you encounter an aggregation function that is not provided by QueryDSL. QueryDSL provides a class called Expressions that you can use to concatenates manually.

Code listing: spring – the boot – jpa – querydsl/SRC/main/Java/com/springboot springbootjpaquerydsl/service/impl UserServiceImpl. Java

@Override public String mysqlFuncDemo(String id, String nickName, int age) { QUserModel userModel = QUserModel.userModel; -avg() Double averageAge = queryfactory.select (userModel.age.avg()). From (userModel).fetchone (); // aggregate function -sum() Integer sumAge = queryfactory. select(userModel.age.sum()). From (userModel).fetchone (); / / aggregation function - concat () String concat = queryFactory. Select (userModel. NickName. Concat (nickName). The from (userModel.) fetchOne (); // Aggregate function -contains() Boolean contains = queryFactory.select(userModel.nickName.contains(nickName)).from(userModel).where(userModel.id.eq(id)).fetchOne(); / / aggregation function - DATE_FORMAT (String) the date = queryFactory. Select (Expressions. StringTemplate ("DATE_FORMAT({0},'%Y-%m-%d')", userModel.createDate)).from(userModel).fetchOne();

    return null;
}
Copy the code

4. Summary

The introduction of QueryDSL is over here, I do not know that readers have seen the above example, there is a direct sense of reading SQL, and this SQL or the use of OOM thought, Hibernate did not do a good job of the things given a pretty perfect solution, It’s easy to get started, and you don’t need to write SQL, but you’re still working on object classes.

5. Example code

Example code -Github

Example code -Gitee

6. Reference

QueryDSL Official Documentation