refer to : https://docs.spring.io/spring-data/jpa/docs/2.7.12/reference/html/

https://segmentfault.com/a/1190000037755804

常用注解

@Table

指定实体类对应的数据库表名称

@Table(name = "device_monitor")
public class DeviceMonitorPO {

@Entity

定义对象将会成为被 JPA 管理的实体，

@Id

指定某个属性为数据库的主键，一个实体里面必须有一个主键，但不一定是这个注解，可以和 @GeneratedValue 配合使用或成对出现。

@Basic

表示属性是到数据库表的字段的映射。如果实体的字段上没有任何注解，默认即为 @Basic。

也就是说默认所有的字段肯定是和数据库进行映射的，并且默认为 Eager 类型。

public @interface Basic {
    //可选，EAGER（默认）：立即加载；LAZY：延迟加载。（LAZY主要应用在大字段上面）
    FetchType fetch() default EAGER;
    //可选。这个字段是否可以为null，默认是true。
    boolean optional() default true;
}

@Transient

表示该属性并非一个到数据库表的字段的映射，表示非持久化属性。JPA 映射数据库的时候忽略它，与 @Basic 有相反的作用。

也就是每个字段上面 @Transient 和 @Basic 必须二选一，而什么都不指定的话，默认是 @Basic。

@Column

定义该属性对应数据库中的列名。

@Temporal

设置 Date 类型的属性映射到对应精度的字段，存在以下三种情况：

@Temporal(TemporalType.DATE)映射为日期 // date （只有日期）

@Temporal(TemporalType.TIME)映射为日期 // time （只有时间）

@Temporal(TemporalType.TIMESTAMP)映射为日期 // date time （日期+时间）

自定义的Repository

那么我们怎么自己去编写一些简单的代码呢?

我们以根据name查询person为例:
在repository接口中添加如下查询方法：

1. 注意方法名一定是findBy+属性名

   Person findByName(String name);
   

   还需要注意根据ID查找的findById是不用自己添加方法的,由接口已经封装,但是源码中返回的是Optional 类型。那么这个时候该如何获得T 实体类类型呢,只需要get()即可,就是findById(Id).get() 即返回T类型

2. 除了添加findBy这种不用写sql的方法外,还有一种可以自己编写sql的方法:

   可以在所添加的方法上通过@Query注解,在value属性上写sql语句来完成对数据库的操作,

   带参查询:（1、根据参数位置2、根据Param注解）

      /**
        * 查询根据参数位置
        * @param name
        * @return
        */
       @Query(value = "select * from person  where name = ?1",nativeQuery = true)
       Person findPersonByName(String Name);
    
       /**
        * 查询根据Param注解
        * @param name
        * @return
        */
       @Query(value = "select p from person p where p.uname = :name")
       Person findPersonByNameTwo(@Param("name") String name);
   

   相信大家也注意到,在@Query中传入了一个属性nativeQuery,

   • @Query有nativeQuery=true，表示可执行的原生sql，原生sql指可以直接复制sql语句给参数赋值就能运行
   • @Query无nativeQuery=true， 表示不是原生sql，查询语句中的表名则是对应的项目中实体类的类名

注意:

对于自定义sql的删改方法,在方法上还要添加@Transactional/@Modifying注解,如下所示:

@Transactional
@Modifying
@Query(value = "delete from Account where id =?1",nativeQuery = true)
void delAccount(int id);

这里去了解了一下其生成sql的原理:

其实JPA在这里遵循Convention over configuration（约定大约配置）的原则，遵循spring 以及JPQL定义的方法命名。Spring提供了一套可以通过命名规则进行查询构建的机制。这套机制会把方法名首先过滤一些关键字，比如 find…By, read…By, query…By, count…By 和 get…By 。系统会根据关键字将命名解析成2个子语句，第一个 By 是区分这两个子语句的关键词。这个 By 之前的子语句是查询子语句（指明返回要查询的对象），后面的部分是条件子语句。如果直接就是 findBy… 返回的就是定义Respository时指定的领域对象集合，同时JPQL中也定义了丰富的关键字：and、or、Between等等，下面我们来看一下JPQL中有哪些关键字：

Keyword Sample JPQL snippet

1. And----findByLastnameAndFirstname----where x.lastname = ?1 and
2. Or----findByLastnameOrFirstname----where x.lastname = ?1 or x.firstname = ?2
3. Is,Equals----findByFirstnameIs,findByFirstnameEquals----where x.firstname = ?1
4. Between----findByStartDateBetween----where x.startDate between ?1 and ?2
5. LessThan----findByAgeLessThan----where x.age < ?1
6. LessThanEqual----findByAgeLessThanEqual----where x.age ⇐ ?1
7. GreaterThan----findByAgeGreaterThan----where x.age > ?1
8. GreaterThanEqual----findByAgeGreaterThanEqual----where x.age >= ?1
9. After----findByStartDateAfter----where x.startDate > ?1
10. Before----findByStartDateBefore----where x.startDate < ?1
11. IsNull----findByAgeIsNull----where x.age is null
12. IsNotNull,NotNull----findByAge(Is)NotNull----where x.age not null
13. Like----findByFirstnameLike----where x.firstname like ?1
14. NotLike----findByFirstnameNotLike----where x.firstname not like ?1
15. StartingWith----findByFirstnameStartingWith----where x.firstname like ?1 (parameter bound with appended %)
16. EndingWith----findByFirstnameEndingWith----where x.firstname like ?1 (parameter bound with prepended %)
17. Containing----findByFirstnameContaining----where x.firstname like ?1 (parameter bound wrapped in %)
18. OrderBy----findByAgeOrderByLastnameDesc----where x.age = ?1 order by x.lastname desc
19. Not----findByLastnameNot----where x.lastname <> ?1
20. In----findByAgeIn(Collection ages)----where x.age in ?1
21. NotIn----findByAgeNotIn(Collection age)----where x.age not in ?1
22. TRUE----findByActiveTrue()----where x.active = true
23. FALSE----findByActiveFalse()----where x.active = false
24. IgnoreCase----findByFirstnameIgnoreCase----where UPPER(x.firstame) = UPPER(?1)

查询集合

public interface DeviceMonitorRepository extends JpaRepository<DeviceMonitorPO, String> {
    List<DeviceMonitorPO> findByBoxNo(String boxNo);
}

分页和排序

public interface DeviceMonitorRepository extends JpaRepository<DeviceMonitorPO, String> {
    List<DeviceMonitorPO> findByBoxNo(String boxNo);

