Simple DAO API Using JDBC

   Update: Source code now available @ http://code.google.com/p/simple-dao-api/ 

This article is about the "Simple DAO API" which I wrote using JDBC API to assist database programming. This TINY LITTLE API containing 7 classes [Not exactly an API, you can not call a set of 7 classes as API I guess]; is INSPIRED by the SPRING DAO classes and is intended for those beginners who just started learning JDBC and trying their small examples, class assignments and for those who are trying to develop small desktop applications, small Servlet based web applications. For this kind of development it'll be difficult to use the those very nice features of APIs like Spring to ease database programming, as it requires bit of time to understand the Spring API, and most of the times you end up not using all the features of the API. You can find the links to download the source code at the end of this article.

As I mentioned above these classes were written after getting inspired by the Spring DAO API classes, you'll see similarities in naming. Lets see how these classes ease JDBC programming. All you've to do is follow the steps given bellow

• Create your DAO class and make it extend BaseDAO which is from this API. This will allow you to have access to methods like "queryForInt()", "queryForObject()", "query()", and "update()" to execute queries.
• Step-1 will force you to call BaseDAO's constructor. Add a constructor and call super class' constructor. You need to pass two parameters to it.
  • Instance of javax.sql.DataSource
  • boolean 'readOnly' flag to set read only property of connection, kind of performance related step, which will avoid write locks on tables when your DAO is only reading data.
• Create a .properties file, <Your DAO class name>.properties, where we'll place all the queries used in this DAO. This will make your code look lot simpler.
• You need to add one field to the DAO class. Instance of 'ClassResources' which is used to read queries from <You DAO class name>.properties file.

That's all you need, now you can just call "query(...), queryForInt(...), queryForObject(...) and update(...)" methods and make your code look lot simpler. Above steps will give you an abstract view of how you need to use these classes. Lets see what are the files you'll end up with for one DAO. Lets say I've to create a DAO class "MyDAO.java". Files you need to create are

• MyDAO.java - Java class file with the code to read data.
• MyDAO.properties - Properties file with all the queries needed by MyDAO.java

That's it. Lets see the code in MyDAO.java. Say I need to write three methods in this DAO

• Method to give employee name, using the employee id
• Method to give complete employee details, using employee id
• Method to give list of employees.

Lets code these methods. First make the DAO extend BaseDAO.

   public class MyDAO extends BaseDAO {
    ...
   }
  

Second step - call BaseDAO's constructor

   public class MyDAO extends BaseDAO {
    public MyDAO(DataSource ds) {
     // data source and read only flag
     super(ds, true);
    }
   }
  

As you can see we are passing 'true' for the read only flag, because all the methods in this DAO are doing read only operations. If you've one write operation then you need to pass 'false' for this 'readOnly' flag.
Third step is adding 'ClassResources' instance to read the queries from the .properties file.

   private static final ClassResources resources = new ClassResources(MyDAO.class);
  

