Over the years, I have contacted / learned a lot of ORM frameworks or libraries.

• dbutils
• mybatis
• sql2o
• beetlsql
• hibernate
• cayenne
• spring-data-jpa
• querydsl

I think spring data JPA + queryds is the most handy tool in springboot applications. But it seems that it is not very popular in China. Looking at domestic open source projects, the projects encountered in work are basically mybatis / mybatis plus. Endless xml and mapper, generated with endless code.

This ORM framework of single table CRUD cannot flexibly JOIN and project queries. To add a JOIN table, you need to write a new mapper method and xml, a new query column, and a new mapper method and xml (of course, I see many people, many people will always be SELECT * to the end, and a findOne method can be used anywhere). You also configure various result set mappings. I'm so tired.

A while ago, I saw that JEECMS actually used QueryDsl, so I wanted to write a tutorial. It can't be regarded as a tutorial. It can only be regarded as a pile of cases, various use cases of QueyDsl. If you know nothing about QueryDsl, you can also take a look directly. It's not difficult. As long as you can write SQL statements, it will use 90%

QueyDsl

Quickly explain how to use this thing. QueryDsl needs to configure JPA to use. It generates query classes inversely according to the JPA Entity class you define. Complete the SQL operation through the operation query class.

The reverse generation process is completely automatic. You only need to configure the maven plug-in and define the entity class.

How to operate these query classes? You can write SQL operations.

It can complete most SQL queries in the project, which is too complex. However, you can use the Native Query of spring data JPA.

Take a quick look

Entity class User definition

import java.io.Serializable;
import java.math.BigDecimal;
import java.time.LocalDateTime;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EnumType;
import javax.persistence.Enumerated;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Index;
import javax.persistence.Table;
import javax.persistence.UniqueConstraint;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.With;

@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@With

@Entity
@Table(name = "user", uniqueConstraints = {
	@UniqueConstraint(columnNames = "name", name = "name")
}, indexes = {
	@Index(columnList = "department_id", name = "department_id")
})
@org.hibernate.annotations.Table(appliesTo = "user", comment = "user")
public class User implements Serializable {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1691873956126863400L;
	
	@Id
	@Column(columnDefinition = "INT UNSIGNED COMMENT 'ID'")
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Integer id;
	
	@Column(columnDefinition = "VARCHAR(50) COMMENT 'name'", nullable = false)
	private String name;
	
	@Column(columnDefinition = "VARCHAR(10) COMMENT 'Gender'", nullable = false)
	@Enumerated(EnumType.STRING)
	private Gender gender;
	
	@Column(columnDefinition = "DECIMAL(10,2)COMMENT 'Account balance'")
	private BigDecimal balance;
	
	@Column(name = "department_id", columnDefinition = "INT UNSIGNED COMMENT 'department ID'", nullable = false)
	private Integer departmentId;
	
	@Column(columnDefinition = "TINYINT UNSIGNED COMMENT 'Whether to enable. 0: disabled, 1: enabled'", nullable = false)
	private Boolean enabled;
	
	@Column(columnDefinition = "TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time'", nullable = false)
	private LocalDateTime createAt;
	
	@Column(columnDefinition = "TIMESTAMP DEFAULT NULL COMMENT 'Modification time'")
	private LocalDateTime updateAt;	
	
	public static enum Gender {
		MALE,		// male
		FEMALE	// female
	}
}

QueryDsl automatically generated query class

import static com.querydsl.core.types.PathMetadataFactory.*;

import com.querydsl.core.types.dsl.*;

import com.querydsl.core.types.PathMetadata;
import javax.annotation.processing.Generated;
import com.querydsl.core.types.Path;


/**
 * QUser is a Querydsl query type for User
 */
@Generated("com.querydsl.codegen.DefaultEntitySerializer")
public class QUser extends EntityPathBase<User> {

    private static final long serialVersionUID = 373632107L;

    public static final QUser user = new QUser("user");

    public final NumberPath<java.math.BigDecimal> balance = createNumber("balance", java.math.BigDecimal.class);

    public final DateTimePath<java.time.LocalDateTime> createAt = createDateTime("createAt", java.time.LocalDateTime.class);

    public final NumberPath<Integer> departmentId = createNumber("departmentId", Integer.class);

    public final BooleanPath enabled = createBoolean("enabled");

    public final EnumPath<User.Gender> gender = createEnum("gender", User.Gender.class);

    public final NumberPath<Integer> id = createNumber("id", Integer.class);

    public final StringPath name = createString("name");

    public final DateTimePath<java.time.LocalDateTime> updateAt = createDateTime("updateAt", java.time.LocalDateTime.class);

    public QUser(String variable) {
        super(User.class, forVariable(variable));
    }

    public QUser(Path<? extends User> path) {
        super(path.getType(), path.getMetadata());
    }

    public QUser(PathMetadata metadata) {
        super(User.class, metadata);
    }

}

Query Demo

JPAQueryFactory query = new JPAQueryFactory(this.entityManager);
		
QUser qUser = QUser.user;	// The generated query object can be understood as a data table
		
User user = query.select(qUser).from(qUser).where(qUser.id.eq(1)).fetchOne();  // Query unique records. If the result is more than one, it will be abnormal

Do you feel it? Write SQL in Java code. JOIN retrieval, projection query and result set encapsulation in the way of code. It's too flexible. Through reasonable abstract design, SQL can be executed directly in the Controller.

Since the query object is generated based on the entity class, the query object will be regenerated after modifying the field name of the entity class. All operations related to modifying / deleting fields in the code will exception at compile time. Instead of xml, you have to look for it one by one, or you can't even find it. I don't know who used this modified field in which xml.

Official address

Official website

https://querydsl.com/

Github

https://github.com/querydsl/querydsl

QueryDsl Example

There are about 10 cases, which are not convenient to start in a post, so a new project is built in Github. This project integrates spring data JPA, querydsl and some common cases.

https://github.com/KevinBlandy/springboot-querydsl-example

Software version

• SpringBoot 2.6.1
• Java 17
• MYSQL 8.x

The database needs to be created manually (see yaml configuration), and the data table (including index) will be created automatically after the system startup.

Example code

It's all in the src/main/resources directory. You can execute each one. I hope you'll like it.

• DataInit initializes presentation data (executed first)
• Example1 query / edit / delete of a single table
• Example2 join query
• Example3 paging / sorting
• Example4 conditional column subquery / query column subquery / exists subquery / count subquery
• Example5 aggregate query
• Example6 conditional grouping
• Example7 locking
• Exapmle8 result set encapsulation
• Some operations of Exapmle9 result column. case / conversion / null judgment
• Support for exapmle10 spring data JPA

Finally, you are welcome to point out the problems in the code and put forward relevant suggestions.

Starting: https://springboot.io/t/topic/4424