'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);
}
- Are my
type = "*.class"
declarations even correct on the@NamedStoredProcedureQuery
annotation? seems like my Types ares getting read a null. I created aPersonalInfo.java
andFinancial.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. - 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). - I even tried using
java.sql.Struct.class
as the Type in the@NamedStoredProcedureQuery
per Google, but nothing seems like it's working. - 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
andfinancial_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 |
---|