customerdaomysqlimpl.java

struts的一些用的书籍

package app16a.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 app16a.dao.DAOException;
import app16a.dao.DBUtil;
import app16a.to.CustomerSearchCriteriaTO;
import app16a.to.CustomerTO;

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();
  	} 
		catch (SQLException ex) {
  		throw new DAOException();
  	}
		catch (Exception ex) {
			throw new DAOException();
	  } 		
		finally {
  		try {
    		pStatement.close();
      	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();
		} 
    catch (SQLException e) {
    	throw new DAOException();
    } 
		catch (Exception ex) {
	  		throw new DAOException();
	  } 				
    finally {
    	try {
      	pStatement.close();
        connection.close();                              
      } 
    	catch (SQLException ex) {
        throw new DAOException();
      }
    }
  }
  
  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);
    } 
    catch (SQLException ex) {
    	throw new DAOException();
    } 
		catch (Exception ex) {
	  		throw new DAOException();
	  } 		    
    finally {
    	try {
      	rs.close();
        pStatement.close();
        connection.close();                              
      } 
    	catch (SQLException ex) {
      	throw new DAOException();
      }
    }
    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();              
    } 
    catch (SQLException e) {
    	throw new DAOException();
    }  
		catch (Exception ex) {
	  		throw new DAOException();
	  } 		    
    finally {
    	try {
      	pStatement.close();
        connection.close();                              
      } 
    	catch (SQLException ex) {
      	throw new DAOException();
      }
    }
  }

  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()) + "%'");
    }
    
    //Trim 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);
        }
     } 
    catch (SQLException e) {
    	throw new DAOException();
    }
		catch (Exception ex) {
	  		throw new DAOException();
	  } 		
    finally {
     	try { 
				rs.close();
        statement.close();
        connection.close();
      }
      catch (SQLException ex) {
        throw new DAOException();
      }
    }
    return arrayList;
  }

}