Spring Data JPA

最新推荐文章于 2024-09-27 00:45:01 发布

算法小菜鸟阿健

最新推荐文章于 2024-09-27 00:45:01 发布

阅读量124

点赞数 1

文章标签： java

本文链接：https://blog.csdn.net/weixin_53020123/article/details/133884543

版权

前提

1.创建pojo

package com.StuSpringData.pojo;

import javax.persistence.*;

/**
 * @author 周健
 * @version 1.0
 * @description: TODO
 * @date 2023/10/13 11:16
 */
@Entity
@Table(name="tb_customer")
public class Customer {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "cust_id")
    private Long custId;

    @Column(name="cust_name")
    private String custName;
    @Column(name="cust_address")
    private String custAddress;

    public Long getCustId() {
        return custId;
    }

    public void setCustId(Long custId) {
        this.custId = custId;
    }

    public String getCustName() {
        return custName;
    }

    public void setCustName(String custName) {
        this.custName = custName;
    }

    public String getCustAddress() {
        return custAddress;
    }

    public void setCustAddress(String custAddress) {
        this.custAddress = custAddress;
    }

    public Customer() {
    }

    public Customer(Long custId, String custName, String custAddress) {
        this.custId = custId;
        this.custName = custName;
        this.custAddress = custAddress;
    }

    @Override
    public String toString() {
        return "Customer{" +
                "custId=" + custId +
                ", custName='" + custName + '\'' +
                ", custAddress='" + custAddress + '\'' +
                '}';
    }
}

2.配置文件

，两种方式，一种是xml文件配置，一种config文件配置

config方式

package com.StuSpringData.config;

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

//import javax.activation.DataSource;

import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;

/**
 * @author 周健
 * @version 1.0
 * @description: TODO
 * @date 2023/10/16 10:56
 */
@Configuration                  //标记当前类为配置类==xml文件
//启动jpa   <jpa:repositories>
@EnableJpaRepositories(basePackages = "com.StuSpringData.repositories")
//开启事务
@EnableTransactionManagement
public class SpringDataJPAConfig {
    /*
     * @Author 周健
     * @Description //数据库
     * @Date 11:05 2023/10/16
     * @Param []
     * @return javax.sql.DataSource
     **/
    @Bean
    public DataSource dataSource() {

        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUsername("root");
        dataSource.setPassword("zhoujian0.");
        dataSource.setUrl("jdbc:mysql://localhost:3306/spring_jpa?characterEncoding=UTF-8");
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        return dataSource;
    }
    /*
     * @Author 周健
     * @Description //EntityManagerFactory
     * @Date 11:06 2023/10/16
     * @Param
     * @return
     **/
    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory() {

        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        vendorAdapter.setGenerateDdl(true);
        vendorAdapter.setShowSql(true);//显示sql

        LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
        factory.setJpaVendorAdapter(vendorAdapter);
        factory.setPackagesToScan("com.StuSpringData.pojo");
        factory.setDataSource(dataSource());
        return factory;
    }
    //事务
    @Bean
    public PlatformTransactionManager transactionManager(EntityManagerFactory entityManagerFactory) {

        JpaTransactionManager txManager = new JpaTransactionManager();
        txManager.setEntityManagerFactory(entityManagerFactory);
        return txManager;
    }

}

xml方式

