java - 无效的列类型 ~ 将 ArrayList 发送到 pl/sql createdNameQuery

Question

这与过去有关question我的。

我收到一个 List<Employee>并从 Employee 中获取 ID对象并将它们放在 ArrayList<String> 中然后发送ArrayList作为 createdNameQuery 中的参数.我收到无效的列类型 sql 异常。我在 pl/sql developer 中测试了查询，它返回了字段。我尝试通过在每个 id 之间放置一个逗号并发送它来构建一串 id，但是，我从该尝试中收到了一个异常。如果我的查询设置不正确或发送数据不正确，我很好奇。

我存储库中的函数:

public List<RequestByRequester> getRequestsByRequesters(
        List<Employee> employeeList) 
        throws NoDataFoundException {

    List<String> idList = new ArrayList<String>();
    for(Employee emp : employeeList) {          
        idList.add(emp.getId().toString());
    }

    log.debug("Input params[requesters=" + idList + "]");

    List<RequestByRequester> resultList = getEm().createNamedQuery(
            "requestByRequestor.getRequestsByRequesters", RequestByRequester.class)
            .setParameter(1,idList)
            .getResultList();

        if(resultList == null || resultList.size() <= 0)
            throw new NoDataFoundException("No requests found by requesters.");
        else
            return resultList;
}

我的命名查询requestByRequestor.getRequestsByRequesters如下:

   @NamedNativeQuery(
    name = "requestByRequestor.getRequestsByRequesters",
    resultClass = RequestByRequester.class,
    query = "SELECT EMP.EMPL_FIRST_NAME || ' ' || EMP.EMPL_LAST_NAME REQUESTER," +
            "       R.RQST_ID RQST_ID," +
            "       R.TITLE TITLE," +
            "       R.DESCRIPTION DESCRIPTION," +
            "       DECODE(R.RESOLUTION_DATE, NULL, 'Open', 'Closed') STATUS" +
            "  FROM TARTS.REQUESTS R, SYS_EMPLOYEES EMP" +
            " WHERE R.EMPL_ID_REQUESTED_BY = EMP.EMPL_ID" +
            "   AND EMP.EMPL_ID IN (?)" +
            " ORDER BY 1, 5 DESC, 2"
    )

编辑:根据要求添加异常(exception)。

当我使用 :ids 时出现此异常在查询中:

Internal Exception: java.sql.SQLException: Missing IN or OUT parameter at index:: 1
Error Code: 17041
Call: SELECT EMP.EMPL_FIRST_NAME || ' ' || EMP.EMPL_LAST_NAME REQUESTER, R.RQST_ID RQST_ID, R.TITLE TITLE, R.DESCRIPTION DESCRIPTION, DECODE(R.RESOLUTION_DATE, NULL, 'Open', 'Closed') STATUS FROM TARTS.REQUESTS R, SYS_EMPLOYEES EMP WHERE R.EMPL_ID_REQUESTED_BY = EMP.EMPL_ID AND EMP.EMPL_ID IN :ids ORDER BY 1, 5 DESC, 2
Query: ReadAllQuery(name="requestByRequestor.getRequestsByRequesters" referenceClass=RequestByRequester sql="SELECT EMP.EMPL_FIRST_NAME || ' ' || EMP.EMPL_LAST_NAME REQUESTER, R.RQST_ID RQST_ID, R.TITLE TITLE, R.DESCRIPTION DESCRIPTION, DECODE(R.RESOLUTION_DATE, NULL, 'Open', 'Closed') STATUS FROM TARTS.REQUESTS R, SYS_EMPLOYEES EMP WHERE R.EMPL_ID_REQUESTED_BY = EMP.EMPL_ID AND EMP.EMPL_ID IN :ids ORDER BY 1, 5 DESC, 2")

使用 ?1 时出现异常或 (?)在查询中:

Internal Exception: java.sql.SQLException: Invalid column type
Error Code: 17004
Call: SELECT EMP.EMPL_FIRST_NAME || ' ' || EMP.EMPL_LAST_NAME REQUESTER, R.RQST_ID RQST_ID, R.TITLE TITLE, R.DESCRIPTION DESCRIPTION, DECODE(R.RESOLUTION_DATE, NULL, 'Open', 'Closed') STATUS FROM TARTS.REQUESTS R, SYS_EMPLOYEES EMP WHERE R.EMPL_ID_REQUESTED_BY = EMP.EMPL_ID AND EMP.EMPL_ID IN ? ORDER BY 1, 5 DESC, 2
bind => [[2192, 632]]
Query: ReadAllQuery(name="requestByRequestor.getRequestsByRequesters" referenceClass=RequestByRequester sql="SELECT EMP.EMPL_FIRST_NAME || ' ' || EMP.EMPL_LAST_NAME REQUESTER, R.RQST_ID RQST_ID, R.TITLE TITLE, R.DESCRIPTION DESCRIPTION, DECODE(R.RESOLUTION_DATE, NULL, 'Open', 'Closed') STATUS FROM TARTS.REQUESTS R, SYS_EMPLOYEES EMP WHERE R.EMPL_ID_REQUESTED_BY = EMP.EMPL_ID AND EMP.EMPL_ID IN ? ORDER BY 1, 5 DESC, 2")

Answer 1

问题(我认为，因为您没有包含命名查询的定义)是您正在执行 native SQL 查询，而 List 不是有效的 SQL/JDBC 参数值。

EclipseLink 支持 JPQL 查询的列表参数，但不支持原生 SQL 查询。

您需要使用 JPQL，或者在 SQL 中定义每个参数

即

EMP.EMPL_ID IN (:id1, :id2, :id3)

.setParameter("id1", idList.get(0));
.setParameter("id2", idList.get(1));