    Page<DeviceMonitorPO> findByBoxNo(String boxNo, Pageable pageable);
}

    @Test
    void contextLoads() {
        Page<DeviceMonitorPO> monitorPOPage = deviceMonitorRepository.findAll(PageRequest.of(0, 3));
        System.out.println(JSON.toJSONString(monitorPOPage.getContent()));
        System.out.println("-----------------------------------");
        
        
        Page<DeviceMonitorPO> deviceMonitorPOPageByBoxno = deviceMonitorRepository.findByBoxNo("1820641425562718208", PageRequest.of(0, 2));
        System.out.println(JSON.toJSONString(deviceMonitorPOPageByBoxno.getContent()));
        System.out.println("+++++++++++++++++++++++++++++++++++++++++++");
        
        
        Page<DeviceMonitorPO> secondFindResultPage = deviceMonitorRepository.findByBoxNo("1820641425562718208",
                PageRequest.of(1, 2, Sort.by("updateTime").descending()));
        System.out.println(JSON.toJSONString(secondFindResultPage.getContent()));
    }

@Query中添加Page

https://www.cnblogs.com/hdwang/p/7843405.html

public interface DeviceMonitorRepository extends JpaRepository<DeviceMonitorPO, String> {

    @Query(value = "select * from device_monitor where box_no=?1 and data_type=?2",
    countQuery = "select count(*) from device_monitor where box_no=?1 and data_type=?2",
    nativeQuery = true)
    Page<DeviceMonitorPO> findByBoxNoAndDateType(String boxNo,Integer dataType, Pageable pageable);
}

@Test
    void contextLoads() {
        Page<DeviceMonitorPO> monitorPOPage = deviceMonitorRepository.findByBoxNoAndDateType("1820641425562718208", 0,
                PageRequest.of(1, 2, Sort.by("update_time").descending()));
        System.out.println(JSON.toJSONString(monitorPOPage.getContent()));
    }

JPA Criteria 查询 & Specification & JpaSpecificationExecutor

https://www.geeksforgeeks.org/jpa-criteria-api/

https://segmentfault.com/a/1190000037755804

https://docs.spring.io/spring-data/jpa/docs/2.7.12/reference/html/#specifications

示例

public interface DeviceMonitorRepository extends JpaRepository<DeviceMonitorPO, String>, JpaSpecificationExecutor<DeviceMonitorPO> {

@Test
void contextLoads() {
    Integer pendingQueryDataType = 0;
    String pendingQueryBoxNo = "1820641425562718208";

    Specification<DeviceMonitorPO> specification = new Specification<>() {
        @Override
        public Predicate toPredicate(Root<DeviceMonitorPO> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
            List<Predicate> predicateList = new LinkedList<>();
            if (Objects.nonNull(pendingQueryDataType)) {
                predicateList.add(criteriaBuilder.equal(root.get("dataType"), pendingQueryDataType));
            }
            if (Objects.nonNull(pendingQueryBoxNo)) {
                predicateList.add(criteriaBuilder.equal(root.get("boxNo"), pendingQueryBoxNo));
            }
            Predicate compositedPredicate = criteriaBuilder.and(predicateList.toArray(Predicate[]::new));
            return compositedPredicate;
        }
    };
    List<DeviceMonitorPO> all = deviceMonitorRepository.findAll(specification);
    System.out.println(JSON.toJSONString(all));

}

几个关键角色

// The type of a simple or compound predicate: a conjunction or disjunction of restrictions. A simple predicate is considered to be a conjunction with a single conjunct.
javax.persistence.criteria.Predicate
    
Predicate，可以看做为一个函数，它接受一些输入参数，并返回一个布尔值（真或假） 比如where age>50，其中age>50就是predicate
    
    
--------------------------------------------------------------------
    
//Used to construct criteria queries, compound selections, expressions, predicates, orderings.
    
//Note that Predicate is used instead of Expression<Boolean> in this API in order to work around the fact that Java generics are not compatible with varags.
    
javax.persistence.criteria.CriteriaBuilder
    
------------------------------------------------

就是设计查询结构（放置查询结构）    
javax.persistence.criteria.CriteriaQuery
    
就是设计查询结构（放置查询结构） 。
我们知道，一个select sql语句，主要包含 ：

- 要查询的项
- from部分
- where条件
- join部分
- order部分
    
最终这5个部分，需要组装到一起。那么如何能将各个部分，放置到他们该放的地方呢？
这里就用到了CriteriaQuery，我们利用CriteriaQuery的各方法，分别放置sql中各部分语句。
    比如放置要查询的项，就利用CriteriaQuery的select方法；放置where条件，就利用CriteriaQuery的where方法，以此类推
    

-----------------------------------------------------------------------------

// A root type in the from clause. Query roots always reference entities.
    
javax.persistence.criteria.Root
    
一般sql语句，都会有from部分，而Root这个类，指的就是from部分
    
---------------------------------------------------------------------------------
    
// specifications for persisting, reading, and maintaining data from Java objects to relational tables in a database.   
org.springframework.data.jpa.domain.Specification

说白了，Specification就是一个技术规范，定义了如何将对实体类的操作，映射到实际的sql语句

• *Predicates*: Criteria API can provides the Predicate interface to the represent conditions in the query. these can be combined using the logical operators like AND, OR etc.
• *Expressions*: It can be represents the values, paths or the functions in the query. CriteriaBuilder can provides the methods to the create expressions for various purposes.

要使用Criteria query，To support specifications, you can extend your repository interface with the JpaSpecificationExecutor interface,

The additional interface has methods that let you run specifications in a variety of ways. For example, the findAll method returns all entities that match the specification, as shown in the following example:

List<T> findAll(Specification<T> spec);

禁用JPQL，因为Java 编译器不能在编译时发现 JPQL 字符串的语法错误，只能等到运行时执行了JPQL语句才抛出运行时异常。使用Criteria 代替

JPQL语言（Java Persistence Query Language）是一种和SQL非常类似的中间性和对象化查询语言，它最终会被编译成具体的地场数据库的SQL语言，从而屏蔽不同的数据库的差异。

JPQL是面向对象进行查询的语言，开发者可以通过访问持久化映射的实体类，以及类中的属性来编写类似SQL的语句。

JPQL语言通过Query接口封装执行，在Query中封装了数据库访问操作的相关方法。

分页查询（待补充）

https://blog.csdn.net/JingAi_jia917/article/details/138221740

当Repository接口继承了JpaSpecificationExecutor后，我们就可以使用如下接口进行分页查询：

