纯净、安全、绿色的下载网站

首页|软件分类|下载排行|最新软件|IT学院

当前位置:首页IT学院IT技术

springdata jpa Example实现动态查询 springdata jpa使用Example快速实现动态查询功能

一号搬砖手   2021-11-18 我要评论
想了解springdata jpa使用Example快速实现动态查询功能的相关内容吗一号搬砖手在本文为您仔细讲解springdata jpa Example实现动态查询的相关知识和一些Code实例欢迎阅读和指正我们先划重点:springdata,jpa,Example实现动态查询,Example查询下面大家一起来学习吧

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

使用

创建实体映射:

@Entity
@Table(name="t_user")
@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    @Column(name="username")
    private String username;
    @Column(name="password")
    private String password;
    @Column(name="email")
    private String email;
    @Column(name="phone")
    private String phone;
    @Column(name="address")
    private String address;
}

测试查询

@Test
public void contextLoads() {
    User user = new User();
    user.setUsername("admin");
    Example<User> example = Example.of(user);
    List<User> list = userRepository.findAll(example);
    System.out.println(list);
}

打印的sql语句如下:

Hibernate: 
    select
        user0_.id as id1_0_,
        user0_.address as address2_0_,
        user0_.email as email3_0_,
        user0_.password as password4_0_,
        user0_.phone as phone5_0_,
        user0_.username as username6_0_ 
    from
        t_user user0_ 
    where
        user0_.username=?

可以发现试用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.

在上面的测试之中我们只是只是定义了Probe而没有ExampleMatcher是因为默认会不传时会使用默认的匹配器点进方法可以看到下面的代码:

static <T> Example<T> of(T probe) {
    return new TypedExample(probe, ExampleMatcher.matching());
}
static ExampleMatcher matching() {
    return matchingAll();
}
static ExampleMatcher matchingAll() {
    return (new TypedExampleMatcher()).withMode(ExampleMatcher.MatchMode.ALL);
}

自定匹配器规则

@Test
public void contextLoads() {
    User user = new User();
    user.setUsername("y");
    user.setAddress("sh");
    user.setPassword("admin");
    ExampleMatcher matcher = ExampleMatcher.matching()
            .withMatcher("username", ExampleMatcher.GenericPropertyMatchers.startsWith())//模糊查询匹配开头即{username}%
            .withMatcher("address" ,ExampleMatcher.GenericPropertyMatchers.contains())//全部模糊查询即%{address}%
            .withIgnorePaths("password");//忽略字段即不管password是什么值都不加入查询条件
    Example<User> example = Example.of(user ,matcher);
    List<User> list = userRepository.findAll(example);
    System.out.println(list);
}

打印的sql语句如下:

select
    user0_.id as id1_0_,
    user0_.address as address2_0_,
    user0_.email as email3_0_,
    user0_.password as password4_0_,
    user0_.phone as phone5_0_,
    user0_.username as username6_0_ 
from
    t_user user0_ 
where
    (
        user0_.username like ?
    ) 
    and (
        user0_.address like ?
    )

参数如下:

2018-03-24 13:26:57.425 TRACE 5880 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [y%]
2018-03-24 13:26:57.425 TRACE 5880 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [VARCHAR] - [%sh%]

补充

官方创建ExampleMatcher例子(1.8 lambda)

ExampleMatcher matcher = ExampleMatcher.matching()
  .withMatcher("firstname", match -> match.endsWith())
  .withMatcher("firstname", match -> match.startsWith());
}

StringMatcher 参数

Matching 生成的语句 说明
DEFAULT (case-sensitive) firstname = ?0 默认(大小写敏感)
DEFAULT (case-insensitive) LOWER(firstname) = LOWER(?0) 默认(忽略大小写)
EXACT (case-sensitive) firstname = ?0 精确匹配(大小写敏感)
EXACT (case-insensitive) LOWER(firstname) = LOWER(?0) 精确匹配(忽略大小写)
STARTING (case-sensitive) firstname like ?0 + ‘%' 前缀匹配(大小写敏感)
STARTING (case-insensitive) LOWER(firstname) like LOWER(?0) + ‘%' 前缀匹配(忽略大小写)
ENDING (case-sensitive) firstname like ‘%' + ?0 后缀匹配(大小写敏感)
ENDING (case-insensitive) LOWER(firstname) like ‘%' + LOWER(?0) 后缀匹配(忽略大小写)
CONTAINING (case-sensitive) firstname like ‘%' + ?0 + ‘%' 模糊查询(大小写敏感)
CONTAINING (case-insensitive) LOWER(firstname) like ‘%' + LOWER(?0) + ‘%' 模糊查询(忽略大小写)

说明:

1. 在默认情况下(没有调用withIgnoreCase())都是大小写敏感的

2. api之中还有个regex但是我在mysql下测试报错不了解具体作用

总结

通过在使用springdata jpa时可以通过Example来快速的实现动态查询同时配合Pageable可以实现快速的分页查询功能

对于非字符串属性的只能精确匹配比如想查询在某个时间段内注册的用户信息就不能通过Example来查询

以上为个人经验希望能给大家一个参考也希望大家多多支持


相关文章

猜您喜欢

  • Python绘制多角星 Python实现绘制多角星实例

    想了解Python实现绘制多角星实例的相关内容吗邓雪婷在本文为您仔细讲解Python绘制多角星的相关知识和一些Code实例欢迎阅读和指正我们先划重点:Python实现绘制多角星,Python绘制多角星实例,Python绘制多角星下面大家一起来学习吧..
  • Python 数据分析 Python数据分析的八种处理缺失值方法详解

    想了解Python数据分析的八种处理缺失值方法详解的相关内容吗Python学习与数据挖掘在本文为您仔细讲解Python 数据分析的相关知识和一些Code实例欢迎阅读和指正我们先划重点:Python,缺失值处理,Python,数据分析下面大家一起来学习吧..

网友评论

Copyright 2020 www.fresh-weather.com 【世纪下载站】 版权所有 软件发布

声明:所有软件和文章来自软件开发商或者作者 如有异议 请与本站联系 点此查看联系方式