springboot-jpa-querydsl

jpa对于简单的增删改查非常方便，但是复杂查询，如多参数动态查询，各种条件查询，非常费劲。
QueryDsl是对jpa查询的增强，语法就像写sql，可以自由组合条件，也可以联表查询、分页查询等，非常方便；

新建项目

build.gradle

plugins {
	id 'org.springframework.boot' version '2.6.2'
	id 'io.spring.dependency-management' version '1.0.11.RELEASE'
	id 'java'
}

group = 'com.example'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '1.8'

configurations {
	compileOnly {
		extendsFrom annotationProcessor
	}
}

repositories {
	maven {
		url 'https://maven.aliyun.com/repository/public/'
	}
}

dependencies {
	implementation 'org.springframework.boot:spring-boot-starter-data-jpa'

	implementation 'org.springframework.boot:spring-boot-starter-web'
	implementation 'org.apache.commons:commons-lang3:3.9'
	compileOnly 'org.projectlombok:lombok'
	runtimeOnly 'mysql:mysql-connector-java'
	annotationProcessor 'org.projectlombok:lombok'
	testImplementation 'org.springframework.boot:spring-boot-starter-test'
	// querydsl
	implementation ('com.querydsl:querydsl-jpa')
	implementation ('com.querydsl:querydsl-apt')
	//关键地方(记得开启annotationProcessor)
	annotationProcessor("com.querydsl:querydsl-apt:5.0.0:jpa",
			"org.hibernate.javax.persistence:hibernate-jpa-2.1-api:1.0.2.Final",
			"javax.annotation:javax.annotation-api:1.3.2",
			"org.projectlombok:lombok"
	)
}

test {
	useJUnitPlatform()
}

application.properties

server.port=8080

spring.jpa.show-sql=false
spring.jpa.hibernate.ddl-auto=none
spring.jpa.generate-ddl=false
spring.jpa.properties.hibernate.show_sql=false
spring.jpa.properties.hibernate.jdbc.time_zone=Asia/Shanghai
spring.datasource.name=demo
spring.datasource.url=jdbc:mysql://localhost:3306/demo?useSSL=false&useUnicode=true&zeroDateTimeBehavior=convertToNull
spring.datasource.username=root
spring.datasource.password=root
spring.jackson.date-format=yyyy-MM-dd HH:mm:ss
spring.jackson.time-zone=GMT+8

文件目录结构：

.
├── main
│   ├── java
│   │   └── com
│   │       └── example
│   │           └── demo
│   │               ├── Application.java
│   │               ├── config
│   │               │   ├── QueryDslOrderUtil.java
│   │               │   └── QuerydslConfig.java
│   │               ├── controller
│   │               │   └── UserController.java
│   │               ├── entity
│   │               │   └── UserEntity.java
│   │               ├── params
│   │               │   └── UserQueryForm.java
│   │               ├── reporsitory
│   │               │   └── UserRepository.java
│   │               └── service
│   │                   ├── UserService.java
│   │                   └── impl
│   │                       └── UserServiceImpl.java
│   └── resources
│       ├── application.properties
│       ├── static
│       └── templates
└── test
    └── java
        └── com
            └── example
                └── demo
                    └── ApplicationTests.java

启动类

package com.example.demo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.domain.EntityScan;
import org.springframework.data.jpa.repository.config.EnableJpaAuditing;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.scheduling.annotation.EnableAsync;
import org.springframework.scheduling.annotation.EnableScheduling;

@EntityScan(basePackages = "com.example.demo.entity")
@EnableJpaRepositories(basePackages = "com.example.demo")
@EnableJpaAuditing
@EnableScheduling
@EnableAsync
@SpringBootApplication
public class Application {

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }

}

配置类

1. querydsl工厂配置类QuerydslConfig

package com.example.demo.config;

import com.querydsl.jpa.impl.JPAQueryFactory;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

/**
 * QueryDSL配置类
 */
@Configuration
public class QuerydslConfig {

    @Resource
    @PersistenceContext
    private EntityManager entityManager;

    @Bean
    public JPAQueryFactory queryFactory() {
        return new JPAQueryFactory(entityManager);
    }

}

2. 分页配置类QueryDslOrderUtil

package com.example.demo.config;

import com.querydsl.core.types.Order;
import com.querydsl.core.types.OrderSpecifier;
import com.querydsl.core.types.dsl.EntityPathBase;

/**
 * 排序工具类
 *
 * @author CCL
 * @createTime 2021年12月06日 13:09:00
 */
