• 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Devaka Cooray
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Knute Snortum
  • Junilu Lacar
  • paul wheaton
Saloon Keepers:
  • Ganesh Patekar
  • Frits Walraven
  • Tim Moores
  • Ron McLeod
  • Carey Brown
Bartenders:
  • Stephan van Hulst
  • salvin francis
  • Tim Holloway

Generate portable SQL statements  RSS feed

 
Ranch Foreman
Posts: 1023
24
IBM DB2 Java Netbeans IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ?
 
Bartender
Posts: 9491
184
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Rancher
Posts: 3743
40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Claude Moore
Ranch Foreman
Posts: 1023
24
IBM DB2 Java Netbeans IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Dave Tolls
Rancher
Posts: 3743
40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Claude Moore
Ranch Foreman
Posts: 1023
24
IBM DB2 Java Netbeans IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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.
 
Stephan van Hulst
Bartender
Posts: 9491
184
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Any luck so far?
 
Claude Moore
Ranch Foreman
Posts: 1023
24
IBM DB2 Java Netbeans IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No Stephan, sorry for that. I haven't tried it yet.
 
Bartender
Posts: 19960
95
Android Eclipse IDE Linux
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Claude Moore
Ranch Foreman
Posts: 1023
24
IBM DB2 Java Netbeans IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Tim Holloway
Bartender
Posts: 19960
95
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Claude Moore
Ranch Foreman
Posts: 1023
24
IBM DB2 Java Netbeans IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry Tim, what is a DIY ORM? What does DIY stand for?
 
Claude Moore
Ranch Foreman
Posts: 1023
24
IBM DB2 Java Netbeans IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
DIY =do it yourself?
 
Tim Holloway
Bartender
Posts: 19960
95
Android Eclipse IDE Linux
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Claude Moore wrote:DIY =do it yourself?



DIY = do it yourself.  
 
Claude Moore
Ranch Foreman
Posts: 1023
24
IBM DB2 Java Netbeans IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Stephan van Hulst
Bartender
Posts: 9491
184
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Good luck, I hope it works well for your application!
 
Consider Paul's rocket mass heater.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!