开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> Java知识库 -> springboot-jpa-querydsl -> 正文阅读

[Java知识库]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);
    }

}

配置类

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);
    }

}

分页配置类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;
}

业务层

接口

package com.example.demo.service;

import com.example.demo.entity.UserEntity;

import java.util.List;

public interface UserService {

    List<UserEntity> getAllUsers();
}

实现类

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;
}