• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

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

 
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Does anyone know HOW to call stored procedure with custom type 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.

Can someone share sample? Can someone someone call ProcessTestRecord from hibernate?

(note: this is exemple about stored procedures, assume it's not possible to rewrite using different technique)

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;
 
Saloon Keeper
Posts: 234
7
Android Python Oracle Postgres Database Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
EclipseLink enhancements and extensions to JPA @OracleObject tells how to map an Oracle Object to fields in a Java class but this is an EclipseLink-specifc extension to JPA 2.0.

For Hibernat may be this article about custom types  is useful.
 
Saloon Keeper
Posts: 28469
210
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Actually, I strongly discourage using stored procedures unless you have a very compelling business case. A while back I made up a list of about 6 reasons NOT to use stored procedures, starting with vendor lockin (you would have been in very bad odor if you'd been working for Amazon when they decided to dump Oracle for PostgreSQL) to simple maintenance (where's that data logic? is it in program code or in a stored procedure or splattered between both) to the fact that many databases don't have version control (a la git) for logic stored in the database instead of a filesystem.

JPA was designed to make it easy to do complex database logic on the client side instead of dumping the workload on the database server. ONLY if there is significantly greater overall efficiency (site-wide) or security would I consider a stored procedure myself.

So you've been notified.

As far as general stored procedure usage with JPA goes, this is a good reference:

https://www.baeldung.com/spring-data-jpa-stored-procedures

If you are dealing with custom datatypes that cannot be handled with that, you'll need to set up a datatype mapping in JPA. I'm not sure what that process involves, though, since I've only done it with JDO (a predecessor to JPA).

 
martin mucha
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Tim Holloway: thanks for answer. I understand what you are saying and fully agree with you. But nothing you wrote answers the question.

Yes, it's rare peculiar usecase. But as I said, I don't want to justify usage of stored proc, I'm trying to find answer(asked by many threads and unaswered everywhere) for: how to call stored procedure with custom type as a parameter from jpa/hibernate. I also said, I can use custom types in hibernate, and call stored procedure in many ways(this is what baeldung shows — super trivial procedure call with primitives as parameter). And since I spent more than week on it, I can confidently say, there isn't documentation page, stack overflow post, baeldung article, blog post of anyone including thorben janssen, or any page on any fora showing how this is done. And if it is, it's exceptionally well hidden. I also spent quite some time debugging hibernate/spring code, and I simply cannot see a way how it could be even possible to call stored procedure having custom type as a parameter from hibernate/jpa. Again I can do this task with oracle driver/pure jdbc/springdata, but that's different than jpa/hibernate.

So the question stands. Can someone call provided ProcessTestRecord from jpa/hibernate? (solutions like unwrapping java.sql.Connection/org.hibernate.Session from entity manager and using low-level tooling to achieve it would be considered as a cheating ;) )
 
martin mucha
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Roland Mueller wrote:EclipseLink enhancements and extensions to JPA @OracleObject tells how to map an Oracle Object to fields in a Java class but this is an EclipseLink-specifc extension to JPA 2.0.

For Hibernat may be this article about custom types  is useful.



sadly no EclipseLink can be used here, we use hibernate.
Sure I saw baeldung example, sadly, there is now (clear) way how to use these types in context of stored procedure. If you try to do so, you will get binding error, since you have nowhere to put @Type annotation, which will do the binding.

Really, over last week I tried a lot of things. If it's on first page of google results, I did that already. (and if it's in super-old, hard to find outdated forum page or in unfinished answer of deprecated hibernate fora I most probably tried that as well ;) )
 
Tim Holloway
Saloon Keeper
Posts: 28469
210
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I looked a little closer and apparently custom data types are not in JPA, presumably because they'd break the write-once/run-anywhere paradigm that Java is predicated on.

I should also note that there is something deeply suspect about defining a complex data structure as a single database column, stored procedure or no. It kind of defeats the whole purpose of how relational databases work.