在resource目录下，心间spring.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:jpa="http://www.springframework.org/schema/data/jpa"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
    https://www.springframework.org/schema/beans/spring-beans.xsd
    http://www.springframework.org/schema/tx
    http://www.springframework.org/schema/tx/spring-tx.xsd
    http://www.springframework.org/schema/data/jpa
    https://www.springframework.org/schema/data/jpa/spring-jpa.xsd">
    <!--    用于整合jpa  @EnableJpaRepositories-->
    <jpa:repositories base-package="com.StuSpringData.repositories"
                      entity-manager-factory-ref="entityManagerFactory"
                      transaction-manager-ref="transactionManager"
    />
    <!--EntityManagerFactory-->
    <bean name="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="jpaVendorAdapter">
            <!--hibernate实现-->
            <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
                <!--生成数据库表-->
                <property name="generateDdl" value="true"></property>
                <property name="showSql" value="true"></property>
            </bean>
        </property>
        <!--设置实体类的包-->
        <property name="packagesToScan" value="com.StuSpringData.pojo"></property>
        <property name="dataSource" ref="dataSource"></property>
    </bean>
    <!--配置数据源-->
    <bean class="com.alibaba.druid.pool.DruidDataSource" name="dataSource">
        <property name="username" value="root"/>
        <property name="password" value="zhoujian0."/>
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/spring_jpa?characterEncoding=UTF-8"/>
    </bean>

    <!--声明事务-->
    <bean class="org.springframework.orm.jpa.JpaTransactionManager" name="transactionManager">
        <property name="entityManagerFactory" ref="entityManagerFactory"></property>
    </bean>
    <!--启动注解的方式生成事务-->
    <tx:annotation-driven transaction-manager="transactionManager"></tx:annotation-driven>
</beans>

JPQL

配置接口

package com.StuSpringData.repositories;

import com.StuSpringData.pojo.Customer;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * @author 周健
 * @version 1.0
 * @description: TODO
 * @date 2023/10/16 10:41
 */
public interface CustomerRepository extends CrudRepository <Customer,Long>{

    //CRUD
    //查询
    @Query("From Customer where custName=:custName")
    List<Customer> findCustomerByCustName(@Param("custName") String custName);
    //U
    @Transactional
    @Modifying //通知springdatajpa是增删改查操作
    @Query("UPDATE Customer c set c.custName=:custName where c.custId=:Id ")
    int updateCustomerByID(@Param("custName") String custName,@Param("Id") Long id);

    //D
    @Transactional
    @Modifying //通知springdatajpa是增删改查操作
    @Query("delete from Customer c  where c.custId=:Id ")
    int deleteCustomer(@Param("Id") Long id);

    //C
    @Transactional
    @Modifying //通知springdatajpa是增删改查操作
    @Query("Insert into Customer (custName) select c.custName from Customer c where c.custId=?1")
    int insertCustomerBySelect(Long id);
    //原生SQl
    @Query(value = "select * From tb_customer where cust_name=:custName",nativeQuery = true)
    List<Customer> findCustomerBySql(@Param("custName") String custName);
}

PagingAndSortingRepository---继承这个可以实现分页和排序

简单操作(save,delete)

package com.springdata_jpa.test;

import com.StuSpringData.config.SpringDataJPAConfig;
import com.StuSpringData.pojo.Customer;
import com.StuSpringData.repositories.CustomerRepository;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.util.Optional;

/**
 * @author 周健
 * @version 1.0
 * @description: TODO
 * @date 2023/10/16 10:44
 */
//基于junit4 spring单元测试
// 利用xml方式配置
//@ContextConfiguration(locations = "/spring.xml")
// 利用config配置方式
@ContextConfiguration(classes = SpringDataJPAConfig.class)
@RunWith(SpringJUnit4ClassRunner.class)
public class testMain {
    @Autowired
    CustomerRepository repository;

    @Test
    public void testR() {
        Optional<Customer> byId = repository.findById(1L);
        System.out.println(byId.get());
    }

    @Test
    public void testC() {
        Customer customer = new Customer();
        customer.setCustName("周健");
        repository.save(customer);
    }

    @Test
    public void testU() {
        Customer customer = new Customer();
        customer.setCustId(8L);
        customer.setCustName("周健");
        repository.save(customer);//先查
    }

    @Test
    public void testD() {
        Customer customer = new Customer();
        customer.setCustId(8L);
        customer.setCustName("周健");
        repository.delete(customer);//底层先查一遍再删除
    }


}

save操作时，有三种情况,

一，在new对象的时候没有指定主键，则默认为新增数据操作，底层只执行insert

二，指定了一条不存在的数据，主键不存在的数据，比如数据库只有22条数据，指定主键为23，数据库中不包含主键23的数据，所以底层会执行insert方法

