Spring Data JPA JSONB参数化 [英] Spring Data JPA JSONB Paramaterization

问题描述

将此查询转换为Spring Data Repository nativeQuery的正确语法(JPA，Spring Data或SpEL)是什么?

What is the correct syntax (JPA, Spring Data, or SpEL) to convert this query into a Spring Data Repository nativeQuery?

SELECT *
FROM   mytable 
WHERE  f_jsonb_arr_lower(myjsonb -> 'myArray', 'subItem', 'email') 
 @> '"[e-mail]"';

我想使用输入参数，而不是对"[e-mail]"进行硬编码.

I want to use an input parameter instead of hard-coding "[e-mail]".

我的模型:带有JSONB列myJsonb的Postgres myTable:

My model: Postgres myTable with a JSONB column myJsonb:

{
  "myArray": [
    {
      "subItem": {
        "email": "[e-mail]"
      }
    },
    {
      "subItem": {
        "email": "[e-mail]"
      }
    }
  ]
}

在此处中描述的索引.

硬编码版本有效:

@Query(value = 
        "SELECT m.* " +
        " FROM mytable AS m " +
        " WHERE f_jsonb_arr_lower(myjsonb -> 'myArray' ,'subItem', 'email') " + 
        "   @> '\"[e-mail]\"' " +
        " ORDER BY ?#{#pageable} ", 
        // Spring Data nativeQueries with Pageable require a separate countQuery:
        countQuery = 
        "SELECT count(m.id) " +
        " FROM mytable AS m " +
        " WHERE f_jsonb_arr_lower(myjsonb -> 'myArray' ,'subItem', 'email') " +
        " @> '\"[e-mail]\"' ",
        nativeQuery = true)
Page<MyTableEntity> findAllHardcodedPageable(Pageable pageable);

但是尝试在Spring Data存储库nativeQuery中利用lowercaseEmailAddress参数无效:

But trying to leverage the lowercaseEmailAddress parameter in a Spring Data repository nativeQuery does not work:

@Query(value = 
        "SELECT m.* " +
        " FROM mytable AS m " +
        " WHERE f_jsonb_arr_lower(myjsonb -> 'myArray' ,'subItem', 'email') " + 
        "   @> '\"?{lowercaseEmailAddress}\"' " +
        " ORDER BY ?#{#pageable} ", 
        countQuery = 
        "SELECT count(m.id) " +
        " FROM mytable AS m " +
        " WHERE f_jsonb_arr_lower(myjsonb -> 'myArray' ,'subItem', 'email') " +
        " @> '\"?{lowercaseEmailAddress}\"' ",
        nativeQuery = true)
Page<MyTableEntity> findAllByEmailPageable
  (String lowercaseEmailAddress, Pageable pageable);

在我的Postgres查询日志记录中，我看到从未设置lowercaseEmailAddress参数:

In my Postgres query logging, I can see that the lowercaseEmailAddress parameter is never set:

LOG:  execute S_2: COMMIT
LOG:  execute S_3: BEGIN
LOG:  execute <unnamed>: SELECT count(m.id)  FROM mytable 
   AS m  WHERE f_jsonb_arr_lower(myjsonb -> 'myArray', 
   'subitem', 'email')  @> '"?1"' 
LOG:  execute S_11: ROLLBACK

推荐答案

找到了答案:

1)仅将双引号String传递给spring数据存储库方法:

1) Pass only a double-quoted String to the spring data repository method:

String emailAddressWithDoubleQuotes = String.format("\"%s\"",emailAddress);
result = repository.findAllByEmailPageable(emailAddressWithDoubleQuotes, pageRequest).getContent();

2)Spring存储库@Query需要在括号中包含SpEL表达式，然后将其强制转换为jsonb:

2) The Spring Repository @Query needs to have the SpEL expression in parenthesis and be casted to jsonb:

static final String FIND_ALL_BY_EMAIL_QUERY = " FROM mytable AS m " +
        " WHERE f_jsonb_arr_lower(metadata -> 'myArray', 'subItem', 'email') " +
        " @> ( ?#{#lowercaseEmailAddress} )\\:\\:jsonb";
@Query( // only use 'ORDER BY #pageableWithNativeSort' on 'value' query:
        value = "SELECT m.* " + FIND_ALL_BY_EMAIL_QUERY + " ORDER BY ?#{#pageableWithNativeSort} ",
        // Spring Data nativeQueries with Pageable require a separate 'countQuery':
        countQuery = "SELECT count(m.id) " + FIND_ALL_BY_EMAIL_QUERY,
        nativeQuery = true)
Page<OrderEntity> findAllBysubItemEmail(
        @Param("lowercaseEmailAddress") String lowercaseEmailAddress,
        @Param("pageableWithNativeSort") Pageable pageableWithNativeSort);

这篇关于Spring Data JPA JSONB参数化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！