The Data Access Object Pattern

We want to hear from you! Please send us your FEEDBACK.

The following article/tutorial may contain actual software programs in source code form. This source code is made available for developers to use as needed, pursuant to the terms and conditions of this license.

SECTION A - Short Description/Synopsis

The Data Access Object design pattern is used to separate Data Source specific code so that rest of the application code is not dependent on any particular type of Data Source.

The following set of Java[tm] platform classes are used:

1. Student.java - A JavaBeans[tm] component architecture
2. Course.java - A JavaBeans component architecture
3. DataTap.java - An interface
4. DataTapFactory.java - A factory class used by the client to get access to a Data Source
5. ConnectionPool.java - A utility class managing the connections to the database
6. DataAccessObject.java - An abstract class implementing DataTap interface
7. DataAccessObjectMYSQL.java - A class extending the DataAccessObject class
8. Main.java - The client to the Data Access Object pattern formed effectively out of the rest of the six Java[tm] platform classes.

This is a simple example where the Data Model consists of three tables: one for the Student information, one for the Course information and another linking the students with the courses chosen by them. The table layouts for two of the three tables can be deduced from the structures of the JavaBeans architecture: Student and Course. The third table linking the other two contains their primary keys - student ID and course ID.

The class Main.java is the client, which in a Web application could be JavaServer Pages[tm] (JSP[tm]) software or another JavaBean architecture in the Business Logic Implementation Layer. For convienience, it has been written as a standalone Java[tm] platform client.

The interface DataTap.java contains all the methods which the client would be invoking on the Data Source. The DataAccessObject.java class implements this interface to provide the Data Source specific implementation of the interface. ConnectionPool.java is a utility class that is used by the "DataAccessObject" class to efficiently use the connections to the Data Source.

The DataTapFactory class is the factory class that provides the client with a reference to the appropriate type of Data Access Object, depending on some distiguisher. Here, the distinguisher is the database name.

The code presented here is complete except for a few places where the right database driver class names have to be inserted.

SECTION B - Task List

1. Decide on the business process implementation. Here, all that is required is that we have a record of all the Students and Courses in a university, as well as a facility that will help us retrieve the relationship between the Students and the Courses, and vice versa.

2. Based on Step 1, we now define the interface the client will have to the Data Access Layer. This is the DataTap.java interface. We define the methods in this interface so that the business process defined in Step 1 can be fulfilled. Hence, the following methods are defined:

   public Collection getAllStudents();

   public Collection getAllCourses();

   public Collection getCoursesForStudent(String studentID);

   public Collection getStudentsInCourse(String courseID);

3. Since we have the business process and the interface in place now, we can determine the data model. The data model consists of three tables. A "student" table, a "course" table and another linking the two which we will call the "student_course" table.

4. We create two other classes that will reflect the "student" and "course" tables in the database. These are the Student.java and Course.java beans respectively.

5. Create a ConnectionPool.java class. This utility class will take care of connection pooling.

6. Next we create a generic implementation of the DataTap interface that we defined in Step 2. This generic class will be abstract and contain information that is not relevant to any particular Data Source. This is the DataAccessObject.java class in our example. As this is a very simple example, most of our data retrieval logic resides here.

7. Create another class, DataAccessObjectMYSQL.java, that extends the abstract class DataAccessObject. Any source code specific to the "My SQL" database will live here. For this example nothing is required, so only a constructor is present.

8. Now create the factory class that will instantiate the appropriate type of DataAccessObject class. The factory class is DataTapFactory.java.

9. Finally, create the client Main.java. This will access the Data Access classes that we have created.

SECTION C - Solution source

Step 1: Create three tables:

student(studentid, name, email, major)

course(courseid, coursename, coursedept)

student_course(studentid, courseid)

You can load sample data into these.

Step 2: Create the interface DataTap.java

import java.util.Collection;

public interface DataTap{

    public Collection getAllStudents();

    public Collection getAllCourses();

    public Collection getCoursesForStudent(String studentID);

    public Collection getStudentsInCourse(String courseID);
}

Step 3: Create the JavaBeans architecture - Student.java and Course.java

Student.java

import java.io.Serializable;

public class Student implements Serializable{

