customersqldaoimpl.java

开发的一个java web的任务管理系统。

/**
 * 
 */
package unicom.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import unicom.bean.Customer;
import unicom.util.DBUtil;

/**
 * @author 陈才旭
 * 功能: 实现ICustomerDao接口，提供对数据库的直接访问
 * 优点：降低耦合，可以支持各种不同的数据库
 * 缺点：接口不好把握 
 * 建议：
 */
public class CustomerSqlDaoImpl implements ICustomerDao {

	//向数据库增加客户记录
	public boolean addCustomer(Customer c) {
		boolean flag = false;
		String type = c.getType();
		String number = c.getNumber();
		String name = c.getName();
		java.sql.Date birthday = c.getBirthday();
		String sex = c.getSex();
		String address = c.getAddress();
		String sql = "insert into TCustomer(id,type,number,name,birthday,sex,address) values(null,?,?,?,?,?,?)";
		Connection conn = DBUtil.getConn();
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql);
		    pstmt.setString(1,type);
		    pstmt.setString(2, number);
		    pstmt.setString(3,name);
		    pstmt.setDate(4, birthday);
		    pstmt.setString(5, sex);
		    pstmt.setString(6, address);
		    pstmt.execute();
			flag = true;
		} catch (SQLException e) {
			e.printStackTrace();
			flag = false;
		}finally{
			//释放资源
			DBUtil.close(conn);
			DBUtil.close(pstmt);
		}
		return flag;
	}

	//检查数据库库中是否存在该Customer记录 flase:不存在，true:表示存在
	public boolean checkCustomerByNumber(String number) {
		boolean flag = false;
		String sql = "select * from TCustomer where number = ?";
		Connection conn = DBUtil.getConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, number);
			rs = pstmt.executeQuery();
			if(rs.next())
				flag = true;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			//释放资源
			DBUtil.close(conn);
			DBUtil.close(pstmt);
		}
		return flag;
	}

	//根据客户number返回Customer对象
	public Customer returnCustomerByNumber(String number) {
		Customer c = new Customer();
		String sql = "select name,birthday,sex,address,type from TCustomer where number = ?";
		Connection conn = DBUtil.getConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
	    try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, number);
			rs = pstmt.executeQuery();
			if(rs.next()){
			c.setName(rs.getString(1));
			c.setBirthday(rs.getDate(2));
			c.setSex(rs.getString(3));
			c.setAddress(rs.getString(4));
			c.setType(rs.getString(5));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			//释放资源
			DBUtil.close(conn);
			DBUtil.close(pstmt);
			DBUtil.close(rs);
		}
		
		return c;
	}

	//以列表形式返回所有客户信息
	public List returnAllCustomer() {
		List list = new ArrayList();
		String sql = "select id,name from TCustomer";
		Connection conn = DBUtil.getConn();
		Statement stmt = null;
		ResultSet rs = null;
		try {
		stmt = conn.createStatement();
		rs = stmt.executeQuery(sql);
		while(rs.next())
		{
			Customer c = new Customer();
			c.setNumber(rs.getString(1));
			c.setName(rs.getString(2));
			list.add(c);
		}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			//释放资源
			DBUtil.close(conn);
			DBUtil.close(rs);
			DBUtil.close(stmt);
		}
		return list;
	}
	


}