在对象列表数组中使用jpa nativequery多列

wkftcu5l 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(330)

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);

在postgresql中

SELECT * FROM TABLE A WHERE (A.COL1, A.COL2) IN (('val1', 'val2'), ('val3', 'val4'));

这是个例外

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

这可能吗？

Java sql postgresql jpa

来源：https://stackoverflow.com/questions/63068678/use-jpa-nativequery-multiple-columns-in-object-list-array

1条答案

按热度按时间

b4qexyjb1#

我将尝试按以下方式重新构造查询：

SELECT * FROM TABLE A
WHERE (A.COL1 = 'val1' and A.COL2 = 'val2')
OR (A.COL1 = 'val3' and A.COL2 = 'val4')

这将允许按照以下方式构造查询：

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);

这也将允许在不使用本机的情况下实现查询，这对于维护jpa哲学是明智的。

赞(0）回复(0）举报 2021-07-24

我来回答

在对象列表数组中使用jpa nativequery多列

1条答案

相关问题

热门标签

最新问答