Skip to main content

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!

Comments

Mohammed said…
Well, Thats a handy API for a developer who does not want to wrap every thing inside try catch block for statements and result set or even connection.

Does this API also support for multiple read queries?
eg:

// get one employee for a department
int empId queryForInt(deptId);
// get employee details
return queryForObject(........,empId);

I know above can be one query but if i had to perform multiple read operations, how many connections will it take?
Rakesh A said…
If you use TransactionManager, as given in the above code block, it'll take one connection.

Popular posts from this blog

Simple Accordion using Java Script and CSS

Well you can find many online, but it's difficult to find one with out any dependent API. Most of the available accordions use other APIs for animation and other stuff. For those who just want accordion with out any other stuff, this one is the perfect one. It's very simple and you don't have to be a geek to understand it. Basic knowledge of Java script and CSS will do to understand how this works. In this article I'll take you through the steps of developing an accordion. Couple of minutes you are ready to write your own. Well then let's start developing one. Layout of the HTML block looks something like the one below Lets look at the CSS first which is very simple. /** container styles **/ .ra-p, .ra-cp { padding: 0 0 0.1em; } /**** heading styles ****/ .ra-p h2, .ra-cp h2 { margin: 0px; padding: 0.2em; cursor: pointer; color: #fff; background-color: #3d80b0; } /**** collapsed heading styles ****/ .ra-cp h

Hosting Multiple Domains In Tomcat

Tomcat allows us to host multiple domains in one instance, using multiple ' Host ' tags. In this article I will explain how to do it on Tomcat. This is very simple configuration using ' Host ' tags in your server.xml . A novice can also understand this configuration very easily. Before going into the details of the configuration first lets have a look at the ' Host ' tag, ' Context ' tag and ' Alias ' tags first. <Host name="domain1" appBase="[application base]" autoDeploy="[true/false]" unpackWARs="[true/false]"> <Alias>...</Alias> <Context path="" docBase="" reloadable="[true/false]"/> </Host> First lets have a look at ' Alias ' tag. This tag is used to provide aliases for your actual domain. For example you have a domain called 'domain1.com', and you want to run the same application for 'www.do

File Uploading Using Servlets, JSP and Commons File Upload API

I’ve seen many developers who are at the early stages of their career have problems with this topic and seen many posts in forums asking how to do it – File Uploading using Servlets, JSP!; this article will provide an example using Commons File Upload API. I tried to make the example as simple as possible, hope it helps those early birds. Example uses JSP to provide the pages with form where user can select the file to upload with other form fields, Commons File Upload API to process submitted form and read form fields separately, and Servlets as middle layer between JSP and Commons File Upload API, example also has ANT build script to generate the distributables. All the code can be downloaded, links to these resources are provided at the end of this post, lets get on with the example then. The flow in this example is as depicted in the following picture. As you can see, user selects the file to upload and provides normal form data as well, using "index.jsp" and submi