0

I read tens of questions, javadoc, tutorials, myriads of posts, but the answer how to do this is still eluding me.

Motivation: we need to pass List of entities to database to perform some optimized stuff. That's not importants and point of question. To do so, we probably don't have better way than invoke some stored procedure into which we pass array of POJOs holding data. Database specialist says, that it's best done if we can pass some custom data type into that procedure. So here we start. (Please don't offer different solution as a solution, since question itself is about mapping custom type; I'd like to hear better solution, but I'd like to know answer to this question)

Our test structures

Table:

CREATE TABLE SCH.test_table(id varchar2(50), a varchar2(50), b varchar2(50));

Types:

CREATE OR REPLACE TYPE SCH.PROC_TEST_TYPE AS OBJECT
(
  a varchar2(50), 
  b varchar2(50)
)

CREATE OR REPLACE TYPE SCH.PROC_TEST_TYPE_LIST AS TABLE OF  PROC_TEST_TYPE;

Stored procedure containing very special operation, here faked with insert into:

CREATE OR REPLACE PROCEDURE SCH.PROC_TEST (
      param IN SCH.PROC_TEST_TYPE_LIST)
   AS      
   BEGIN
      FORALL i IN 1 .. param.COUNT          
         INSERT INTO SCH.test_table
              VALUES (
                      '',
                      param (i).a,
                      param (i).b);   
   END PROC_TEST;

It can be called like this:

CALL SCH.proc_test(SCH.PROC_TEST_TYPE_LIST(SCH.PROC_TEST_TYPE('aaa', 'bbb'), SCH.PROC_TEST_TYPE('ccc', 'ddd')));

and it 'works'. Now how to call that from hibernate???

Stored procedure reference from java code:

@NamedStoredProcedureQuery(
        name = "procTest",
        procedureName = "sch.proc_test",
        parameters = {
                @StoredProcedureParameter(mode = ParameterMode.IN, type = String.class, name = "a")
        }
)

and here I ends. Actually I tried to pass single SCH.PROC_TEST_TYPE and I didn't manage even that. I tried to pass it as string PROC_TEST_TYPE('aaa', 'bbb'), I tried to implement UserType and pass that one ( https://discourse.hibernate.org/t/mapping-custom-column-type-with-postgresql-and-hibernate/1904/8 ), but no luck.

It should be possible, namely if there are myriads of legacy databases. So provided, that there is some db with given stored procedure, how can I call it from spring/hibernate?

I also found this, which seems promising: Java - How to call an oracle procedure with custom types? but I have no idea how to do this lower-level stuff in app using hibernate.

Martin Mucha
  • 2,385
  • 1
  • 29
  • 49
  • I wouldn't use hibernate but directly JDBC, probably through Springs `JdbcTemplate`. Hibernate doesn't do anything for you except making things more complicated than they already are. Just make sure you understand the JPA lifecycle and how it interacts with changes to the database made outside JPA. – Jens Schauder Jan 20 '21 at 06:37
  • if you can share the example even though it's not hibernate, it would be great. I'm trying to hack it somehow, but seing sample code which works for you would be helpful if anything else to spot potential errors in my solution. – Martin Mucha Jan 20 '21 at 10:51

0 Answers0