springboot + querydsl 入门到会用 - 第一篇（整合）

KevinBlandy · 2020 年8 月 21 日 05:10

自从邂逅了spring-data-jpa + querydsl 这个组合后，我再也没用过mybatis。

QueryDsl简单了解

QueryDSL可以在任何支持的ORM框架或者SQL平台上以一种通用的API方式来构建查询。目前QueryDSL支持的平台包括JPA,JDO,SQL,Mongodb 等等。

官网

Gitthub

入门到会用

这个教程是演示querydsl + spring-data-jpa整合使用（其他的我也不会）。对于spring-data-jpa，如果你不熟悉也没太大的关系，不影响使用querydsl。

开始之前，这里要做一些说明。

这里不会涉及太多JPA相关的东西

QueryDsl和jpa整合其实很简单（就几行代码）。但是jap和springboot的整合会涉及很多的配置属性，代码。这里不会过多的去解释它们。

实体类建模不采用面向对象的思想建模

JPA让人讨厌的很大一个原因是因为那一堆 @OnToOne, @OneToMany 。。。等关系描述。并且由此带来诸多的概念：延迟加载，级联删除，级联保存，孤儿删除。。。更是头疼不已。代码是死的，人是活的，不一定非要墨守成规。不用面向对象思想建模，就彻底解决了这些关联问题。

演示工程的创建

在编辑器初始化工程的过程，省略。

Maven完整的核心依赖

<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>io.springboot.querydsl</groupId>
	<artifactId>springboot-querydsl</artifactId>
	<version>0.0.1-SNAPSHOT</version>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.3.3.RELEASE</version>
		<relativePath /> <!-- lookup parent from repository -->
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
		<java.version>1.8</java.version>

		<skipTests>true</skipTests>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
			<exclusions>
				<exclusion>
					<groupId>org.springframework.boot</groupId>
					<artifactId>spring-boot-starter-tomcat</artifactId>
				</exclusion>
			</exclusions>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-undertow</artifactId>
		</dependency>
		
		<!-- jpa -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		
		<!-- mysql驱动 -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>
		
		<!-- 数据源 -->
		<dependency>
			<groupId>com.zaxxer</groupId>
			<artifactId>HikariCP</artifactId>
		</dependency>
		
		<!-- QueryDsl -->
		<dependency>
			<groupId>com.querydsl</groupId>
			<artifactId>querydsl-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>com.querydsl</groupId>
			<artifactId>querydsl-apt</artifactId>
			<scope>provided</scope>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
				<configuration>
					<executable>true</executable>
					<includeSystemScope>true</includeSystemScope>
				</configuration>
			</plugin>
			<!-- QueryDsl代码生成插件 -->
			<plugin>
				<groupId>com.mysema.maven</groupId>
				<artifactId>apt-maven-plugin</artifactId>
				<version>1.1.3</version>
				<executions>
					<execution>
						<goals>
							<goal>process</goal>
						</goals>
						<configuration>
							<!-- 查询对象的生成路径 -->
							<outputDirectory>target/generated-sources/java</outputDirectory>
							<!-- 生成查询对象的处理器， 使用JPA-->
							<processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
						</configuration>
					</execution>
				</executions>
			</plugin>
		</plugins>
	</build>
</project>

除了jpa和必须的依赖（驱动，连接池）以外，querydsl只有3个组件。俩依赖，一个插件。插件的功能在于，在maven打包的时候，根据实体类生成查询对象。

配置文件


server:
  port: 80

logging:
  level:
    root: DEBUG
    # 在日志中输SQL参数的绑定信息
    'org.hibernate.type.descriptor.sql.BasicBinder': TRACE

spring:
  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/querydsl?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2b8&allowMultiQueries=true
    username: root
    password: root

  data:
    jpa:
      repositories:
        enabled: true
        bootstrap-mode: default
  jpa:
    # 指定关系型数据库的方言
    database-platform: org.hibernate.dialect.MySQL57Dialect
    # 不在视图层维护连接 
    open-in-view: false
    # 日志中输出SQL
    show-sql: false
    properties:
    # 格式化日志中输出的SQL
      hibernate.format_sql: false
    hibernate:
      # SQL建表策略： UPDATE
      ddl-auto: update

