SpringBoot环境下QueryDSL-JPA的使用

标签：nat   rate   sql   isnull   pac   autowire   frame   findall   man   

4.0.0com.xiaohang.springio
    spring-data-jpa
    1.0-SNAPSHOTjarorg.springframework.boot
        spring-boot-parent
        2.1.0.RELEASEUTF-81.71.7org.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.10mysql
            mysql-connector-java
            
            5.1.46org.projectlombok
            lombok
            1.16.18com.querydsl
            querydsl-jpa
        com.querydsl
            querydsl-apt
            providedorg.springframework.boot
                spring-boot-maven-plugin
                org.springframework
                        springloaded
                        1.2.5.RELEASEcom.mysema.maven
                apt-maven-plugin
                1.1.3processtarget/generated-sources/javacom.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: false

3、实体类

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 {
}

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的使用

标签：nat   rate   sql   isnull   pac   autowire   frame   findall   man   

原文地址：https://blog.51cto.com/12012821/2409553