这与我过去的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));