三，制定了一条存在的数据，即数据库中22条数据包含主键为20的数据，这个时候save底层执行update方法

解释，当指定主键后，save方法会执行两个操作，先select查询一遍数据库，如果没找到则全部执行insert方法，若找到数据则执行update操作，注意这种操作比较危险，实体类中没有set数据的属性，会被全部重置为null

delete操作时，底层也是执行两个操作，先查询，再删除，若是未查询到数据，则不执行任何操作，找到数据则执行delete方法

JPQL方式

package com.springdata_jpa.test;

import com.StuSpringData.config.SpringDataJPAConfig;
import com.StuSpringData.pojo.Customer;
import com.StuSpringData.repositories.CustomerRepository;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Optional;

/**
 * @author 周健
 * @version 1.0
 * @description: TODO
 * @date 2023/10/16 13:45
 */
@ContextConfiguration(classes = SpringDataJPAConfig.class)
@RunWith(SpringJUnit4ClassRunner.class)
public class jpqlTest {
    @Autowired
    CustomerRepository repository;

    @Test
    public void testR() {
        List<Customer> customer = repository.findCustomerByCustName("周健");
        System.out.println(customer);
    }

    @Test
    public void testU() {
        System.out.println(
                repository.updateCustomerByID("周佳怡", 5L)
        );
    }

    @Test
    public void testD() {
        System.out.println(
                repository.deleteCustomer(5L)
        );
    }

    @Test
    public void testC() {
        System.out.println(
                repository.insertCustomerBySelect(6L)
        );
    }

    @Test
    public void testRBySQL() {
        List<Customer> customer = repository.findCustomerBySql("周健");
        System.out.println(customer);
    }
}

需要在注入的接口中定义好各种注解，和sql语句，然后按规矩调用就行。

sql语句需要配合@Query来使用，如果是原生sql需要加nativeQuery参数，

增删改操作还需要加@Modifying这个注解的作用是告诉spring data jpa现在执行的是增删改查操作，同样必不可少的还有事务注解。

还需要注意传参方式有两种一种用？（当占位符）的代表形参的方式（不需要加入注解），还有一种利用字符代表形参（利用 :当做占位符），但是需要配合@Param注解才能传入参数，

注意：新增只能在hibernate的orm下支持，且只支持(insert into .....select)的方式。就是只支持插入查询出的数据（伪插入）(完全可以利用save方式新增，所以也不需要jpql提供insert支持)不能直接利用站位符的形式插入数据 (暂时未验证)

规定方法名（只支持查询和删除）

配置接口

package com.StuSpringData.repositories;

import com.StuSpringData.pojo.Customer;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.repository.CrudRepository;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

public interface CustomerMethodNameRepository extends CrudRepository<Customer,Long> {
    List<Customer> findByCustName(String custName);
    boolean existsByCustName(String custName);
    @Modifying
    @Transactional
    int deleteByCustId(Long id);

    List<Customer> findByCustNameLike(String custName);
}

规定方法名方式

package com.springdata_jpa.test;

import com.StuSpringData.config.SpringDataJPAConfig;
import com.StuSpringData.pojo.Customer;
import com.StuSpringData.repositories.CustomerMethodNameRepository;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.util.List;

/**
 * @author 周健
 * @version 1.0
 * @description: TODO
 * @date 2023/10/16 14:44
 */
@ContextConfiguration(classes = SpringDataJPAConfig.class)
@RunWith(SpringJUnit4ClassRunner.class)
public class MethodNameTest {
    @Autowired
    CustomerMethodNameRepository repository;

    @Test
    public void test01() {
        List<Customer> list = repository.findByCustName("周健");
        System.out.println(list);
    }

    @Test
    public void test02() {
        boolean list = repository.existsByCustName("周健");
        System.out.println(list);
    }

    @Test
    public void test03() {
        int list = repository.deleteByCustId(12L);
        System.out.println(list);
    }
    //模糊查询，需要自己拼接
    @Test
    public void test04() {
        List<Customer> list = repository.findByCustNameLike("周%");
        System.out.println(list);
    }

}

