On mixing Java and SQL queries, how to do best?

Question

As part of my Java program, I need to do a run a lot of queries against (Oracle) database.

Currently, we create a mix SQL and Java, which (i know) is a bad bad thing.

What is a right way to handle something like this? If possible, include examples.

Thank you.

EDIT:

A bit more information about the application. It is a web application that derives content mainly from the database (it takes user input and paints content to be seen next based on what database believes to be true).

The biggest concern I have with how it's done today is that mixing Java code and a SQL queries look "out-of-place" when coupled as tightly as it is (Queries hardcoded as part of source code)

I am looking for a cleaner way to handle this situation, which would improve maintainability and clarity of the project at hand

Answer 1

For what you've described, incorporating an object relational mapper (ORM) or rewriting as stored procedures is probably more work than you want to embrace. Both have non-trivial learning curves.

Instead a good practice is consolidating SQL in a class per table or purpose. Take a look at the table data gateway object and the data access object design patterns to see how this is done in practice.

The upshot of this approach is myriad. You are better positioned for reuse because queries are in one spot. Client code becomes more readable as you replace several lines of JDBC and SQL with a method call (e.g. userTableDataGateway.getContentToShow(pageId)). Finally, this will help you see the problem more clearly an ORM helps solve.

Answer 2

Well, one thing you could consider is an Object Relational Mapper (for example, Hibernate). This would allow you to map your database schema to Java objects, which would generally clean up your Java code.

However, if performance and speed is of the essence, you might be better off using a plain JDBC driver.

This would of course also be dependent upon the task your application is trying to accomplish. If, for example, you need to do batch updates based on a CSV file, I migh go with a pure JDBC solution. If you're designing a web application, I would definitely go with an ORM solution.

Also, note that a pure JDBC solution would involve having SQL in your Java code. Actually, for that matter, you would have to have some form of SQL, be it HQL, JPQL, or plain SQL, in any ORM solution as well. Point being, there's nothing wrong with some SQL in your Java application.

Edit in response the OP's edits

If I were writing a web application from scratch, I would use an ORM. However, since you already have a working application, making the transition from a pure JDBC solution to an ORM would be pretty painful. It would clean up your code, but there is a significant learning curve involved and it takes quite a bit of set-up. Some of the pain from setting-up would be alleviated if you are working with some sort of bean-management system, like Spring, but it would still be pretty significant.

It would also depend on where you want to go with your application. If you plan on maintaining and adding to this code for a significant period, a refactor may be in order. I would not, however, recommend a re-write of your system just because you don't like having SQL hard-coded in your application.

Answer 3

Based on your updates, I concur with Tim Pote's edits re: the learning curve to integrate ORM. However, instead of integrating ORM, you could do things like using prepared statements, which you in turn store in a properties file. Or even store your queries in the DB so that you can make subtle updates to them that can then be read in immediately without restarting your app server. Both of these strategies would declutter your Java code of hard-coded SQL.

Ultimately though, I don't think there's a clear answer to your question, because there's nothing inherently wrong with what you're doing. It's just a bit inflexible, but perhaps acceptably so for your circumstances.

That said, I'm posting this as an answer!

Answer 4

I'm not sure of the state of the project but you may also be able to find an 'alternate' object relational mapper called MyBatis. It has a lower learning curve than the popular hibernate or eclipselink and let's you actually write the queries so you know what the code is doing. That is if ORM is your thing.

I'm working with JPA right now (mainly because it is the current trend and it needs to be learned). JPA is the Java standard for ORM. If you are going to learn what is currently a typical ORM way of doing things, JPA is probably the best way to go. Frameworks like Hibernate and Eclipselink drive it. Depending on what framework you choose to underpin your JPA app, you can use proprietary features but that will tie you to that framework pretty much for good. JPA is not hard to start using, but can be very cryptic when it doesn't work since it obfuscates the interaction with the database quite a bit (mind you, it does allow the option using native SQL queries, but that kind of negates the reason why people say JPA style DB access is good).

And yes, there are still people using JDBC with prepared statements. And normally there are practices/patterns that you will use when programming with plain old JDBC that act like a very, very minimalist ORM... or really, closer to MyBatis. Again, if you go this route, use prepared statements. They negate a number of dangers.

This is a religious kind of question, so you will hear a lot of proselytizing the way you wrote the question. In fact someone might shoot down your question for this. I think the only thing you could ask that might be worse is whether emacs or vi is better to a crowd of unix geeks.

Answer 5

Your question seems too generic, however if you have a mix of Direct SQL on Oracle and Java SQL, it would be better to invest some time in an ORM like Hibernate or Apache Cayenne. The ORM is a separate design approach to segregate Database operations from the Java side. All the db interactions and DB design is implemented on the ORM and all the access and business logic will reside in Java, this is a suggestion. Still unclear about your actual problem though.

Answer 6

The biggest concern I have with how it's done today is that mixing Java code and a SQL queries look "out-of-place" when coupled as tightly as it is (Queries hardcoded as part of source code)

This assumption of yours is not really "correct" in a way that there is going to be a true / false answer to your question. This question here explains that there are several ways of dealing with mixing Java and SQL:

Java Programming - Where should SQL statements be stored?

It essentially distinguishes between SQL being:

• Hardcoded in business objects
• Embedded in SQLJ clauses
• Encapsulated in separate classes e.g. Data Access Objects
• Metadata driven (decouple the object schema from the data schema - describe the mappings between them in metadata)
• Put into external files (e.g. Properties or Resource files)
• Put into stored procedures

I'll add to that:

• Embedded in CriteriaQuery statements
• Embedded in jOOQ statements.

Answer 7

Apache Cayenne, is one of the easiest ORM to use. It comes with a Cayenne Modeller to Model data objects and does mappings. I would recommend Cayenne for a beginner in ORM. It can create mapping classes and DB sync through the modeller.