自从邂逅了spring-data-jpa + querydsl 这个组合后，我再也没用过mybatis。
QueryDsl简单了解
QueryDSL可以在任何支持的ORM框架或者SQL平台上以一种通用的API方式来构建查询。目前QueryDSL支持的平台包括JPA,JDO,SQL,Mongodb 等等。
官网
http://www.querydsl.com
Gitthub
https://github.com/querydsl/querydsl
入门到会用
这个教程是演示querydsl + spring-data-jpa整合使用（其他的我也不会）。对于spring-data-jpa，如果你不熟悉也没太大的关系，不影响使用querydsl。
开始之前，这里要做一些说明。
这里不会涉及太多JPA相关的东西
QueryDsl和jpa整合其实很简单（就几行代码）。但是jap和springboot的整合会涉及很多的配置属性，代码。这里不会过多的去解释它们。
实体类建模不采用面向对象的思想建模
JPA让人讨厌的很大一个原因是因为那一堆 @OnToOne, @OneToMany 。。。等关系描述。并且由此带来诸多的概念：延迟加载，级联删除，级联保存，孤儿删除。。。更是头疼不已。代码是死的，人是活的，不一定非要墨守成规。不用面向对象思想建模，就彻底解决了这些关联问题。
演示工程的创建
在编辑器初始化工程的过程，省略。
Maven完整的核心依赖
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">
除了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
}
}
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
}
其他的几个Repository
package io.springboot.querydsl.repository;
import io.springboot.querydsl.entity.User;
public interface UserRepository extends BaseRepository
}
package io.springboot.querydsl.repository;
import io.springboot.querydsl.entity.Email;
public interface EmailRepository extends BaseRepository
}
package io.springboot.querydsl.repository;
import io.springboot.querydsl.entity.Role;
public interface RoleRepository extends BaseRepository
}
package io.springboot.querydsl.repository;
import io.springboot.querydsl.entity.UserRole;
public interface UserRoleRepository extends BaseRepository
}
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
}
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
@Autowired
protected BaseRepository
@Autowired
protected JPAQueryFactory jpaQueryFactory;
@Override
@Transactional(readOnly = true, rollbackFor = Throwable.class)
public List
return this.baseRepository.findAll();
}
@Override
@Transactional(readOnly = true, rollbackFor = Throwable.class)
public List
return this.baseRepository.findAll(sort);
}
@Override
@Transactional(readOnly = true, rollbackFor = Throwable.class)
public List
return this.baseRepository.findAllById(ids);
}
@Override
@Transactional(rollbackFor = Throwable.class)
public List saveAll(Iterable entities) {
return this.baseRepository.saveAll(entities);
}
@Override
@Transactional(rollbackFor = Throwable.class)
public void flush() {
this.baseRepository.flush();
}
@Transactional(rollbackFor = Throwable.class)
public S saveAndFlush(S entity) {
return this.baseRepository.saveAndFlush(entity);
}
@Override
@Transactional(rollbackFor = Throwable.class)
public void deleteInBatch(Iterable
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 List findAll(Example example) {
return this.baseRepository.findAll(example);
}
@Override
@Transactional(readOnly = true, rollbackFor = Throwable.class)
public List findAll(Example example, Sort sort) {
return this.baseRepository.findAll(example, sort);
}
@Override
@Transactional(readOnly = true, rollbackFor = Throwable.class)
public Page
return this.baseRepository.findAll(pageable);
}
@Override
@Transactional(rollbackFor = Throwable.class)
public S save(S entity) {
return this.baseRepository.save(entity);
}
@Override
@Transactional(readOnly = true, rollbackFor = Throwable.class)
public Optional
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 entities) {
this.baseRepository.deleteAll(entities);
}
@Override
@Transactional(rollbackFor = Throwable.class)
public void deleteAll() {
this.baseRepository.deleteAll();
}
@Override
@Transactional(readOnly = true, rollbackFor = Throwable.class)
public Optional findOne(Example example) {
return this.baseRepository.findOne(example);
}
@Override
@Transactional(readOnly = true, rollbackFor = Throwable.class)
public Page findAll(Example example, Pageable pageable) {
return this.baseRepository.findAll(example, pageable);
}
@Override
@Transactional(readOnly = true, rollbackFor = Throwable.class)
public long count(Example example) {
return this.baseRepository.count(example);
}
@Override
@Transactional(readOnly = true, rollbackFor = Throwable.class)
public boolean exists(Example example) {
return this.baseRepository.exists(example);
}
@Override
@Transactional(readOnly = true, rollbackFor = Throwable.class)
public Optional
return this.baseRepository.findOne(spec);
}
@Override
@Transactional(readOnly = true, rollbackFor = Throwable.class)
public List
return this.baseRepository.findAll(spec);
}
@Override
@Transactional(readOnly = true, rollbackFor = Throwable.class)
public Page
return this.baseRepository.findAll(spec, pageable);
}
@Override
@Transactional(readOnly = true, rollbackFor = Throwable.class)
public List
return this.baseRepository.findAll(spec, sort);
}
@Override
@Transactional(readOnly = true, rollbackFor = Throwable.class)
public long count(Specification
return this.baseRepository.count(spec);
}
@Override
@Transactional(readOnly = true, rollbackFor = Throwable.class)
public Optional
return this.baseRepository.findOne(predicate);
}
@Override
@Transactional(readOnly = true, rollbackFor = Throwable.class)
public Iterable
return this.baseRepository.findAll(predicate);
}
@Override
@Transactional(readOnly = true, rollbackFor = Throwable.class)
public Iterable
return this.baseRepository.findAll(predicate, sort);
}
@Override
@Transactional(readOnly = true, rollbackFor = Throwable.class)
public Iterable
return this.baseRepository.findAll(predicate, orders);
}
@Override
@Transactional(readOnly = true, rollbackFor = Throwable.class)
public Iterable
return this.baseRepository.findAll(orders);
}
@Override
@Transactional(readOnly = true, rollbackFor = Throwable.class)
public Page
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
return function.apply(this.jpaQueryFactory);
}
@Transactional(readOnly = true, rollbackFor = Throwable.class)
public
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
}
package io.springboot.querydsl.service;
import org.springframework.stereotype.Service;
import io.springboot.querydsl.entity.Email;
@Service
public class EmailService extends AbstractService
}
package io.springboot.querydsl.service;
import org.springframework.stereotype.Service;
import io.springboot.querydsl.entity.Role;
@Service
public class RoleService extends AbstractService
}
package io.springboot.querydsl.service;
import org.springframework.stereotype.Service;
import io.springboot.querydsl.entity.UserRole;
@Service
public class UserRoleService extends AbstractService
}
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，本质上就是学怎么用它）
完整的工程源码
https://github.com/springboot-community/springboot-querydsl