注意：改方法是通过方法命名来完成sql拼接的，

学习可查看只有查询部分删除部分没有，但是规则是一样的，https://blog.csdn.net/xhaimail/article/details/102585807

模糊查询需要自己拼接%号

Query by Example

配置接口

package com.StuSpringData.repositories;

import com.StuSpringData.pojo.Customer;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.QueryByExampleExecutor;

public interface CustomerQBERepository
        extends QueryByExampleExecutor<Customer>,
        PagingAndSortingRepository<Customer , Long>,
        JpaRepository<Customer,Long> {

}

Query by Example方式

package com.springdata_jpa.test;

import com.StuSpringData.config.SpringDataJPAConfig;
import com.StuSpringData.pojo.Customer;
import com.StuSpringData.repositories.CustomerQBERepository;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Example;
import org.springframework.data.domain.ExampleMatcher;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.util.List;

/**
 * @author 周健
 * @version 1.0
 * @description: TODO
 * @date 2023/10/16 15:16
 */
@ContextConfiguration(classes = SpringDataJPAConfig.class)
@RunWith(SpringJUnit4ClassRunner.class)
public class QBETest {
    @Autowired
    CustomerQBERepository repository;
    /**
     * 按客户名称，客户地址动态查询
     **/
    @Test
    public void test01(){
        //查询条件
        Customer customer = new Customer();
        customer.setCustName("周健");
//        customer.setCustAddress("成都市");
//        //通过匹配器对条件进行设置
//        ExampleMatcher exampleMatcher = ExampleMatcher.matching()
//                .withIgnorePaths("CustAddress");
        //构建查询条件
//        Example<Customer> example = Example.of(customer,exampleMatcher);
        Example<Customer> example = Example.of(customer);
        List<Customer> list = (List<Customer>) repository.findAll(example);
        System.out.println(list);
    }
}

这个很基础，还可以通过 ExampleMatcher拼接条件，还需呀继续学习。

注意：Query by Example方式不支持嵌套或分组的属性约束，比如and or 拼接条件，只支持简单的约束条件QBE只支持＝和like，不支持or > <之类的查询

精确匹配：这表示你可以直接指定一个属性的确切值来进行匹配，例如 firstname = "John" 将会匹配所有名字为 "John" 的记录。

字符串匹配：

start：允许你以特定的字符串开始进行匹配。例如，firstname starts with "Jo" 可以匹配所有以 "Jo" 开头的名字。
contains：允许你在字符串中查找包含特定子字符串的记录。例如，address contains "Street" 可以匹配所有地址中包含 "Street" 的记录。
ends：允许你以特定的字符串结尾进行匹配。例如，email ends with "@example.com" 可以匹配所有以 "@example.com" 结尾的邮箱地址。
regex：支持正则表达式匹配，允许更复杂的字符串模式匹配。例如，phone matches regex "\d{3}-\d{3}-\d{4}" 可以匹配符合 xxx-xxx-xxxx 格式的电话号码。

只支持字符串 start/contains/ends/regex 匹配和其他属性类型的精确匹配（其他属性的值只支持精确匹配，即=号）Query by Example只能针对字符串进行条件设置

Query by Example (QBE) 在匹配查询条件时只支持对字符串类型的属性进行查询，而不支持对其他属性类型（比如数字、日期等）进行查询。

Specifications方式

配置接口

package com.StuSpringData.repositories;

import com.StuSpringData.pojo.Customer;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * @author 周健
 * @version 1.0
 * @description: TODO
 * @date 2023/10/16 10:41
 */
public interface CustomerSpecificationsRepository
        extends PagingAndSortingRepository<Customer,Long>,
        JpaSpecificationExecutor<Customer>
{
}

Specifications方式

package com.springdata_jpa.test;

import com.StuSpringData.config.SpringDataJPAConfig;
import com.StuSpringData.pojo.Customer;
import com.StuSpringData.repositories.CustomerSpecificationsRepository;
import com.alibaba.druid.util.StringUtils;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import javax.persistence.Parameter;
import javax.persistence.criteria.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 周健
 * @version 1.0
 * @description: TODO
 * @date 2023/10/16 16:21
 */