    private String _studentID;
    private String _studentName;
    private String _eMail;
    private String _deptName;

    public Student(){
    }

    public Student(String id, String name, String email, String dept){
     _studentID = id;
     _studentName = name;
     _eMail = email;
     _deptName = dept;
    }

    /**
     * Get the value of _studentID.
     * @return value of _studentID.
     */
    public String getStudentID() {
     return _studentID;
    }

    /**
     * Set the value of _studentID.
     * @param v  Value to assign to _studentID.
     */
    public void setStudentID(String  v) {
     this._studentID = v;
    }

    /**
     * Get the value of _studentName.
     * @return value of _studentName.
     */
    public String getStudentName() {
     return _studentName;
    }

    /**
     * Set the value of _studentName.
     * @param v  Value to assign to _studentName.
     */
    public void setStudentName(String  v) {
     this._studentName = v;
    }

    /**
     * Get the value of _eMail.
     * @return value of _eMail.
     */
    public String getEMail() {
     return _eMail;
    }

    /**
     * Set the value of _eMail.
     * @param v  Value to assign to _eMail.
     */
    public void setEMail(String  v) {
     this._eMail = v;
    }

    /**
     * Get the value of _deptName.
     * @return value of _deptName.
     */
    public String getDeptName() {
     return _deptName;
    }

    /**
     * Set the value of _deptName.
     * @param v  Value to assign to _deptName.
     */
    public void setDeptName(String  v) {
     this._deptName = v;
    }

    public String toString(){
     return (_studentID+"-"+_studentName+"-"+_eMail+"-"+_deptName);
    }
}

Course.java

import java.io.Serializable;

public class Course implements Serializable{

    private String _courseID;
    private String _courseName;
    private String _deptName;

    public Course(){
    }

    public Course(String id, String name, String deptName){
     _courseID = id;
     _courseName = name;
     _deptName = deptName;
    }

    /**
     * Get the value of _courseID.
     * @return value of _courseID.
     */
    public String getCourseID() {
     return _courseID;
    }

    /**
     * Set the value of _courseID.
     * @param v  Value to assign to _courseID.
     */
    public void setCourseID(String  v) {
     this._courseID = v;
    }

    /**
     * Get the value of _courseName.
     * @return value of _courseName.
     */
    public String getCourseName() {
     return _courseName;
    }

    /**
     * Set the value of _courseName.
     * @param v  Value to assign to _courseName.
     */
    public void setCourseName(String  v) {
     this._courseName = v;
    }
 

    /**
     * Get the value of _deptName.
     * @return value of _deptName.
     */
    public String getDeptName() {
     return _deptName;
    }

    /**
     * Set the value of _deptName.
     * @param v  Value to assign to _deptName.
     */
    public void setDeptName(String  v) {
     this._deptName = v;
    }

    public String toString(){
     return(_courseID+"-"+_courseName+"-"+_deptName);
    }
}

Step 4: The ConnectionPool utility class

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

public class ConnectionPool{

    private ArrayList _freePool;
    private String _url;
    private String _username;
    private String _password;
    private String _dbDriverName;

    public ConnectionPool(){
     _freePool = new ArrayList();
    }

    public ConnectionPool(String connectURL, String uName,
     String pWord, String driverName){
         this();
         _url = connectURL;
         _username = uName;
         _password = pWord;
         _dbDriverName = driverName;
         _createConnections(_url,_username,_password,_dbDriverName);
    }

    public synchronized Connection getConnection(){
     if(_freePool.size() == 0){
     //None Available
     //Create a new set of Connections
     _createConnections(_url,_username,_password,_dbDriverName);
     }
     //Last connection in the Array so that we save time on re-arranging the Array
     return ((Connection)_freePool.remove(_freePool.size() - 1));
    }

    public void closeConnection(Connection con){
     System.out.println("Current size of Pool: "+_freePool.size());
     _freePool.add(con);
     System.out.println("Size of Pool after connection closed: "+
     _freePool.size());
    }

