Using Spring Framework on Creating DAO Layer

On J2EE or J2SE project development, sometimes there’s one block which have a function to manage everything related with the database, result fetching of supplied queries was managed on this block. With this type of design, we’re easier to track if there’s an error on our java application.
For Java Developer which familiar to use database as a part of the application, doesn’t have any difficulty to create a block which have a function as a database access, and then place the block inside the architecture of the application.

When creating this database access block, there’s some way that we can use, one of them is using Spring Framework. Yes, now we will try to use Spring Framework to access database, by creating database access or more familiar as DAO Layer. And for this try-it-out we will use MySQL Database (Again….? Oh God…). First thing we have to do to try-this-out is create a table on a database on MySQL Database and complete this table with the data needed. We will name this table as web_account and containing about 10 or 20 data inside. For the field of this table, let’s just create with our desire. Or maybe we can copy-paste this below SQL Code:

CREATE TABLE `web_account` (
  `USERNAME` varchar(30) NOT NULL DEFAULT '',
  `PASSWORD` varchar(30) NOT NULL DEFAULT '',
  `CREATE_TIME` datetime DEFAULT NULL,
  `CREATE_USER` varchar(30) DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `UPDATE_USER` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`USERNAME`)
);

INSERT INTO `web_account` VALUES ('admin','adminya33','2009-03-15 08:32:21','admin','2009-03-15 08:32:21','admin'),('andre','andre456','2009-03-15 08:34:53','admin','2009-03-15 08:34:53','admin'),('fitri','fitri54','2009-03-15 08:33:22','admin','2009-03-15 08:33:22','admin'),('ganteng','ganteng99','2009-03-15 08:34:21','admin','2009-03-15 08:34:21','admin'),('gerry','gerry87','2009-03-15 08:33:41','admin','2009-03-15 08:33:41','admin'),('gue','guelagi','2009-03-15 08:33:56','admin','2009-03-15 08:33:56','admin'),('hebat','hebatlah','2009-03-15 08:34:36','admin','2009-03-15 08:34:36','admin'),('heru','heur334','2009-03-15 08:32:54','admin','2009-03-15 08:32:54','admin'),('joe','joe77','2009-03-15 08:32:39','admin','2009-03-15 08:32:39','admin'),('kasep','sekap25','2009-03-15 08:33:08','admin','2009-03-15 08:33:08','admin'),('linux','linux09','2009-03-15 08:35:22','admin','2009-03-15 08:35:22','admin'),('mojo','monk123','2009-03-15 08:31:50','admin','2009-03-15 08:31:50','admin');

From the structure of the table above, we have to create a Java Class which has class properties that can represent the fields of the table above. The purpose of this class creation is this class will be the container of the data table. On usage of java code, this class will be an object variable that can be accessed as Array of Object or Single Object.

We will create this class by using simple POJO (Plain Old Java Object), below is the source code of this class:

package org.mojo.spring.entity;

import java.util.Date;

/**
 * Created by IntelliJ IDEA.
 * User: Mojo
 * Date: Mar 15, 2009
 * Time: 8:45:10 AM
 * To change this template use File | Settings | File Templates.
 */
public class WebAccount {
    private String userName;
    private String password;
    private Date createDate;
    private String createUser;
    private Date updateDate;
    private String updateUser;

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Date getCreateDate() {
        return createDate;
    }

    public void setCreateDate(Date createDate) {
        this.createDate = createDate;
    }

    public String getCreateUser() {
        return createUser;
    }

    public void setCreateUser(String createUser) {
        this.createUser = createUser;
    }

    public Date getUpdateDate() {
        return updateDate;
    }

    public void setUpdateDate(Date updateDate) {
        this.updateDate = updateDate;
    }

    public String getUpdateUser() {
        return updateUser;
    }

    public void setUpdateUser(String updateUser) {
        this.updateUser = updateUser;
    }

    public String toString() {
        return "WebAccount{" +
                "userName='" + userName + '\'' +
                ", password='" + password + '\'' +
                ", createDate=" + createDate +
                ", createUser='" + createUser + '\'' +
                ", updateDate=" + updateDate +
                ", updateUser='" + updateUser + '\'' +
                '}';
    }
}

