JPA--动态查询--Example

其他网址

IT利刃出鞘

10254人浏览 · 2021-08-29 23:38:49

IT利刃出鞘 · 2021-08-29 23:38:49 发布

原文网址：JPA--动态查询--Example_IT利刃出鞘的博客-CSDN博客

简介

Example官方介绍

Query by Example (QBE) is a user-friendly querying technique with a simple interface. It allows dynamic query creation and does not require to write queries containing field names. In fact, Query by Example does not require to write queries using store-specific query languages at all.

谷歌翻译：
按例查询（QBE）是一种用户界面友好的查询技术。它允许动态创建查询，并且不需要编写包含字段名称的查询。实际上，按示例查询不需要使用特定的数据库的查询语言来编写查询语句。

Example api的组成

Probe: 含有对应字段的实例对象。
ExampleMatcher：ExampleMatcher携带有关如何匹配特定字段的详细信息，相当于匹配条件。
Example：由Probe和ExampleMatcher组成，用于查询。

限制

属性不支持嵌套或者分组约束，例： firstname = ?0 or (firstname = ?1 and lastname = ?2)
灵活匹配只支持字符串类型，其他类型只支持精确匹配

Limitations

1. No support for nested/grouped property constraints like firstname = ?0 or (firstname = ?1 and lastname = ?2)
2. Only supports starts/contains/ends/regex matching for strings and exact matching for other property types

说明

继承结构

源码在：org.springframework.data.repository.query.QueryByExampleExecutor

JpaRepository接口继承了此QueryByExampleExecutor接口：

public interface JpaRepository<T, ID> extends PagingAndSortingRepository<T, ID>, QueryByExampleExecutor<T> {

所有方法

忽略空值

Example查询，默认情况下会忽略空值，官方文档也有说明：

This is a simple domain object. You can use it to create an Example. By default, fields having null values are ignored, and strings are matched using the store specific defaults. Examples can be built by either using the of factory method or by using ExampleMatcher. Example is immutable.

公共代码

配置文件及依赖

application.yml

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/jpa?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
    username: root
    password: 222333

  jpa:
    # 打印sql
    show-sql: true
    # 关闭属性的延时加载
    open-in-view: false
#    hibernate:
#      # 是否开启自动更新数据库表结构。生产中不要开启。
#      #   有以下几个值供选择。常用：update、none
#      #   create：每次加载hibernate时都删除上次生成的表，再根据你的model类生成新表。即使两次没有改变也这样执行，可能导致表数据丢失。
#      #   create-drop：每次加载hibernate时，先删除已存在的表结构再重新生成，sessionFactory一关闭，表就自动删除。
#      #   update：第一次加载hibernate时根据model类自动建立表结构，以后加载hibernate时根据model类自动更新表结构。
#      #           即使表结构变了但表中的行仍然存在不会删除以前的行。
#      #           部署到服务器后，表结构是不会被马上建立起来的，是要等应用第一次运行起来后才会。
#      #   validate：每次加载hibernate时，验证创建数据库表结构，只会和数据库中的表进行比较，不会创建新表，但是会插入新值。
#      #   none：关闭自动更新
#      ddl-auto: none
#
#      # hibernate5及之后的命名策略配置。
#      naming:
#        # 负责模型对象层次的处理，将对象模型处理为逻辑名称
#        #   有以下5个值供选择：
#        #     ImplicitNamingStrategyJpaCompliantImpl（默认值）后四者均继承自它。
#        #     ImplicitNamingStrategyComponentPathImpl
#        #     ImplicitNamingStrategyLegacyHbmImpl
#        #     ImplicitNamingStrategyLegacyJpaImpl
#        #     SpringImplicitNamingStrategy
#        implicit-strategy: org.hibernate.boot.model.naming.ImplicitNamingStrategyJpaCompliantImpl
#
#        # 映射成真实的数据名称的处理，将逻辑名称处理为物理名称。
#        #   有以下2个值供选择：
#        #     PhysicalNamingStrategyStandardImpl：直接映射，若有@Column则以其为准。等同于之前的DefaultNamingStrategy
#        #     SpringPhysicalNamingStrategy（默认值）：字段为小写，当有大写字母的时候会转换为分隔符号“_”。等同于之前的ImprovedNamingStrategy
#        physical-strategy: org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy
#
#        # hibernate5之前的命名策略配置。
#        #   有以下2个值供选择：
#        #     DefaultNamingStrategy（默认值）：直接映射，若有@Column则以其为准。
#        #     ImprovedNamingStrategy：字段为小写，当有大写字母的时候会转换为分隔符号“_”。
#        # naming-strategy: org.hibernate.cfg.ImprovedNamingStrategy

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-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.3</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>demo_jpa</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demo_jpa</name>
    <description>demo_jpa</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.hibernate.javax.persistence</groupId>
            <artifactId>hibernate-jpa-2.1-api</artifactId>
            <version>1.0.2.Final</version>
        </dependency>