    private void _createConnections
     (String connectURL, String uName, String pWord, String driverName){
         try{
             Class.forName(driverName).newInstance();
             for(int i=0; i<10; ++i){
                 Connection con = DriverManager.getConnection
                  (connectURL,uName,pWord);
              _freePool.add(con);
             }
         }catch(ClassNotFoundException ex){
             System.out.println(ex);
         }catch(InstantiationException ex){
             System.out.println(ex);
         }catch(IllegalAccessException ex){
             System.out.println(ex);
         }catch(SQLException ex){
             System.out.println(ex);
         }
    }
}

Step 5: Abstract class DataAccessObject.java

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.ArrayList;

public abstract class DataAccessObject implements DataTap{

    private static final String GET_ALL_STUDENTS = "Select * from student";
    private static final String GET_ALL_COURSES = "Select * from course";
    private static String ALL_COURSES_FOR_A_STUDENT =
     "SELECT c.courseid, c.coursename, c.coursedept "+
     "FROM course c, student s, student_course sc "+
     "WHERE s.studentid=sc.studentid AND sc.courseid=c.courseid "+
     "AND s.studentid=";
    private static String ALL_STUDENTS_FOR_A_COURSE =
     "SELECT s.studentid, s.name, s.email, s.major "+
     "FROM student s, course c, student_course sc "+
     "WHERE c.courseid=sc.courseid AND sc.studentid=s.studentid "+
     "AND c.courseid=";

    private ConnectionPool _connectionPool;

    public DataAccessObject(){
    }

    public Collection getAllStudents(){
     Collection results = new ArrayList();
     try{
         Connection con = _connectionPool.getConnection();
         Statement stmt = con.createStatement();
         ResultSet rs =stmt.executeQuery(GET_ALL_STUDENTS);
         while(rs.next()){
          Student student = new Student
          (rs.getString(1),rs.getString(2),
           rs.getString(3),rs.getString(4));
          results.add(student);
         }
         stmt.close();
         _connectionPool.closeConnection(con);
     }catch(SQLException ex){
         System.out.println(ex);
     }
     return results;
    }

    public Collection getAllCourses(){
     Collection results = new ArrayList();
     try{
         Connection con = _connectionPool.getConnection();
         Statement stmt = con.createStatement();
         ResultSet rs =stmt.executeQuery(GET_ALL_COURSES);
         while(rs.next()){
          Course course = new Course
          (rs.getString(1),rs.getString(2),rs.getString(3));
          results.add(course);
         }
         stmt.close();
         _connectionPool.closeConnection(con);
     }catch(SQLException ex){
         System.out.println(ex);
     }
     return results;
    }

    public Collection getCoursesForStudent(String studentID){
     Collection results = new ArrayList();
     try{
         Connection con = _connectionPool.getConnection();
         Statement stmt = con.createStatement();
         ResultSet rs =stmt.executeQuery
          (ALL_COURSES_FOR_A_STUDENT + studentID);
         while(rs.next()){
          Course course = new Course
          (rs.getString(1),rs.getString(2),rs.getString(3));
          results.add(course);
         }
         stmt.close();
         _connectionPool.closeConnection(con);
     }catch(SQLException ex){
         System.out.println(ex);
     }
     return results;
    }

    public Collection getStudentsInCourse(String courseID){
     Collection results = new ArrayList();
     try{
         Connection con = _connectionPool.getConnection();
         Statement stmt = con.createStatement();
         ResultSet rs =stmt.executeQuery
          (ALL_STUDENTS_FOR_A_COURSE + courseID);
         while(rs.next()){
          Student student = new Student
          (rs.getString(1),rs.getString(2),
           rs.getString(3),rs.getString(4));
          results.add(student);
         }
         stmt.close();
         _connectionPool.closeConnection(con);
     }catch(SQLException ex){
         System.out.println(ex);
     }
     return results;
    }

    public void setConnectionPool(ConnectionPool connPool){
     _connectionPool = connPool;
    }
}

Step 6: Concrete class DataAccessObjectMYSQL.java

public class DataAccessObjectMYSQL extends DataAccessObject {

    public DataAccessObjectMYSQL(){
     super();
    }
}

Step 7: The Factory class DataTapFactory.java

public class DataTapFactory{

