employeejdbc.java

这是一本描述JDBC数据库的书籍

/*
 * EmployeeJDBC.java
 *
 * Created on June 10, 2005, 2:31 PM
 *
 * To change this template, choose Tools | Options and locate the template under
 * the Source Creation and Management node. Right-click the template and choose
 * Open. You can then make changes to the template in the Source Editor.
 */

package ch02.jdbc;
import java.sql.*;
import common.*;
import ch02.*;
import java.util.*;
import ch02.pojo.*;
import ch02.manager.*;
import ch02.oid.HLIDGenerator;

/**
 *
 * @author kevin
 */
public class EmployeeJDBC implements EmployeeDAO{
    
    
    public void addEmployee(IEmployee employee){
        String id = HLIDGenerator.nextEmployeeID();
        employee.setPersonID(id);
        Connection conn = null;
        PreparedStatement pstmtPerson = null;
        PreparedStatement pstmtEmployee = null;
        try{
            String sqlPerson = "insert into person_tbl (id, firstname, " +
                    "lastname, address) values (?,?,?,?)";
            String sqlEmployee = "insert into employee_tbl (id, ssn, " + 
                    "person_id) values (?, ?, ?)";
            ConnectionFactory factory = ConnectionFactory.getConnectionFactory();
            conn = factory.getConnection();
            pstmtPerson = conn.prepareStatement(sqlPerson);
            pstmtEmployee = conn.prepareStatement(sqlEmployee);
            pstmtPerson.setString(1, id);
            pstmtPerson.setString(2, employee.getFirstName());
            pstmtPerson.setString(3, employee.getLastName());
            pstmtPerson.setString(4, employee.getAddress());
            
            pstmtEmployee.setString(1, employee.getEmployeeID());
            pstmtEmployee.setString(2, employee.getSsn());
            pstmtEmployee.setString(3, id);
            
            conn.setAutoCommit(false);
            
            try{
                pstmtPerson.executeUpdate();
                pstmtEmployee.executeUpdate();
                conn.commit();
            }catch(SQLException e){
                e.printStackTrace();
                conn.rollback();
            }
            
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            DBUtils.close(pstmtPerson, conn);
            DBUtils.close(pstmtEmployee);
        }
        
    }
    public IEmployee getEmployee(String employeeID){
        String sqlEmployee = "select id, ssn, person_id from employee_tbl where id = ?";
        String sqlPerson = "select id, firstname, lastname, address from " +
                " person_tbl where id = ?";
        Connection conn = null;
        PreparedStatement pstmtEmployee = null;
        PreparedStatement pstmtPerson = null;
        ResultSet rsEmployee = null;
        ResultSet rsPerson = null;
        Employee employee = null;
        try{
            ConnectionFactory factory = ConnectionFactory.getConnectionFactory();
            conn = factory.getConnection();
            pstmtEmployee = conn.prepareStatement(sqlEmployee);
            pstmtEmployee.setString(1, employeeID);
            rsEmployee = pstmtEmployee.executeQuery();
            rsEmployee.next();
            String person_id = rsEmployee.getString("person_id");
            
            pstmtPerson = conn.prepareStatement(sqlPerson);
            pstmtPerson.setString(1, person_id);
            rsPerson = pstmtPerson.executeQuery();
            rsPerson.next();
            
            employee = new Employee();
            employee.setPersonID(person_id);
            employee.setFirstName(rsPerson.getString("firstname"));
            employee.setLastName(rsPerson.getString("lastname"));
            employee.setAddress(rsPerson.getString("address"));
            employee.setEmployeeID(rsEmployee.getString("id"));
            employee.setSsn(rsEmployee.getString("ssn"));
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            DBUtils.close(rsEmployee, pstmtEmployee, conn);
            DBUtils.close(rsPerson, pstmtPerson);
        }        
        return employee;
    }
    public void updateEmployee(IEmployee employee){
        if(!employee.isDirty())return;
        String sqlEmployee = "update employee_tbl set  ssn=? where id=?";
        String sqlPerson = "update person_tbl set firstname=?, " + 
                "lastname=?, address=? where id=?";
        Connection conn = null;
        PreparedStatement pstmtEmployee = null;
        PreparedStatement pstmtPerson = null;
        try{
            ConnectionFactory factory = ConnectionFactory.getConnectionFactory();
            conn = factory.getConnection();
            pstmtEmployee = conn.prepareStatement(sqlEmployee);

            pstmtEmployee.setString(1, employee.getSsn());
            pstmtEmployee.setString(2, employee.getEmployeeID());
            
            pstmtPerson = conn.prepareStatement(sqlPerson);
            pstmtPerson.setString(1, employee.getFirstName());
            pstmtPerson.setString(2, employee.getLastName());
            pstmtPerson.setString(3, employee.getAddress());
            pstmtPerson.setString(4, employee.getPersonID());
            
            conn.setAutoCommit(false);
            try{
                pstmtEmployee.executeUpdate();
                pstmtPerson.executeUpdate();
                conn.commit();
            }catch(SQLException e){
                e.printStackTrace();
                conn.rollback();
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            DBUtils.close(pstmtPerson,conn);
            DBUtils.close(pstmtEmployee);
        }
    }
    public void deleteEmployee(String employeeID){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try{
            conn = ConnectionFactory.getConnectionFactory().getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery("select person_id from employee_tbl " + 
                    " where id=\'" + employeeID + "\'");
            rs.next();
            String person_id = rs.getString("person_id");
            conn.setAutoCommit(false);
            try{
                stmt.executeUpdate("delete from employee_tbl where id=\'" + employeeID + "\'");
                stmt.executeUpdate("delete from person_tbl where id=\'" + person_id + "\'");
                conn.commit();
            }catch(SQLException e){
                e.printStackTrace();
                conn.rollback();
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            DBUtils.close(rs, stmt, conn);
        }
        
    }
    public List loadEmployee(){
        String sql = "select e.id id, e.ssn ssn, p.id person_id, " +
                " p.firstname firstname, p.lastname lastname, p.address " +
                " address from employee_tbl e, person_tbl p where p.id = e.person_id";
        
        List results = new ArrayList();
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        ResultSet rs = null;
        
        try{
            ConnectionFactory factory = ConnectionFactory.getConnectionFactory();
            conn = factory.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while(rs.next()){
                IEmployee emp = new Employee();
                emp.setEmployeeID(rs.getString("id"));
                emp.setSsn(rs.getString("ssn"));
                emp.setPersonID(rs.getString("person_id"));
                emp.setFirstName(rs.getString("firstname"));
                emp.setLastName(rs.getString("lastname"));
                emp.setAddress(rs.getString("address"));
                results.add(emp);
            }
            
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            DBUtils.close(rs, pstmt, conn);
            
        }
        return results;
    }
    
    
}