@ContextConfiguration(classes = SpringDataJPAConfig.class)
@RunWith(SpringJUnit4ClassRunner.class)
public class SpecificationsTest {
    @Autowired
    CustomerSpecificationsRepository repository;
    @Test
    public void test01(){
        List<Customer> customers = repository.findAll(new Specification<Customer>() {
            @Override
            public Predicate toPredicate(Root<Customer> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
                //root  ==  from   ,获取列
                //criteriaBuilder  ==  where   ,设置各种条件
                //query   == 组合(oder by,where)
                return null;
            }
        });
    }
    /**
     * 查询客户范围
     * id  between  >
     * 地址精确
     **/
    @Test
    public void test02(){
        List<Customer> customers = repository.findAll(new Specification<Customer>() {
            @Override
            public Predicate toPredicate(Root<Customer> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
                //root  ==  from   ,获取列
                //criteriaBuilder  ==  where   ,设置各种条件
                //query   == 组合(oder by,where)
                Path<Long>  custId=root.get("custId");
                Path<String>  custName=root.get("custName");
                Path<String>  custAddress=root.get("custAddress");
                //参数1：为那个字段设置条件，参数2：值
                Predicate custAddressP=criteriaBuilder.equal(custAddress,"CHENGDU");
                Predicate custIdP=criteriaBuilder.greaterThan(custId,0L);
                CriteriaBuilder.In<String> in=criteriaBuilder.in(custName);
                in.value("周健").value("周佳怡");
                Predicate predicate = criteriaBuilder.and
                        (custAddressP,custIdP,in);
                return predicate;
            }
        });
        System.out.println(customers);
    }
    /*
     * @Author 周健
     * @Description //动态条件
     * @Date 16:41 2023/10/16
     * @Param []
     * @return void
     **/
    @Test
    public void test03(){
        Customer params = new Customer();
        params.setCustAddress("CHENGDU");
        params.setCustId(0L);
        params.setCustName("周健");
        List<Customer> customers = repository.findAll(new Specification<Customer>() {
            @Override
            public Predicate toPredicate(Root<Customer> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
                //root  ==  from   ,获取列
                //criteriaBuilder  ==  where   ,设置各种条件
                //query   == 组合(oder by,where)
                Path<Long>  custId=root.get("custId");
                Path<String>  custName=root.get("custName");
                Path<String>  custAddress=root.get("custAddress");
                //参数1：为那个字段设置条件，参数2：值
                List<Predicate> list= new ArrayList<>();
                if(!StringUtils.isEmpty(params.getCustAddress())){
                    Predicate custAddressP=criteriaBuilder.equal(custAddress,params.getCustAddress());
                    list.add(custAddressP);
                }
                if(params.getCustId()>-1){
                    Predicate custIdP=criteriaBuilder.greaterThan(custId,params.getCustId());
                    list.add(custIdP);
                }
                if (!StringUtils.isEmpty(params.getCustName())){
                    CriteriaBuilder.In<String> in=criteriaBuilder.in(custName);
                    in.value(params.getCustName());
                    list.add(in);
                }
                Predicate predicate = criteriaBuilder.and(list.toArray(new Predicate[list.size()]));
                Order desc=criteriaBuilder.desc(custId);
                return query.where(predicate).orderBy(desc).getRestriction();
            }
        });
        System.out.println(customers);
    }

}

限制：不能分组，不能用聚合函数要用的话只能用entityManager拼接
root == from ,获取列，criteriaBuilder == where ,设置各种条件，query == 组合(oder by,where)

Querydsl方式

接口

package com.StuSpringData.repositories;

import com.StuSpringData.pojo.Customer;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;
import org.springframework.data.repository.PagingAndSortingRepository;

/**
 * @author 周健
 * @version 1.0
 * @description: TODO
 * @date 2023/10/16 10:41
 */
public interface CustomerQueryDSLRepository
        extends PagingAndSortingRepository<Customer,Long>,
        QuerydslPredicateExecutor<Customer>
{
}

