SpringBoot环境下QueryDSL-JPA的使用
1、Pom.xml文件依赖
4.0.0
com.xiaohang.springio
spring-data-jpa
1.0-SNAPSHOT
jar
org.springframework.boot
spring-boot-parent
2.1.0.RELEASE
UTF-8
1.7
1.7
org.springframework.boot
spring-boot-starter-web
org.springframework.boot
spring-boot-starter-test
org.springframework.boot
spring-boot-starter-data-jpa
org.springframework.boot
spring-boot-starter-data-rest
com.alibaba
druid-spring-boot-starter
1.1.10
MySQL
mysql-connector-java
5.1.46
org.projectlombok
lombok
1.16.18
com.querydsl
querydsl-jpa
com.querydsl
querydsl-apt
provided
org.springframework.boot
spring-boot-maven-plugin
org.springframework
springloaded
1.2.5.RELEASE
com.mysema.maven
apt-maven-plugin
1.1.3
process
target/generated-sources/java
com.querydsl.apt.jpa.JPAAnnotationProcessor
2、application.yml配置文件
# 数据库基本配置
spring.datasource.type: com.alibaba.druid.pool.DruidDataSource
spring.datasource.username: root
spring.datasource.password: root
spring.datasource.url: jdbc:mysql://192.168.56.1:3306/test01?useUnicode=true&characterEncoding=UTF-8&useSSL=false
spring.datasource.driver-class-name: com.mysql.jdbc.Driver
# 数据库方言
spring.jpa.properties.hibernate.dialect: org.hibernate.dialect.MySQL57Dialect
spring.jpa.show-sql: true
spring.jpa.hibernate.ddl-auto: update
# 数据库平台
spring.jpa.database-platform: mysql
# JPA配置
spring.jpa.database: mysql
# 禁用视图
spring.jpa.open-in-view: false3、实体类
Customer.java
创新互联建站专注于企业成都全网营销、网站重做改版、花都网站定制设计、自适应品牌网站建设、H5高端网站建设、商城网站建设、集团公司官网建设、外贸营销网站建设、高端网站制作、响应式网页设计等建站业务，价格优惠性价比高，为花都等各大城市提供网站开发制作服务。
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 {
} 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 {
} 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)
@SpringBootTest
public 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 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 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 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 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 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 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 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 employeesJPAQuery = jpaQueryFactory.selectFrom(qe);
// offset 起始页
List employeesList = employeesJPAQuery.offset(3).limit(2).fetch();
employeesList.forEach(employees -> {
System.out.println(employees);
});
// 写法二
Long total = employeesJPAQuery.fetchCount();
QueryResults customerQueryResults = jpaQueryFactory.selectFrom(qc).offset(0).limit(3).fetchResults();
List results = customerQueryResults.getResults();
results.forEach(customer -> {
System.out.println(results);
});
}
} 6、推存阅读及参考
https://www.jianshu.com/p/69dcb1b85bbb
本文名称：SpringBoot环境下QueryDSL-JPA的使用
分享URL：http://scyanting.com/article/pcieco.html
