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

By Angsuman Chakraborty, Gaea News Network
Saturday, August 19, 2006

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);

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.


November 19, 2009: 12:59 pm

Hello, I read your article and I’am totally agree with you. In my case, I developed a similar framework, except that the difference was that instead of reading SQL statements from an XML file, each SQL statement corresponds to a method, all these within a class called In essence is the same, but without use of a HashMap and the XML file. It’s a good idea this mini Java Database Framework and is useful for small applications (or even not so small) while not requiring the services of a sophisticated ORM. However, I would like to send me your code of the framework and know more in detail this idea that you post here. Thanks in advance. Greetings.

Madhu Sudhan
March 19, 2009: 9:58 pm

Hi -
Read you article. I have a very simple requirement of archiving 3 tables to a flat file and deleting the archived data from the database. I will be running this in a batch mode with capabilities to re-start. I will maintain control data in a table which will give auto restart capabilities. Can you please send me your framework and any other suggestions if you have on any batch framework that you know off.

September 13, 2006: 10:13 am

[...] PHP, Web, WordPress, WebLog, ORM, Web Services, Database, RDBMS, Headline News | | RSS 2.0 | Trackback this Article | Email thisArticle [...]

September 5, 2006: 11:34 am

Spring an ORM? That’s not true, it’s an Inversion of Control container that can integrate with ORM tools. Sounds like you want to take a look at Spring-JDBC functionality. it provides a JDBC wrapper that allows you to pass in SQL (parameterized if you’d like) and get back either a collection or Map. Spring manages the connection and the JDBC code. It’s miles from an ORM, but great for read-only or small applications that don’t need ORM functionality.

August 29, 2006: 3:42 pm

Im curious, how do you map the result set rows into objects and vice versa. With just 80 lines of code this can’t be done. Or are you only using a HashMap for each row? In that case you are miles away of the intention of the ORM tools. But you’re right for simple problems your approach is fully sufficient.

August 21, 2006: 1:36 pm

The problem with some of the methods of commons dbutils is that they don’t scale well with large datasets. For example take a look at this:
Object[] result = (Object[]) run.query(
“SELECT * FROM raw WHERE query=?”, “java”, h);

It will try to create few hundred thousand objects!
In short I don’t need it to anything fancy like creating dummy objects for me. I can read ResultSet, thank you very much.

I have looked at dbutils. It is still too complicated for my needs. In fact iBatis is simpler and better in comparison.

August 21, 2006: 12:59 pm

Maybe you need to look at commons-dbutils?

will not be displayed