public class QueryDslOrderUtil {

    /**
     * 根据入参动态排序
     * 使用示例： jpaQuery.orderBy(QueryDslOrderUtil.getSortedColumn(qSysUserEntity,params.getSortType(),params.getSortField
     * ()));
     *
     * @param pathBase  q实体
     * @param sortType  排序类型，升序ASC，降序DESC
     * @param sortField 排序字段名
     * @param <T>
     * @return
     */
    public static <T> OrderSpecifier<?> orderByField(EntityPathBase<T> pathBase, String sortType, String sortField) {
        /*if (StringUtils.isAnyBlank(sortType, sortField)) {
            sortType = "desc";
            sortField = "createTime";
        }*/
        Order order = "ascend".equalsIgnoreCase(sortType) ? Order.ASC : Order.DESC;
        com.querydsl.core.types.Path<Object> fieldPath = com.querydsl.core.types.dsl.Expressions.path(Object.class,
                pathBase, sortField);
        return new OrderSpecifier(order, fieldPath);
    }
}

控制器类Controller

package com.example.demo.controller;

import com.example.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class UserController {

    @Autowired
    private UserService userService;

    @GetMapping("/getAllUsers")
    public Object getAllUsers() {
        return userService.getAllUsers();
    }
}

实体类UserEntity

package com.example.demo.entity;

import lombok.Data;
import org.hibernate.annotations.DynamicInsert;
import org.hibernate.annotations.DynamicUpdate;
import org.hibernate.annotations.SelectBeforeUpdate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;

import javax.persistence.*;
import java.util.Date;
import java.util.List;

@Data
@Entity
@Table(name = "t_user")
@SelectBeforeUpdate
@DynamicInsert
@DynamicUpdate
@EntityListeners(value = AuditingEntityListener.class)
public class UserEntity {

    /**
     * 用户ID
     */
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long userId;

    /**
     * 用户名
     */
    private String username;

    /**
     * 密码
     */
    //	@NotBlank(message="密码不能为空", groups = AddGroup.class)
    private String password;

    /**
     * 盐
     */
    private String salt;

    /**
     * 邮箱
     */
    private String email;

    /**
     * 手机号
     */
    private String mobile;

    /**
     * 状态  0删除，1正常，2待激活，3禁用
     */
    private Integer status;

    /**
     * 角色ID列表
     */

    @Transient
    private List<Long> roleIdList;

    /**
     * 创建者ID
     */
    private Long createUserId;

    /**
     * 创建时间
     */
    private Date createTime;
    /**
     * 部门ID
     */
    private Long deptId;
    /**
     * 是否默认密码
     */
    private Boolean isInitPwd;


    /**
     * 是否超级管理员
     */

    @Transient
    private boolean superManager;
}

业务层

1. 接口

package com.example.demo.service;

import com.example.demo.entity.UserEntity;

import java.util.List;

public interface UserService {

    List<UserEntity> getAllUsers();
}

2. 实现类

package com.example.demo.service.impl;

import com.example.demo.config.QueryDslOrderUtil;
import com.example.demo.entity.QUserEntity;
import com.example.demo.entity.UserEntity;
import com.example.demo.params.UserQueryForm;
import com.example.demo.reporsitory.UserRepository;
import com.example.demo.service.UserService;
import com.querydsl.core.Tuple;
import com.querydsl.jpa.impl.JPAQuery;
import com.querydsl.jpa.impl.JPAQueryFactory;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import java.util.Collections;
import java.util.List;

@Service
@Slf4j
public class UserServiceImpl implements UserService {
    @Autowired
    private JPAQueryFactory jpaQueryFactory;
    @Autowired
    private UserRepository userRepository;

    private final QUserEntity qUserEntity = QUserEntity.userEntity;

    /**
     * 查询所有-querydsl
     *
     * @return
     */
    @Override
    public List<UserEntity> getAllUsers() {
        return jpaQueryFactory.select(qUserEntity).from(qUserEntity).fetch();
    }

    public List<UserEntity> getByParam(UserEntity userEntity) {
        JPAQuery<UserEntity> query = jpaQueryFactory.select(qUserEntity).from(qUserEntity);


        return jpaQueryFactory.select(qUserEntity).from(qUserEntity).fetch();
    }