If you absolutely must, however, it might be worth considering adding a stored procedure that builds the structure from independent String parameeters. There's no obvious benefit I can see on the Java side to having a Java multi-property object in code, but passing it as what amounts to a binary unit to the database. Java is not C, where you're simply dealing with raw pointers and you definitely DON'T want to store such an item in Java Serialized form if you expect to be able to read it back 5 years later. There's no standard for Java serial format and they can and do change it from one JVM release to another.

Having said all that, when you said you found nothing in Baeldung, did that include this:

https://www.baeldung.com/hibernate-custom-types

 
martin mucha
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Tim Holloway wrote:I looked a little closer and apparently custom data types are not in JPA, presumably because they'd break the write-once/run-anywhere paradigm that Java is predicated on.

I should also note that there is something deeply suspect about defining a complex data structure as a single database column, stored procedure or no. It kind of defeats the whole purpose of how relational databases work.

If you absolutely must, however, it might be worth considering adding a stored procedure that builds the structure from independent String parameeters. There's no obvious benefit I can see on the Java side to having a Java multi-property object in code, but passing it as what amounts to a binary unit to the database. Java is not C, where you're simply dealing with raw pointers and you definitely DON'T want to store such an item in Java Serialized form if you expect to be able to read it back 5 years later. There's no standard for Java serial format and they can and do change it from one JVM release to another.

Having said all that, when you said you found nothing in Baeldung, did that include this:

https://www.baeldung.com/hibernate-custom-types



native queries also break write-once/run-anywhere and these are present, so this is not sound argumentation.

I wanted to fill you in with my actual problem, but it's really long and boring. I guess you don't want to read that. I can post it if you really want though but it's about parallelism from multiple machines, concurrent writes, performance , ...and not sound initial design for this task, indeed.

Now. Even if my task complete bullshit (which it is), the question is simple: how to call stored procedure with one custom parameter from hibernate.  Even if what I need it for is stupid (which might be) there might be valid usecases for this. I'm not that interested in solving issue I have, actually. I can solve that in several ways. I'm really curious about calling proc.

Please check the provided procedure and types again. There is no "complex data structure as a single database column". The procedure takes one param, one type.  It is record of 2 strings, which will be say stored normally into some table of two columns. There isn't anything nasty going on, just passing record/struct type. Sure, in this simplified scenario stored proc does not offer any benefit, because it's simplified example focusing only calling proc, not actual task. Why to use custom type here? Again, it's simplified scenario, I need to pass array of custom types; I dumbed the question down to passing just single record, because even that is unclear.

(sorry about edits: I need to have array of types and thought I post it like it here, but I dumbed it down even more, since even passing single custom type does not work)
 
Tim Holloway
Saloon Keeper
Posts: 28469
210
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I considered native queries, and you have a point, but there's relatively less potential breakage there. After all SQL has a standard, right? Snicker. Stored Procedures are much less portable relatively..

Please be patient, as it sometimes takes a while for me to get the idea behind things. Partly because so many questions here are posted by people who are trying to make things work in the way that they think they should work when there's actually a simpler way. Partly because I don't have the personality to notice everthing I should on the first 15 tries or so.

If you can do this in Spring Data JDBC, then I'd say that there's a high probability that Spring Data JPA can do it as well. Or at a minimum as a JPA Native Query. I'd avoid going to raw JDBC directly as it could get out of sync with JPA's caching mechanisms.

If I read your original post correctly (finally), you're not actually looking to pass a structured parameter to the stored procedure, but an array of structured parameters. That could be an issue in that JPA normally expects to deal with single parameters values per action. You might want to break the problem down into a single-value call and and see how that goes, then work up to a multi-value call.

Don't worry about boring me, If you can fit a decent explanation on one screen, that's enough. I don't need or want a complete source code listing or anything like that. Although error messages and stack traces are generally useful.
 
martin mucha
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Tim Holloway wrote:I considered native queries, and you have a point, but there's relatively less potential breakage there. After all SQL has a standard, right? Snicker. Stored Procedures are much less portable relatively..

Please be patient, as it sometimes takes a while for me to get the idea behind things. Partly because so many questions here are posted by people who are trying to make things work in the way that they think they should work when there's actually a simpler way. Partly because I don't have the personality to notice everthing I should on the first 15 tries or so.

