oracletcustomerdao.java

这是我在东方标准学习的时候做的一个项目

package dao.product.oracle;

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

import dao.product.DbUtil;
import dao.product.TCustomerDAO;

import bean.TCustomer;


public class OracleTCustomerDAO implements TCustomerDAO {

	public boolean add(TCustomer custom) {
		DbUtil db = new OracleDbUtil();
		Connection conn = db.getConnection();
		String sql = null;
		sql = "insert into TCustomer (customer_id,id_type,id_number,custom_name,custom_birthday,custom_sex,custom_address) values (custom_sequence.nextval,?,?,?,?,?,?)";
		try {
			PreparedStatement prepStmt = conn.prepareStatement(sql);
			prepStmt.setString(1, custom.getId_type());
			prepStmt.setString(2, custom.getId_number());
			prepStmt.setString(3, custom.getCustom_name());
			prepStmt.setString(4, custom.getCustom_birthday());
			prepStmt.setString(5, custom.getCustom_sex());
			prepStmt.setString(6, custom.getCustom_address());
			prepStmt.execute();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			db.close();
		}
	}

	public boolean update(TCustomer custom) {
		DbUtil db = new OracleDbUtil();
		Connection conn = db.getConnection();
		String sql = "update TCustomer set id_type=?,id_number=?,custom_name=?,custom_birthday=?,custom_sex=?,custom_address=? where customer_id=?";
		try {
			PreparedStatement prepStmt = conn.prepareStatement(sql);
			prepStmt.setString(1, custom.getId_type());
			prepStmt.setString(2, custom.getId_number());
			prepStmt.setString(3, custom.getCustom_name());
			prepStmt.setString(4, custom.getCustom_birthday());
			prepStmt.setString(5, custom.getCustom_sex());
			prepStmt.setString(6, custom.getCustom_address());
			prepStmt.setInt(7, custom.getCustomer_id());
			prepStmt.execute();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			db.close();
		}
	}

	public boolean delete(int customer_id) {
		DbUtil db = new OracleDbUtil();
		Connection conn = db.getConnection();
		String sql = "delete TCustomer where customer_id=?";
		try {
			PreparedStatement prepStmt = conn.prepareStatement(sql);
			prepStmt.setInt(1, customer_id);
			prepStmt.execute();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			db.close();
		}
	}

	public TCustomer findById(int customer_id) {
		TCustomer custom = new TCustomer();
		DbUtil db = new OracleDbUtil();
		Connection conn = db.getConnection();
		String sql = "select * from TCustomer where customer_id=?";
		try {
			PreparedStatement prepStmt = conn.prepareStatement(sql);
			prepStmt.setInt(1, customer_id);
			ResultSet rs = prepStmt.executeQuery();
			if (rs.next()) {
				custom.setCustomer_id(rs.getInt("customer_id"));
				custom.setId_type(rs.getString("id_type"));
				custom.setId_number(rs.getString("id_number"));
				custom.setCustom_name(rs.getString("custom_name"));
				custom.setCustom_birthday(rs.getString("custom_birthday"));
				custom.setCustom_sex(rs.getString("custom_sex"));
				custom.setCustom_address(rs.getString("custom_address"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.close();
		}
		return custom;
	}
	
	public TCustomer findByIdInfo(String id_type, String id_number) throws SQLException {
		TCustomer custom = null;
		DbUtil db = new OracleDbUtil();
		Connection conn = db.getConnection();
		String sql = "select * from TCustomer where id_type=? and id_number=?";
		try {
			PreparedStatement prepStmt = conn.prepareStatement(sql);
			prepStmt.setString(1, id_type);
			prepStmt.setString(2, id_number);
			ResultSet rs = prepStmt.executeQuery();
			if (rs.next()) {
				custom = new TCustomer();
				custom.setCustomer_id(rs.getInt("customer_id"));
				custom.setId_type(rs.getString("id_type"));
				custom.setId_number(rs.getString("id_number"));
				custom.setCustom_name(rs.getString("custom_name"));
				custom.setCustom_birthday(rs.getString("custom_birthday"));
				custom.setCustom_sex(rs.getString("custom_sex"));
				custom.setCustom_address(rs.getString("custom_address"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SQLException();//不影响关闭连接的情况下抛出相应异常
		} finally {
			db.close();
		}
		return custom;
	}

	public List findAll() {
		List<TCustomer> list = new ArrayList<TCustomer>();
		DbUtil db = new OracleDbUtil();
		Connection conn = db.getConnection();
		String sql = "select * from TCustomer";
		try {
			PreparedStatement prepStmt = conn.prepareStatement(sql);
			ResultSet rs = prepStmt.executeQuery();
			while (rs.next()) {
				TCustomer custom = new TCustomer();
				custom.setCustomer_id(rs.getInt("customer_id"));
				custom.setId_type(rs.getString("id_type"));
				custom.setId_number(rs.getString("id_number"));
				custom.setCustom_name(rs.getString("custom_name"));
				custom.setCustom_birthday(rs.getString("custom_birthday"));
				custom.setCustom_sex(rs.getString("custom_sex"));
				custom.setCustom_address(rs.getString("custom_address"));
				list.add(custom);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.close();
		}
		return list;
	}

}