这基本都是spring-data-jpa相关的一些配置，特别的地方，都写上了注释。很好理解。

JPAQueryFactoryConfiguration

JPA整合QueryDsl，其实就这点代码

package io.springboot.querydsl.configuration;

import javax.persistence.EntityManager;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import com.querydsl.jpa.impl.JPAQueryFactory;

@Configuration
public class JPAQueryFactoryConfiguration {
	
	@Bean
	public JPAQueryFactory jpaQueryFactory(@Autowired EntityManager entityManager) {
		return new JPAQueryFactory(entityManager);
	}
}

实体类

建模，这里定义了4个实体类，描述了常见的一对多,多对多关系。

BaseEntity 抽象出公共的字段
User 用户
Email 用户邮箱一对多关联
Role 角色
UserRole 用户角色多对多关联

这里省略了Getter/Setter 方法

BaseEntity

package io.springboot.querydsl.entity;

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

import javax.persistence.Column;
import javax.persistence.MappedSuperclass;

@MappedSuperclass
public abstract class BaseEntity implements Serializable {

	/**
	 * 
	 */
	private static final long serialVersionUID = 7054150882445633369L;

	// 创建时间
	@Column(columnDefinition = "timestamp DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'", nullable = false)
	private LocalDateTime createdDate;

	// 最后修改时间
	@Column(columnDefinition = "timestamp NULL DEFAULT NULL COMMENT '最后一次修改时间'")
	private LocalDateTime lastModifiedDate;
}

User

package io.springboot.querydsl.entity;

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

@Entity
@Table(name = "user", indexes = { 
	@Index(name = "name", columnList = "name", unique = true),
})
@org.hibernate.annotations.Table(appliesTo = "user", comment = "用户")
public class User extends BaseEntity {

	/**
	 * 
	 */
	private static final long serialVersionUID = -5342379801159855228L;

	@Id
	@Column(columnDefinition = "INT(11) UNSIGNED COMMENT 'id'")
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Integer id;

	// 昵称
	@Column(columnDefinition = "VARCHAR(20) COMMENT '昵称'", nullable = false)
	private String name;

	// 性别
	@Column(columnDefinition = "TINYINT(1) unsigned COMMENT '性别。0：女，1：男，2：未知'", nullable = false)
	private Gender gender;

	// 账户是否已经验证
	@Column(columnDefinition = "TINYINT(1) unsigned COMMENT '账户是否已经验证'", nullable = false)
	private Boolean validated;

	// 性别枚举
	public static enum Gender {
		GIRL, BOY, UNKNOWN
	}
}

Email

package io.springboot.querydsl.entity;

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

@Entity
@Table(name = "email", indexes = { 
	@Index(name = "userEmailAccount", columnList = "user_id,account", unique = true), 
	@Index(name = "account", columnList = "account")
})
@org.hibernate.annotations.Table(appliesTo = "email", comment = "用户邮箱")
public class Email extends BaseEntity {

	/**
	 * 
	 */
	private static final long serialVersionUID = -730436482990380359L;

	@Id
	@Column(columnDefinition = "INT(11) UNSIGNED COMMENT 'id'")
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Integer id;

	// 用户id
	@Column(name = "user_id", columnDefinition = "INT(11) UNSIGNED COMMENT '用户id'")
	private Integer userId;

	// 邮箱账户
	@Column(name = "account", columnDefinition = "VARCHAR(20) COMMENT '昵称'", nullable = false)
	private String account;
}

Role

package io.springboot.querydsl.entity;

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

@Entity
@Table(name = "role", indexes = { 
	@Index(name = "name", columnList = "name", unique = true),
})
@org.hibernate.annotations.Table(appliesTo = "role", comment = "角色")
public class Role extends BaseEntity {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1749885146919803064L;

	@Id
	@Column(columnDefinition = "INT(11) UNSIGNED COMMENT 'id'")
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Integer id;

	// 名称
	@Column(columnDefinition = "VARCHAR(20) COMMENT '名称'", nullable = false)
	private String name;
}

UserRole

package io.springboot.querydsl.entity;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.IdClass;
import javax.persistence.Index;
import javax.persistence.Table;

@Entity
@Table(name = "user_role", indexes = { 
	@Index(name = "roleId", columnList = "role_id"),
})
@org.hibernate.annotations.Table(appliesTo = "user_role", comment = "用户角色关联")
@IdClass(UserRole.Id.class)
public class UserRole extends BaseEntity {
	
