JPA使用Example查询结果为空的问题

2020-01-04 23:19
字数 486
阅读 291

使用spring-boot-starter-data-jpa，能够非常快速搭建基于JPA规范的关系数据库数据访问层，大大提高了开发效率。但在使用 Example 查询时，如果实体存在基本数据类型，可能会导致查询结果为空的的问题。

1、实体 ArticleMetaData 包括两个基本数据类型 wordage 及 viewsCount，代码如下：


import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import org.hibernate.annotations.GenericGenerator;

import javax.persistence.*;
import javax.validation.constraints.NotNull;
import java.io.Serializable;
import java.util.Date;

@Data
@Entity
@Table
public class ArticleMetaData implements Serializable {
    @Id
    @Column(length = 36)
    @GenericGenerator(name = "uuid2", 
                      strategy = "org.hibernate.id.UUIDGenerator")
    @GeneratedValue(generator = "uuid2")
    private String id;

    @Column(length = 50)
    @NotNull
    private String title;

    @Column(length = 100)
    @NotNull
    private String content;

    @JsonFormat(pattern = "yyyy.MM.dd HH:mm", timezone = "GMT+8")
    @Column
    @Temporal(TemporalType.TIMESTAMP)
    private Date publishTime;

    @Column
    private int wordage;

    @Column
    private int viewsCount;
}

2、其数据访问层接口 ArticleMetaDataRepository 扩展了 JpaRepository，代码如下：


import org.springframework.data.jpa.repository.JpaRepository;
import tech.scut.my.entity.ArticleMetaData;

public interface ArticleMetaDataRepository extends JpaRepository {
}

3、服务层接口实现使用了 Example 查询，其代码片段如下：


@Override
public Page query(ArticleMetaData metadata, Pageable pageable) {
ExampleMatcher matcher = ExampleMatcher.matching()
    .withMatcher("title", 
        ExampleMatcher.GenericPropertyMatchers.contains())
    .withMatcher("content", 
        ExampleMatcher.GenericPropertyMatchers.contains());
Example example = Example.of(metadata, matcher);
return repository.findAll(example, pageable);
}

4、通过添加模拟数据进行测试，发现查询结果为空，打开 show-sql，发现基本数据类型被添加到 where 语句中，并且使用了默认值。控制台显示日志如下：


Hibernate: 
    select
        articlemet0_.id as id1_1_,
        articlemet0_.content as content2_1_,
        articlemet0_.publish_time as publish_3_1_,
        articlemet0_.title as title4_1_,
        articlemet0_.views_count as views_co5_1_,
        articlemet0_.wordage as wordage6_1_ 
    from
        article_meta_data articlemet0_ 
    where
        articlemet0_.views_count=0 
        and articlemet0_.wordage=0 
    order by
        articlemet0_.views_count desc limit ?

5、经排查，发现是基本数据类型导致，通过修改服务层代码如下：


@Override
public Page query(ArticleMetaData metadata, Pageable pageable) {
ExampleMatcher matcher = ExampleMatcher.matching()
       .withMatcher("title",
ExampleMatcher.GenericPropertyMatchers.contains())
       .withMatcher("content",
ExampleMatcher.GenericPropertyMatchers.contains())
       .withIgnorePaths("wordage") // 因为是基本类型，需要忽略掉
    .withIgnorePaths("viewsCount"); // 因为是基本类型，需要忽略掉
Example example = Example.of(metadata, matcher);

return repository.findAll(example, pageable);
}

6、至此，查询结果正常显示。最终打印日志如下：


Hibernate: 
    select
        articlemet0_.id as id1_1_,
        articlemet0_.content as content2_1_,
        articlemet0_.publish_time as publish_3_1_,
        articlemet0_.title as title4_1_,
        articlemet0_.views_count as views_co5_1_,
        articlemet0_.wordage as wordage6_1_ 
    from
        article_meta_data articlemet0_ 
    where
        ?=1 
    order by
        articlemet0_.views_count desc limit ?

通过本文发现，Spring data jpa 果然博大精深，需要不断深入学习，才能避免掉进坑里。

JPA使用Example查询结果为空的问题

推荐阅读