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.