And now we are ready to create a DAO Layer using Spring Framework, don’t forget to download the needed libraries such as Spring Library, MySQL Connector library, and common-logging library, because these libraries is needed to follow this try-it-out. The common-logging library has to include too in this try-it-out, because the Spring Framework have a very high dependencies to this library. And the MySQL Connector library is needed too, because we use MySQL Database on this try-it-out (Again… :P).

For the DAO Layer that we will create below, we just limit it on fetching how many rows data on the table (SELECT COUNT(1) ) and fetching all data (SELECT *) only. The others method such as insert, update, or delete, we can add it later. For the select count(1) method the run-down scenario is: this method will have a return data type as integer, while the select * method will have a return data type as a list. Let’s see below source code:

package org.mojo.spring.dao.JdbcDao;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.mojo.spring.entity.WebAccount;

import javax.sql.DataSource;
import java.util.List;
import java.util.ArrayList;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;

/**
 * Created by IntelliJ IDEA.
 * User: Mojo
 * Date: Mar 15, 2009
 * Time: 8:46:57 AM
 * To change this template use File | Settings | File Templates.
 */
public class WebAccountJDBCDAO extends JdbcDaoSupport {
    private DataSource ds;
    private JdbcTemplate tpl;

    public int getNumOfWebAccount() {
        logger.info("getNumOfWebAccount");
        int iRes = 0;
        ds = getDataSource();
        tpl = new JdbcTemplate(ds);
        String sQuery = "SELECT COUNT(1) from web_account";
        try {
            iRes = tpl.queryForInt(sQuery);
            logger.info("Result of getNumOfWebAccount : " + iRes);
        } catch (Exception e) {
            logger.info("Exception in getNumOfWebAccount()" + e.getMessage());
        }
        return iRes;
    }

    public List<WebAccount> getWebAccountList(boolean flagLimit, int limit) {
        final List<WebAccount> result = new ArrayList();
        final int iLimit = limit;
        ds = getDataSource();
        JdbcTemplate tpl = new JdbcTemplate(ds);
        String sQuery = "SELECT * FROM rss_feeder";

         if (flagLimit) {
            sQuery += " LIMIT ?";
        }

        class PreparedStatementHandler implements PreparedStatementSetter {
            public void setValues(PreparedStatement ps) throws SQLException {
                int n = 0;
                ps.setInt(++n, iLimit);
            }
        }

        class ServiceHandler implements RowCallbackHandler {
            public void processRow(ResultSet rs) throws SQLException {
                WebAccount wa = new WebAccount();
                wa.setUserName(rs.getString("WEB_ACCOUNT"));
                wa.setPassword(rs.getString("PASSWORD"));
                wa.setCreateDate(rs.getDate("CREATE_DATE"));
                wa.setCreateUser(rs.getString("CREATE_USER"));
                wa.setUpdateDate(rs.getDate("UPDATE_DATE"));
                wa.setUpdateUser(rs.getString("UPDATE_USER"));
                result.add(wa);
            }
        }

        if (flagLimit) {
            tpl.query(sQuery, new PreparedStatementHandler(), new ServiceHandler());
        } else {
            tpl.query(sQuery, new ServiceHandler());
        }
        return result;
    }
}

Let’s discuss above codes. On the top of the source code, there’s a declaration where this class is located, and the next lines, we just import the needed class of Spring Framework library and other Java default library.

package org.mojo.spring.dao.JdbcDao;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.mojo.spring.entity.WebAccount;

import javax.sql.DataSource;
import java.util.List;
import java.util.ArrayList;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;
………

We have named this class as WebAccountJDBDDAO class, marked by the class declaration on the next line after comment lines (I’m Using IntelliJidea as my favorite IDE). After the class declaration, we create 2 variables, ds as DataSource type and tpl as JdbcTemplate type.

