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

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

    使用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 果然博大精深,需要不断深入学习,才能避免掉进坑里。

back_to_top