Separating SQL from the Java Code

Question

This is a problem that I always encounter when I have to connect to a data base; how to separate SQL from the normal java code? I usually use a separate class for the Database connections, however, when you have several databases and several tables in each database, it is always difficult to do this 100%

As an example, if we want to put all the java SQL in a class named DBConnector.java, how do we code generically for different Insertions, Deletions, Data Retrieval, etc? What I'm thinking as the ideal case is that all the SQL statements should be in the same class and should be compatible with different flavors of same operation within the scope of the database application, thus providing a logical separation from rest of the code.

public void insertData (String db, String table, <Whatever Fields to be Inserted>)
{
  //generic SQL INSERT statement and execution 
}

public ResultSet retrieveData (String db, String table, <Whatever Fields Relevant>) 
{
  //generic retrieval of data
}

Is there any way to accomplish this? Or should we just add functionality for different flavors of Inserting, Querying, etc?

Thank you!

Answer 1

If you want a sound architecture you'll want at least few layers to seperate concerns.

First of all, start off with model classes (most of the time, you'll want one for every table in your database). Write them yourself, or have them generated automatically by using an ORM (e.g. EclipseLink, Hibernate). These should be POJO (Plain Old Java Objects), which means they are simple objects with properties (e.g. Name of type String, Id of type integer etc...). Your model objects should be carriers of data and nothing more (certainly no logic or processing).

Then, make DAO (Data Access Objects) for all of the model classes (you can build a GenericDao class to inherit from if you want to). Here you will provide CRUD operations (insert, update, delete) via methods which take the model objects as arguments. This is database-backend specific, although you can insert a database-agnostic DAO-layer if you want.

Third, have a service or manager layer per logical group of classes (this is the layer all frontend and controller code should be talking to for all wanted functionality). A typical method could be called registerCustomer(...) (which may use different DAO classes). Or findCustomerByName() etc.

Structuring your application in this way is called MVC (Model - View - Controller), so that's the term to google for if you want more information.

This way, you typically will not have SQL queries any higher then the DAO layer, which means your application is a) maintainable and b) it's easier to change backends later.

Answer 2

Izza, there is a discussion about separating SQL from java code here: Java - Storing SQL statements in an external file Your solution is understandable, but it will have some problems if queries is not standart (for example, not just where a = 10, but contains in (...) or group by so I suggest you to avoid it. To minimize boilerplate code when you are working with DB in Java you should use Spring JDBC. Also you could use Hibernate, if it is acceptable in your case, it allowsyou to avoid some usages of sql.

Answer 3

The best approach is to use Hibernate, which is now the industry standard.

In a nutshell, it generates the SQL needed and the code deals with java objects that represent rows. If you call setters, Hibernate figures out the SQL needed to accomplish the update.

For getters, your code might look like for example:

shoppingCart.getCustomer().getCountry().getCode();

And hibernate figures out the SQL joins needed to get from the shopping_cart table to the country table via the customer table.

It is really awesome and worth transitioning to.

Answer 4

You should use some DAOFactory, this class is used for getting connections. For reflecting tables in database you should create DTO - Data Tranfer Objects, that represent entities. So if you have table User, just create UserDTO.java with attributes and getters and setters. And class for communication with database is DAO - Data Access Object. You should only here create SQL statetements and methods for getting data from your database. Well-designed structure in the first place.Then is your code getting more cleaner, faster and secure. I recommend to your create your own ORM. So, have look and some test with different frameworks

---

EasyORM

double count = 0;
TransDB trans = new TransDB() ;
List<Trans> list = new ArrayList<Trans>();
list = trans.getAll();
for (Trans element : list)
{
count+= element.getData();
}
...

Hibernate

double count = 0;
Session session = null;
List<Trans> list = new ArrayList<Trans>();
list = HibernateUtil.getSessionFactory().openSession();
list = (List<Trans>) session
.createQuery("from Trans").list();
for (Trans element : list)
{
count += element.getData().doubleValue();
}
...

and compare??

Evaluation(in ms)

EasyORM: MySQL: init - 6344, avg - 4868 MS SQL: init - 8126, avg - 6752

Hibernate: MySQL: init - 27406, avg - 23728 MS SQL: init - 28605(+250%), avg - 24912

So your own ORM actually generated from the SQL script is of the order faster than Hibernate(to 10) and why? By inserting between-layer certainly can not go to improve throughput. This is only one test, i have also others. So for me i recommend to you create your own ORM, also here is some disadvantages like time consume for example or problematic change used DMS but advantages as full control over generated commands, you can use features specific to particular DMS(ORDM, special commands etc.). So i don't think that Hibernate is the best, really no.