CODEWITHSUNDEEP
javaspringpostgresqlspring-data-jpanative
Jacobs2000
Jacobs2000

Reputation: 928

org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $1

I am using JPA with spring Boot, and Postgres. I am trying to run a native query using a repository that extends org.springframework.data.repository.CrudRepository. The query looks like this:

public interface MyRepository extends CrudRepository<MyObject, String> {    
    ...
    @Query(value = "SELECT * FROM objects WHERE metadata @> 
    jsonb_build_object('tenant', :tenant , 'objectType', :objectType )", nativeQuery = true)
    List<MyObject> findAllBy(@Param("tenant") String tenant, @Param("objectType") String objectType);
    ...
}

When I run the query I get the error:

org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $1

I went over many similar cases but can't get this query to work in this or similar variations.

Table create statement:

create table objects
(
    key varchar(256),
    tenant varchar(256),
    metadata jsonb,
    data jsonb
);

Any ideas what I'm doing wrong?

Additional variation I have tried (this time with a pageable object):

@Query(value = "SELECT * FROM objects WHERE metadata @> jsonb_build_object(\\'tenant\\', ?1 , \\'objectType\\', ?2) ORDER BY ?#{#pageable}",
        countQuery = "SELECT count(*) FROM objects WHERE metadata->>\\'tenant\\' = ?1 and metadata->>\\'objectType\\' = ?2",
        nativeQuery = true)
Page<OSObject> findAllBy(String tenant, String objectType, Pageable pageable);

This results in: org.hibernate.exception.SQLGrammarException: could not extract ResultSet

I'm still in the dark...

Upvotes: 5

Views: 7432

Answers (1)

wvh
wvh

Reputation: 191

I think this is because jsonb_build_object accepts variadic arguments. You need a typecast to tell Postgresql the data type of the keys and values provided as variables (text or ints or whatever else), otherwise it doesn't know what data type to use inside the json.

jsonb_build_object('key', $1::text)

I don't know anything about Java and Spring, but I imagine it tries to use a prepared statement with dollar variables under the hood. Try to cast your variables with ::text.

Upvotes: 3

Related Questions