Java – invalid column type ~ send ArrayList to PL / SQL creatednamequery

This is related to my past question

I received a list < employee > and got the ID from the employee object and put them into ArrayList < string > and then sent the ArrayList as a parameter in creatednamequery I received an invalid column type SQL exception I tested the query in the PL / SQL developer and returned the fields I've tried to build a string of IDS by placing a coma between each ID and sending it, but I received an exception to that attempt I'm curious if my query settings are incorrect or send data incorrectly

Functions in my Repository:

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

My named query requestbyrequestor Getrequestsbyrequests are as follows:

@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"
    )

Edit: add exceptions as required

This exception occurred when I used: IDS in a query:

Inner 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,’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,2 query: readallquery (name = "requestbyrequestor. Getrequestsbyrequests" referenceclass = requestbyrequester SQL = "select emp.emp_first_name||||emp.emp_last_name request, code (R. Resolution_date, 'open', 'closed') status comes from the stats REQUESTS R,SYS_ Employees EMP, where r.empl_ ID_ REQUESTED_ BY = EMP. EMPL_ ID and EMP EMPL_ ID IN：ids ORDER BY 1,2“)

Use in query? 1 or (?) Exception occurred during:

Inner exception: Java sql. Sqlexception: invalid column type error code: 17004 call: select EMP EMPL_ FIRST_ NAME || ”|| EMP. EMPL_ LAST_ NAME REQUESTER,SYS_ EMPLOYEES EMP WHERE R.EMPL_ ID_ REQUESTED_ BY = EMP. EMPL_ ID and EMP EMPL_ ID IN？ Order 1,2 bind = > [[2192632]] query: readallquery (name = "requestbyrequestor. Getrequestsbyrequests" referenceclass = requestbyrequester SQL = "select emp.emp_first_name |" | emp.emp_last_name requester, where r.emp_id_requested_by = emp.emp_id and emp.emp_id in? Order by 1,2 ")

Solution

The problem (I think because you didn't include a definition of a named query) is that you are executing a native SQL query, and list is not a valid SQL / JDBC parameter value

Eclipse link supports the list parameter of jpql query, but does not support native SQL query

You need to use jpql or define each parameter in SQL

Namely

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

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