customeraccess.java

Athena酒店小组_Athena酒店管理系统

/*
 * CustomerAccess.java
 *
 * Created on 2007年5月23日, 上午1:39
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package evan;

import java.sql.*;
import java.util.*;

import plugin.*;
import helper.*;

/**
 *
 * @author Evan Chan
 */
public class CustomerAccess
{
    private IDBResource dbRes;
    
    /** Creates a new instance of CustomerAccess */
    public CustomerAccess(IDBResource dbRes)
    {
	this.dbRes = dbRes;
    }
    
    public int insert(CustomerInfo customerInfo)
    {
	try
	{
	    Connection conn = dbRes.getConnection();
	    String sql = "INSERT INTO CustomerInfo(CustomerId, Name, EnglishName, Sex, Citizenship, Nation, " +
		    "Birthplace, Id, Birthday, Marriage, Career, Religion, CustomerType, Blacklist, Cellphone, " +
		    "HomeTel, OfficeTel, Fax, Msn, Email, Company, " +
		    "Post, Address, SourceId) " +
		    " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	    PreparedStatement ps = conn.prepareStatement(sql);
	    
	    ps.setString(1, customerInfo.getCustomerId());
	    ps.setString(2, customerInfo.getName());
	    ps.setString(3, customerInfo.getEnglishName());
	    ps.setString(4, customerInfo.getSex());
	    ps.setString(5, customerInfo.getCitizenship());
	    ps.setObject(6, customerInfo.getNation());
	    ps.setString(7, customerInfo.getBirthplace());
	    ps.setString(8, customerInfo.getId());
            java.sql.Date date=new java.sql.Date(customerInfo.getBirthday().getYear(),customerInfo.getBirthday().getMonth(),customerInfo.getBirthday().getDay());
	    ps.setDate(9, date);
	    ps.setString(10, customerInfo.getMarriage());
	    ps.setString(11, customerInfo.getCareer());
	    ps.setString(12, customerInfo.getReligion());
	    ps.setString(13, customerInfo.getCustomerType());
            
	    ps.setBoolean(14, customerInfo.getBlacklist());
	    ps.setString(15, customerInfo.getCellphone());
	    ps.setString(16, customerInfo.getHomeTel());
	    ps.setString(17, customerInfo.getOfficeTel());
	    ps.setString(18, customerInfo.getFax());
	    ps.setString(19, customerInfo.getMsn());
	    ps.setObject(20, customerInfo.getEmail());
	    ps.setString(21, customerInfo.getCompany());
	    ps.setString(22, customerInfo.getPost());
	    ps.setString(23, customerInfo.getAddress());
	    ps.setString(24, customerInfo.getSourceId());
	    
	    int rs = ps.executeUpdate();
	    return rs;
	}
	catch (SQLException ex)
	{
	    ex.printStackTrace();
	    return -1;
	}
    }
    
    public int update(CustomerInfo customerInfo)
    {
	try
	{
	    Connection conn = dbRes.getConnection();
	    String sql = "UPDATE CustomerInfo SET CusomerId = ?, Name = ?, EnglishName = ?, " +
		    "Sex = ?, Citizenship = ?, Nation = ?, Birthplace = ?, Id = ?, Birthday = ?, Marriage = ?, " +
		    "Career = ?, Religion = ?, CustomerType = ?, Blacklist = ?, NetCommunication = ?, " +
		    "Cellphone = ?, HomeTel = ?, OfficeTel = ?, Fax = ?, Msn = ?, " +
		    "Email = ?, Company = ?, Post = ?, Address = ?, SourceId=?) " +
		    "WHERE CustomerId = ?";
	    PreparedStatement ps = conn.prepareStatement(sql);	    
	    
	    ps.setString(1, customerInfo.getCustomerId());
	    ps.setString(2, customerInfo.getName());
	    ps.setString(3, customerInfo.getEnglishName());
	    ps.setString(4, customerInfo.getSex());
	    ps.setString(5, customerInfo.getCitizenship());
	    ps.setString(6, customerInfo.getNation());
	    ps.setString(7, customerInfo.getBirthplace());
	    ps.setString(8, customerInfo.getId());
	    ps.setObject(9, customerInfo.getBirthday());
	    ps.setString(10, customerInfo.getMarriage());
	    ps.setString(11, customerInfo.getCareer());
	    ps.setString(12, customerInfo.getReligion());
	    ps.setString(13, customerInfo.getCustomerType());
	    ps.setBoolean(14, customerInfo.getBlacklist());
	    ps.setString(15, customerInfo.getCellphone());
	    ps.setString(16, customerInfo.getHomeTel());
	    ps.setString(17, customerInfo.getOfficeTel());
	    ps.setString(18, customerInfo.getFax());
	    ps.setString(19, customerInfo.getMsn());
	    ps.setString(20, customerInfo.getEmail());
	    ps.setString(21, customerInfo.getCompany());
	    ps.setString(22, customerInfo.getPost());
	    ps.setString(23, customerInfo.getAddress());
	    ps.setString(25, customerInfo.getSourceId());
	    
	    int rs = ps.executeUpdate();
	    return rs;
	}
	catch (SQLException ex)
	{
	    ex.printStackTrace();
	    return -1;
	}
    }
    