………
public class WebAccountJDBCDAO extends JdbcDaoSupport {
    private DataSource ds;
    private JdbcTemplate tpl;
………

DataSources is a type of object that we picked up from javax.sql package, while the JdbcTemplate is a type of object that we picked up form Spring package, located on org.springframework.jdbc.core.JdbcTemplate. And then, let’s take look one by one the methods that exist on the codes.

Method getNumOfWebAccount:

……
 public int getNumOfWebAccount() {
        logger.info("getNumOfWebAccount");
        int iRes = 0;
        ds = getDataSource();
        tpl = new JdbcTemplate(ds);
        String sQuery = "SELECT COUNT(1) from web_account";
        try {
            iRes = tpl.queryForInt(sQuery);
            logger.info("Result of getNumOfWebAccount : " + iRes);
        } catch (Exception e) {
            logger.info("Exception in getNumOfWebAccount()" + e.getMessage());
        }
        return iRes;
    }
……

On this method we can see, the query that will be execute is only simple query which is “select count(1) from web_account”. If we execute this query on MySQL Console, the result of this query is the number of how much rows are exists on web_account table. On this method we put the result of the query into a variable iRes, this iRes variable has a integer as it’s data type. If we want to arrange the steps of this method, the steps will be seen as:

  1. Create integer type variable and name it as iRes.

  2. Initialize the ds variable by calling getDataSource interface.

  3. Create new JdbcTemplate object and the parameter is ds.

  4. Create a String variable, named as sQuery and directly fill the string by the MySQL query.

  5. Execute the query on try-catch block and then put the result of the query into iRes variable.

  6. Crate a return statement of the methods.

While the lines containing logger, is our way to mark the process flow, so if there’s any error occurred on the codes we can trace it easily.

And then let’s discuss the 2nd method which is getWebAccountList method. On this method, we create 2 inner class, the purpose of this inner class creation is to make our codes look more orderly:

…….
       class PreparedStatementHandler implements PreparedStatementSetter {
            public void setValues(PreparedStatement ps) throws SQLException {
                int n = 0;
                ps.setInt(++n, iLimit);
            }
        }

     class ServiceHandler implements RowCallbackHandler {
            public void processRow(ResultSet rs) throws SQLException {
                WebAccount wa = new WebAccount();
                wa.setUserName(rs.getString("USERNAME"));
                wa.setPassword(rs.getString("PASSWORD"));
                wa.setCreateDate(rs.getDate("CREATE_TIME"));
                wa.setCreateUser(rs.getString("CREATE_USER"));
                wa.setUpdateDate(rs.getDate("UPDATE_TIME"));
                wa.setUpdateUser(rs.getString("UPDATE_USER"));
                result.add(wa);
            }
        }
…….

If we take a look on the codes of both inner classes, each of this inner class handled two important functions. The function on the first inner class handled PrepareStatement, this class implements PrepareStatementSetter belongs to Spring Framework. And the 2nd inner class handled the localization of query result onto WebAccount object variable, this inner class implements RowcallbackHandler belongs to Spring Framework also.

On the 1st inner class we override setValues methods that owned by the PreparedStatementHandler class as a requirement of implementation. And the 2nd class we also have to override processRow method as a requirement of RowCallBackHandler class implementation. And then let’s take a look at the main code of the getWebAccountList method that we’ve already created before.

……
 public List<WebAccount> getWebAccountList(boolean flagLimit, int limit) {
        final List<WebAccount> result = new ArrayList();
        final int iLimit = limit;
        ds = getDataSource();
        JdbcTemplate tpl = new JdbcTemplate(ds);
        String sQuery = "SELECT * FROM rss_feeder";

         if (flagLimit) {
            sQuery += " LIMIT ?";
        }
……..
……..
……..
        if (flagLimit) {
            tpl.query(sQuery, new PreparedStatementHandler(), new ServiceHandler());
        } else {
            tpl.query(sQuery, new ServiceHandler());
        }
        return result;
    }
……

On the main code of the above method we have return value as a list and 2 parameters which is flagLimit as boolean type and limit as integer type. The steps of this method arrangement is same with the first method that we have created before on getNumOfWebAccount method, just because that we have 2 parameters on this method, so let’s create a little programming logic here, which is on branching lines. On the branching lines we create a logic if the value on flagLimit is true then the query will be added by “LIMIT ?” so the query will be “SELECT * FROM web_account limit ?”, and if the value of flagLimit is false then the query will not be change, but it still same just like the first initialization.

The dots that exist in the middle of the main codes are the codes of inner classes that we have discussed before. And then we have arrived to the code where the lines of query execute. In this lines there’s also a branch that will decide whether the code will use the inner PreparedStatementHandler class of not, the decision of this usage is depends on flagLimit variable value, go head, do your own analyze the branching codes. And the end of this method is the existence of the return statement declared as a list that represented by the result variable. That’s it, that’s how we use Spring Framework on creating DAO Layer. And the next step are we will try to use the DAO Layer in the Java Consol Application.

In order to use this DAO Layer, we have to make a Spring Framework configuration file. Ok, let’s just create this file, let’s name it as “applicationContext.xml” file, just like another article posted before which using Spring Framework also. We will place this configuration file on a config folder which is a line with the src folder on this try-it out project. Below is the source code of Spring Configuration file.

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans-2.0.dtd">
<beans>
    <!--Data Source-->
    <bean id="ds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost/java_spring"/>
        <property name="username" value="root"/>
        <property name="password" value="root99"/>
    </bean>