    public static DataTap getDataSourceAccessor(String dbType, String url,
         String uName, String pWord,
         String driverName)
    {
     if(dbType.equals("MYSQL")){
         ConnectionPool connPool = new
          ConnectionPool(url,uName,pWord,driverName);
         DataAccessObject dataAccess = new DataAccessObjectMYSQL();
         dataAccess.setConnectionPool(connPool);
         DataTap dataSource = (DataTap)dataAccess;
         return dataSource;
     }
     return null;
    }
}

Step 8: Client class Main.java

import java.util.Collection;
import java.util.Iterator;

public class Main
{
 public static void main (String[] args)
 {
  try
  {
   String username = <username-here>;
   String password = <password-here>;
   String driverName = <driver-name-here>;
   String url = <database-url-here>;
   String dbType = "MYSQL";

   DataTap source = DataTapFactory.getDataSourceAccessor
    (dbType, url, username, password, driverName);
   Collection students = source.getAllStudents();
   Iterator iter = students.iterator();

   while(iter.hasNext()){
    Student s = (Student)iter.next();
    System.out.println(s.toString());
   }
  }
  catch (Exception e){
   e.printStackTrace();
  }

 }
}

Conclusion:
Now assume that a change in business process requires us to change the Data Source from a database to a text file. The change will not require us to change the business logic in any way. The changes that are required will be adding an extra class to the Data Access Layer and also making some modifications to the DataTapFactory class.

The extra class that needs to be added is the DataAccessObjectTXT.java class, which again extends the abstract class DataAccessObject.java. But the methods in this class will have implementations that can handle text file parsing capabilities. So the DataAccessObjectTXT.java class could look like this:

import java.io.Serializable;
import java.util.ArrayList;
import java.util.Collection;

public class DataAccessObjectTXT extends DataAccessObject
    implements Serializable{

    public DataAccessObjectTXT(){
     super();
    }

 public Collection getAllStudents(){
  //Dummy Method
  Student s = new Student("S101", "StudentFromTXTFile", "a@b.com", "CSE");
  Collection c = new ArrayList();
  c.add(s);
  return c;
 }

 public Collection getAllCourses(){
  //Dummy Method
  Course cs = new Course("C101", "CourseFromTXTFile", "CSE");
  Collection c = new ArrayList();
  c.add(cs);
  return c;
 }

 public Collection getCoursesForStudent(String studentID){
  //Dummy Method
  Course cs = new Course("C202", "CourseFromTXTFile", "ECE");
  Collection c = new ArrayList();
  c.add(cs);
  return c;
 }

 public Collection getStudentsInCourse(String courseID){
  //Dummy Method
  Student s = new Student("S202", "StudentFromTXTFile", "a@b.com", "ECE");
  Collection c = new ArrayList();
  c.add(s);
  return c;
 }
}

Also, the modified Factory and Client classes (DataTapFactory.java and Main.java respectively) would be as follows:

DataTapFactory.java: The modified part is highlighted

public class DataTapFactory{

    public static DataTap getDataSourceAccessor(String dbType, String url,
         String uName, String pWord,
         String driverName)
    {
     if(dbType.equals("MYSQL")){
         ConnectionPool connPool = new
          ConnectionPool(url,uName,pWord,driverName);
         DataAccessObject dataAccess = new DataAccessObjectMYSQL();
         dataAccess.setConnectionPool(connPool);
         DataTap dataSource = (DataTap)dataAccess;
         return dataSource;
     }else if(dbType.equals("TXT")){
         DataAccessObject dataAccess = new DataAccessObjectTXT();
         DataTap dataSource = (DataTap)dataAccess;
         return dataSource;
     }
     return null;
    }
}
 
 

Main.java
 

import java.util.Collection;
import java.util.Iterator;

public class Main_Modified
{
 public static void main (String[] args)
 {
  try
  {
   String username = <username-here>; //Can be null
   String password = <password-here>; //Can be null
   String driverName = <driver-name-here>;//Can be null
   String url = <database-url-here>;//Can be null
   String dbType = "TXT";

   DataTap source = DataTapFactory.getDataSourceAccessor
    (dbType, url, username, password, driverName);
   Collection students = source.getAllStudents();
   Iterator iter = students.iterator();

   while(iter.hasNext()){
    Student s = (Student)iter.next();
    System.out.println(s.toString());
   }
  }
  catch (Exception e){
   e.printStackTrace();
  }

 }
}

DOC ID #1238