    public int delete(CustomerInfo customerInfo)
    {
	try
	{
	    Connection conn = dbRes.getConnection();
	    String sql = "DELETE FROM CustomerInfo WHERE CustomerId = ?";
	    PreparedStatement ps = conn.prepareStatement(sql);
	    
	    ps.setString(1, customerInfo.getCustomerId());
	    
	    int rs = ps.executeUpdate();
	    return rs;
	}
	catch (SQLException ex)
	{
	    ex.printStackTrace();
	    return -1;
	}
    }
    
    public CustomerInfo getCustomerById(String customerId)
    {
	try
	{
	    Connection conn = dbRes.getConnection();
	    String sql = "SELECT * FROM CustomerInfo WHERE CustomerId = ?";
	    PreparedStatement ps = conn.prepareStatement(sql);
	    ps.setString(1, customerId);
	    ResultSet rs = ps.executeQuery();
	    ArrayList<CustomerInfo> customers = new ArrayList<CustomerInfo>();
	    readData(customers, rs);
	    if (customers.size() != 1)
	    {
		return null;
	    }
	    return customers.get(0);
	}
	catch (SQLException ex)
	{
	    ex.printStackTrace();
	    return null;
	}
    }
    
    public ArrayList<CustomerInfo> getAllCustomers()
    {
	try
	{
	    Connection conn = dbRes.getConnection();
	    String sql = "SELECT * FROM CustomerInfo";
	    PreparedStatement ps = conn.prepareStatement(sql);
	    ResultSet rs = ps.executeQuery();
	    ArrayList<CustomerInfo> customers = new ArrayList<CustomerInfo>();
	    readData(customers, rs);
	    return customers;
	}
	catch (SQLException ex)
	{
	    ex.printStackTrace();
	    return null;
	}
    }
    
    public ArrayList<CustomerInfo> getCustomersByCondition(ArrayList<String> names, ArrayList<Object> values)
    {
	try
	{
	    Connection conn = dbRes.getConnection();
	    String sql = "SELECT * FROM CustomerInfo " + SQLHelper.createCondition(names);
	    PreparedStatement ps = conn.prepareStatement(sql);
	    for(int i = 0; i < values.size(); i++)
	    {
		ps.setObject(i+1, values.get(i));
	    }
	    ResultSet rs = ps.executeQuery();
	    ArrayList<CustomerInfo> customers = new ArrayList<CustomerInfo>();
	    readData(customers, rs);
	    return customers;
	}
	catch (SQLException ex)
	{
	    ex.printStackTrace();
	    return null;
	}
    }
    
    
    private void readData(ArrayList<CustomerInfo> customers, ResultSet rs) throws SQLException
    {
	while(rs.next())
	{
	    CustomerInfo customer = new CustomerInfo();
	    customer.setCustomerId(rs.getString("CustomerId"));
	    customer.setName(rs.getString("Name"));
	    customer.setEnglishName(rs.getString("EnglishName"));
	    customer.setSex(rs.getString("Sex"));
	    customer.setCitizenship(rs.getString("Citizenship"));
	    customer.setNation(rs.getString("Nation"));
	    customer.setBirthplace(rs.getString("Birthplace"));
            customer.setId(rs.getString("Id"));
            customer.setBirthday(rs.getDate("Birthday"));
	    customer.setMarriage(rs.getString("Marriage"));
	    customer.setCareer(rs.getString("Career"));
	    customer.setReligion(rs.getString("Religion"));
	    customer.setCustomerType(rs.getString("CustomerType"));
	    customer.setBlacklist(rs.getBoolean("Blacklist"));
	    customer.setCellphone(rs.getString("Cellphone"));
	    customer.setHomeTel(rs.getString("HomeTel"));
	    customer.setOfficeTel(rs.getString("OfficeTel"));
	    customer.setFax(rs.getString("Fax"));
	    customer.setMsn(rs.getString("Msn"));
	    customer.setEmail(rs.getString("Email"));
	    customer.setCompany(rs.getString("Company"));
	    customer.setPost(rs.getString("Post"));
	    customer.setAddress(rs.getString("Address"));
	    customer.setSourceId(rs.getString("SourceId"));
	    customers.add(customer);
	}
    }    
}