SpringBoot环境下QueryDSL-JPA的使用

2024-11-16 技术教程

1、Pom.xml文件依赖

<?xml version="1.0" encoding="UTF-8"?><project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-×××tance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.xiaohang.springio</groupId> <artifactId>spring-data-jpa</artifactId> <version>1.0-SNAPSHOT</version> <packaging>jar</packaging> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-parent</artifactId> <version>2.1.0.RELEASE</version> </parent> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <maven.compiler.source>1.7</maven.compiler.source> <maven.compiler.target>1.7</maven.compiler.target> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-rest</artifactId> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.10</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId>  <version>5.1.46</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.16.18</version> </dependency>  <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> <plug×××> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> <dependencies> <dependency> <groupId>org.springframework</groupId> <artifactId>springloaded</artifactId> <version>1.2.5.RELEASE</version> </dependency> </dependencies> </plugin>  <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> <processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor> </configuration> </execution> </executions> </plugin> </plug×××> </build></project>2、application.yml配置文件

# 数据库基本配置spring.datasource.type: com.alibaba.druid.pool.DruidDataSourcespring.datasource.username: rootspring.datasource.password: rootspring.datasource.url: jdbc:mysql://192.168.56.1:3306/test01?useUnicode=true&characterEncoding=UTF-8&useSSL=falsespring.datasource.driver-class-name: com.mysql.jdbc.Driver# 数据库方言spring.jpa.properties.hibernate.dialect: org.hibernate.dialect.MySQL57Dialectspring.jpa.show-sql: truespring.jpa.hibernate.ddl-auto: update# 数据库平台spring.jpa.database-platform: mysql# JPA配置spring.jpa.database: mysql# 禁用视图spring.jpa.open-in-view: false3、实体类

Customer.java

package com.xiaohang.springio.jpastudy.entity;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;import javax.persistence.*;@Data@NoArgsConstructor@AllArgsConstructor@Entity(name = "t_customer")public class Customer { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; @Column(name = "customer_name") private String name; @Column(name = "customer_part") private String part;}

Employees.java

package com.xiaohang.springio.jpastudy.entity;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;import javax.persistence.*;@Data@NoArgsConstructor@AllArgsConstructor@Entity(name = "t_employee")public class Employees { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; @Column(name = "employee_name") private String name; @Column(name = "employee_part") private String part;}4、Repository

package com.xiaohang.springio.jpastudy.repository;import com.xiaohang.springio.jpastudy.entity.Employees;import org.springframework.data.jpa.repository.JpaRepository;public interface EmployeesRepository extends JpaRepository<Employees, Long> {}

package com.xiaohang.springio.jpastudy.repository;import com.xiaohang.springio.jpastudy.entity.Customer;import org.springframework.data.jpa.repository.JpaRepository;public interface CustomerRepository extends JpaRepository<Customer, Long> {}5、JPAQueryFactory类的使用重点