        <dependency>
            <groupId>com.github.xiaoymin</groupId>
            <artifactId>knife4j-spring-boot-starter</artifactId>
            <version>3.0.2</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

表结构及数据

t_user.sql

DROP TABLE IF EXISTS t_user;

CREATE TABLE `t_user`
(
    `id`   bigint(0) NOT NULL AUTO_INCREMENT,
    `user_name` varchar(32) NULL DEFAULT NULL,
    `age`  int(10) NULL DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB;

INSERT INTO `t_user`(user_name, age) values ('Tony1', 21);
INSERT INTO `t_user`(user_name, age) values ('Tony2', 22);
INSERT INTO `t_user`(user_name, age) values ('Tony3', 23);
INSERT INTO `t_user`(user_name, age) values ('Tony3', 24);

其他

entity

package com.example.demo.user.entity;

import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.*;

@Data
@NoArgsConstructor
@Entity
@Table(name = "t_user")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String userName;

    private Integer age;
}

实例：简单实例

不传ExampleMatcher则会使用默认的ExampleMatcher。

Controller

package com.example.demo.user.controller;

import com.example.demo.user.entity.User;
import com.example.demo.user.repository.UserRepository;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Example;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@Api(tags = "动态查询（使用Example）")
@RestController
@RequestMapping("/byExample")
public class DynamicQueryByExampleController {
    @Autowired
    private UserRepository userRepository;

    @ApiOperation("简单用法")
    @PostMapping("simple")
    public void simple() {
        User user = new User();
        user.setUserName("Tony3");
        Example<User> example = Example.of(user);
        List<User> all = userRepository.findAll(example);
        System.out.println(all);
    }
}

执行结果

ibernate: select user0_.id as id1_0_, user0_.age as age2_0_, user0_.user_name as user_nam3_0_ from t_user user0_ where user0_.user_name=?
[User(id=3, userName=Tony3, age=23), User(id=4, userName=Tony3, age=24)]

实例：自定义ExampleMatcher

所有方法

实例

Controller

package com.example.demo.user.controller;

import com.example.demo.user.entity.User;
import com.example.demo.user.repository.UserRepository;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Example;
import org.springframework.data.domain.ExampleMatcher;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@Api(tags = "动态查询（使用Example）")
@RestController
@RequestMapping("/byExample")
public class DynamicQueryByExampleController {
    @Autowired
    private UserRepository userRepository;

    @ApiOperation("自定义Matcher")
    @PostMapping("customMatcher")
    public void customMatcher() {
        User user = new User();
        user.setUserName("Tony");
        user.setAge(23);

        // 写法1
        ExampleMatcher exampleMatcher = ExampleMatcher.matching()
                //模糊匹配：{userName}%
                .withMatcher("user_name", ExampleMatcher.GenericPropertyMatcher::startsWith)
                //准确匹配
                .withMatcher("age", ExampleMatcher.GenericPropertyMatcher::exact);

        // // 写法2
        // ExampleMatcher exampleMatcher = ExampleMatcher.matching()
        //         //模糊匹配：{userName}%
        //         .withMatcher("user_name", ExampleMatcher.GenericPropertyMatchers.startsWith())
        //         //准确匹配
        //         .withMatcher("age", ExampleMatcher.GenericPropertyMatchers.exact());

        // // 写法3
        // ExampleMatcher exampleMatcher = ExampleMatcher.matching()
        //         .withMatcher("user_name", match -> match.startsWith())
        //         .withMatcher("age", match -> match.exact());

        Example<User> example = Example.of(user, exampleMatcher);
        List<User> all = userRepository.findAll(example);
        System.out.println(all);
    }
}

执行结果（SQL与期望不符）

Hibernate: select user0_.id as id1_0_, user0_.age as age2_0_, user0_.user_name as user_nam3_0_ from t_user user0_ where user0_.age=23 and user0_.user_name=?
[]

按理user_name应该是模糊查询的：like Tony%。

我也不知道这是什么鬼，没有走预期的SQL。（如果有大佬知道原因的，请在评论里指点下😆）

实际先不用Example这种动态查询，它功能不如Specification，现在去学Specification。