'How to handle 2 OUT user-defined custom data types from Java Spring Data JPA @NamedStoredProcedureQuery defined on @Entity class?

UPDATE: I have tried implementing the SQLData interface for my Financial.class and PersonalInfo.class Java POJOs defined on my @NamedStoredProcedureQuery for the type. I also implemented the required getSQLTypeName, readSQL, and writeSQL methods per an Oracle doc: https://docs.oracle.com/cd/A97335_02/apps.102/a83724/samapp6.htm

I was hoping this would work, but it looks like its still giving me the same Type cannot be null exception. Does it matter that these personal_information_t and financial_t Objects defined in my Oracle DB are inheriting from a superclass, called base_t ?


Hi guys, I'm simply trying to use the @NamedStoredProcedureQuery directly on my @Entity class to call a Stored Procedure that is in my Oracle Database (that is in a separate schema, "JJR"). I can indeed correctly connect to this database programmatically from my Java Spring Boot application, and I can run JPA queries successfully like .findAll() so I know its not a connection issue but I believe something to do with my @NamedStoredProcedure declaration. All the tutorials on Google for Spring JPA Stored Procedure queries are using standard data types like Long or String, and only returning one OUT parameter.

However, I'm returning two OUT parameters and to make it more complicated, they are user-defined types (defined in the Oracle Database), so I'm trying to figure out how to handle this as my current attempt is returning back this exception:

org.springframework.dao.InvalidDataAccessApiUsageException: Type cannot be null; nested exception is java.lang.IllegalArgumentException: 

My Stored Procedure structure (It is inside a Package in my oracle db of pkg_employee_data in schema/user JJR). The user-defined types (personal_information_t and financial_t both have multiple fields in them, i.e. personal_information_t has an userid, firstname, lastname, and financial_t has salary, networth etc..:

PROCEDURE get_employee_data (
            empid      IN       emp.emp_id%TYPE, // NUMBER(38,0)
            persinfo   OUT      personal_information_t, // user-defined type
            financ     OUT      financial_t / user-defined type

   );

And how I'm defining the @NamedStoredProcedureQuery

@Entity
@NamedStoredProcedureQuery(name = "Employee.getEmployeeData",
        procedureName = "pkg_employee_data.get_employee_data", parameters = {
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "empid", type = Long.class),
        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "persinfo", type = PersonalInfo.class),
        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "financ", type = Financial.class)})
public class Employee {

    // Should I list the columns I want mapped here in the Entity 
    // from the "persinfo" and "financ" user-defined types, that 
    // should have a bunch fields/data inside them?
    @Id
    private long userId;
    private String firstName;
    private Double salary;

}

And the @Repository declaration, where I map this method:

@Repository
public interface EmployeeRepository extends JpaRepository<Employee,Long> {

    @Procedure(name = "Employee.getEmployeeData")
    Map<String, Object> getEmployee(@Param("empid") long empid);
}

  1. Are my type = "*.class" declarations even correct on the @NamedStoredProcedureQuery annotation? seems like my Types ares getting read a null. I created a PersonalInfo.java and Financial.java @Entities in my code as I hoped it would map from the Oracle user-defined types but it doesn't seem like that's working.
  2. On all my @Entities, I have to declare some meta-information like @Table(schema = "JJR", name = "MY_TABLE_NAME") because as I stated above, I'm logging into a specific user/schema in my Oracle DB. Do I need to put this on the @NamedStoredProcedureQuery too? I'm not mapping to a table here technically though (I just need the data from the two OUT parameters).
  3. I even tried using java.sql.Struct.class as the Type in the @NamedStoredProcedureQuery per Google, but nothing seems like it's working.
  4. What actually columns/fields do I need defined in the Employee Entity, which has the @NamedStoredProcedureQuery annotation on it? I'm not really mapping to a Table, like normally we use @Entity. This stored procedure is just returning those two OUT parameters (personal_information_t and financial_t and I need the data from it). Should the fields in Employee class be simply the fields that are in the Oracle user-defined types that I need?


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source