clientdao.java

该项为帮助银行工作人员处理银行业务

package com.bluedot.bank.framework.dao;

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 com.bluedot.bank.framework.sql.MySqlInfo;
import com.bluedot.bank.framework.web.actionform.ClientBean;

public class ClientDao {
	public void create(ClientBean c) {
		Connection connection = null;
		connection = new MySqlInfo().getConnection();
		try {
			PreparedStatement statement = connection
					.prepareStatement("insert into customers(customer_id,first_name,last_name,middle_inital,street,city,state,zip,phone,email)values(?,?,?,?,?,?,?,?,?,?)");
			int index = 1;

			statement.setString(index++, c.getCustomer_id());
			statement.setString(index++, c.getFirst_name());
			statement.setString(index++, c.getLast_name());
			statement.setString(index++, c.getMiddle_inital());
			statement.setString(index++, c.getStreet());
			statement.setString(index++, c.getCity());
			statement.setString(index++, c.getState());
			statement.setString(index++, c.getZip());
			statement.setString(index++, c.getPhone());
			statement.setString(index++, c.getEmail());

			statement.execute();
			connection.commit();
			statement.close();
		} catch (SQLException e) {
			new MySqlInfo().rollback(connection);
			throw new RuntimeException(e);
		} finally {
			new MySqlInfo().closeCon(connection);
		}
	}

	public void update(ClientBean c) {
		Connection connection = null;
		connection = new MySqlInfo().getConnection();
		try {
			PreparedStatement statement = connection
					.prepareStatement("update customers set first_name=?,last_name=?,middle_inital=?,street=?,city=?,state=?,zip=?,phone=?,email=? where customer_id=? ");
			int index = 1;

			statement.setString(index++, c.getFirst_name());
			statement.setString(index++, c.getLast_name());
			statement.setString(index++, c.getMiddle_inital());
			statement.setString(index++, c.getStreet());
			statement.setString(index++, c.getCity());
			statement.setString(index++, c.getState());
			statement.setString(index++, c.getZip());
			statement.setString(index++, c.getPhone());
			statement.setString(index++, c.getEmail());
			statement.setString(index++, c.getCustomer_id());

			statement.execute();
			connection.commit();
			statement.close();
		} catch (SQLException e) {
			new MySqlInfo().rollback(connection);
			throw new RuntimeException(e);
		} finally {
			new MySqlInfo().closeCon(connection);
		}
	}

	public void delete(String id) {
		Connection connection = null;
		connection = new MySqlInfo().getConnection();
		try {
			PreparedStatement statement = connection
					.prepareStatement("delete from customers where customer_id=?");
			statement.setString(1, id);
			statement.execute();
			statement.close();
			connection.commit();
		} catch (SQLException e) {
			new MySqlInfo().rollback(connection);
			throw new RuntimeException(e);
		} finally {
			new MySqlInfo().closeCon(connection);
		}
	}
	
	public List<ClientBean> select() {
		List<ClientBean> list = new ArrayList<ClientBean>();
		Connection connection = null;
		connection = new MySqlInfo().getConnection();
		try {
			PreparedStatement statement = connection
					.prepareStatement("select customer_id,first_name,last_name,middle_inital,street,city,state,zip,phone,email from customers");

			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				ClientBean c = new ClientBean();
				c.setCustomer_id(rs.getString("customer_id"));
				c.setFirst_name(rs.getString("first_name"));
				c.setLast_name(rs.getString("last_name"));
				c.setMiddle_inital(rs.getString("middle_inital"));
				c.setStreet(rs.getString("street"));
				c.setCity(rs.getString("city"));
				c.setState(rs.getString("state"));
				c.setZip(rs.getString("zip"));
				c.setPhone(rs.getString("phone"));
				c.setEmail(rs.getString("email"));
				list.add(c);
			}
			statement.close();
			rs.close();
			connection.commit();
		} catch (SQLException e) {
			new MySqlInfo().rollback(connection);
			throw new RuntimeException(e);
		} finally {
			new MySqlInfo().closeCon(connection);
		}
		return list;
	}

	public List<ClientBean> select(String type, String info) {
		List<ClientBean> list = new ArrayList<ClientBean>();
		Connection connection = null;
		connection = new MySqlInfo().getConnection();
		String sql = "";

		if (type.equals("customer_id")) {
			sql = "customer_id";
		}
		if (type.equals("first_name")) {
			sql = "first_name";
		}
		if (type.equals("last_name")) {
			sql = "last_name";
		}
		if (type.equals("middle_inital")) {
			sql = "middle_inital";
		}
		if (type.equals("street")) {
			sql = "street";
		}
		if (type.equals("city")) {
			sql = "city";
		}
		if (type.equals("state")) {
			sql = "state";
		}
		if (type.equals("zip")) {
			sql = "zip";
		}
		if (type.equals("phone")) {
			sql = "phone";
		}
		if (type.equals("email")) {
			sql = "email";
		}

		String b;
		b="%"+info.replaceAll("!","!!").replaceAll("%","!%")+"%";
		try {
			PreparedStatement statement = connection
					.prepareStatement("select customer_id,first_name,last_name,middle_inital,street,city,state,zip,phone,email from customers where "
							+ sql + " like ? escape '!'");
			
			statement.setString(1, b);

			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				ClientBean c = new ClientBean();
				c.setCustomer_id(rs.getString("customer_id"));
				c.setFirst_name(rs.getString("first_name"));
				c.setLast_name(rs.getString("last_name"));
				c.setMiddle_inital(rs.getString("middle_inital"));
				c.setStreet(rs.getString("street"));
				c.setCity(rs.getString("city"));
				c.setState(rs.getString("state"));
				c.setZip(rs.getString("zip"));
				c.setPhone(rs.getString("phone"));
				c.setEmail(rs.getString("email"));
				list.add(c);
			}
			statement.close();
			rs.close();
			connection.commit();
		} catch (SQLException e) {
			new MySqlInfo().rollback(connection);
			throw new RuntimeException(e);
		} finally {
			new MySqlInfo().closeCon(connection);
		}
		return list;
	}


	public List<ClientBean> sel(String string, String customer_id) {
		List<ClientBean> list = new ArrayList<ClientBean>();
		Connection connection = null;
		connection = new MySqlInfo().getConnection();
		String sql = "customer_id";
		try {
			PreparedStatement statement = connection
					.prepareStatement("select customer_id,first_name,last_name,middle_inital,street,city,state,zip,phone,email from customers where "
							+ sql + "= ?");
			String b;
			b=customer_id;
			statement.setString(1, b);

			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				ClientBean c = new ClientBean();
				c.setCustomer_id(rs.getString("customer_id"));
				c.setFirst_name(rs.getString("first_name"));
				c.setLast_name(rs.getString("last_name"));
				c.setMiddle_inital(rs.getString("middle_inital"));
				c.setStreet(rs.getString("street"));
				c.setCity(rs.getString("city"));
				c.setState(rs.getString("state"));
				c.setZip(rs.getString("zip"));
				c.setPhone(rs.getString("phone"));
				c.setEmail(rs.getString("email"));
				list.add(c);
			}
			statement.close();
			rs.close();
			connection.commit();
		} catch (SQLException e) {
			new MySqlInfo().rollback(connection);
			throw new RuntimeException(e);
		} finally {
			new MySqlInfo().closeCon(connection);
		}
		return list;
	}

}