Does anyone know HOW to call stored procedure with custom type parameter from jpa/hibernate?

Hibernate ORM
1

Does anyone know HOW to call stored procedure with custom type parameter from jpa/hibernate?

I can call stored procedure with primitives as parameters in many ways. I can call one with custom type using just oracle driver, or jdbc or springdata using org.springframework.jdbc.object.StoredProcedure. That’s all easy. But calling stored procedure with custom type from hibernate/jpa is unbeatable and it’s shown nowhere. But nowhere is also said, it’s not possible. And it’s too common to be unsupported… It is not normal to hibernate not support this, yet, there is no sample anywhere.

I tried with UserType, SqlType, CompsiteUserType, I tried to gap trivial examples which can be found to actual code needed for this, in the end nothing works, and even hours debugging of hibernate code does not reveal to me, what could be missing.

IMPORTANT: I understand, that there are 500+ pages on web, where it’s explained you shouldn’t use stored procedures, how to use named native query instead, links how to call trivial stored procedure with varchar parameter, etc., etc. I read it all, yet I still need that SP call. I confidently say, that there isn’t single place, where this is shown. There are tens of these questions, every one of them unanswered.

Can someone share sample? Can someone someone call ProcessTestRecord from hibernate? Or confirm, that hibernate does not support such calls?

CREATE OR REPLACE TYPE TestRecord AS OBJECT (
value1 VARCHAR2(50),
value2 VARCHAR2(50)
);

CREATE OR REPLACE PROCEDURE ProcessTestRecord(
item IN TestRecord
) AS
BEGIN
– whatever
END;

1 Like
2

You will have to map the TestRecord type as embeddable. Support for this was only added recently to Hibernate ORM 6.2. Take a look at https://github.com/hibernate/hibernate-orm/blob/main/hibernate-core/src/test/java/org/hibernate/orm/test/mapping/embeddable/StructEmbeddableTest.java#L528

If you have your mapping e.g.

@Struct(name = "TestRecord")
public record TestRecord(
    @Column(length = 50) String value1, 
    @Column(length = 50) String value2) {}

You can simply pass a parameter of that type:

ProcedureCall procedure = entityManager.createStoredProcedureCall( "ProcessTestRecord" );
ProcedureParameter<TestRecord> p = procedure.registerParameter( 1, TestRecord.class, ParameterMode.IN );
procedure.setParameter( p, new TestRecord( "a", "b" ) );
1 Like
3

Great! Thanks for answer. So prior to this version, there is no way how to do this in hibernate and one has to resort to more low-level approaches, right?

4

I guess you could theoretically implement this with a custom UserType as well in older versions, but you’ll have to familiarize yourself with the JDBC API for struct handling.

5

well I can do it with oracle driver, jdbc, springdata. I spent several days debugging hibernate and I have no idea how to use UserType in context of stored procedures, and actually doubt it’s possible. I don’t think it’s possible and there is very probable not a single blog post, documentation page or whatever showing it. If it is possible or you have it even somewhere in some test case, please share how it’s done.

6

It is possible, people have done it in the past. If you’re not willing to spend the time to make this work with Hibernate ORM, then why not just use JDBC directly? Or upgrade to Hibernate ORM 6.2?

You have to implement the UserType#nullSafeSet and UserType#nullSafeGet methods within which you can interact with the JDBC PreparedStatement/ResultSet and use the PreparedStatement#setStruct() and ResultSet#getStruct() methods. You implement the mapping from your domain data type TestRecord to a Struct and the other way around, and that should work.

7
  1. I’m willing to spend the time. I’m at least 5 days in reading sources anywhere, debugging/shotgun debugging trying to make that work. I found at least 20 threads with people begging for help with this, not a single page showing how this is done. There are just stored proc call or user type used in entity. That’s not the same.
  2. Implementing UserType is trivial, I did that. It does not work, as when you pass it to stored procedure call you will get binding error. IIUC UserType is binding class, which binds some java class X to sql type. Easy. But UserType does not carry information, it’s binding class. If you pass it into stored procedure call, there is not data to be send. And if you pass class X you will get binding error. UserType works with @Type annotation on entities, but not with stored procedure call. afaik. Use with stored proc is not shown anywhere and afaik, does not work.
  3. sure, I can and will upgrade the hibernate, but this is not greenfield project, there is process related to upgrading frameworks. It cannot be done overnight.

Do you have ANY example of successful usage of UserType with stored procedure? Link, blog post, any java test whatsoever, documentation page, anything I can look at?

8

You should be able to pass a TypedParameterValue for parameters in ORM 5 e.g.

ProcedureCall procedure = entityManager.createStoredProcedureCall( "ProcessTestRecord" );
ProcedureParameter<TestRecord> p = procedure.registerParameter( 1, TestRecord.class, ParameterMode.IN );
procedure.setParameter( p, new TypeParameterValue( TestRecordUserType.INSTANCE, new TestRecord( "a", "b" ) ) );
9

Thanks, please give me some time to process it.
Thanks for quick help, I’ll be in touch.

Note: We’re using older spring-boot-parent and your code isn’t immediately applicable. I need to upgrade it at least to latest 2.7.17 springboot and retry, but even with current version it might works. The missing piece to puzzle seemed to be TypeParameterValue / TypedParameterValue. I didn’t see it mentioned anywhere.