    /**
     * querydsl 分页复杂查询
     *
     * @param params
     * @return
     */
    public List<UserEntity> queryPage(UserQueryForm params) {
        JPAQuery<UserEntity> query = jpaQueryFactory.select(qUserEntity).from(qUserEntity);

        if (StringUtils.isNotBlank(params.getUsername())) {
            query.where(qUserEntity.username.like("%" + params.getUsername() + "%"));
        }
        if (StringUtils.isNotBlank(params.getMobile())) {
            query.where(qUserEntity.mobile.eq(params.getMobile()));
        }
        if (StringUtils.isNotBlank(params.getEmail())) {
            query.where(qUserEntity.email.eq(params.getEmail()));
        }
        if (!CollectionUtils.isEmpty(params.getDeptIdList())) {
            query.where(qUserEntity.deptId.in(params.getDeptIdList()));
        }
        //根据入参排序
        if (StringUtils.isNotBlank(params.getSortField())) {
            query.orderBy(QueryDslOrderUtil.orderByField(qUserEntity, params.getSortType(), params.getSortField()));
        } else {
            //默认排序
            query.orderBy(qUserEntity.createTime.desc());
        }

        query.where(qUserEntity.status.ne(1));
        List<UserEntity> userList = query.offset((params.getPageNum() - 1) * params.getPageSize()).limit(
                params.getPageSize()).fetch();


        return userList;

    }

    /**
     * 示例： 此方法中有些类没有贴出来，看使用语法即可
     * @param params
     * @return
     */
    public PageInfo queryPage2(UserQueryForm params) {

        JPAQuery<Tuple> query = jpaQueryFactory.select(qUserEntity,
                qSysDeptEntity.deptName).from(qUserEntity).leftJoin(qSysUserRoleEntity).on(
                qUserEntity.userId.eq(qSysUserRoleEntity.userId)).leftJoin(qSysDeptEntity).on(
                qSysDeptEntity.id.eq(qUserEntity.deptId));

        if (StringUtils.isNotBlank(params.getUsername())) {
            query.where(qUserEntity.username.like("%" + params.getUsername() + "%"));
        }
        if (StringUtils.isNotBlank(params.getMobile())) {
            query.where(qUserEntity.mobile.like("%" + params.getMobile() + "%"));
        }
        if (StringUtils.isNotBlank(params.getEmail())) {
            query.where(qUserEntity.email.like("%" + params.getEmail() + "%"));
        }
        if (!CollectionUtils.isEmpty(params.getDeptIdList())) {
            query.where(qUserEntity.deptId.in(params.getDeptIdList()));
        }
        if (params.getRoleId() != null) {
            query.where(qSysUserRoleEntity.roleId.eq(params.getRoleId()));
        }
        //根据入参排序
        if (StringUtils.isNotBlank(params.getSortField())) {
            String sortField = params.getSortField();
            if (StringUtils.equals("deptName", sortField)) {
                query.orderBy(QueryDslOrderUtil.orderByField(qSysDeptEntity, params.getSortType(), params.getSortField()));
            } else {
                query.orderBy(QueryDslOrderUtil.orderByField(qUserEntity, params.getSortType(), params.getSortField()));
            }
        } else {
            //默认排序
            query.orderBy(qUserEntity.createTime.desc());
        }
        query.where(qUserEntity.status.ne(0));
        List<Tuple> fetch = Collections.emptyList();
        try {
            fetch = query.offset((params.getPageNum() - 1) * params.getPageSize()).limit(
                    params.getPageSize()).fetch();
        } catch (Exception e) {
            log.error("用户分页查询异常", e);
        }

        return new PageInfo(userList, (int) query.stream().count(), params.getPageSize(), params.getPageNum());
    }


    /**
     * 查询所有-repository
     *
     * @return
     */
    public List<UserEntity> getAllUsers2() {
        return userRepository.findAll();
    }

}

持久层

package com.example.demo.reporsitory;

import com.example.demo.entity.UserEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;
import org.springframework.stereotype.Repository;

/**
 * 这里面提供了很多通用方法，如findAll.findById,save,delete,update等等
 * 适用于简单的增删改查，非常方便
 */
@Repository
public interface UserRepository extends JpaRepository<UserEntity, Long>, QuerydslPredicateExecutor<UserEntity> {

}

入参类

package com.example.demo.params;

import lombok.Data;

import java.util.List;

@Data
public class UserQueryForm {
    private String username;
    private String mobile;
    private Long roleId;
    private String email;
    private List<Long> deptIdList;
    private int pageNum = 1;
    private int pageSize = 10;
    String sortType = "ascend";
    String sortField;
}

QueryDsl代码生成

只要entity类和启动类按上面方式加上注解，然后使用命令：

gradle build -x test

就可以在build目录下生成Q文件