	/**
	 * 
	 */
	private static final long serialVersionUID = 1782979029236838525L;

	@Column(name = "user_id", columnDefinition = "INT(11) UNSIGNED COMMENT '用户ID'", nullable = false)
	@javax.persistence.Id
	private Integer userId;
	
	@javax.persistence.Id
	@Column(name = "role_id", columnDefinition = "INT(11) UNSIGNED COMMENT '角色ID'", nullable = false)
	private Integer roleId;
	
	public static class Id implements Serializable {
		private static final long serialVersionUID = 2751217704686895162L;
		private Integer userId;
		private Integer roleId;
		public Id() {
		}
		public Id(Integer userId, Integer roleId) {
			super();
			this.userId = userId;
			this.roleId = roleId;
		}
		@Override
		public int hashCode() {
			final int prime = 31;
			int result = 1;
			result = prime * result + ((roleId == null) ? 0 : roleId.hashCode());
			result = prime * result + ((userId == null) ? 0 : userId.hashCode());
			return result;
		}
		@Override
		public boolean equals(Object obj) {
			if (this == obj)
				return true;
			if (obj == null)
				return false;
			if (getClass() != obj.getClass())
				return false;
			Id other = (Id) obj;
			if (roleId == null) {
				if (other.roleId != null)
					return false;
			} else if (!roleId.equals(other.roleId))
				return false;
			if (userId == null) {
				if (other.userId != null)
					return false;
			} else if (!userId.equals(other.userId))
				return false;
			return true;
		}
	}
}

Repository

BaseRepository

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;
import org.springframework.data.repository.NoRepositoryBean;

@NoRepositoryBean
public interface BaseRepository<T, ID> extends JpaRepository<T, ID>, JpaSpecificationExecutor<T>, QuerydslPredicateExecutor<T> {

}

其他的几个Repository

package io.springboot.querydsl.repository;

import io.springboot.querydsl.entity.User;

public interface UserRepository extends BaseRepository<User, Integer>{

}

package io.springboot.querydsl.repository;

import io.springboot.querydsl.entity.Email;

public interface EmailRepository extends BaseRepository<Email, Integer> {

}

package io.springboot.querydsl.repository;

import io.springboot.querydsl.entity.Role;

public interface RoleRepository extends BaseRepository<Role, Integer> {

}

package io.springboot.querydsl.repository;

import io.springboot.querydsl.entity.UserRole;

public interface UserRoleRepository extends BaseRepository<UserRole, UserRole.Id> {

}

Service

BaseService

package io.springboot.querydsl.service;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;

public interface BaseService <T, ID> extends JpaRepository<T, ID>, JpaSpecificationExecutor <T>, QuerydslPredicateExecutor<T> {

}

AbstractService

package io.springboot.querydsl.service;

import java.util.List;
import java.util.Optional;
import java.util.function.Function;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Example;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.transaction.annotation.Transactional;

import com.querydsl.core.types.OrderSpecifier;
import com.querydsl.core.types.Predicate;
import com.querydsl.jpa.impl.JPAQueryFactory;

import io.springboot.querydsl.repository.BaseRepository;

public class AbstractService<T, ID>  implements BaseService <T, ID>{
	
	@Autowired
	protected BaseRepository<T,ID> baseRepository;
	
