customerdaomysqlimpl.java

struts的一些用的书籍

package app15a.dao;

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import app15a.dao.DAOException;
import app15a.dao.DBUtil;
import app15a.to.CustomerTO;
import app15a.to.CustomerSearchCriteriaTO;

public class CustomerDAOMySQLImpl extends DAOBase 
  implements CustomerDAO {

  private static final String GET_CUSTOMER_SQL = 
    "SELECT name, address, phone FROM customers WHERE id = ?";
  private static final String CREATE_CUSTOMER_SQL = 
    "INSERT INTO customers (name,address,phone) VALUES (?, ?, ?)";
  private static final String DELETE_CUSTOMER_SQL = 
    "DELETE FROM customers WHERE id = ?";
  private static final String UPDATE_CUSTOMER_SQL = 
    "UPDATE customers SET name=?, address=?, phone=? WHERE id = ?";
  private static final String SEARCH_CUSTOMER_SQL =
  	"SELECT id, name, address, phone FROM customers WHERE ";
  
  public void createCustomer(CustomerTO customer) throws DAOException {
    Connection connection = null;
    PreparedStatement pStatement = null;
			           
    try {
      connection = getConnection();
      //Prepare a statement to insert a record
      pStatement = connection.prepareStatement(CREATE_CUSTOMER_SQL);
      pStatement.setString(1, customer.getName());
      pStatement.setString(2, customer.getAddress());
      pStatement.setString(3, customer.getPhone());
      pStatement.executeUpdate();
      pStatement.close();
    } 
    catch (SQLException ex) {         
      throw new DAOException();
    }
    finally {
      try {
      	connection.close();                              
      } 
      catch (SQLException ex) {         
        throw new DAOException();
      }
    }      
  }
  
  public void updateCustomer(CustomerTO customer) throws DAOException {
    Connection connection = null;
    PreparedStatement pStatement = null;
      
    try {
      connection = getConnection();
      // Prepare a statement to insert a record
      pStatement = connection.prepareStatement(UPDATE_CUSTOMER_SQL);
      pStatement.setString(1, customer.getName());
      pStatement.setString(2, customer.getAddress());
      pStatement.setString(3, customer.getPhone());
      pStatement.setInt(4, customer.getId());
      pStatement.executeUpdate();
      pStatement.close();
    } 
    catch (SQLException e) {
      throw new DAOException();
    } 
    finally {
      try {
        connection.close();                              
      } 
      catch (SQLException ex) {
      }
    }
  }
  
  public CustomerTO getCustomer(int customerId) throws DAOException {
    Connection connection = null;
    PreparedStatement pStatement = null;
    ResultSet rs = null;
    CustomerTO customer = new CustomerTO();
      
    try {
      connection = getConnection();
      // Prepare a statement to insert a record
      pStatement = connection.prepareStatement(GET_CUSTOMER_SQL);         
      pStatement.setInt(1,customerId);
      rs = pStatement.executeQuery();
      if (rs.next()) {
        customer.setName(rs.getString("name"));
        customer.setAddress(rs.getString("address"));              
        customer.setPhone(rs.getString("phone"));              
        customer.setId(customerId);
      }
      rs.close();
      pStatement.close();
    } 
    catch (SQLException ex) {
      throw new DAOException();
    } 
    finally {
      try {
        connection.close();                              
      } 
      catch (SQLException ex) {
      }
    }
    return customer;
  }
  
  public void deleteCustomer(int customerId) throws DAOException {
    Connection connection = null;
    PreparedStatement pStatement = null;
    try {
      connection = getConnection();
      pStatement = connection.prepareStatement(DELETE_CUSTOMER_SQL);
      pStatement.setInt(1, customerId);
      pStatement.executeUpdate();              
      pStatement.close();
    } 
    catch (SQLException e) {
      throw new DAOException();
    }  
    finally {
      try {
        connection.close();                              
      } 
      catch (SQLException ex) {
      }
    }
  }

  public ArrayList searchCustomers(
    CustomerSearchCriteriaTO searchCriteria) throws DAOException {
		      
    ArrayList arrayList = new ArrayList();
    Connection connection = null;
    Statement statement = null;
    ResultSet rs = null;
    
    //Build the search criterias
    StringBuffer criteriaSql = new StringBuffer(512);
    criteriaSql.append(SEARCH_CUSTOMER_SQL);
    if (searchCriteria.getName() != null) {
      criteriaSql.append("name LIKE '%" +
        DBUtil.fixSqlFieldValue(searchCriteria.getName()) + "%' AND ");
    }      
    if (searchCriteria.getAddress() != null) {
      criteriaSql.append("address LIKE '%" +
        DBUtil.fixSqlFieldValue(searchCriteria.getAddress()) + 
        "%' AND ");
    }       
    if (searchCriteria.getPhone() != null) {
      criteriaSql.append("phone LIKE '%" + DBUtil.fixSqlFieldValue(searchCriteria.getPhone()) + "%'");
    }
    
    //Remove unused 'And' & 'WHERE'
    if (criteriaSql.substring(criteriaSql.length()-5).
      equals(" AND "))
      criteriaSql.delete(criteriaSql.length()-5,
        criteriaSql.length()-1);
    if (criteriaSql.substring(criteriaSql.length()-7).
      equals(" WHERE "))
      criteriaSql.delete(criteriaSql.length()-7,
        criteriaSql.length()-1);

    try {
      connection = getConnection();
      statement = connection.createStatement();
      rs = statement.executeQuery(criteriaSql.toString());
       
      while (rs.next()) {
        CustomerTO customer = new CustomerTO();            
        customer.setId(rs.getInt("id"));
        customer.setName(rs.getString("name"));
        customer.setAddress(rs.getString("address"));
        customer.setPhone(rs.getString("phone"));
        arrayList.add(customer);
      }
      rs.close();
      statement.close();
    } 
    catch (SQLException e) { 
      throw new DAOException();
    }
    finally {
      try { 
        connection.close();
      }
      catch (SQLException ex) {
      }
    }
    return arrayList;
  }
}