    /**
     * Returns a {@link Page} of entities matching the given {@link Specification}.
     *
     * @param spec can be {@literal null}.
     * @param pageable must not be {@literal null}.
     * @return never {@literal null}.
     */
    Page<T> findAll(@Nullable Specification<T> spec, Pageable pageable);

结合jpa-spec可以很容易构造出Specification:

 @Test
    void contextLoads2() {

        Integer pendingQueryDataType = 0;
        String pendingQueryBoxNo = "1820641425562718208";

        Specification<DeviceMonitorPO> specification = new Specification<>() {
            @Override
            public Predicate toPredicate(Root<DeviceMonitorPO> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
                List<Predicate> predicateList = new LinkedList<>();
                if (Objects.nonNull(pendingQueryDataType)) {
                    predicateList.add(criteriaBuilder.equal(root.get("dataType"), pendingQueryDataType));
                }
                if (Objects.nonNull(pendingQueryBoxNo)) {
                    predicateList.add(criteriaBuilder.equal(root.get("boxNo"), pendingQueryBoxNo));
                }
                Predicate compositedPredicate = criteriaBuilder.and(predicateList.toArray(Predicate[]::new));
                return compositedPredicate;
            }
        };
        Page<DeviceMonitorPO> all = deviceMonitorRepository.findAll(specification, PageRequest.of(0, 3));
        System.out.println(JSON.toJSONString(all.getContent()));
    }

metamodel type---JPA 静态元模型创建类型安全的查询

参考：https://docs.jboss.org/hibernate/orm/6.4/userguide/html_single/Hibernate_User_Guide.html#tooling-maven-modelgen

https://docs.jboss.org/hibernate/orm/5.0/topical/html/metamodelgen/MetamodelGenerator.html

编写条件查询或创建动态实体图时，需要引用实体类及其属性。最快捷、最简单的方法是将所需的名称作为字符串提供。

但这有几个缺点，例如，在编写查询时，必须记住或查找实体属性的所有名称。但是，如果必须重构实体并修改某些属性的名称，那么在项目的后期阶段也会导致更大的问题。

使用静态元模型来编写条件查询和动态实体图。这是 JPA 规范定义的一个小特性，它提供了一种类型安全的方式来引用实体及其属性。

pom.xml中，添加以下依赖：

<dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-jpamodelgen</artifactId>
            <!--                            <version>5.6.15.Final</version>-->
            <version>${hibernate-jpamodelgen.version}</version>
        </dependency>

build中的plugins，添加以下插件：

<build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.10.1</version>
                <configuration>
                    <annotationProcessorPaths>
                        <path>
                            <groupId>org.hibernate</groupId>
                            <artifactId>hibernate-jpamodelgen</artifactId>
<!--                            <version>5.6.15.Final</version>-->
                            <version>${hibernate-jpamodelgen.version}</version>
                        </path>
                    </annotationProcessorPaths>
                </configuration>
            </plugin>

