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

这与我过去的question有关.我收到了一份List Employee并从Employee对象中获取id并将它们放入ArrayList String然后将该ArrayList作为createdNameQuery中的参数发送.我收到一个无效的列类型sql异常.我在pl / sql开...

这与我过去的question有关.

我收到了一份List< Employee>并从Employee对象中获取id并将它们放入ArrayList< String>然后将该ArrayList作为createdNameQuery中的参数发送.我收到一个无效的列类型sql异常.我在pl / sql开发人员中测试了查询,并返回了字段.我已经尝试通过在每个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"
    )

编辑：根据要求添加例外.

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

内部异常：java.sql.SQLException：在index :: 1处缺少IN或OUT参数
错误代码：17041
致电：选择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和EMP.EMPL_ID IN：ids ORDER BY 1,5 DESC,2
查询：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,’打开’,’关闭’)状态来自TARTS.REQUESTS R,SYS_EMPLOYEES EMP,其中R.EMPL_ID_REQUESTED_BY = EMP.EMPL_ID和EMP.EMPL_ID IN：ids ORDER BY 1,5 DESC,2“)

在查询中使用？1或(？)时出现异常：

内部异常：java.sql.SQLException：列类型无效
错误代码：17004
致电：选择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和EMP.EMPL_ID IN？订购1,5 DESC,2
    bind => [[2192,632]]
查询：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,’打开’,’关闭’)状态来自TARTS.REQUESTS R,SYS_EMPLOYEES EMP,其中R.EMPL_ID_REQUESTED_BY = EMP.EMPL_ID和EMP.EMPL_ID IN？ORDER BY 1,5 DESC,2“)

解决方法:

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

EclipseLink支持JPQL查询的List参数,但不支持本机SQL查询.

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

即

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

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