package com.xiaohang.springio.jpastudy.service;import com.querydsl.core.BooleanBuilder;import com.querydsl.core.QueryResults;import com.querydsl.core.types.Projections;import com.querydsl.jpa.JPAExpressions;import com.querydsl.jpa.impl.JPAQuery;import com.querydsl.jpa.impl.JPAQueryFactory;import com.xiaohang.springio.jpastudy.entity.Customer;import com.xiaohang.springio.jpastudy.entity.Employees;import com.xiaohang.springio.jpastudy.entity.QCustomer;import com.xiaohang.springio.jpastudy.entity.QEmployees;import com.xiaohang.springio.jpastudy.repository.CustomerRepository;import com.xiaohang.springio.jpastudy.repository.EmployeesRepository;import org.junit.Test;import org.junit.runner.RunWith;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.boot.test.context.SpringBootTest;import org.springframework.context.annotation.Bean;import org.springframework.test.context.junit4.SpringRunner;import javax.persistence.EntityManager;import java.util.List;@RunWith(SpringRunner.class)@SpringBootTestpublic class BookServiceTest { // 注入JPAQueryFactory @Bean @Autowired public JPAQueryFactory jpaQuery(EntityManager entityManager) { return new JPAQueryFactory(entityManager); } @Autowired private JPAQueryFactory jpaQueryFactory; @Autowired private EmployeesRepository employeesRepository; @Autowired private CustomerRepository customerRepository; @org.junit.Test public void jpaQuery() { Employees employees = new Employees(); employees.setName("老李"); employees.setPart("运营部"); employeesRepository.saveAndFlush(employees); } @org.junit.Test public void updateBook() { Customer customer = new Customer(); customer.setName("老黄"); customer.setPart("生产部"); customerRepository.saveAndFlush(customer); } /** * 数据库联接查询 */ @org.junit.Test public void findAllAuthors() { QCustomer qc = QCustomer.customer; QEmployees qe = QEmployees.employees; // 内联接 List<Employees> employeesList = jpaQueryFactory.select(Projections.constructor(Employees.class, qe.id, qe.name, qe.part)).from(qe).innerJoin(qc).on(qc.id.eq(qe.id)).fetch(); employeesList.forEach(e -> { System.out.println(e.getName()); }); // 左联接 List<Employees> employeesList1 = jpaQueryFactory.select(Projections.constructor(Employees.class, qe.id, qe.name, qe.part)).from(qe).leftJoin(qc).on(qc.id.eq(qe.id)).fetch(); employeesList1.forEach(employees -> { System.out.println(employees.getName()); }); // 右联接 List<Employees> employeesList2 = jpaQueryFactory.select(Projections.constructor(Employees.class, qe.id, qe.name, qe.part)).from(qe).rightJoin(qc).on(qc.id.eq(qe.id)).fetch(); employeesList2.forEach(employees -> { System.out.println(employees.getName()); }); // 左外 List<Employees> employeesList3 = jpaQueryFactory.select(Projections.constructor(Employees.class, qe.id, qe.name, qe.part)).from(qe).leftJoin(qc).on(qc.id.eq(qe.id)).where(qc.id.isNull()).fetch(); employeesList3.forEach(employees -> { System.out.println(employees.getName()); }); } /** * 多条件查询 */ @org.junit.Test public void findAll() { // select customer0_.id as id1_1_, customer0_.customer_name as customer2_1_, customer0_.customer_part as customer3_1_ from t_customer customer0_ where (customer0_.customer_name like ? escape '!' or customer0_.customer_name=?) and (customer0_.id between ? and ?) QCustomer qc = QCustomer.customer; BooleanBuilder builder = new BooleanBuilder(); builder.and(qc.name.like('%' + "刘" + '%')); builder.or(qc.name.eq("老王")); builder.and(qc.id.between(3, 5)); List<Customer> customerList = jpaQueryFactory.selectFrom(qc).where(builder).fetch(); customerList.forEach(customer -> { System.out.println(customer.getName()); }); } /** * 子查询 */ @org.junit.Test public void findAllDto() { QCustomer qc = QCustomer.customer; QEmployees qe = QEmployees.employees; List<Customer> customerList = jpaQueryFactory.selectFrom(qc).where(qc.id.eq(JPAExpressions.select(qe.id).from(qe).where(qe.name.eq("老张")))).fetch(); customerList.forEach(customer -> { System.out.println(customer.getName()); }); } /** * 排序 */ @Test public void testDesc() { QCustomer qc = QCustomer.customer; List<Customer> customerList = jpaQueryFactory.selectFrom(qc).orderBy(qc.id.desc()).fetch(); customerList.forEach(customer -> { System.out.println(customer.getName()); }); } /** * 分页 */ @Test public void testPage() { QCustomer qc = QCustomer.customer; QEmployees qe = QEmployees.employees; // 写法一 JPAQuery<Employees> employeesJPAQuery = jpaQueryFactory.selectFrom(qe); // offset 起始页 List<Employees> employeesList = employeesJPAQuery.offset(3).limit(2).fetch(); employeesList.forEach(employees -> { System.out.println(employees); }); // 写法二 Long total = employeesJPAQuery.fetchCount(); QueryResults<Customer> customerQueryResults = jpaQueryFactory.selectFrom(qc).offset(0).limit(3).fetchResults(); List<Customer> results = customerQueryResults.getResults(); results.forEach(customer -> { System.out.println(results); }); }}6、推存阅读及参考

https://www.jianshu.com/p/69dcb1b85bbb