Object-Relational Mapping是一種常見的設計模式
一個資料庫表格會對應一個java的類別，這個java的類別我們通常稱為VO(Value Object)或是Data Transfer Object(DTO)
用來在client端和server端之間傳遞資料
範例中以POJO(Plain-Old-Java-Object)類別來實作business entity(business table)
在POJO中有三大部分
- 成員變數 instance variable
- get方法 用來抓資料
- 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
輸出結果