If you can do this in Spring Data JDBC, then I'd say that there's a high probability that Spring Data JPA can do it as well. Or at a minimum as a JPA Native Query. I'd avoid going to raw JDBC directly as it could get out of sync with JPA's caching mechanisms.

If I read your original post correctly (finally), you're not actually looking to pass a structured parameter to the stored procedure, but an array of structured parameters. That could be an issue in that JPA normally expects to deal with single parameters values per action. You might want to break the problem down into a single-value call and and see how that goes, then work up to a multi-value call.

Don't worry about boring me, If you can fit a decent explanation on one screen, that's enough. I don't need or want a complete source code listing or anything like that. Although error messages and stack traces are generally useful.



ad native queries: yes, you're correct. native queries has sql-92 standard. On the other hand, if you need to resort to native queries, it's not because of sql92 standard, but because you need something more than sql92 standard. If sql92 is all you need, I'm not momentarily aware of anything which wont be covered by hql/jpql. So I'd tend to still believe, that empirically there is not a principal difference.

regarding low-level approach: yes, you are correct again. I'm stubbornly searching for hibernate/jpa solution exactly because of problems with bypassing persistence context, entities lifecycle etc.

I finally got to some resolute answer: what we tried to do here is possible, but only in hibernate orm 6.2. I.e from this year april. Before that it's not possible.
https://discourse.hibernate.org/t/does-anyone-know-how-to-call-stored-procedure-with-custom-type-parameter-from-jpa-hibernate/8558
(sorry, I reposted question from here in hibernate forum as well)

And since we're not at this version yet, the solution will be (if I will do it with stored procedure in the end): implement custom method on spring repository, flush persistence context, call proc via spring StoredProcedure class, which wraps JdbcTemplate class (works directly with datasource and connection), hope for best. Then I need to debug it a lot to find out how it actually works in real life as I never worked with it. Yuck.

