Java Database Framework (ORM Replacement) in 80 Lines of Code

I wrote about how I use a simple Java framework instead of ORM like Hibernate or Spring for effectively solving my data handling requirements. It has made me tremendously productive. It sparked a major debate in ServerSide. So I decided to provide more details about my ORM-replacement framework. My needs are simple and are likely to match with any SQL happy Java developers.

To recapitulate my requirements are:

1. I needed a way to put all the SQL queries and DDL’s in a separate file. This allows me or a DBA to later analyze the query with a fine tooth comb and optimize if necessary. It also allows me to easily change them without changing the code. Most of all cleanliness of the solution is appealing. At this point I am sure you are thinking of iBatis. I tried iBatis. Initially I liked it and thought I had my solution. However as I went down the lane I realized it too gave me features that I didn’t need. Even this was more complicated than I needed. All I needed was a HashMap saved to a file in XML format. And my database class should support query execution by name (think key-value).

2. Secondly I needed connection pooling to prevent opening and closing too many connections and also running out of connections. I found a nice solution in Proxool. Additionally it supports having multiple connection profiles in a simple text file and optionally logging queries.

3. I needed to integrate these two capabilities in a simple database class along with utility methods like cleanly closing connection and optionally logging the query data.

- read details here.

I solved it in 80 lines of code. You may need to add few more methods to address your requirements. I followed the YAGNI principle here, I will add the extra methods only when I need it and not before.

This is the API:

public class com.taragana.util.Db {
    // Returns a Connection from Connection Pool
    public static java.sql.Connection getConnection() throws java.sql.SQLException;
    // Returns a SQL Query String for a given name (queries are stored in a xml file as name-value pairs)
    public static java.lang.String getQuery(java.lang.String);
    // Executes a SQL query for a given name on a given Statement
    public static java.sql.ResultSet executeQuery(java.lang.String, java.sql.Statement) throws java.sql.SQLException;
    // Executes a SQL update (DDL, INSERT, UPDATE, DELETE) for a given name on a given Statement
    public static int executeUpdate(java.lang.String, java.sql.Statement) throws java.sql.SQLException;
    // Makes best effort to close a Connection
    public static void close(java.sql.Connection);
}

This framework is obviously not for the weak-hearted. You have to be comfortable with SQL and JDBC. It simply takes away some pain associated with JDBC without encumbering you with the load of a heavy ORM framework. At the end of the day, if you fall in the above category, it will make you much more productive and without any learning curve.

I heard some lame benefits of using ORM framework like database independence.
Firstly if you know SQL well and stay away from using database specific features then your SQL code is pretty much database independent most of the time. My advice is (that you should) follow SQL-92. Even if you have to make changes while porting to a different database, they will be very minor in nature. This observation is based on my experience of developing and maintaining two enterprise products simultaneously in MS SQL Server, Oracle and Sysbase.
Secondly how many times a developer on average changes database of his products midway? If you are thinking of a high number then you need a good architect in your team. Your core problem is lack of architectural abilities.

So what does a simple SQL INSERT call looks like?

Connection conn = Db.getConnection();
Statement stmt = conn.createStatement();
Db.executeUpdate(args[0], stmt);
Db.close(conn);

That’s it! No Class loading, no driver loading, no tricks. You have connection pooling (proxool), optional query logging, query by name, ability to connect to different databases by name using a simple configuration file.

Note: First argument to executeUpdate is a String (passed by commandline in this example) which contains the name of the SQL query, not the query itself.

If you are interested in the source code / classes of this micro-framework let me know in comments or by emailing me - angsuman[at]taragana[dot]com.

Looking forward to your criticisms and comments.