This field is declared as "private static final", as one instance will serve the purpose for all instances of this DAO[if you've multiple instances], and its used only in this class, so declared as 'private'. The constructor takes 'MyDAO.class' as parameter which will be used to get the class name and to read the properties file.
We are done with the required steps. Now let see the actual database related code. The first method which gives employee name by employee id, before writing the code lest add the query to the MyDAO.properties file.

   SELECT_NAME=SELECT name FROM employee WHERE emp_id=?
  

Now the code to execute this query

   public String getEmployeeName(final int empId) throws SQLException {
    return queryForObject(GenericMappers.stringRowMapper, resources, "SELECT_NAME", empId);
   }
  

Thats all you need to query database for a String. To query one object we use 'queryForObject()' method. As 'String' is also an object we used 'queryForObject()'. If you are querying for an Integer you can use 'queryForInt()'. If you are querying multiple rows then you need to use 'query()'. In the above method 'stringRowMapper' will do the mapping of result set to String.
Now the second method to get employee details using employee id. First the query

   SELECT_EMPLOYEE=SELECT emp_id, name, designation FROM employee WHERE emp_id=?
  

Code to execute this query

   public EmployeeDTO getEmployeeDetails(final int empId) throws SQLException {
    return queryForObject(empMapper, resources, "SELECT_EMPLOYEE", empId);
   }
  

Its almost similar to the first method except two changes, the first parameter - row mapper and third parameter - the query key. We need to write the code to map employee information to an Object of type 'EmployeeDTO'. This DTO is just a plain java bean with all the needed fields and getter/setter methods for them.

   public class EmployeeDTO {
    private int empId;
    private String name, designation;
    public void setEmpId(final int empId) {
     this.empId = empId;
    }
    public int getEmpId() {
     return empId;
    }
    // other setter/getter methods
   }
  

Now the row mapping code, all the row mapper classes must be instances of 'RowMapper' interface, which is declared in 'GenericMappers' class.

   private static final RowMapper<EmployeeDTO> empMapper = new RowMapper<EmployeeDTO>() {
    public EmployeeDTO mapRow(ResultSet rs) throws SQLException {
     EmployeeDTO dto = new EmployeeDTO();
     dto.setEmpId(rs.getInt(1));
     dto.setName(rs.getString(2));
     dto.setDesignation(rs.getString(3));
     return dto;
    }
   };
  

Simple isn't it!. Now the last one to get list of employees. Each employee is represented by EmployeeDTO, we'll use the same row mapper declared above.

   SELECT_ALL_EMPLOYEES=SELECT emp_id, name, designation FROM employee
  

   public List<EmployeeDTO> getEmployees() throws SQLException {
    return query(empMapper, resources, "SELECT_ALL_EMPLOYEES");
   }
  

All the three method are single line methods. Code looks really simple and clean. Complete code is give below.
MyDAO.properties

   SELECT_NAME=SELECT name FROM employee WHERE emp_id=?

   SELECT_EMPLOYEE=SELECT emp_id, name, designation FROM employee WHERE emp_id=?

   SELECT_ALL_EMPLOYEES=SELECT emp_id, name, designation FROM employee
  

MyDAO.java

   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.util.List;

   import javax.sql.DataSource;

   import com.rakesh.common.db.BaseDAO;
   import com.rakesh.common.db.util.GenericMappers;
   import com.rakesh.common.db.util.GenericMappers.RowMapper;
   import com.rakesh.common.util.ClassResources;

   public class MyDAO extends BaseDAO {
    private static final ClassResources resources = new ClassResources(
      MyDAO.class);

    public MyDAO(DataSource ds) {
     super(ds, true);
    }

    public String getEmployeeName(final int empId) throws SQLException {
     return queryForObject(GenericMappers.stringRowMapper, resources,
       "SELECT_NAME", empId);
    }

    public EmployeeDTO getEmployeeDetails(final int empId) throws SQLException {
     return queryForObject(empMapper, resources, "SELECT_EMPLOYEE", empId);
    }

    public List<EmployeeDTO> getEmployeeList() throws SQLException {
     return query(empMapper, resources, "SELECT_ALL_EMPLOYEES");
    }

    private static final RowMapper<EmployeeDTO> empMapper = new RowMapper<EmployeeDTO>() {
     public EmployeeDTO mapRow(ResultSet rs) throws SQLException {
      EmployeeDTO dto = new EmployeeDTO();
      dto.setEmpId(rs.getInt(1));
      dto.setName(rs.getString(2));
      dto.setDesignation(rs.getString(3));
      return dto;
     }
    };
   }
  

Now if you have a situation where you need to execute multiple quries, using 'queryXXX()' methods will result one connection for each method call. This is not good, to solve this we need to use Transactions, which will allow you to use one connection for all the queries. Sample code block looks like the one below

   TransactionManager trans = TransactionManager.start();
    try {
     // execute all queries
     queryForInt(...);
     query(...);
     update(...);
     trans.commit();
    } catch (Exception e) {
     trans.rollback();
     // do rest of the work
    } finally {
     trans.finished();
    }
  

If you are using DAO object in a Servlet based web application, instead of creating DAO objects in "doGet() OR doPost()" methods, you can create DAO objects in "init()" method and keep the reference in the servlet. Use this reference in "doXXX()" methods. By doing this you can restrict DAO object instances/references.
If you are using DAO objects in Struts based web application, instead of creating DAO objects in "execute()" method, you can override the method "setServlet()", this method will be called twice in struts action class life cycle - When it creates action class instance and when it destroys action class instance. The difference - the parameter. It takes one parameter 'ActionServlet' instance. When the "setServlet()" gets called while creating action instance, this parameter will be a not null, and null in other case. You can have an 'if' condition and create DAO object first time. Example code might look like the one below

   public void setServlet(ActionServlet servlet) {
    super.setServlet(servlet);
    if (servlet != null) {
     // create DAO instance
    } else {
     // assign null to DAO object and remove reference.
    }
   }
  

That is all I've! My intention writing these classes is not to provide any solution, but to help those beginners and those who are coding small assignments using JDBC. And also to show, how we can eliminate redundant code like connection management, statement creation, parameter setting, execution, mapping result set to objects, etc. I hope it helps few people who are looking out for some information in this regard.
Please find the links to download the source code and Java documentation for those classes below
Download Source Code
Download Jar file
Download Java Documentation
Right click on the above links and select Save Target As/Save Link As options.
Do drop a note, I'd love to see what you think about my effort!