    <bean id="webAccountDAO" class="org.mojo.spring.dao.JdbcDao.WebAccountJdbcDAO">
        <property name="dataSource" ref="ds"/>
    </bean>
</beans>

Let’s take a look of this configuration file, the first bean that we have to create is ds, this bean is a bean which have a function to access MySQL Database, this bean use the class that belongs to Spring Framework which is DriverManagerDataSource class. And then, just because this is the Spring access block to connect to database, so we have to supply the property that related with the connection to database creation, such as username, url, password, dan driverClassName of the database that we will use and it’s property given is depends on the setting of our MySQL database settings.

And then the 2nd bean is a bean that will represent DAO Layer that we have created above. The property that has to be filled up by this bean is dataSource property, while the reference of this bean property is ds bean that we have cerated before.

And the usage of this DAO Layer on Java Console Application, shown as the codes below:

package org.mojo.spring.application;

import org.mojo.spring.dao.JdbcDao.WebAccountJdbcDAO;
import org.mojo.spring.entity.WebAccount;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.FileSystemXmlApplicationContext;

import java.util.List;

/**
 * Created by IntelliJ IDEA.
 * User: Mojo
 * Date: Mar 15, 2009
 * Time: 11:05:55 AM
 * To change this template use File | Settings | File Templates.
 */
public class SpringReadDatabase {

    private WebAccountJdbcDAO dao = null;
    public static void main(String[] args) {
        //call SpringConfiguration File
        ApplicationContext ctx = new FileSystemXmlApplicationContext("config/applicationContext.xml");
        SpringReadDatabase app = new SpringReadDatabase();

        //call Desired bean
        app.dao = (WebAccountJdbcDAO) ctx.getBean("webAccountDAO");

        //declare Variable
        WebAccount[] waList = null;

        //getNumOfWebAccount
        int i = app.dao.getNumOfWebAccount();
        System.out.println("Num Rows : " + i);

        //getWebAccountList
        List<WebAccount> list = app.dao.getWebAccountList(false,0);

        //convert List to ObjectArray
        if (list.size() > 0) {
            waList = new WebAccount[list.size()];
            for (short j = 0; j < list.size(); j++) {
                waList[j] = list.get(j);
            }
        }

        //iterate ObjectArray to PrintOut to console
        for (int j = 0; j < waList.length; j++) {
            WebAccount webAccount = waList[j];
            System.out.println("Name : " + webAccount.getUserName());
            System.out.println("Password : " + webAccount.getPassword());
            System.out.println("FullWebAccount : " + webAccount);
        }

    }

    public void setDao(WebAccountJdbcDAO dao) {
        this.dao = dao;
    }
}

That’s it, that’s how we use the DAO Layer that we have created before by using Spring Framework in Java Console Application. To discuss this java console application, please do your own analyzing in order to get more understanding of the codes that we have created.

 

I hope this will helpful.

Menteng, March 15th 2009

 

josescalia

One thought on “Using Spring Framework on Creating DAO Layer

  1. Pingback: Using Spring Framework on Creating DAO Layer | XML Developer

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s