Postgresql array comparision with List input Dropwizard JDBI

Question

My question is similar to this

PostgreSQL check if array contains any element from left-hand array

but I want to execute (same query which is given in the above question) i.e. "&& operator" query via code. Dropwizard + JDBI framework.

@SqlQuery("SELECT ARRAY[1,2,3,4] && <input_array>")
public abstract Object query(@BindIn("input_array") List<Integer> list);

@BindIn annotation does not work. Getting below error.

! org.postgresql.util.PSQLException: ERROR: operator does not exist: 
integer[] && integer
!   Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
!   Position: 41
! at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270)
! at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1998)
! at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
! at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:570)
! at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:420)
! at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:413)
! at org.skife.jdbi.v2.SQLStatement.internalExecute(SQLStatement.java:1327)
! ... 75 common frames omitted
! Causing: org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: org.postgresql.util.PSQLException: ERROR: operator does not exist: integer[] && integer
!   Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
!   Position: 41 [statement:"SELECT ARRAY[1,2] && <input_array>", located:"SELECT ARRAY[1,2] && :__input_array_0,:__input_array_1", rewritten:"/* Monoliths.query */ SELECT ARRAY[1,2] && ?,?", arguments:{ positional:{}, named:{__input_array_1:3,__input_array_0:2}, finder:[]}]
! at org.skife.jdbi.v2.SQLStatement.internalExecute(SQLStatement.java:1338)
! at org.skife.jdbi.v2.Query.fold(Query.java:173)
! at org.skife.jdbi.v2.Query.first(Query.java:273)
! at org.skife.jdbi.v2.Query.first(Query.java:264)
! at org.skife.jdbi.v2.sqlobject.ResultReturnThing$SingleValueResultReturnThing.result(ResultReturnThing.java:110)
! at org.skife.jdbi.v2.sqlobject.ResultReturnThing.map(ResultReturnThing.java:46)
! at org.skife.jdbi.v2.sqlobject.QueryHandler.invoke(QueryHandler.java:41)
! at org.skife.jdbi.v2.sqlobject.SqlObject.invoke(SqlObject.java:224)
! at org.skife.jdbi.v2.sqlobject.SqlObject$3.intercept(SqlObject.java:133)

Please guide me, I'm new to both Dropwizard - JDBI, and PostgreSQL.

Above method is called as 
    List<Integer> list = new ArrayList<>();
    list.add(2);
    list.add(3);
    query(list);

Dropwizard version - 1.1.0

Java 8

I don't know these tools so can't help much, but I can confirm it's binding individual elements not an array, as if you wrote SELECT ARRAY[1,2,3] && 3; instead of SELECT ARRAY[1,2,3] && ARRAY[2,3]; Maybe something is "helping" by unpacking the outer array? — Craig Ringer
– Craig Ringer, Commented Jun 5, 2018 at 0:42

Spasa Mihajlovic · Accepted Answer · 2018-08-12 16:49:35Z

0

I think you should try something like this:

@SqlQuery("SELECT ARRAY[1,2,3,4] && ARRAY[<input_array>]::integer[]")
public abstract Object query(@BindIn("input_array") List<Integer> list);`

edited Aug 12, 2018 at 16:49

answered Aug 12, 2018 at 13:23

Spasa Mihajlovic

1811 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql array comparision with List input Dropwizard JDBI

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related