CODEWITHSUNDEEP
javasqlpostgresqlspring-data-jpajsonb
Niv
Niv

Reputation: 291

JPA Query for PostgreSQL

I have the below query which works when running through Postgres-

select * from my_schema.my_table where (jsonb_1 @> '"789"' or jsonb_2 @> '"789"') and (status != 'BAD_STATUS');

It checks if there's a value of "789" in jsonb_1 (list of strings) example: ["456", "789"] or "789" in jsonb_2 (list of strings) example: ["123", "456"] and with status not BAD_STATUS. I'm trying to represent the same query in Spring Data JPA (version 1.5.6.RELEASE) and tried the below two but getting exceptions.

Query1: 

@Query(value = "select * from my_schema.my_table where (jsonb_1 @> :jsonbValue or jsonb_2 @> :jsonbValue) and (status != :badStatus)", nativeQuery = true)
List<MyEntity> findAllByJsonbValueAndStatusNot(@Param("jsonbValue") String jsonbValue, @Param("badStatus") String badStatus);

Exception:

2018-10-12 07:41:18,750 DEBUG http-nio-9030-exec-1 - [correlationId:, errorCode:] - org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions.122 - could not extract ResultSet [n/a]
org.postgresql.util.PSQLException: ERROR: operator does not exist: jsonb @> character varying
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 60
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:288)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:430)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:356)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:168)
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:116)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2117)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1900)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1876)
    at org.hibernate.loader.Loader.doQuery(Loader.java:919)[...]

Query2: 

@Query(value = "select * from my_schema.my_table where (jsonb_1 @> ?1 or jsonb_2 @> ?1) and (status != ?2)", nativeQuery = true)
List<MyEntity> findAllByJsonbValueAndStatusNot(String jsonbValue, String badStatus);

Exception:

2018-10-12 07:29:41,126 ERROR http-nio-9030-exec-1 - [correlationId:, errorCode:] - com.my.services.common.exception.ExceptionHandler.handleAppErrors.39 - Internal server error occurred
org.springframework.dao.InvalidDataAccessApiUsageException: Name for parameter binding must not be null or empty! On JDKs < 8, you need to use @Param for named parameters, on JDK 8 or better, be sure to compile with -parameters.; nested exception is java.lang.IllegalArgumentException: Name for parameter binding must not be null or empty! On JDKs < 8, you need to use @Param for named parameters, on JDK 8 or better, be sure to compile with -parameters.
at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:384)
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:246)

Caused by: java.lang.IllegalArgumentException: Name for parameter binding must not be null or empty! On JDKs < 8, you need to use @Param for named parameters, on JDK 8 or better, be sure to compile with -parameters.
at org.springframework.util.Assert.hasText(Assert.java:181)
at [...]`

Upvotes: 0

Views: 7929

Answers (2)

Kevin McCann
Kevin McCann

Reputation: 551

It's saying you can't use the operator @> to compare a string and a jsonb (which must be one of your column types in the DB). You'll have to cast the string as jsonb or the jsonb to string to do the comparison.

Postgres Docs on Type Casts

You can use the LIKE operator to compare strings on the cast string:

WHERE jsonb::varchar LIKE 'jsonbValue'

LIKE operator

or use the @> operator on cast jsonb:

WHERE jsonb @> :jsonbValue::jsonb

JSONb functions in postgres

EDIT:

For the second query, you are using named parameters that don't exist in your named query. Either replace ?1 with :jsonbValue or remove the @Param annotations.

Upvotes: 1

coladict
coladict

Reputation: 5095

Seems like a typecasting issue. Try this

@Query(value = "select * from my_schema.my_table where (jsonb_1 @> :jsonbValue\:\:jsonb or jsonb_2 @> :jsonbValue\:\:jsonb) and (status != :badStatus)", nativeQuery = true)

or the alternative syntax

@Query(value = "select * from my_schema.my_table where (jsonb_1 @> cast(:jsonbValue as jsonb) or jsonb_2 @> cast(:jsonbValue as jsonb)) and (status != :badStatus)", nativeQuery = true)

Upvotes: 2

Related Questions