爪哇咖啡屋 : [轉貼]Design Pattern - Data Access Object

範例中以POJO(Plain-Old-Java-Object)類別來實作business entity(business table)

在POJO中有三大部分

1. 成員變數 instance variable
2. get方法 用來抓資料
3. set方法 用來存資料

public class EmpVO {
	private int empno;//變數成員，對應是Database裡的欄位
	private String ename;
	private java.sql.Date hiredate;
	private double salary;
	private Integer deptno;
	private String title;
	public int getEmpno() {//get方法，將值取出
		return empno;
	}
	public void setEmpno(int empno) {//set方法，將值插入
		this.empno = empno;
	}
	public String getEname() {
		return ename;
	}
	public void setEname(String ename) {
		this.ename = ename;
	}
	public java.sql.Date getHiredate() {
		return hiredate;
	}
	public void setHiredate(java.sql.Date hiredate) {
		this.hiredate = hiredate;
	}
	public double getSalary() {
		return salary;
	}
	public void setSalary(double salary) {
		this.salary = salary;
	}
	public Integer getDeptno() {
		return deptno;
	}
	public void setDeptno(Integer deptno) {
		this.deptno = deptno;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
}

另外我們還會在針對另一個資料庫的表格存取

設計一個(DAO)Data Access Object類別來對應

原本我們的SQL指令都是四散在程式當中，如果有一天要換資料庫會很麻煩

所以我們會將JDBC資料庫裡面用來跟資料庫做存取的程式碼放在這個類別內

import java.sql.SQLException;
import java.util.*;
public interface IEmpDAO {//DAO上面指派下來的介面
	public void getConnection() throws SQLException;//
	public int insert(EmpVO emp) throws SQLException;
	public int update(EmpVO emp) throws SQLException;
	public int delete(int empno) throws SQLException;
	public EmpVO findByPrimaryKey(int empno) throws SQLException;
	public List<EmpVO> getAll() throws SQLException;
	public void closeConn() throws SQLException;
} // end of class IEmpDAO

實作介面的類別

import java.util.*;
import java.sql.*;
public class EmpDAO implements IEmpDAO {//實作IEmpDAO介面
	private static final String INSERT_STMT = //SQL insert動態指令
		"INSERT INTO employee VALUES (?, ?, ?, ?, ?, ?)";
	private static final String UPDATE_STMT = //SQL update動態指令
		"UPDATE employee SET ename=?, hiredate=?, salary=?, deptno=?, title=? WHERE empno=?";
	private static final String DELETE_STMT = //SQL delete動態指令
		"DELETE FROM employee WHERE empno=?";
	private static final String GET_ONE_STMT = //SQL select指令
		"SELECT empno, ename, hiredate, salary, deptno, title FROM employee WHERE empno=?";
	private static final String GET_ALL_STMT = //SQL select指令
		"SELECT empno, ename, hiredate, salary, deptno, title FROM employee ORDER BY empno";
	Connection conn = null;
	public void getConnection() throws SQLException {//overloading getConnection方法
		String connUrl = "jdbc:sqlserver://localhost:1433;databaseName=jdbc";
		conn = DriverManager.getConnection(connUrl, "sa", "passw0rd");
	}
	public int insert(EmpVO emp) throws SQLException {//overloading insert方法
		int updateCount = 0;
		PreparedStatement pstmt = conn.prepareStatement(INSERT_STMT);
		pstmt.setInt(1, emp.getEmpno());
		pstmt.setString(2, emp.getEname());
		pstmt.setDate(3, emp.getHiredate());
		pstmt.setDouble(4, emp.getSalary());
		pstmt.setInt(5, emp.getDeptno());
		pstmt.setString(6, emp.getTitle());
		updateCount = pstmt.executeUpdate();
		return updateCount;
	}
	public int update(EmpVO emp) throws SQLException {//overloading update方法
		int updateCount = 0;
		PreparedStatement pstmt = conn.prepareStatement(UPDATE_STMT);
		pstmt.setString(1, emp.getEname());
		pstmt.setDate(2, emp.getHiredate());
		pstmt.setDouble(3, emp.getSalary());
		pstmt.setInt(4, emp.getDeptno());
		pstmt.setString(5, emp.getTitle());
		pstmt.setInt(6, emp.getEmpno());
		updateCount = pstmt.executeUpdate();
		return updateCount;
	}
	public int delete(int empno) throws SQLException {//overloading delete方法
		int updateCount = 0;
		PreparedStatement pstmt = conn.prepareStatement(DELETE_STMT);
		pstmt.setInt(1, empno);
		updateCount = pstmt.executeUpdate();
		return updateCount;
	}
	public EmpVO findByPrimaryKey(int empno) throws SQLException {//overloading findByPrimaryKey方法
		EmpVO emp = null;
		PreparedStatement pstmt = conn.prepareStatement(GET_ONE_STMT);
		pstmt.setInt(1, empno);
		ResultSet rs = pstmt.executeQuery();
		if (rs.next()) {
			emp = new EmpVO();
			emp.setEmpno(rs.getInt("empno"));
			emp.setEname(rs.getString("ename"));
			emp.setHiredate(rs.getDate("hiredate"));
			emp.setSalary(rs.getDouble("salary"));
			emp.setDeptno(rs.getInt("deptno"));
			emp.setTitle(rs.getString("title"));
		}
		return emp;
	}
	public List<EmpVO> getAll() throws SQLException {//overloading getAll()方法
		EmpVO emp = null;
		List<EmpVO> emps = new ArrayList<EmpVO>();
		PreparedStatement pstmt = conn.prepareStatement(GET_ALL_STMT);
		ResultSet rs = pstmt.executeQuery();
		while (rs.next()) {
			emp = new EmpVO();
			emp.setEmpno(rs.getInt("empno"));
			emp.setEname(rs.getString("ename"));
			emp.setHiredate(rs.getDate("hiredate"));
			emp.setSalary(rs.getDouble("salary"));
			emp.setDeptno(rs.getInt("deptno"));
			emp.setTitle(rs.getString("title"));
			emps.add(emp);
		}
		return emps;
	}
	public void closeConn() throws SQLException {//overloading closeConn()方法
		if (conn != null)
			conn.close();
	}
} // end of class EmpDAO

然而有時候我們會必須要自己寫一個程式來測試輸出是否正確
 

import java.sql.SQLException;
import java.util.*;
public class EmpDAODemo {
	public static void main(String[] args) {
		IEmpDAO dao = new EmpDAO();//多型
		try {
			dao.getConnection();
			// insert
			EmpVO emp1 = new EmpVO();//new一個自定的EmpVO物件等於橫向的列
			emp1.setEmpno(1009);
			emp1.setEname("Jean Tsao");
			emp1.setHiredate(java.sql.Date.valueOf("2015-05-16"));
			emp1.setSalary(45600);
			emp1.setDeptno(100);
			emp1.setTitle("engineer");
			int count1 = dao.insert(emp1);//insert資料進SQL
			System.out.println("insert " + count1 + " rows");//
			System.out.println("---------------------------------");
			// update
			EmpVO emp2 = new EmpVO();//new另一個自定的EmpVO物件，並將欲更新的資料填入
			emp2.setEmpno(1009);
			emp2.setEname("Jean Tsao");
			emp2.setHiredate(java.sql.Date.valueOf("2015-05-16"));
			emp2.setSalary(55600);
			emp2.setDeptno(100);
			emp2.setTitle("senior engineer");
			int count2 = dao.update(emp2);//update資料進SQL
			System.out.println("update " + count2 + " rows");
			System.out.println("---------------------------------");
			// find by PrimaryKey
			EmpVO emp3 = dao.findByPrimaryKey(1009);//從primary抓出列資訊印出
			System.out.println("empno = " + emp3.getEmpno());
			System.out.println("empname = " + emp3.getEname());
			System.out.println("hiredate = " + emp3.getHiredate());
			System.out.println("salary = " + emp3.getSalary());
			System.out.println("deptno = " + emp3.getDeptno());
			System.out.println("title = " + emp3.getTitle());
			System.out.println("---------------------------------");
			// get all emp
			List<EmpVO> emps = dao.getAll();//從list抓出所有資訊
			for (EmpVO emp : emps) {//for each將資料一筆一筆印出
				System.out.print(emp.getEmpno() + ", ");
				System.out.print(emp.getEname() + ", ");
				System.out.print(emp.getHiredate() + ", ");
				System.out.print(emp.getSalary() + ", ");
				System.out.print(emp.getDeptno() + ", ");
				System.out.print(emp.getTitle() + "\n");
			}
			System.out.println("---------------------------------");
			// delete
			int count3 = dao.delete(1009);
			System.out.println("delete " + count3 + " rows");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				dao.closeConn();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
} // end of class EmpDAODemo

輸出結果