[Logo]
Forums Register Login
Generate portable SQL statements
ORMs provide an effective and practical way to handle Java class persistence and support multiple databases. To achive multiple database support, I presume that ORMs use internally a number of classes to generate portable SQL statements.
I wonder if a framework like Hibernate allows developers to write portable query, without being forced to use Entity for that.
In a nutshell, I'd like to have a mean to generate SQL statements in a portable way, while continuing to use PreparedStatement for data access.
Is that possible ?
(1 like)
I don't know if this is exactly what you are looking for, but maybe you can take a look at JOOQ. I know you can use it to generate SQL for a number of dialects, but it's been a while since I've used it.
Hibernate doesn't generate portable SQL statements.
It generates a statement based on the dialect needed, which is dependent on the database used.
So the SQL generated by Hibernate for an Oracle query is not necessarily the same as one generated for a MySQL query.

Unless I've misunderstood what it is you are after.
Sorry Dave, I was really inaccurate in my question. I'd like to ask if we could use only JQL (or something similar) to translate a JQL query into a specific DBMS dialect.
I'm pretty sure, at least for Hibernate, that the whole query building part of its internals is reliant on the Entity definitions.
I did poke around inside it years ago.  I'm not sure I can see how that could be leveraged to allow the use of database-agnostic SQL, which would then be used to produce database-specific SQL.
 

Stephan van Hulst wrote:I don't know if this is exactly what you are looking for, but maybe you can take a look at JOOQ. I know you can use it to generate SQL for a number of dialects, but it's been a while since I've used it.


Thanks Stephan for your advice, I'll give it  a try.
Any luck so far?
No Stephan, sorry for that. I haven't tried it yet.
(1 like)
Hibernate uses a "dialect" plugin to deal with the variances in SQL and DDL that occur from one DBMS brand to another.

If I understand the question, you'd like to do something similar, but do brute-force JDBC instead of using the ORM.

I would advise against that, if that's what you have in mind.

People think that if they use low-level functions directly, they can produce more efficient code than a machine can. But that is something that doesn't hold true once you start scaling up. In fact, benchmarks have indicated that an ORM can easily be twice as efficient as raw JDBC.

That may seem counter-intuitive, but it's also true with assembly language and high-level programming languages. You can optimize the stuffing out of low-level code, but it's the algorithms that determine the real efficiency. In most cases, a HeapSort in Python is going to blow away even the most finely-tuned assembly-language bubble sort. And this is where automation gives an edge.

I can write extremely efficient assembly code. I did so for years, even as my boss was trying to finds viable higher-level solutions (this was before C was commonplace). We wanted a high-level solution primarily because assembly language generally requires about 10 times as much work as most HLLs, even for those of us who practically dream in it and we had more work than time available.

But when we finally got a decent HLL for OS-level programming, I discovered another advantage. The compiler was capable of optimizing on a scale equivalent to a total re-write of the assembly code. A one-line HLL statement change might completely alter how the compile code managed its register set. And if we felt a little pinched in writing the code once, you can believe no one was going to rip it all up and start over again every 20 minutes or so.

A dynamic system such as Hibernate can keep global tabs on the total information flow within the ORM. As a result, it can load-balance, re-write SQL based on observed performance and do whatever other tricks might occur to the system's authors. This is obviously a lot of overhead, so for simple applications, it's not worth it. But for complex systems, the rewards can be considerable.


Having said that, I realize that there are other reasons why you might want to avoid Entity classes. For example, a generic database manipulation app where you wanted to obtain arbitrary tables. Hibernate does already have a mechanism whereby it can return tuples of column values, though, so you don't have to go out of the way for that.

And, if neither of the above applies, you could always write your own code to invoke the Hibernate dialect translators. Hibernate is, after all, open-source.

Just be sure you want to have to support any one-off solution you come up with for several years to come if you do.
Tim,  first of all thanks for your reply.

Tim Holloway wrote:Hibernate uses a "dialect" plugin to deal with the variances in SQL and DDL that occur from one DBMS brand to another.
If I understand the question, you'd like to do something similar, but do brute-force JDBC instead of using the ORM.
I would advise against that, if that's what you have in mind.
People think that if they use low-level functions directly, they can produce more efficient code than a machine can. But that is something that doesn't hold true once you start scaling up. In fact, benchmarks have indicated that an ORM can easily be twice as efficient as raw JDBC.



More or less, you're right. When I started working to the project I'm currently involved with, many years ago, Hibernate wasn't really an option. At least, for my working the context of the time. So, I needed and built an home-made bunch of classes to execute query and inserts / updates against my database. Putting it simply, my custom classes are helper classes for preparing statements, setting parameters, and returning resulset. Nothing really complicated. But, at the very end, they turned out to be quite effective. Of course, within the context I work, i.e with a legacy, old database.
Now things had quite changed, and I'm evaluating if I should give it with my framework and adopting Hibernate (or similar ORM). Adopting Hibernate seems most promising approach, and I agree. Meanwhile, I'd like try a soft transition, that's why I asked this question. Essentialy, if I were able to make my helper classes portable, I could migrate existing business objects piecemeal.

Tim Holloway wrote:
And, if neither of the above applies, you could always write your own code to invoke the Hibernate dialect translators. Hibernate is, after all, open-source.



That's a clever idea, but of course it would require a deep understanding of hibernate - i don't think it would be a simple approach.
I know what you mean. I've encountered at least 2 different DIY ORM systems in my long and evil career.

In the long term, it's best to migrate, definitely. I actually code to the JPA standard, since that gives you more flexibility. Initially, in fact, I was using Apache OpenJPA until I ran ahead of its abilities for the time.

Custom persistence systems can be a pain. I can go to any computer bookstore and get help on Hibernate or ask on a plethora of online forums - including, of course, the JavaRanch. A DIY system is typically under-documented and under-supported. And if you're really unlucky, that means that you may be the done supporting it. Plus, of course, DIY systems address an immediate problem, whereas standardized frameworks have generally addressed a wider array of issues and services. As an example, the second ORM system I worked with had no transactional capabilities and little support for table joins. Something that I find essential on projects I work with. It also wouldn't run anywhere but in the authors' (also) DIY web application framework, which ruled out things like offline utilities.

Fortunately, that particular system was one where I could run the 2 ORMs (DIY and Hibernate) alongside each other and move over gradually.  In fact, it's still moving, since the system's owners have built up a significant technological debt and don't dare ask me for more work, since they owe me quite a bit already. I fear, in fact, that it's going to collapse on them someday not too far in the future.
Sorry Tim, what is a DIY ORM? What does DIY stand for?
DIY =do it yourself?
(1 like)
 

Claude Moore wrote:DIY =do it yourself?



DIY = do it yourself. 
Thanks to everybody for their reply. At the end of the week, after a couple of tests, I think I'll go with Hibernate, without trying alternatives - like Stephan's suggested one, despite it's actually interesting.
Good luck, I hope it works well for your application!
Wink, wink, nudge, nudge, say no more ... https://richsoil.com/cards


This thread has been viewed 255 times.

All times above are in ranch (not your local) time.
The current ranch time is
Jan 22, 2018 13:18:41.