        </plugins>
    </build>

执行以下命令：

mvn clean install

在target/generated-sources/annotations路径下，就能看到生成的X_静态类

Query By Example

示例：

@Test
    void contextLoads() {
        Integer pendingQueryDataType = 0;
        String pendingQueryBoxNo = "1820641425562718208";

        DeviceMonitorPO deviceMonitorPO = new DeviceMonitorPO();
        deviceMonitorPO.setDataType(pendingQueryDataType);
        deviceMonitorPO.setBoxNo(pendingQueryBoxNo);

        //
        ExampleMatcher exampleMatcher = ExampleMatcher.matching();
        // 这里注意，必须使用exampleMatcher变量等于，因为exampleMatcher.withMatcher会返回一个新的ExampleMatcher实例
        exampleMatcher = exampleMatcher.withMatcher("boxNo", ExampleMatcher.GenericPropertyMatchers.exact());
        Example<DeviceMonitorPO> deviceMonitorPOExample = Example.of(deviceMonitorPO, exampleMatcher);
        List<DeviceMonitorPO> all = deviceMonitorRepository.findAll(deviceMonitorPOExample);

        System.out.println(JSON.toJSONString(all));
        

    }

Example 包含2部分：Probe + ExampleMatcher

The Query by Example API consists of three parts:

• Probe: The actual example of a domain object with populated fields.
• ExampleMatcher: The ExampleMatcher carries details on how to match particular fields. It can be reused across multiple Examples.
• Example: An Example consists of the probe and the ExampleMatcher. It is used to create the query.

其中，Probe就是表示实体类，实体类中的属性，如果值不是空，那么就将该属性，作为查询条件

ExampleMatcher，表示如何查询匹配该属性值，比如是name = 'zhangsan' 还是 name like '%zhangsan%' 或者其他的匹配形式

ExampleMatcher有一些关键的配置属性，如下：

ExampleMatcher常见的实现类，是TypedExampleMatcher

而上面的StringMatcher，主要包含以下几种：

enum StringMatcher {

		/**
		 * Store specific default.
		 */
		DEFAULT,
		/**
		 * Matches the exact string
		 */
		EXACT,
		/**
		 * Matches string starting with pattern
		 */
		STARTING,
		/**
		 * Matches string ending with pattern
		 */
		ENDING,
		/**
		 * Matches string containing pattern
		 */
		CONTAINING,
		/**
		 * Treats strings as regular expression patterns
		 */
		REGEX;
	}

实体类多余的属性

如果实体类，比表中，多了一个属性，那么在该属性上添加@Transient注解，这样就不会报错了。如下：

@Transient
private String haha;

QueryDSL

http://querydsl.com/

https://github.com/querydsl/querydsl/tree/master/querydsl-jpa

http://querydsl.com/static/querydsl/latest/reference/html/ch02.html#jpa_integration

代码生成项目，添加以下依赖和插件：

添加依赖

 <dependency>
     <groupId>com.querydsl</groupId>
     <artifactId>querydsl-jpa</artifactId>
</dependency>

添加插件

<build>
        <plugins>
            <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>
                <dependencies>
                    <dependency>
                        <groupId>com.querydsl</groupId>
                        <artifactId>querydsl-apt</artifactId>
                        <version>${querydsl.version}</version>
                    </dependency>
                </dependencies>
            </plugin>

        </plugins>
    </build>

执行以下命令，生成Qxx查询类

mvn clean install

在target/generated-sources/java路径下，找到自动生成的类

实际项目，添加以下依赖：