	@Autowired
	protected JPAQueryFactory jpaQueryFactory;
	
	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public List<T> findAll() {
		return this.baseRepository.findAll();
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public List<T> findAll(Sort sort) {
		return this.baseRepository.findAll(sort);
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public List<T> findAllById(Iterable<ID> ids) {
		return this.baseRepository.findAllById(ids);
	}

	@Override
	@Transactional(rollbackFor = Throwable.class)
	public <S extends T> List<S> saveAll(Iterable<S> entities) {
		return this.baseRepository.saveAll(entities);
	}

	@Override
	@Transactional(rollbackFor = Throwable.class)
	public void flush() {
		this.baseRepository.flush();
	}

	@Transactional(rollbackFor = Throwable.class)
	public <S extends T> S saveAndFlush(S entity) {
		return this.baseRepository.saveAndFlush(entity);
	}

	@Override
	@Transactional(rollbackFor = Throwable.class)
	public void deleteInBatch(Iterable<T> entities) {
		this.baseRepository.deleteInBatch(entities);
	}

	@Override
	@Transactional(rollbackFor = Throwable.class)
	public void deleteAllInBatch() {
		this.baseRepository.deleteAllInBatch();
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public T getOne(ID id) {
		return this.baseRepository.getOne(id);
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public <S extends T> List<S> findAll(Example<S> example) {
		return this.baseRepository.findAll(example);
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public <S extends T> List<S> findAll(Example<S> example, Sort sort) {
		return this.baseRepository.findAll(example, sort);
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public Page<T> findAll(Pageable pageable) {
		return this.baseRepository.findAll(pageable);
	}

	@Override
	@Transactional(rollbackFor = Throwable.class)
	public <S extends T> S save(S entity) {
		return this.baseRepository.save(entity);
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public Optional<T> findById(ID id) {
		return this.baseRepository.findById(id);
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public boolean existsById(ID id) {
		return this.baseRepository.existsById(id);
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public long count() {
		return this.baseRepository.count();
	}

	@Override
	@Transactional(rollbackFor = Throwable.class)
	public void deleteById(ID id) {
		this.baseRepository.deleteById(id);		
	}

	@Override
	@Transactional(rollbackFor = Throwable.class)
	public void delete(T entity) {
		this.baseRepository.delete(entity);		
	}

	@Override
	@Transactional(rollbackFor = Throwable.class)
	public void deleteAll(Iterable<? extends T> entities) {
		this.baseRepository.deleteAll(entities);
	}

	@Override
	@Transactional(rollbackFor = Throwable.class)
	public void deleteAll() {
		this.baseRepository.deleteAll();
	}
	
	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public <S extends T> Optional<S> findOne(Example<S> example) {
		return this.baseRepository.findOne(example);
	}
	
	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public <S extends T> Page<S> findAll(Example<S> example, Pageable pageable) {
		return this.baseRepository.findAll(example, pageable);
	}
	
	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public <S extends T> long count(Example<S> example) {
		return this.baseRepository.count(example);
	}
	
	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public <S extends T> boolean exists(Example<S> example) {
		return this.baseRepository.exists(example);
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public Optional<T> findOne(Specification<T> spec) {
		return this.baseRepository.findOne(spec);
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public List<T> findAll(Specification<T> spec) {
		return this.baseRepository.findAll(spec);
	}
	
	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public Page<T> findAll(Specification<T> spec, Pageable pageable) {
		return this.baseRepository.findAll(spec, pageable);
	}
	
	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public List<T> findAll(Specification<T> spec, Sort sort) {
		return this.baseRepository.findAll(spec, sort);
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public long count(Specification<T> spec) {
		return this.baseRepository.count(spec);
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public Optional<T> findOne(Predicate predicate) {
		return this.baseRepository.findOne(predicate);
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public Iterable<T> findAll(Predicate predicate) {
		return this.baseRepository.findAll(predicate);
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public Iterable<T> findAll(Predicate predicate, Sort sort) {
		return this.baseRepository.findAll(predicate, sort);
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public Iterable<T> findAll(Predicate predicate, OrderSpecifier<?>... orders) {
		return this.baseRepository.findAll(predicate, orders);
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public Iterable<T> findAll(OrderSpecifier<?>... orders) {
		return this.baseRepository.findAll(orders);
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public Page<T> findAll(Predicate predicate, Pageable pageable) {
		return this.baseRepository.findAll(predicate, pageable);
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public long count(Predicate predicate) {
		return this.baseRepository.count(predicate);
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public boolean exists(Predicate predicate) {
		return this.baseRepository.exists(predicate);
	}

	
	// 自定义的2个方法，用于在事务中获取到JPAQueryFactory，执行自定义的查询逻辑
	
	@Transactional(rollbackFor = Throwable.class)
	public <R> R apply(Function<JPAQueryFactory, R> function) {
		return function.apply(this.jpaQueryFactory);
	}
	
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public <R> R applyReadOnly(Function<JPAQueryFactory, R> function) {
		return function.apply(this.jpaQueryFactory);
	}
}

其他的几个Service

package io.springboot.querydsl.service;

import org.springframework.stereotype.Service;

import io.springboot.querydsl.entity.User;

@Service
public class UserService extends AbstractService<User, Integer> {

}

package io.springboot.querydsl.service;

import org.springframework.stereotype.Service;

import io.springboot.querydsl.entity.Email;

@Service
public class EmailService extends AbstractService<Email, Integer> {

}

package io.springboot.querydsl.service;

import org.springframework.stereotype.Service;

import io.springboot.querydsl.entity.Role;

@Service
public class RoleService extends AbstractService<Role, Integer>{

}

package io.springboot.querydsl.service;

import org.springframework.stereotype.Service;

import io.springboot.querydsl.entity.UserRole;

@Service
public class UserRoleService extends AbstractService<UserRole, UserRole.Id> {

}

Application启动类

package io.springboot.querydsl;

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.EnableJpaRepositories;

@SpringBootApplication
@EnableJpaRepositories(basePackages = { "io.springboot.querydsl.repository" })
@EntityScan(basePackages = { "io.springboot.querydsl.entity" })
public class QueryDslAppliccation {
	
	public static void main(String[] args) {
		SpringApplication.run(QueryDslAppliccation.class, args);
	}
}

Maven打包

执行MAVEN打包，插件会在指定目录下生成查询对象，以Q开头。

启动工程自动创建数据表

如果启动过程没有异常的话，就会根据实体类在数据库自动的创建数据表（因为配置了自动建表策略）。

CREATE TABLE `email` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `last_modified_date` timestamp NULL DEFAULT NULL COMMENT '最后一次修改时间',
  `account` varchar(20) NOT NULL COMMENT '昵称',
  `user_id` int(11) unsigned DEFAULT NULL COMMENT '用户id',
  PRIMARY KEY (`id`),
  UNIQUE KEY `userEmailAccount` (`user_id`,`account`),
  KEY `account` (`account`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户邮箱';

CREATE TABLE `role` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `last_modified_date` timestamp NULL DEFAULT NULL COMMENT '最后一次修改时间',
  `name` varchar(20) NOT NULL COMMENT '名称',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色';

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `last_modified_date` timestamp NULL DEFAULT NULL COMMENT '最后一次修改时间',
  `gender` tinyint(1) unsigned NOT NULL COMMENT '性别。0：女，1：男，2：未知',
  `name` varchar(20) NOT NULL COMMENT '昵称',
  `validated` tinyint(1) unsigned NOT NULL COMMENT '账户是否已经验证',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户';

CREATE TABLE `user_role` (
  `role_id` int(11) unsigned NOT NULL COMMENT '角色ID',
  `user_id` int(11) unsigned NOT NULL COMMENT '用户ID',
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `last_modified_date` timestamp NULL DEFAULT NULL COMMENT '最后一次修改时间',
  PRIMARY KEY (`role_id`,`user_id`),
  KEY `roleId` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户角色关联';

本篇总结

这里这里大篇幅的东西都是 spring-data-jpa相关的，QueryDsl的整合其实就几个东西

导入相关依赖
把JPAQueryFactory添加倒IOC（学QueryDsl，本质上就是学怎么用它）

完整的工程源码

Xy718 · 2020 年8 月 21 日 05:21

谢谢91凯文老师的分享

Xy718 · 2020 年12 月 10 日 07:15

补充一点点：
如果你使用的是一个多模块项目
M_A作为其他模块orm的PO
M_B作为业务模块使用M_A为PO的话，需要在M_A下的pom.xml加入QueryDSL的插件，M_B就不需要了

M_A:

    <dependencies>
        <!-- QueryDsl -->
        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-apt</artifactId>
            <version>4.3.1</version>
            <scope>provided</scope>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.16</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
            <version>2.3.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-jpa</artifactId>
            <version>4.3.1</version>
        </dependency>

    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>com.mysema.maven</groupId>
                <artifactId>apt-maven-plugin</artifactId>
                <version>1.1.3</version>
                <executions>
                    <execution>
                        <goals>
                            <goal>process</goal>
                        </goals>
                        <configuration>
                            <!-- 查询对象的生成路径 -->
                            <outputDirectory>target/generated-sources/java</outputDirectory>
                            <!-- 生成查询对象的处理器， 使用JPA-->
                            <processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
                        </configuration>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

M_B:

	<build>
		<plugins>
		</plugins>
	</build>

当然，M_B的启动类上依旧要加上@EnableJpaRepositories和@EntityScan注解，并且指向相应的M_XXX的包下
就完事了！

Xy718 · 2020 年12 月 15 日 07:25

k哥，有下一篇吗

KevinBlandy · 2020 年12 月 15 日 12:20

暂时没精力写第二篇了

Xy718 · 2020 年12 月 15 日 12:25

@)Z6(A8191B9BC2W}0N71V

JackGirl · 2021 年8 月 10 日 01:43

@org.hibernate.annotations.Table(appliesTo = “user_role”, comment = “用户角色关联”)
这个注解的作用是啥

KevinBlandy · 2021 年8 月 10 日 09:15

设置指定表的“注释”信息。这是Hibernate提供的。

jpa的@Table注解没有提供属性，好像也没其他的注解提供这个功能。

Mercuria1x · 2021 年8 月 23 日 06:40

我看网上说EntityManager 不是是线程安全的,所以不适用@Autowired,使用@PersistenceContext
在使用的时候一般都类似于这样

@Service
public class TestServiceImpl implements TestService {
	@PersistenceContext
	private EntityManager entityManager;

	@Override
	public void test(){
		JPAQueryFactory jpaQueryFactory = new JPAQueryFactory(entityManager);
		xxxxxxxxxxxx
	}
}

楼主这样没有问题吗

@Configuration
public class JPAQueryFactoryConfiguration {
	
	@Bean
	public JPAQueryFactory jpaQueryFactory(@Autowired EntityManager entityManager) {
		return new JPAQueryFactory(entityManager);
	}
}

KevinBlandy · 2021 年8 月 23 日 06:44

我好像没注意过这个问题还，我一直以为它是线程安全的。谢谢指点。我晚上研究一下。

KevinBlandy · 2021 年8 月 23 日 15:37

感谢 @Mercuria1x 指出的问题，之前没有考虑过 EntityManager的线程安全问题。所以对于抽象出来的AbstractService 需要修改。（spring-data-jpa 2.5 也增加/废弃了一些方法）

import java.util.List;
import java.util.Optional;
import java.util.function.Function;

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

import org.springframework.data.domain.Example;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.transaction.annotation.Transactional;

import com.querydsl.core.types.OrderSpecifier;
import com.querydsl.core.types.Predicate;
import com.querydsl.jpa.impl.JPAQueryFactory;

import io.springcloud.repository.BaseRepository;


public abstract  class AbstractService<T, ID>  implements BaseService <T, ID>{
	
	@Resource
	protected BaseRepository<T,ID> baseRepository;
	
//	@Resource
//	protected JPAQueryFactory jpaQueryFactory;
	
	@PersistenceContext  // EntityManager 线程不是安全的
	private EntityManager entityManager;
	
	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public List<T> findAll() {
		return this.baseRepository.findAll();
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public List<T> findAll(Sort sort) {
		return this.baseRepository.findAll(sort);
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public List<T> findAllById(Iterable<ID> ids) {
		return this.baseRepository.findAllById(ids);
	}

	@Override
	@Transactional(rollbackFor = Throwable.class)
	public <S extends T> List<S> saveAll(Iterable<S> entities) {
		return this.baseRepository.saveAll(entities);
	}

	@Override
	@Transactional(rollbackFor = Throwable.class)
	public void flush() {
		this.baseRepository.flush();
	}

	@Transactional(rollbackFor = Throwable.class)
	public <S extends T> S saveAndFlush(S entity) {
		return this.baseRepository.saveAndFlush(entity);
	}

	@Override
	@Transactional(rollbackFor = Throwable.class)
	@Deprecated
	public void deleteInBatch(Iterable<T> entities) {
		this.baseRepository.deleteInBatch(entities);
	}

	@Override
	@Transactional(rollbackFor = Throwable.class)
	public void deleteAllInBatch() {
		this.baseRepository.deleteAllInBatch();
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	@Deprecated
	public T getOne(ID id) {
		return this.baseRepository.getOne(id);
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public <S extends T> List<S> findAll(Example<S> example) {
		return this.baseRepository.findAll(example);
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public <S extends T> List<S> findAll(Example<S> example, Sort sort) {
		return this.baseRepository.findAll(example, sort);
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public Page<T> findAll(Pageable pageable) {
		return this.baseRepository.findAll(pageable);
	}

	@Override
	@Transactional(rollbackFor = Throwable.class)
	public <S extends T> S save(S entity) {
		return this.baseRepository.save(entity);
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public Optional<T> findById(ID id) {
		return this.baseRepository.findById(id);
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public boolean existsById(ID id) {
		return this.baseRepository.existsById(id);
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public long count() {
		return this.baseRepository.count();
	}

	@Override
	@Transactional(rollbackFor = Throwable.class)
	public void deleteById(ID id) {
		this.baseRepository.deleteById(id);		
	}

	@Override
	@Transactional(rollbackFor = Throwable.class)
	public void delete(T entity) {
		this.baseRepository.delete(entity);		
	}

	@Override
	@Transactional(rollbackFor = Throwable.class)
	public void deleteAll(Iterable<? extends T> entities) {
		this.baseRepository.deleteAll(entities);
	}

	@Override
	@Transactional(rollbackFor = Throwable.class)
	public void deleteAll() {
		this.baseRepository.deleteAll();
	}
	
	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public <S extends T> Optional<S> findOne(Example<S> example) {
		return this.baseRepository.findOne(example);
	}
	
	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public <S extends T> Page<S> findAll(Example<S> example, Pageable pageable) {
		return this.baseRepository.findAll(example, pageable);
	}
	
	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public <S extends T> long count(Example<S> example) {
		return this.baseRepository.count(example);
	}
	
	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public <S extends T> boolean exists(Example<S> example) {
		return this.baseRepository.exists(example);
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public Optional<T> findOne(Specification<T> spec) {
		return this.baseRepository.findOne(spec);
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public List<T> findAll(Specification<T> spec) {
		return this.baseRepository.findAll(spec);
	}
	
	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public Page<T> findAll(Specification<T> spec, Pageable pageable) {
		return this.baseRepository.findAll(spec, pageable);
	}
	
	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public List<T> findAll(Specification<T> spec, Sort sort) {
		return this.baseRepository.findAll(spec, sort);
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public long count(Specification<T> spec) {
		return this.baseRepository.count(spec);
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public Optional<T> findOne(Predicate predicate) {
		return this.baseRepository.findOne(predicate);
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public Iterable<T> findAll(Predicate predicate) {
		return this.baseRepository.findAll(predicate);
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public Iterable<T> findAll(Predicate predicate, Sort sort) {
		return this.baseRepository.findAll(predicate, sort);
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public Iterable<T> findAll(Predicate predicate, OrderSpecifier<?>... orders) {
		return this.baseRepository.findAll(predicate, orders);
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public Iterable<T> findAll(OrderSpecifier<?>... orders) {
		return this.baseRepository.findAll(orders);
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public Page<T> findAll(Predicate predicate, Pageable pageable) {
		return this.baseRepository.findAll(predicate, pageable);
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public long count(Predicate predicate) {
		return this.baseRepository.count(predicate);
	}

	@Override
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public boolean exists(Predicate predicate) {
		return this.baseRepository.exists(predicate);
	}

	@Override
	public <S extends T> List<S> saveAllAndFlush(Iterable<S> entities) {
		return this.baseRepository.saveAllAndFlush(entities);
	}

	@Override
	public void deleteAllInBatch(Iterable<T> entities) {
		this.baseRepository.deleteAllInBatch(entities);
	}

	@Override
	public void deleteAllByIdInBatch(Iterable<ID> ids) {
		this.baseRepository.deleteAllByIdInBatch(ids);
	}

	@Override
	public T getById(ID id) {
		return this.baseRepository.getById(id);
	}

	@Override
	public void deleteAllById(Iterable<? extends ID> ids) {
		this.baseRepository.deleteAllById(ids);
	}
	
	@Transactional(rollbackFor = Throwable.class)
	public <R> R apply(Function<JPAQueryFactory, R> function) {
		return function.apply(new JPAQueryFactory(this.entityManager));
	}
	
	@Transactional(readOnly = true, rollbackFor = Throwable.class)
	public <R> R applyReadOnly(Function<JPAQueryFactory, R> function) {
		return function.apply(new JPAQueryFactory(this.entityManager));
	}
}