Querydsl方式

package com.springdata_jpa.test;


import com.StuSpringData.config.SpringDataJPAConfig;
import com.StuSpringData.pojo.Customer;
import com.StuSpringData.pojo.QCustomer;
import com.StuSpringData.repositories.CustomerQueryDSLRepository;
import com.alibaba.druid.util.StringUtils;
import com.querydsl.core.Tuple;
import com.querydsl.core.types.dsl.BooleanExpression;
import com.querydsl.jpa.impl.JPAQuery;
import com.querydsl.jpa.impl.JPAQueryFactory;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import java.util.List;

/**
 * @author 周健
 * @version 1.0
 * @description: todo
 * @date 2023/10/16 17:05
 */
@ContextConfiguration(classes = SpringDataJPAConfig.class)
@RunWith(SpringJUnit4ClassRunner.class)
public class QueryDSLTest {
    @Autowired
    CustomerQueryDSLRepository repository;

    @Test
    public void test01() {
        QCustomer customer = QCustomer.customer;

        //通过id查找
        BooleanExpression eq = customer.custId.eq(1l);
        ;
        System.out.println(repository.findOne(eq));
    }
    /**
     * 查询客户范围（in）
     * id  between  >
     * 地址精确
     **/
    @Test
    public void test02() {
        QCustomer customer = QCustomer.customer;
        BooleanExpression eq = customer.custName.in("周佳怡", "周健")
                .and(customer.custId.gt(0L))
                .and(customer.custAddress.eq("CHENGDU"));

        System.out.println(repository.findAll(eq));
    }

    /**
     * 查询客户范围（in）
     * id  between  >
     * 地址精确
     **/
    @Test
    public void test03(){
        Customer params = new Customer();
        params.setCustAddress("CHENGDU");
        params.setCustId(0L);
        params.setCustName("周健");

        QCustomer customer = QCustomer.customer;
        //初始条件永真
        BooleanExpression eq = customer.isNotNull().or(customer.isNull());

        eq=params.getCustId()>0?
                eq.and(customer.custId.gt(params.getCustId())):eq;
        eq=!StringUtils.isEmpty(params.getCustName())?
                eq.and(customer.custName.in(params.getCustName().split(","))):eq;
        eq=!StringUtils.isEmpty(params.getCustAddress())?
                eq.and(customer.custAddress.eq(params.getCustAddress())):eq;
        System.out.println(repository.findAll(eq));
    }

    /**
     * 自定义列查询，分组
     * 用原生的方式
     * （specification同样）
     *
     * 通过Repository进行查询，列和表都是固定的
     **/

//    @Autowired //有线程安全问题jpa中
    @PersistenceContext
    EntityManager em;
    @Test
    public void test04(){
        JPAQueryFactory factory = new JPAQueryFactory(em);
        QCustomer customer = QCustomer.customer;
        //查询id和名字
        //不指定select就是查询所有
        //构建基于QueryDSL的查询
        JPAQuery<Tuple> tupleJPAQuery = factory.select(customer.custId, customer.custName)
                .from(customer)
                .where(customer.custId.eq(1L))
                .orderBy(customer.custId.desc());
        //执行查询
        List<Tuple> fetch = tupleJPAQuery.fetch();
        //处理返回数据
        for (Tuple tuple : fetch) {
            System.out.println(tuple.get(customer.custId));
            System.out.println(tuple.get(customer.custName));
        }
    }
    @Test
    public void test05(){
        JPAQueryFactory factory = new JPAQueryFactory(em);
        QCustomer customer = QCustomer.customer;
        //查询id和名字
        //不指定select就是查询所有
        //构建基于QueryDSL的查询
        JPAQuery<Long> tupleJPAQuery = factory.select(customer.custId.sum())
                .from(customer)
//                .where(customer.custId.eq(1L))
                .orderBy(customer.custId.desc());
        //执行查询
        List<Long> fetch = tupleJPAQuery.fetch();
        //处理返回数据
        for (Long tuple : fetch) {
            System.out.println(tuple);
        }
    }

}