DAO (Data Access Object) pattern

Objective

In this tutorial, we will learn how to connect to the database from a Java program that minimize the number of connection to the database. This task is usually done by the class of Data Access Object (DAO) which play the role of connect and work (in and out) from/to the database.
In this topic, we distinguish two cases: the system has only one DAO class or many DAO classes.

System with only one DAO class

In this case, we have to:
– Declare a global and static variable of type Connection in the DAO class, and set it to private to use inside this class only.
– In the constructor method of the DAO class, we test if the variable con is already initiated. If it is not still initiated, we have initiate it.
– In all database methods of the DAO class, DO NOT close the con variable after using.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

public class ClientDAO {
	public static Connection con;//declare the variable
	//constructor
	public ClientDAO(){
		if(con == null){
			String dbUrl = "jdbc:mysql://localhost:3306/hotel";
			String dbClass = "com.mysql.jdbc.Driver";

			try {
				Class.forName(dbClass);
				con = DriverManager.getConnection (dbUrl, "root", "12345678");
			}catch(Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * get a client whose id is @key
	 * @param key
	 * @return
	 */
	public Client getClientById(int key){
		Client client = null;
		String sql = "SELECT * FROM tblClient WHERE id=?";
		try{
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setInt(1, key);
			ResultSet rs = ps.executeQuery();

			if(rs.next()){
				client = new Client();
				client.setId(rs.getInt("id"));
				client.setName(rs.getString("name"));
				client.setAddress(rs.getString("address"));
				client.setTel(rs.getString("tel"));
				client.setEmail(rs.getString("email"));
				client.setNote(rs.getString("note"));
			}
                  //DO NOT close the connection here!
		}catch(Exception e){
			e.printStackTrace();
		}	
		return client;
	}
}

System with more than one class DAO

In this case, all DAO classes have to use only one connection to the database, so we need to:
– Create an abstract class which represents for all DAO classes to connect to the Database.
– In this abstract DAO class, declare a global and static variable of type Connection in the DAO class, and set it to protected to use inside this class and its inheritance only.
– In the constructor method of the abstract DAO class, we test if the variable con is already initiated. If it is not still initiated, we have initiate it.
– In all inherited DAO classes, call the constructor of their parent in their constructor.
– In all database methods of all DAO classes, DO NOT close the con variable after using.
Note that, you could use the pattern of getConnection() to statically get the connection of the database to use. But it is not necessary because this kind of pattern is usually used to provide the connection to outside the class. Meanwhile the connection to the database is usually used inside the DAO class and its inherited classes only.

Example code

DAO.java

import java.sql.Connection;
import java.sql.DriverManager;

public class DAO {
	protected static Connection con;//declare the connection
	
	//constructor
	public DAO(){
		if(con == null){
			String dbUrl = "jdbc:mysql://localhost:3306/hotel";
			String dbClass = "com.mysql.jdbc.Driver";

			try {
				Class.forName(dbClass);
				con = DriverManager.getConnection (dbUrl, "root", "12345678");
			}catch(Exception e) {
				e.printStackTrace();
			}
		}
	}
}

ClientDAO.java

import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class ClientDAO extends DAO{
	//constructor
	public ClientDAO(){
		super();
	}
	
	/**
	 * get a client whose id is @key
	 * @param key
	 * @return
	 */
	public Client getClientById(int key){
		Client client = null;
		String sql = "SELECT * FROM tblClient WHERE id=?";
		try{
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setInt(1, key);
			ResultSet rs = ps.executeQuery();

			if(rs.next()){
				client = new Client();
				client.setId(rs.getInt("id"));
				client.setName(rs.getString("name"));
				client.setAddress(rs.getString("address"));
				client.setTel(rs.getString("tel"));
				client.setEmail(rs.getString("email"));
				client.setNote(rs.getString("note"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}	
		return client;
	}
}

RoomDAO.java

import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class RoomDAO extends DAO{
	//constructor
	public RoomDAO(){
		super();
	}
	
	/**
	 * get a room whose id is @key
	 * @param key
	 * @return
	 */
	public Room getRoomById(int key){
		Room room = null;
		String sql = "SELECT * FROM tblClient WHERE id=?";
		try{
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setInt(1, key);
			ResultSet rs = ps.executeQuery();

			if(rs.next()){
				room = new Room();
				room.setId(rs.getString("id"));
				room.setName(rs.getString("name"));
				room.setType(rs.getString("type"));
				room.setDisplayPrice(rs.getFloat("price"));
				room.setDescription(rs.getString("des"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}	
		return room;
	}
}

One thought on “DAO (Data Access Object) pattern”

  1. I in addition to my buddies have already been examining the excellent information from the website and before long got a horrible suspicion I never thanked the blog owner for those secrets. My young men were definitely stimulated to read through them and already have simply been making the most of those things. Appreciation for getting simply considerate and for getting some fine issues millions of individuals are really desirous to understand about. My honest apologies for not expressing gratitude to earlier.

    Like

    Reply

Leave a comment