英文:
use jpa nativequery multiple columns in object list array
问题
List<Object[]> queryList = new ArrayList<>();
String[] arr = {"val1", "val2"};
queryList.add(arr);
String sql = "SELECT * FROM TABLE A WHERE (A.COL1, A.COL2) IN (:queryList)";
Query query = entityManager.createNativeQuery(sql);
query.setParameter("queryList", queryList);
-- In postgresql like this
SELECT * FROM TABLE A WHERE (A.COL1, A.COL2) IN (('val1', 'val2'), ('val3', 'val4'));
-- Here is the Exception
Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: record = bytea
建议：No operator matches the given name and argument types. You might need to add explicit type
Is this possible?
英文:
>Use jpa nativequery multiple columns in object list array
List<Object []> queryList = new ArrayList<>();
String[] arr = {"val1", "val2"};
queryList.add(arr);
String sql = SELECT * FROM TABLE A WHERE (A.COL1, A.COL2) IN (:queryList)
Query query = entityManager.createNativeQuery(sql);
query.setParameter("queryList", queryList);
> In postgresql like this
SELECT * FROM TABLE A WHERE (A.COL1, A.COL2) IN (('val1', 'val2'), ('val3', 'val4'));
> Here is the Exception
Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: record = bytea
建議：No operator matches the given name and argument types. You might need to add explicit type
Is this possible?
答案1
得分: 0
我会尝试按以下方式重新构造查询：
SELECT * FROM 表A
WHERE (A.COL1 = 'val1' and A.COL2 = 'val2')
OR (A.COL1 = 'val3' and A.COL2 = 'val4')
这将允许按以下方式构造查询：
List<String[]> queryList = new ArrayList<>();
String[] arr1 = {"val1", "val2"};
String[] arr2 = {"val3", "val4"};
queryList.add(arr1);
queryList.add(arr2);
String sql = "SELECT * FROM 表A "; //不要忘记末尾的空格
if (!queryList.isEmpty()){
sql = sql + "WHERE "; //不要忘记末尾的空格
for (String[] queryParam : queryList ){
sql = sql + " (A.COL1 = '" + queryParam[0] + "' and A.COL2 = '" + queryParam[1] + "') OR "; //不要忘记末尾的空格和简单的引号
}
//最后移除最后一个 OR。
Integer indexLastOR = sql.lastIndexOf("OR");
sql = sql.substring(0, indexLastOR);
}
Query query = entityManager.createNativeQuery(sql);
这也将允许在不使用本地查询的情况下实现查询，这是维护JPA理念的建议做法。
英文:
I would try to restructure the query as follows:
SELECT * FROM TABLE A
WHERE (A.COL1 = 'val1' and A.COL2 = 'val2')
OR (A.COL1 = 'val3' and A.COL2 = 'val4')
This would allow the query to be constructed as follows:
List<String[]> queryList = new ArrayList<>();
String[] arr = {"val1", "val2"};
String[] arr = {"val3", "val4"};
queryList.add(arr);
String sql = "SELECT * FROM TABLE A "; //dont forget space at end
if (!queryList.isEmpty()){
sql = sql + "WHERE "; //dont forget space at end
for (String[] queryParam : queryList ){
sql = sql + " (A.COL1 = '"+ queryParam[0] + "' and A.COL2 = '" + queryParam[1] + "') OR "; //dont forget space at end and simple colons for param
}
//finally remove the last OR.
Integer indexLastOR = sql.lastIndexOf("OR");
sql = sql.substring(0, indexLastOR);
}
Query query = entityManager.createNativeQuery(sql);
This will also allow the query to be implemented without being native, which is advisable to maintain the JPA philosophy.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论