    <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-jpa</artifactId>
        </dependency>

QuerydslPredicateExecutor示例

public interface DeviceMonitorRepository extends JpaRepository<DeviceMonitorPO, String>, JpaSpecificationExecutor<DeviceMonitorPO>, QuerydslPredicateExecutor<DeviceMonitorPO> {
}

@Test
    void contextLoads() {
        Integer pendingQueryDataType = 0;
        String pendingQueryBoxNo = "1820641425562718208";

        QDeviceMonitorPO qDeviceMonitorPO = QDeviceMonitorPO.deviceMonitorPO;
        Predicate predicate = qDeviceMonitorPO.boxNo.eq(pendingQueryBoxNo).and(qDeviceMonitorPO.dataType.eq(pendingQueryDataType));
        Iterable<DeviceMonitorPO> iterable = deviceMonitorRepository.findAll(predicate);
        List<DeviceMonitorPO> monitorPOList = new LinkedList<>();
        iterable.forEach(ele -> {
            monitorPOList.add(ele);
        });

        System.out.println(JSON.toJSONString(monitorPOList));

    }

JPAQueryFactory 示例---多表关联查询

https://querydsl.com/static/querydsl/latest/reference/html/

https://querydsl.com/static/querydsl/latest/apidocs/com/querydsl/core/group/GroupBy.html

GroupBy ： https://querydsl.com/static/querydsl/latest/apidocs/com/querydsl/core/group/GroupBy.html

GroupByBuilder ： https://querydsl.com/static/querydsl/latest/apidocs/com/querydsl/core/group/GroupByBuilder.html

Group： https://querydsl.com/static/querydsl/latest/apidocs/com/querydsl/core/group/Group.html

https://querydsl.com/static/querydsl/latest/reference/html/ch03s02.html

官方example：https://github.com/querydsl/querydsl/blob/master/querydsl-collections/src/test/java/com/querydsl/collections/GroupByTest.java

Querydsl 提供了两种自定义结果的方法，FactoryExpressions 用于基于行的转换和 ResultTransformer 用于聚合。

com.querydsl.core.types.FactoryExpression 接口用于 Bean 创建、构造函数调用和更复杂对象的创建。可以通过 com.querydsl.core.types.Projections 类访问 Querydsl 的 FactoryExpression 实现的功能。

对于 com.querydsl.core.ResultTransformer 接口，GroupBy 是主要实现。

简单示例

https://querydsl.com/static/querydsl/latest/reference/html/ch02.html#d0e245

https://querydsl.com/static/querydsl/latest/reference/html/ch03s02.html

@Configuration
public class QueryDslConfiguration {