And what all this is supposed to help with, if you are not bored that easily: data flows in. Multiple machines, multiple protocols. Data has candidate key, and into table should go only 'last' record having specific key, overwriting previous one if present. No duplicates. Pessimistic/optimistic locking is out of question, so is calls to db with single record(performance). Serializable transaction isolation was rejected by database guys(performance, allegedly). It would be possible to cram incoming data in bulk into some tmp table and merge this into main one, but I don't feel comfortable having one such table for N machines and M threads writing. And allowing duplictas in main table, when reading selecting only records having latest timestamp and regularly pruning the table to get rid of unwated records didn't please db guys either as that would fragment the table yielding bad performance. So the best idea so far was stored proc called with N records (thousand, ten thousand, one? ... depending on individual case) dedup them (this will be done sooner on java side) and merge all of them into main table. We can do this via native queries as well, but only one by one, which is slow due to huge mount of RTs. This still does not solve duplicates, as it's possible to merge duplicity into existence in main table, but from stored-procedure-being-used on, it's db guys problem They wanted it for this specific usecase, they will have it. I do understand that this situation is far from ideal and should be solved differently altogether, but there are times, when requirement arises and you cannot change stuff being used at the moment, or cannot do it soon enough. (but if you have better idea, I'm all ears ;) )

Anyways, as I said, this our problem is irrelevant to question asked for which we got answer from hibernate team: <6.2 unsupported/impossible ≥6.2 trivial.

Thanks for discussion/help!
M.

 
Tim Holloway
Saloon Keeper
Posts: 28469
210
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well, I'm glad you have a solution, although it sounds like your DBAs may not be comfortable with some of the more advanced options for performance and data integrity.

The first thing I think of when dealing with mixed multiple concurrent contributors is XA transactions.

A simpler approach used by Oracle Financials, for example, is not to apply work directly to the database, but to maintain a separate transaction table that you can contribute entries into with only minimal transaction control. That is, transactions are simple commits from a single app independent of what other apps may be doing. The database backend then pulls the transactions and applies them and in some systems, puts the applied transactions into an archive/history/playback table.

Also, at the risk of telling you something you already know, you should be able to atomically obtain unique IDs from Oracle's DUAL table without fear of ID collisions between multiple current contributors.
 
martin mucha
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Tim Holloway wrote:Well, I'm glad you have a solution, although it sounds like your DBAs may not be comfortable with some of the more advanced options for performance and data integrity.

The first thing I think of when dealing with mixed multiple concurrent contributors is XA transactions.

A simpler approach used by Oracle Financials, for example, is not to apply work directly to the database, but to maintain a separate transaction table that you can contribute entries into with only minimal transaction control. That is, transactions are simple commits from a single app independent of what other apps may be doing. The database backend then pulls the transactions and applies them and in some systems, puts the applied transactions into an archive/history/playback table.

Also, at the risk of telling you something you already know, you should be able to atomically obtain unique IDs from Oracle's DUAL table without fear of ID collisions between multiple current contributors.



xa transactions wont do it, since the flows are not dependant and don't even know about each other.
unique ID is not a problem, I can do that, but the problem is you need to have unique  record per candidate natural key, and cannot throw data away if that record already exists. Without serializable tx separation/locking duplicates might (and does) occur.

the separate transaction table you mentioned is over my db knowledge. Can you elaborate more?

update: the hibernate guy updated his resolute answer, claiming now that it's somehow possible even in older hibernate versions. Sigh. But without providing any example and suggesting smth I'm highly convinced it's simply not possible. If anything, there is one takeaway, at least for me: if one wants to be certain to be able to do whatever he needs in future, he should steer away from hibernate. The benefits of hibernate for someone minimally competent to design business layer are minuscule and don't overweight the potential problems which comes with hibernate.
 
Tim Holloway
Saloon Keeper
Posts: 28469
210
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm not sure that XA is that limited, but it's not something I've had to deal with in detail.

I'm afraid I can't understand what you said about candidate keys.

A transaction table is just an intermediary between the outside world and the internals of a system and it's often used as a normalization point for various differing feeds as well as the anchor for when one external transaction triggers multiple internal transactions (not all of which need to be local to that particular database or for that matter, even be specifically transactions for a database).

If memory serves, the Oracle Financials transaction table is a flat table with no primary key, which can be problematic in JPA, but there are ways around that. The Financials backend determines what types of transactions it wants to pull and when at at that point it's Oracle's problem.

You're being a little harsh on JPA. Your demands are quite outside common usage and we've already determined that you could actually solve your problem without extreme effort if you really wanted to. A simple glue procedure on the database server would suffice.

On the other hand, I spent quite a few years on a multi-client winery-management system using JPA where pretty much the entire winemaking process from grape-growing to fermenting processes to decanting into individual bottles (including inventory of corks and labels) was all handled in JPA. The nastiest parts of it were that it involved auto-generated compound keys (winery ID string plus row ID as integer) augmented by the fact that the database had been ported from iSeries DB2 to eSeries DB2 and that meant I had to allow for the fact that the key strings were originally space-padded to a fixed length as mainframes traditionally preferred.

Actually, even my open-source recipe manager app gets a little gnarly, though in its case, Spring Data's Repository facilities were used and that required a slightly different mindset external to JPA proper.

I don't know enough about the particular kinds of your installation, but my specialty is simple evil solutions to complex systems. I've worked with and interconnected everything from top-end IBM mainframes as an OS-level programmer down to the cheap IoT Arduino and Raspberry Pi processors with custom hardware attachments. I've had the questionable fortune to actually end up tracing thorough Hibernate JPA code on the winery project. So I know that often there's a cleaner and simpler solution to complex problems if one simply knows where to look and doesn't get hung up on a pre-conceived solution or arbitrary constraints (which, alas, are often handed down by clueless management).

Ironically, I've generally spent more time on chasing down wayward commas and mis-capitalizations in source code than on the "hard" parts of most of my projects.
 
I don't even know how to spell CIA. But this tiny ad does:
We need your help - Coderanch server fundraiser
https://coderanch.com/wiki/782867/Coderanch-server-fundraiser
reply
    Bookmark Topic Watch Topic
  • New Topic