10

Thanks for your patience. Now I’d like to ask 2 questions, maybe I’m getting something wrong.

You wrote entityManager.createStoredProcedureCall but there is no such method neither on jpa EntityManager nor on deprecated HibernateEntityManager. If I assume, that there is a mistake and that you wanted to write em.unwrap(org.hibernate.Session.class).createStoredProcedureCall, that could work. But then in your call new TypeParameterValue — I cannot find that class anywhere. Is it correct? All I can see is TypedParameterValue, but that class does not accept org.hibernate.usertype.UserType but org.hibernate.type.Type so if I use implements UserType as you recommended, I cannot use this TypeParameterValue.

Now I’m not sure if I’m overlooking / misunderstanding your code somehow, or if there is some bug in it. I asked your for patience as I wanted to try to search for solutions for these, but I didn’t find any. Can you explain, please?

hibernate 5.6.7.Final.

11

If I assume, that there is a mistake and that you wanted to write em.unwrap(org.hibernate.Session.class).createStoredProcedureCall , that could work.

Correct.

All I can see is TypedParameterValue , but that class does not accept org.hibernate.usertype.UserType but org.hibernate.type.Type so if I use implements UserType as you recommended, I cannot use this TypeParameterValue .

Bear with me, I’m trying to help you without having the actual code in front of me and obviously I’m not a perfect Java compiler to provide you perfect examples :wink:

I believe in Hibernate ORM 5 you could actually also implement the Type interface or query the CustomType which wraps your UserType through the TypeConfiguration. So that is how you would gain access to something that you can pass to the constructor of TypeParameterValue.

12

I understand it’s hard to tell what to do without code. Please feel free to look into MWE I stripped from my original project. In master branch there is fully functional code using just springdata/jdbc, using gvenzl/oracle-xe testcontainer. Db structures are declared in file init.sql

If you run ctmwe.storedprocs.CallingStoredProcWithListOfRecordsParamUsingPlainJdbcIT#test it will take a while to start container first, but it will succeeded. (you need to have docker installed). Next invocation are quick, the container will be reused also for our hibernate approach. Just don’t forget to stop container when you’re done with testing.

In branch defunct there is our current state of code where we ended with your last suggestion, the code is present in file ctmwe.storedprocs.CallingStoredProcWithCustomTypeUsingHibernateIT.

In last post you wrote: you could actually also implement the Type interface or query the CustomType which wraps your UserType through the TypeConfiguration — to be honest, I have no idea what you are suggesting. I saw the Type interface, I know it does exist, I have no idea how to use it. And in documentation I can see that Type and UserType serve totally different purpose. So it’s rather unclear how to use them together. Please elaborate.

What “querying CustomType wrapping UserType through TypeConfiguration” could mean … I really don’t understand a word. Please elaborate.

13

I understand it’s hard to tell what to do without code. Please feel free to look into MWE I stripped from my original project. In master branch there is fully functional code using just springdata/jdbc, using gvenzl/oracle-xe testcontainer. Db structures are declared in file init.sql

No idea where you shared this code.

In last post you wrote: you could actually also implement the Type interface or query the CustomType which wraps your UserType through the TypeConfiguration — to be honest, I have no idea what you are suggesting. I saw the Type interface, I know it does exist, I have no idea how to use it. And in documentation I can see that Type and UserType serve totally different purpose. So it’s rather unclear how to use them together. Please elaborate.

See, that’s the problem. You’re trying to do something very advanced that involves the Hibernate ORM types SPI, but you seem to lack an understanding of the basic structure of Hibernate ORM types. You will have to dig into the code base to understand all of these concepts. There is no documentation other than Javadoc about such advanced concepts.

Here is code that you can use to access the Type object which wraps your UserType, given that your user type is registered under the key TestRecord e.g. via org.hibernate.boot.MetadataBuilder#applyBasicType(org.hibernate.usertype.UserType, java.lang.String...).

BasicTypeRegistry basicTypeRegistry = entityManager.unwrap(SessionImplementor.class).getSessionFactory().getMetamodel().getTypeConfiguration().getBasicTypeRegistry();
Type t = basicTypeRegistry.getRegisteredType( "TestRecord" );

This is what you can then use in the constructor of TypeParameterValue. Like I wrote before, a possible alternative is to implement the Type interface directly like many OSS Hibernate extensions also do and register the class as BasicType. See e.g. https://github.com/vladmihalcea/hypersistence-utils/blob/master/hypersistence-utils-hibernate-55/src/main/java/io/hypersistence/utils/hibernate/type/json/JsonBinaryType.java

14

Jesus, it was too early for me I guess (coding early and with kid).

let me try your proposal actually in code.

15

Hello
I have this problem:

  1. I have a oracle pl/sql type:
create type my_type as object
(
  colum1 VARCHAR2(50),
  colum2 VARCHAR2(20)
)
```then i have a collection of that type:

create type my_type_2 as table of my_type

How i can to create an appropriate @Struct structure that map this out parameter using JPA @NamedStoredProcedureQuery annotation.
16

Read the documentation about the @Struct annotation to understand how to model this.

In the @NamedStoredProcedureQuery you simply specify resultClasses and for that collection of object type, you use MyType[].class.