    @Bean
    public JPAQueryFactory jpaQueryFactory(EntityManager em){
        return new JPAQueryFactory(em);
    }
}

查询全表---selectFrom

@Test
void contextLoads() {
    // 查询全表
    QScadaGraphPO qScadaGraphPO = QScadaGraphPO.scadaGraphPO;
    List<ScadaGraphPO> scadaGraphPOList = jpaQueryFactory.selectFrom(qScadaGraphPO)
        .where(qScadaGraphPO.bizScene.eq(1).and(qScadaGraphPO.branchId.eq("a76ab35f-fa42-467d-a439-4278562931ef")))
        .fetch();
    System.out.println(JSON.toJSONString(scadaGraphPOList));

}

查询单列---select...from...

@Test
void contextLoads() {
    QScadaGraphPO qScadaGraphPO = QScadaGraphPO.scadaGraphPO;
    List<String> graphNames = jpaQueryFactory.select(qScadaGraphPO.name).from(qScadaGraphPO)
        .where(qScadaGraphPO.bizScene.eq(1).and(qScadaGraphPO.branchId.eq("a76ab35f-fa42-467d-a439-4278562931ef")))
        .fetch();
    System.out.println(JSON.toJSONString(graphNames));
}

查询多列---select... Projections.bean

@Test
void contextLoads() {
    QScadaGraphPO qScadaGraphPO = QScadaGraphPO.scadaGraphPO;
    // 查询多列
    List<ScadaGraphPO> scadaGraphPOList = jpaQueryFactory.select(
        Projections.bean(ScadaGraphPO.class, qScadaGraphPO.name, qScadaGraphPO.branchId, qScadaGraphPO.scadaLayerPath))
        .from(qScadaGraphPO)
        .where(qScadaGraphPO.bizScene.eq(1).and(qScadaGraphPO.branchId.eq("a76ab35f-fa42-467d-a439-4278562931ef")))
        .fetch();
    System.out.println(JSON.toJSONString(scadaGraphPOList));
}

查询多列---返回Tuple

@Test
void contextLoads() {
    QScadaGraphPO qScadaGraphPO = QScadaGraphPO.scadaGraphPO;

    List<Tuple> tupleList = jpaQueryFactory.select(qScadaGraphPO.name, qScadaGraphPO.branchId, qScadaGraphPO.scadaLayerPath)
        .from(qScadaGraphPO)
        .where(qScadaGraphPO.bizScene.eq(1).and(qScadaGraphPO.branchId.eq("a76ab35f-fa42-467d-a439-4278562931ef")))
        .fetch();
    if (CollectionUtils.isNotEmpty(tupleList)) {
        Tuple tuple = tupleList.get(0);
        String graphName = tuple.get(qScadaGraphPO.name);
        String graphBranchId = tuple.get(qScadaGraphPO.branchId);
        String graphLayerPath = tuple.get(qScadaGraphPO.scadaLayerPath);
        System.out.println("graphName:"+graphName+",graphBranchId:"+graphBranchId+",graphLayerPath:"+graphLayerPath);
    }
}

分页

count查询

@Test
void contextLoads() {
    QScadaGraphPO qScadaGraphPO = QScadaGraphPO.scadaGraphPO;
    Long totalCount = jpaQueryFactory.select(Wildcard.count).from(qScadaGraphPO)
        .where(qScadaGraphPO.bizScene.eq(1).and(qScadaGraphPO.branchId.eq("a76ab35f-fa42-467d-a439-4278562931ef")))
        .fetchOne();
    System.out.println(totalCount);
}

分页查询

@Test
void contextLoads() {
    int pageNum = 1;  // 页码，从1开始
    int pageSize = 3;
    QScadaGraphPO qScadaGraphPO = QScadaGraphPO.scadaGraphPO;

    List<ScadaGraphPO> scadaGraphPOList = jpaQueryFactory.selectFrom(qScadaGraphPO)
        .where(qScadaGraphPO.bizScene.eq(1).and(qScadaGraphPO.branchId.eq("a76ab35f-fa42-467d-a439-4278562931ef")))
        .orderBy(qScadaGraphPO.updateTime.desc())
        .offset((pageNum-1) * pageSize)  // skip rows
        .limit(pageSize)
        .fetch();
    System.out.println(JSON.toJSONString(scadaGraphPOList));

}

多条件查询

@Test
void contextLoads() {
    Integer pendingQueryBizScene = 1;
    String pendingQueryBranchId = "a76ab35f-fa42-467d-a439-4278562931ef";
    //        String pendingQueryBranchId = "";
    QScadaGraphPO qScadaGraphPO = QScadaGraphPO.scadaGraphPO;
    JPAQuery<ScadaGraphPO> scadaGraphPOJPAQuery = jpaQueryFactory.selectFrom(qScadaGraphPO);
    if (Objects.nonNull(pendingQueryBizScene)) {
        scadaGraphPOJPAQuery.where(qScadaGraphPO.bizScene.eq(pendingQueryBizScene));
    }
    if (StringUtils.isNotEmpty(pendingQueryBranchId)) {
        scadaGraphPOJPAQuery.where(qScadaGraphPO.branchId.eq(pendingQueryBranchId));
    }

    List<ScadaGraphPO> scadaGraphPOList2 = scadaGraphPOJPAQuery.fetch();
}

多表关联查询

https://querydsl.com/static/querydsl/latest/reference/html/ch03s02.html

https://deepinout.com/sql/sql-questions/389_sql_mapping_onetomany_relationship_using_querydsl_sql.html

OneToOne

采用的模式是select(Projections.bean，另外，如果属性不一致的话，那么采用as方法

package org.wp.querydsldemo.po;

import java.time.LocalDateTime;

public class DeviceAlarmConfigPOExtend {

    private String id;

    private String name;
		
    // 这个是额外的嵌套属性
    private DeviceAlarmRulePO alarmRule;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

  

    public DeviceAlarmRulePO getAlarmRule() {
        return alarmRule;
    }

    public void setAlarmRule(DeviceAlarmRulePO alarmRule) {
        this.alarmRule = alarmRule;
    }
}

@Test
void contextLoads() {
    QDeviceAlarmConfigPO qDeviceAlarmConfigPO = QDeviceAlarmConfigPO.deviceAlarmConfigPO;
    QDeviceAlarmRulePO qDeviceAlarmRulePO = QDeviceAlarmRulePO.deviceAlarmRulePO;

    List<DeviceAlarmConfigPOExtend> deviceAlarmConfigPOExtends = jpaQueryFactory.select(Projections.bean(
        DeviceAlarmConfigPOExtend.class,
        qDeviceAlarmConfigPO.id,qDeviceAlarmConfigPO.alarmType,qDeviceAlarmConfigPO.name,
        Projections.bean(
            DeviceAlarmRulePO.class,
            qDeviceAlarmRulePO.id,
            qDeviceAlarmRulePO.monId,
            qDeviceAlarmRulePO.monName,
            qDeviceAlarmRulePO.comparisonMode,
            qDeviceAlarmRulePO.comparisonValue
        ).as("alarmRule")
    ))
        .from(qDeviceAlarmConfigPO)
        .leftJoin(qDeviceAlarmRulePO).on(qDeviceAlarmConfigPO.id.eq(qDeviceAlarmRulePO.alarmConfigId))
        .where(qDeviceAlarmConfigPO.id.eq("1797472963327889408"))
        .fetch();
    System.out.println(JSON.toJSONString(deviceAlarmConfigPOExtends));

针对上面的写法，可以做一个优化

让被查询的字段，做一个复用，单独定义一个数组，来存储被查询的字段

@Test
void contextLoads() {
    QDeviceAlarmConfigPO qDeviceAlarmConfigPO = QDeviceAlarmConfigPO.deviceAlarmConfigPO;
    QDeviceAlarmRulePO qDeviceAlarmRulePO = QDeviceAlarmRulePO.deviceAlarmRulePO;

    Expression<?>[] alarmRuleExpressions = {qDeviceAlarmRulePO.id,qDeviceAlarmRulePO.monId,qDeviceAlarmRulePO.monName,
                                            qDeviceAlarmRulePO.comparisonMode,qDeviceAlarmRulePO.comparisonValue};

    Expression<?>[] alarmConfigExpressions = {qDeviceAlarmConfigPO.id,qDeviceAlarmConfigPO.alarmType,qDeviceAlarmConfigPO.name,
                                              Projections.bean(
                                                  DeviceAlarmRulePO.class,
                                                  alarmRuleExpressions
                                              ).as("alarmRule")};

    List<DeviceAlarmConfigPOExtend> deviceAlarmConfigPOExtends = jpaQueryFactory.select(Projections.bean(
        DeviceAlarmConfigPOExtend.class,
        alarmConfigExpressions
    ))
        .from(qDeviceAlarmConfigPO)
        .leftJoin(qDeviceAlarmRulePO).on(qDeviceAlarmConfigPO.id.eq(qDeviceAlarmRulePO.alarmConfigId))
        .where(qDeviceAlarmConfigPO.id.eq("1797472963327889408"))
        .fetch();
    System.out.println(JSON.toJSONString(deviceAlarmConfigPOExtends));
}

OneToMany

采用transform...GroupBy.groupBy

public class BoxPOExtend {

    private String boxNo;

    private String alias;

    private Integer boxChannelType;

    private String groupName;

	// 这个是额外的嵌套属性
    private List<DeviceMonitorPO> deviceMonitorPOList;
    
    
}

@Test
    void contextLoads() {
        Integer pendingQueryDataType = 16;
        String firstBoxNo = "1820641425562718208";
        String secondBoxNo = "1745347080979132417";

        QBoxPO qBoxPO = QBoxPO.boxPO;
        QDeviceMonitorPO qDeviceMonitorPO = QDeviceMonitorPO.deviceMonitorPO;

        List<BoxPOExtend> boxPOExtends = jpaQueryFactory.select(qBoxPO.boxNo, qBoxPO.alias, qBoxPO.boxChannelType, qBoxPO.groupName,
                        qDeviceMonitorPO.name, qDeviceMonitorPO.dataType, qDeviceMonitorPO.branchId)
                .from(qBoxPO).leftJoin(qDeviceMonitorPO).on(qBoxPO.boxNo.eq(qDeviceMonitorPO.boxNo))
                .where(qBoxPO.boxNo.in(firstBoxNo, secondBoxNo).and(qDeviceMonitorPO.dataType.eq(pendingQueryDataType)))
                .transform(
                        GroupBy.groupBy(qBoxPO.boxNo)
                                .list(Projections.bean(BoxPOExtend.class, qBoxPO.alias, qBoxPO.boxNo, qBoxPO.boxChannelType, qBoxPO.groupName,
                                        list(Projections.bean(DeviceMonitorPO.class, qDeviceMonitorPO.name, qDeviceMonitorPO.dataType, qDeviceMonitorPO.branchId))
                                                .as("deviceMonitorPOList")))
                );
        System.out.println(JSON.toJSONString(boxPOExtends));

    }

FAQ

FAQ1.org.hibernate.LazyInitializationException:

org.hibernate.LazyInitializationException: could not initialize proxy [org.wp.jpademo.po.DeviceMonitorPO#1745343730292273152] - no Session

参考： https://www.baeldung.com/hibernate-initialize-proxy-exception

https://www.cnblogs.com/jpfss/p/11058617.html

1. Overview

Working with Hibernate, we might have encountered an error that says: org.hibernate.LazyInitializationException : could not initialize proxy – no Session*.*

In this quick tutorial, we’ll take a closer look at the root cause of the error and learn how to avoid it.

2. Understanding the Error

Access to a lazy-loaded object outside of the context of an open Hibernate session will result in this exception.

It’s important to understand what Session, Lazy Initialisation, and Proxy Object are, and how they come together in the Hibernate framework:

• Session is a persistence context that represents a conversation between an application and the database.
• Lazy Loading means that the object won’t be loaded to the Session context until it is accessed in code.
• Hibernate creates a dynamic Proxy Object subclass that will hit the database only when we first use the object.

This error occurs when we try to fetch a lazy-loaded object from the database by using a proxy object, but the Hibernate session is already closed.

3.How to Fix

1.Open Session in Upper Layer

The best practice is to open a session in the persistence layer, using the DAO Pattern, for example.

We can open the session in the upper layers to access the associated objects in a safe manner. For instance, we can open the session in the View layer.

As a result, we’ll see an increase in response time, which will affect the performance of the application.

在SpringBoot的配置文件中，增加一下配置：

spring.jpa.open-in-view=true

该配置会注册一个OpenEntityManagerInViewInterceptor。在处理请求时，将 EntityManager 绑定到整个处理流程中（model->dao->service->controller），开启和关闭session。这样一来，就不会出现 no Session 的错误了（可以尝试将该配置的值置为 false, 就会出现懒加载的错误了。）

2.Turning on *enable_lazy_load_no_trans* Propert

This Hibernate property is used to declare a global policy for lazy-loaded object fetching.

By default, this property is false. Turning it on means that each access to an associated lazy-loaded entity will be wrapped in a new session running in a new transaction:

比如，在一个quartz定时任务处理的，不需要通过 web 请求,就可以直接访问数据库。这种情况下，spring.jpa.open-in-view 这个配置就不起作用了，需要通过其它的方式处理懒加载的问题。比如将enable_lazy_load_no_trans设置为true

spring.jpa.properties.hibernate.enable_lazy_load_no_trans=true

表示，允许在没有 transaction 的情况下，支持懒加载；即在定时任务中，访问数据库后，做出以下保证，获取到的对象，是立即加载，不会出现访问对象时出现 no Session的问题

3.在实体类上，添加注解

@Proxy(lazy=false)

FAQ2 对象为空时，抛异常

// org.springframework.data.jpa.repository.JpaRepository#getReferenceById

/**
	 * Returns a reference to the entity with the given identifier. Depending on how the JPA persistence provider is
	 * implemented this is very likely to always return an instance and throw an
	 * {@link javax.persistence.EntityNotFoundException} on first access. Some of them will reject invalid identifiers
	 * immediately.
	 *
	 * @param id must not be {@literal null}.
	 * @return a reference to the entity with the given identifier.
	 * @see EntityManager#getReference(Class, Object) for details on when an exception is thrown.
	 * @since 2.7
	 */
	T getReferenceById(ID id);

可以使用以下进行替换

// org.springframework.data.repository.CrudRepository#findById

/**
	 * Retrieves an entity by its id.
	 *
	 * @param id must not be {@literal null}.
	 * @return the entity with the given id or {@literal Optional#empty()} if none found.
	 * @throws IllegalArgumentException if {@literal id} is {@literal null}.
	 */
	Optional<T> findById(ID id);

逆向生成JPA Entity

1.新建一个springBoot项目

pom.xml中，添加

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

2.配置DataSource

3.找到Persistence工具包

Iterable、Iterator这俩兄弟细致解读

https://blog.csdn.net/weixin_45663027/article/details/134135420

更新

表字段全部更新---saveAndFlush

先查询，然后再更新 以常见的表单为例

@Test
void contextLoads() {
    Integer pendingQueryDataType = 0;
    String pendingQueryBoxNo = "1820641425562718208";

    DeviceMonitorPO deviceMonitorPO = deviceMonitorRepository.getReferenceById("1745343730292273152");
    // 启动/停止
    deviceMonitorPO.setName("哈哈哈卫鹏");

    deviceMonitorRepository.saveAndFlush(deviceMonitorPO);
}

这个还是有问题：如果前端给过来的一个实体类，其实后端也不知道要更新哪些属性，此时，如果直接调用saveAndFlush，那么就会导致部署字段为null

为了解决这个问题，引入了cn.hutool.core.bean.BeanUtil

    @Test
    void contextLoads() {
        // 前端提供的实体类
        DeviceMonitorPO frontProvideMonitor = new DeviceMonitorPO();
        frontProvideMonitor.setName("启动/停止");
		// 从数据库中，查询到的实体类
        DeviceMonitorPO dbExistMonitorPO = deviceMonitorRepository.getReferenceById("1745343730292273152");

		// 忽略空值字段
        CopyOptions ignoreNullValueOption = CopyOptions.create().setIgnoreNullValue(true);
        // cn.hutool.core.bean.BeanUtil
        BeanUtil.copyProperties(frontProvideMonitor, dbExistMonitorPO, ignoreNullValueOption);
        // 更新数据库
        deviceMonitorRepository.saveAndFlush(dbExistMonitorPO);
        
    }

更新表中的部分字段----query dsl

注意：query dsl的update操作 和 delete操作，默认必须要添加事务

javax.persistence.TransactionRequiredException: Executing an update/delete

// org.springframework.transaction.annotation.Transactional
    @Transactional()
    @Rollback(false)    // 专门用于测试的，实际生产代码中，不需要此注解
    @Test
    void contextLoads() {
        QScadaGraphPO qScadaGraphPO = QScadaGraphPO.scadaGraphPO;
        long affectedRows = jpaQueryFactory.update(qScadaGraphPO)
                .set(qScadaGraphPO.name, "细格栅渠道态哈哈哈").set(qScadaGraphPO.updateBy, "哈哈哈卫鹏")
                .where(qScadaGraphPO.id.eq("1705025930973929472")).execute();
        System.out.println(affectedRows);


    }

删除

repository：

deviceMonitorRepository.deleteById("1745343730292273152");

query dsl方式如下：

QScadaGraphPO qScadaGraphPO = QScadaGraphPO.scadaGraphPO;
long affectedRows = jpaQueryFactory.delete(qScadaGraphPO).where(qScadaGraphPO.id.eq("1705025930973929472")).execute();

事务 & 多数据源

切换数据源

JPA自带事务，无法连续切库。

原因：

默认情况下，spring.jpa.open-in-view这个配置的值为true，表示：

Register OpenEntityManagerInViewInterceptor. Binds a JPA EntityManager to the thread for the entire processing of the request

意思就是说，JPA方法执行完成之后，并没有释放数据库连接（需要等到Controller方法执行完成才会释放）

解决方案：

为了解决这个问题，我们可以配置spring.jpa.open-in-view=false

这样，当JPA方法执行完成之后，就会自动释放数据库连接，然后就可以正常切换数据源了

事务

大原则：在同一个事务中，无法切换多个数据源。

原因：因为开启了事务后，spring事务管理器会保证在事务下，整个线程后续拿到的都是同一个connection。

总结：

先开始事务，再切数据源，会导致切换失败

先切数据源，然后再开启事务，切换成功

因此，在一个事务内，不允许切换数据源，可以在事务开始前，先切换数据源，再开始事务。

另外，说明切换数据源的生命周期更长，事务的生命周期更短