Home » Eclipse Projects » EclipseLink » Exception ResultSet using eclipseLink @NamedStoredProcedureQuery
| Exception ResultSet using eclipseLink @NamedStoredProcedureQuery [message #1803733] |
Thu, 07 March 2019 14:41  |
Eclipse User |
|
|
|
I need help with a exception below when implements a class AttributeConverter:
java.sql.SQLException: Result Set Closed: next
at oracle.jdbc.driver.InsensitiveScrollableResultSet.next(InsensitiveScrollableResultSet.java:565)
at org.eclipse.persistence.mappings.converters.ConverterClass.convertDataValueToObjectValue(ConverterClass.java:163)
at org.eclipse.persistence.mappings.foundation.AbstractDirectMapping.getObjectValue(AbstractDirectMapping.java:619)
at org.eclipse.persistence.mappings.foundation.AbstractDirectMapping.valueFromRow(AbstractDirectMapping.java:1223)
at org.eclipse.persistence.mappings.foundation.AbstractDirectMapping.buildCloneFromRow(AbstractDirectMapping.java:208)
at org.eclipse.persistence.internal.descriptors.ObjectBuilder.buildAttributesIntoWorkingCopyClone(ObjectBuilder.java:2007)
at org.eclipse.persistence.internal.descriptors.ObjectBuilder.buildWorkingCopyCloneFromRow(ObjectBuilder.java:2260)
at org.eclipse.persistence.internal.descriptors.ObjectBuilder.buildObjectInUnitOfWork(ObjectBuilder.java:858)
at org.eclipse.persistence.internal.descriptors.ObjectBuilder.buildObject(ObjectBuilder.java:745)
at org.eclipse.persistence.internal.descriptors.ObjectBuilder.buildObject(ObjectBuilder.java:699)
at org.eclipse.persistence.queries.EntityResult.getValueFromRecord(EntityResult.java:201)
at org.eclipse.persistence.queries.ResultSetMappingQuery.buildObjectsFromRecords(ResultSetMappingQuery.java:270)
at org.eclipse.persistence.queries.ResultSetMappingQuery.buildObjectsFromRecords(ResultSetMappingQuery.java:239)
at org.eclipse.persistence.internal.jpa.StoredProcedureQueryImpl.getOutputParameterValue(StoredProcedureQueryImpl.java:485)
at org.eclipse.persistence.internal.jpa.StoredProcedureQueryImpl.getOutputParameterValue(StoredProcedureQueryImpl.java:519)
My converter class
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.AttributeConverter;
import javax.persistence.Converter;
@Converter
public class CursorConverter implements AttributeConverter<List<String>, ResultSet>{
@Override
public ResultSet convertToDatabaseColumn(List<String> attribute) {
return null;
}
@Override
public List<String> convertToEntityAttribute(ResultSet rs) {
List<String> companyCodes= new ArrayList<>() ;
try {
while(rs.next()) {
companyCodes.add(rs.getString("CODE_COMPANY"));
}
//rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
return companyCodes;
}
}
My repository class:
import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.query.Procedure;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
@Transactional(readOnly=true)
@Repository
public interface EmployeeRepository extends JpaRepository<Employee,String>{
@Procedure(name="Employee.procFind",outputParameterName="EMPL_INFO")
List<Employee> find(@Param("KEY") String key);
}
My Object class:
import java.io.Serializable;
import java.util.Date;
import java.util.List;
import javax.persistence.Column;
import javax.persistence.Convert;
import javax.persistence.Entity;
import javax.persistence.NamedStoredProcedureQuery;
import javax.persistence.ParameterMode;
import javax.persistence.QueryHint;
import javax.persistence.StoredProcedureParameter;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
import org.apache.commons.lang.builder.ToStringBuilder;
import org.eclipse.persistence.annotations.ConversionValue;
import org.eclipse.persistence.annotations.IdValidation;
import org.eclipse.persistence.annotations.ObjectTypeConverter;
import org.eclipse.persistence.annotations.PrimaryKey;
import org.eclipse.persistence.config.QueryHints;
@Entity
@PrimaryKey(validation=IdValidation.NONE,columns={@Column(name="KEY")})
@NamedStoredProcedureQuery(name = "Employee.procFind", procedureName = "ORACLE.PROC_FIND_EMPLOYEE",hints = {
@QueryHint(value = "FALSE", name = QueryHints.QUERY_RESULTS_CACHE) },resultClasses = {Employee.class},parameters = {
@StoredProcedureParameter(name= "KEY" , mode = ParameterMode.IN, type = String.class),
@StoredProcedureParameter(name = "EMPL_INFO", mode = ParameterMode.REF_CURSOR, type = void.class)})
public class Employee implements Serializable{
/**
*
*/
private static final long serialVersionUID = 1L;
@Column(name = "KEY_EMPLOYEE")
private String key;
@Convert(converter=CursorConverter.class)
@Column(name = "CURSOR_CODES")
private List<String> companyCodes;
...
}
This is my procFind procedure
PROCEDURE PROC_FIND_EMPLOYEE (KEY IN VARCHAR2,EMPL_INFO OUT SYS_REFCURSOR)
BEGIN
OPEN EMPL_INFO FOR
SELECT KEY_EMPLOYEE,
CURSOR_CODES_SOUTH (ROW_ID) CURSOR_CODES,
FROM EMPLOYEE_NORTH
WHERE KEY_EMPLOYEE = KEY
UNION ALL
SELECT KEY_EMPLOYEE,
CURSOR_CODES_NORTH (ROW_ID) CURSOR_CODES,
FROM EMPLOYEE_SOUTH
WHERE KEY_EMPLOYEE = KEY;
END PROC_FIND_EMPLOYEE;
and this is my cursor_codes_south function
FUNCTION CURSOR_CODES_SOUTH(COM_ID IN VARCHAR2) RETURN SYS_REFCURSOR
IS
CUR_COM SYS_REFCURSOR;
V_SQL VARCHAR2(500);
BEGIN
V_SQL := 'SELECT CODE_COMPANY
FROM COMPANY_SOUTH
WHERE ROW_ID = :b1';
OPEN CUR_COM FOR V_SQL USING COM_ID;
RETURN CUR_COM;
END;
well, looking at the code of the org.eclipse.persistence.jpa project in the StoredProcedureQueryImpl class I checked that the main ResultSet is always closing. (Line 134) :(
Can you guys help me, please?
Or another solution to this problem, as I can not modify oracle procedures. :(
Thank you.
|
|
|
| Re: Exception ResultSet using eclipseLink @NamedStoredProcedureQuery [message #1803737 is a reply to message #1803733] |
Thu, 07 March 2019 16:29  |
| Eclipse User |
|
|
|
You posted the same issue here https://stackoverflow.com/questions/54953127/exception-resultset-using-eclipselink-namedstoredprocedurequery
I don't know why you wouldn't map out the CURSOR_CODES_NORTH and CURSOR_CODES_SOUTH and then build the list directly, but you might bet better off using straight JDBC for this, as the design doesn't fit with the purpose of JPA. JPA is expected to operate over the results and close the cursor, and is giving your converter a chance to operate on the rows, not the cursor itself
If you are going to use this stored procedure, you have to remember that the cursor it is returning is just a list of strings; it doesn't have the "KEY_EMPLOYEE"/"CURSOR_CODES" fields your JPA entity expects, so there is no way to build entities from it. Get it to return just a List of Strings, not Employee objects:
@NamedStoredProcedureQuery(name = "Employee.procFind", procedureName = "ORACLE.PROC_FIND_EMPLOYEE",hints = {
@QueryHint(value = "FALSE", name = QueryHints.QUERY_RESULTS_CACHE) },resultClasses = {String.class},parameters = {
@StoredProcedureParameter(name= "KEY" , mode = ParameterMode.IN, type = String.class),
@StoredProcedureParameter(name = "EMPL_INFO", mode = ParameterMode.REF_CURSOR, type = void.class)})
When you execute it, it will give you a List of strings that was contained within the ref cursor, without the need for a converter.
|
|
| |
| Re: Exception ResultSet using eclipseLink @NamedStoredProcedureQuery [message #1803928 is a reply to message #1803761] |
Wed, 13 March 2019 14:34 |
| Eclipse User |
|
|
|
My suggestion wasn't to change the stored procure or what it returns, but to change your application's expectations: The JPA query used to execute this query should return a list of strings, not employee instances.
My comment on using JDBC is because I can see no use for this Java class/entity in a JPA model as it stands -- there are many other options but they all depend on factors outside of what you have presented. If you can get a query that returns struct, you can certainly map them to java objects, but again that means changing your stored procedure and what it returns. You could also map out that stored procedure logic, so that your Employee class has two lists representing the different north and south values, that you union in your code, making the Employee entity more representative of your actual database model.
Best Regards,
Chris
stand though.
|
|
|
Goto Forum:
Current Time: Thu May 15 12:38:17 EDT 2025
Powered by FUDForum. Page generated in 0.03878 seconds |
Home