自从邂逅了spring-data-jpa + querydsl 这个组合后,我再也没用过mybatis。
QueryDsl简单了解
QueryDSL可以在任何支持的ORM框架或者SQL平台上以一种通用的API方式来构建查询。目前QueryDSL支持的平台包括JPA,JDO,SQL,Mongodb 等等。
![53578cdd6cecdd13fd5fd2f01f5de89a.png](https://i-blog.csdnimg.cn/blog_migrate/a805f2c6c1b5000145f7fd1c6e025592.jpeg)
官网
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完整的核心依赖
4.0.0io.springboot.querydslspringboot-querydsl0.0.1-SNAPSHOTorg.springframework.bootspring-boot-starter-parent2.3.3.RELEASEUTF-8UTF-81.8trueorg.springframework.bootspring-boot-starter-testtestorg.springframework.bootspring-boot-starter-weborg.springframework.bootspring-boot-starter-tomcatorg.springframework.bootspring-boot-starter-undertoworg.springframework.bootspring-boot-starter-data-jpamysqlmysql-connector-javacom.zaxxerHikariCPcom.querydslquerydsl-jpacom.querydslquerydsl-aptprovidedorg.springframework.bootspring-boot-maven-plugintruetruecom.mysema.mavenapt-maven-plugin1.1.3processtarget/generated-sources/javacom.querydsl.apt.jpa.JPAAnnotationProcessor
除了jpa和必须的依赖(驱动,连接池)以外,querydsl只有3个组件。俩依赖,一个插件。插件的功能在于,在maven打包的时候,根据实体类生成查询对象。
配置文件
server: port: 80logging: level: root: DEBUG # 在日志中输SQL参数的绑定信息 'org.hibernate.type.descriptor.sql.BasicBinder': TRACEspring: 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;@Configurationpublic class JPAQueryFactoryConfiguration {@Beanpublic 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;@MappedSuperclasspublic 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.Idprivate 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;}@Overridepublic 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;}@Overridepublic 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;@NoRepositoryBeanpublic interface BaseRepository extends JpaRepository, JpaSpecificationExecutor, QuerydslPredicateExecutor {}
其他的几个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 extends JpaRepository, JpaSpecificationExecutor , QuerydslPredicateExecutor {}
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 implements BaseService {@Autowiredprotected BaseRepository baseRepository;@Autowiredprotected JPAQueryFactory jpaQueryFactory;@Override@Transactional(readOnly = true, rollbackFor = Throwable.class)public List findAll() {return this.baseRepository.findAll();}@Override@Transactional(readOnly = true, rollbackFor = Throwable.class)public List findAll(Sort sort) {return this.baseRepository.findAll(sort);}@Override@Transactional(readOnly = true, rollbackFor = Throwable.class)public List findAllById(Iterable ids) {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 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 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 findAll(Pageable pageable) {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 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 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 findOne(Specification spec) {return this.baseRepository.findOne(spec);}@Override@Transactional(readOnly = true, rollbackFor = Throwable.class)public List findAll(Specification spec) {return this.baseRepository.findAll(spec);}@Override@Transactional(readOnly = true, rollbackFor = Throwable.class)public Page findAll(Specification spec, Pageable pageable) {return this.baseRepository.findAll(spec, pageable);}@Override@Transactional(readOnly = true, rollbackFor = Throwable.class)public List findAll(Specification spec, Sort sort) {return this.baseRepository.findAll(spec, sort);}@Override@Transactional(readOnly = true, rollbackFor = Throwable.class)public long count(Specification spec) {return this.baseRepository.count(spec);}@Override@Transactional(readOnly = true, rollbackFor = Throwable.class)public Optional findOne(Predicate predicate) {return this.baseRepository.findOne(predicate);}@Override@Transactional(readOnly = true, rollbackFor = Throwable.class)public Iterable findAll(Predicate predicate) {return this.baseRepository.findAll(predicate);}@Override@Transactional(readOnly = true, rollbackFor = Throwable.class)public Iterable findAll(Predicate predicate, Sort sort) {return this.baseRepository.findAll(predicate, sort);}@Override@Transactional(readOnly = true, rollbackFor = Throwable.class)public Iterable findAll(Predicate predicate, OrderSpecifier>... orders) {return this.baseRepository.findAll(predicate, orders);}@Override@Transactional(readOnly = true, rollbackFor = Throwable.class)public Iterable findAll(OrderSpecifier>... orders) {return this.baseRepository.findAll(orders);}@Override@Transactional(readOnly = true, rollbackFor = Throwable.class)public Page 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 apply(Function function) {return function.apply(this.jpaQueryFactory);}@Transactional(readOnly = true, rollbackFor = Throwable.class)public R applyReadOnly(Function function) {return function.apply(this.jpaQueryFactory);}}
其他的几个Service
package io.springboot.querydsl.service;import org.springframework.stereotype.Service;import io.springboot.querydsl.entity.User;@Servicepublic class UserService extends AbstractService {}
package io.springboot.querydsl.service;import org.springframework.stereotype.Service;import io.springboot.querydsl.entity.Email;@Servicepublic class EmailService extends AbstractService {}
package io.springboot.querydsl.service;import org.springframework.stereotype.Service;import io.springboot.querydsl.entity.Role;@Servicepublic class RoleService extends AbstractService{}
package io.springboot.querydsl.service;import org.springframework.stereotype.Service;import io.springboot.querydsl.entity.UserRole;@Servicepublic 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开头。
![13b2f2b5daba57dff27b860df0f85825.png](https://i-blog.csdnimg.cn/blog_migrate/2e935aa491f063aa4f343dfc509dcf6d.jpeg)
启动工程自动创建数据表
如果启动过程没有异常的话,就会根据实体类在数据库自动的创建数据表(因为配置了自动建表策略)。
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