如何基于接收到的参数使用JPA创建带有分页结果的自定义动态查询

huangapple go评论99阅读模式
英文:

How to create custom dynamic query with paged results with JPA based on received parameters

问题

我想根据在搜索 API 端点接收到的参数构建动态的 JPA 查询,可能有三个参数:nome、cidade 和 causas(作为一个可以传递多个数字的数组):

@GetMapping("/search")
public ResponseEntity<?> findByNome(Pageable pageable,
                                    @RequestParam Optional<String> nome,
                                    @RequestParam Optional<Long[]> causas,
                                    @RequestParam Optional<Long> cidade) {

有了这三个参数,我需要使用 6 种可能的查询组合:

  • 仅传递 cidade
  • cidade 和 causas
  • 仅传递 nome
  • nome 和 causas
  • cidade 和 nome
  • cidade、nome 和 causas

为了实现这一点,目前我是这样做的:

@Repository
public interface InstituicaoRepository extends PagingAndSortingRepository<Instituicao, Long> {

    // *仅传递 cidade
    Page<Instituicao> findByCidadeId(long cidadeId, Pageable pageable);
    
    // *cidade 和 causas
    @Query("select i from Instituicao i where i.cidadeId = ?1 and i.causaId in (?2)")
    Page<Instituicao> findByCidadeIdAndCausaIds(long cidadeId, Long[] causaIds, Pageable pageable);

    // *仅传递 nome
    Page<Instituicao> findByNomeContainingIgnoreCase(String nome, Pageable pageable);
    
    // *nome 和 causas
    @Query("select i from Instituicao i where lower(i.nome) like lower(concat('%', ?1, '%')) and i.causaId in (?2)")
    Page<Instituicao> findByNomeAndCausaIds(String nomeInstituicao, Long[] causaIds, Pageable pageable);

    // *cidade 和 nome
    @Query("select i from Instituicao i where i.cidadeId = ?1 and lower(i.nome) like lower(concat('%', ?2, '%'))")
    Page<Instituicao> findByCidadeIdAndNome(long cidadeId, String nomeInstituicao, Pageable pageable);
    
    // *cidade, nome 和 causas
    @Query("select i from Instituicao i where i.cidadeId = ?1 and lower(i.nome) like lower(concat('%', ?2, '%')) and i.causaId in (?3)")
    Page<Instituicao> findByCidadeIdAndNomeAndCausaIds(long cidadeId, String nomeInstituicao, Long[] causaIds, Pageable pageable);
}

调用每个 InstituicaoRepository 的方法,然后我手动使用 if 语句来检查哪些参数存在和不存在(每次看到我的代码时都让我想哭)。

有没有提高效率的改进建议?
请注意,JPA 的分页需要包含在内。

英文:

I'd like to build a dynamic JPA query based on what parameters are received in my searach API endpoint, there 3 parameters possible: nome, cidade, causas (as an array that can have multiple numbers passed):

@GetMapping(&quot;/search&quot;)
    public ResponseEntity&lt;?&gt; findByNome(Pageable pageable,
                                        @RequestParam Optional&lt;String&gt; nome,
                                    @RequestParam Optional&lt;Long[]&gt; causas,
                                    @RequestParam Optional&lt;Long&gt; cidade){

with these 3 parameters, there are 6 possible combinations of queries i need to use:
*only cidade passed
*cidade and causas
*only nome
*nome and causas
*cidade and nome
*cidade, nome and causas

To achieve the same currently i'm doing like this:

 @Repository
public interface InstituicaoRepository extends PagingAndSortingRepository&lt;Instituicao, Long&gt; {

    //*only cidade passed
    Page&lt;Instituicao&gt; findByCidadeId(long cidadeId, Pageable pageable);
    //*cidade and causas
    @Query(&quot;select i from Instituicao i where i.cidadeId = ?1 and i.causaId in (?2)&quot;)
    Page&lt;Instituicao&gt; findByCidadeIdAndCausaIds(long cidadeId, Long[] causaIds, Pageable pageable);

    //*only nome
    Page&lt;Instituicao&gt; findByNomeContainingIgnoreCase(String nome, Pageable pageable);
    //*nome and causas
    @Query(&quot;select i from Instituicao i where lower(i.nome) like lower(concat(&#39;%&#39;,?1,&#39;%&#39;)) and i.causaId in (?2)&quot;)
    Page&lt;Instituicao&gt; findByNomeAndCausaIds(String nomeInstituicao, Long[] causaIds, Pageable pageable);

    //*cidade and nome
    @Query(&quot;select i from Instituicao i where i.cidadeId = ?1 and lower(i.nome) like lower(concat(&#39;%&#39;,?2,&#39;%&#39;))&quot;)
    Page&lt;Instituicao&gt; findByCidadeIdAndNome(long cidadeId, String nomeInstituicao, Pageable pageable);
    //*cidade, nome and causas
    @Query(&quot;select i from Instituicao i where i.cidadeId = ?1 and lower(i.nome) like lower(concat(&#39;%&#39;,?2,&#39;%&#39;)) and i.causaId in (?3)&quot;)
    Page&lt;Instituicao&gt; findByCidadeIdAndNomeAndCausaIds(long cidadeId, String nomeInstituicao, Long[] causaIds, Pageable pageable);

}

to invoke each InstituicaoRepository's method then i manually use ifs to check what parameters are and aren't present (which makes me wanna cry each time i look at my code)

Any suggestion on improving this to make it more efficient?
Please note that JPA's pagination needs to be included.

答案1

得分: 2

你的方法不太好,因为你的代码库中会有许多方法,并且随着时间的推移而增加,这已经在 Spring.io 中提到过。解决这个问题的更好方法是使用 Specification,你可以从 JpaSpecificationExecutor 扩展你的代码库,并将要执行的规范传递到其他层,这样做更加清晰,可以使你的代码易于阅读。甚至有一种更好的方法,就是使用 Querydsl,你甚至不需要指定 Specification,Spring Data 本身会处理许多情况。阅读来自 Spring 的以下链接:
advanced-spring-data-jpa-specifications-and-querydsl

英文:

Your approach is not good because you will have many method in your repository and it will grow by time, it is already mentioned in Spring.io itself, The better way to tackle this problem is using Specification, you can extend your repository from JpaSpecificationExecutor and pass the specification to be executed from other layers, it is cleaner and make your code easy to read. There is even a better way which is querydsl in which you even don't need to specify Specification and Spring data itself handle many situation for you. Read the following link from Spring itself:
advanced-spring-data-jpa-specifications-and-querydsl

答案2

得分: 1

我开发了一个名为 spring-dynamic-jpa 的库,旨在更轻松地使用JPA实现动态查询。

您可以使用它来编写查询模板。查询模板将根据您在调用方法时传递的参数,在执行之前构建为不同的查询字符串。

public interface InstituicaoRepository extends JpaRepository<Instituicao, Long> {
    @DynamicQuery("select i from Instituicao i\n" +
            "<@where>\n" +
            "   <#if cidadeId??>\n" +
            "       i.cidadeId = :cidadeId\n" +
            "   </#if>\n" +
            "   <#if nomeInstituicao??>\n" +
            "       and lower(i.nome) like lower(concat('%',:nomeInstituicao,'%'))\n" +
            "   </#if>\n" +
            "   <#if causaIds??>\n" +
            "       and i.causaId in :causaIds\n" +
            "   </#if>\n" +
            "</@where>")
    Page<Instituicao> findInstituicao(Long cidadeId, String nomeInstituicao, List<Long> causaIds, Pageable pageable);
}
英文:

I have developed a library called spring-dynamic-jpa to make it easier to implement dynamic queries with JPA.

You can use it to write the query templates. The query template will be built into different query strings before execution depending on your parameters when you invoke the method.

public interface InstituicaoRepository extends JpaRepository&lt;Instituicao, Long&gt; {
    @DynamicQuery(&quot;select i from Instituicao i\n&quot; +
            &quot;&lt;@where&gt;\n&quot; +
            &quot;   &lt;#if cidadeId??&gt;\n&quot; +
            &quot;       i.cidadeId = :cidadeId\n&quot; +
            &quot;   &lt;/#if&gt;\n&quot; +
            &quot;   &lt;#if nomeInstituicao??&gt;\n&quot; +
            &quot;       and lower(i.nome) like lower(concat(&#39;%&#39;,:nomeInstituicao,&#39;%&#39;))\n&quot; +
            &quot;   &lt;/#if&gt;\n&quot; +
            &quot;   &lt;#if causaIds??&gt;\n&quot; +
            &quot;       and i.causaId in :causaIds\n&quot; +
            &quot;   &lt;/#if&gt;\n&quot; +
            &quot;&lt;/@where&gt;&quot;)
    Page&lt;Instituicao&gt; findInstituicao(Long cidadeId, String nomeInstituicao, List&lt;Long&gt; causaIds, Pageable pageable);
}

huangapple
  • 本文由 发表于 2020年10月24日 02:20:13
  • 转载请务必保留本文链接:https://go.coder-hub.com/64505441.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定