java - Postgresql array comparision with List input Dropwizard JDBI
问题描述
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
解决方案
我认为你应该尝试这样的事情:
@SqlQuery("SELECT ARRAY[1,2,3,4] && ARRAY[<input_array>]::integer[]")
public abstract Object query(@BindIn("input_array") List<Integer> list);`
推荐阅读
- python - 根据值对列表进行排序,如果相同的值使用索引对其进行排序
- c++ - volatile 和 mutex 是否确保 C++ 中的内存排序?
- java - “选择新构造函数”查询返回 java.lang.Object 而不是构造函数类的实例
- ruby - 奥丁计划:关于棋盘颜色的问题
- swift - 使用信号量来限制最新的 DispatchQueue
- python - 您可以将字符串中的字符添加到列表中吗?
- python-3.x - 我想比较两个字符串,以便创建一个在 string1 中找到但在第二个中没有的缺失元素的新字符串
- ios - 当我点击“下一首”按钮时,它会暂停音乐而不是自动转到下一首歌曲
- javascript - 如何让我的石头剪刀布游戏在使用 javascript(奥丁项目)获得 5 次胜利后重置?
- python - 如何在 PyCharm 中创建运行当前文件的运行配置?