DAO (Data Access Objects)

DAO와 DatabaseUtil 이 만들어지게 된 논리적 흐름.

1. 프로그램 실행시 DB에 연결을 유지시켜주는 것보다 DB에 연결을 시도할 때마다 연결하고 연결을close 해주는 것이 좋은데

이렇게 연결을 자꾸 연결했다, 닫았다 를 반복한다면, 퍼포먼스가 떨어진다.

그렇다면, 어떻게 구현을 할까??? 연결을 반복하면 안되지만, 연결을 유지해서도 안된다.

2. 연결을 만들어 놓자!!!!! 그러면 만들어진 연결을 가져다 쓰면 될것 아니겠음??

그 연결을 관리하는 클래스가 필요했다. BasicDataSource!!

DatabaseUtil Class의 탄생

3. 그런데 이렇게 만들어진 DAO와 Util이 여러개가 있다면? 

- 객체가 생성될 때마다 제한을 두는 행동들이 의미가 없어진다.

때문에 싱글톤 패턴이 필요해진다.

Customer에 대한 DAO 클래스

public class CustomerDAO {

	private Connection conn;
	private PreparedStatement pStmt;
	private ResultSet rSet;
	private DatabaseUtil dbUtil;
	private static CustomerDAO instance = new CustomerDAO();

	private CustomerDAO() {
		dbUtil = DatabaseUtil.getInstance();
	}

	public static CustomerDAO getInstance() {
		return instance;
	}

	public int insertCustomer(Customer cust) throws CustomerNotFoundException,SQLIntegrityConstraintViolationException, SQLException {
		int count = 0;
		String sql = "insert into customer values (?,?,?,?,?,?)";
		if (cust.equals(null) || cust.getName().equals(null)
				|| cust.getPassword().equals(null) || cust.getId().equals(null)
				|| cust.getEmail().equals(null)) {
			throw new CustomerNotFoundException("찾을수 없어요");
		}

		try {
			conn = dbUtil.getConnection();
//			conn.setAutoCommit(false);
			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1, cust.getId());
			pStmt.setString(2, cust.getPassword());
			pStmt.setString(3, cust.getName());
			pStmt.setString(4, cust.getPhoneNumber());
			pStmt.setString(5, cust.getEmail());
			pStmt.setInt(6, cust.getMileage());
			count = pStmt.executeUpdate();
			if (count == 0) {
				System.out.println("실패");
			}
//			conn.setAutoCommit(true);
//			conn.commit();

		} catch(SQLException e){
			e.printStackTrace();
			System.out.println("에러발생");
		}
		finally {
			dbUtil.close(conn, pStmt);
		}
		return count;
	}

	public ArrayList selectCustomer() throws SQLException {
		ArrayList list = new ArrayList();
		String sql = "select customer_id,customer_pwd,customer_name,customer_pnumber,customer_email,customer_mileage from customer";

		conn = dbUtil.getConnection();
		pStmt = conn.prepareStatement(sql);
		rSet = pStmt.executeQuery();
		while (rSet.next()) {
			String id = rSet.getString(1);
			String pw = rSet.getString(2);
			String name = rSet.getString(3);
			String tel = rSet.getString(4);
			String email = rSet.getString(5);
			int mileage = rSet.getInt(6);
			list.add(new Customer(id, pw, name, tel, email, mileage));
		}
		return list;
	}

	public Customer selectCustomerById(String cust_id) throws SQLException {
		String sql = "select customer_id,customer_pwd,customer_name,customer_pnumber,customer_email,customer_mileage "
				+ "from customer " + "where customer_id = ?";
		conn = dbUtil.getConnection();
		pStmt = conn.prepareStatement(sql);
		pStmt.setString(1, cust_id);
		rSet = pStmt.executeQuery();
		Customer cust = null;
		if (rSet.next()) {
			String id = rSet.getString(1);
			String pw = rSet.getString(2);
			String name = rSet.getString(3);
			String tel = rSet.getString(4);
			String email = rSet.getString(5);
			int mileage = rSet.getInt(6);
			cust = new Customer(id, pw, name, tel, email, mileage);
		} else {
			System.out.println(cust_id + "를 찾을 수 없습니다.");
		}
		return cust;
	}

	public ArrayList selectCustomerByName(String cust_name)
			throws SQLException {
		ArrayList list = new ArrayList();
		String sql = "select customer_id,customer_pwd,customer_name,customer_pnumber,customer_email,customer_mileage "
				+ "from customer " + "where customer_name = ?";
		conn = dbUtil.getConnection();
		pStmt = conn.prepareStatement(sql);
		pStmt.setString(1, cust_name);
		rSet = pStmt.executeQuery();
		Customer cust = null;
		while (rSet.next()) {
			String id = rSet.getString(1);
			String pw = rSet.getString(2);
			String name = rSet.getString(3);
			String tel = rSet.getString(4);
			String email = rSet.getString(5);
			int mileage = rSet.getInt(6);
			list.add(new Customer(id, pw, name, tel, email, mileage));
		}
		return list;
	}

	public ArrayList selectCustomerByMileage(int min, int max)
			throws SQLException {
		ArrayList list = new ArrayList();
		String sql = "select customer_id,customer_pwd,customer_name,customer_pnumber,customer_email,customer_mileage "
				+ "from customer " + "where customer_mileage between ? and ?";
		conn = dbUtil.getConnection();
		pStmt = conn.prepareStatement(sql);
		pStmt.setInt(1, min);
		pStmt.setInt(2, max);
		rSet = pStmt.executeQuery();
		Customer cust = null;
		while (rSet.next()) {
			String id = rSet.getString(1);
			String pw = rSet.getString(2);
			String name = rSet.getString(3);
			String tel = rSet.getString(4);
			String email = rSet.getString(5);
			int mileage = rSet.getInt(6);
			list.add(new Customer(id, pw, name, tel, email, mileage));
		}
		return list;
	}

	public int updateCustomer(Customer cust) throws CustomerNotFoundException,
			SQLException {
		int count = 0;
		String sql = "update customer"
				+ " set customer_pwd = ?,customer_name = ?,"
				+ "customer_pnumber = ?,customer_email = ?,customer_mileage =?"
				+ "where customer_id = ?";
		if (cust == null || cust.getName() == null
				|| cust.getPassword() == null || cust.getId() == null
				|| cust.getEmail() == null) {
			throw new CustomerNotFoundException("찾을수 없어요");
		}

		try {
			conn.setAutoCommit(false);
			conn = dbUtil.getConnection();
			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1, cust.getPassword());
			pStmt.setString(2, cust.getName());
			pStmt.setString(3, cust.getPhoneNumber());
			pStmt.setString(4, cust.getEmail());
			pStmt.setInt(5, cust.getMileage());
			pStmt.setString(6, cust.getId());
			count = pStmt.executeUpdate();

			conn.setAutoCommit(true);
			conn.commit();
		} catch (SQLException e) {
			System.out.println("에러발생");
			conn.rollback();
		} finally {
			dbUtil.close(conn, pStmt);
		}
		return count;
	}

	public int deleteCustomerById(String cust_id) throws SQLException {
		int count = 0;
		String sql = "delete " + "from customer " + "where customer_id = ?";

		try {
			conn = dbUtil.getConnection();
			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1, cust_id);
			count = pStmt.executeUpdate();
			if (count == 0) {
				System.out.println(cust_id + "가 없습니다.");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			// System.out.println("에러발생");
		} finally {
			dbUtil.close(conn, pStmt);
		}

		return count;

	}

}

Database를 접근하는 유일한 길 DatabaseUtil 클래스

public class DatabaseUtil {
	private static DatabaseUtil instance = new DatabaseUtil();
	private BasicDataSource source ;
	private DatabaseUtil(){
		source = new BasicDataSource();
		source.setDriverClassName("oracle.jdbc.driver.OracleDriver");
		source.setUrl("jdbc:oracle:thin:@127.0.0.1:1521:XE");
		source.setUsername("scott");
		source.setPassword("tiger");
		
		source.setInitialSize(2);
		source.setMaxTotal(10);
	}
	public static DatabaseUtil getInstance(){
		return instance ;
	}
	
	public Connection getConnection() throws SQLException{
		
		return source.getConnection();
	}
	public void close(Connection conn,Statement stmt) throws SQLException{
		if(conn!=null) conn.close();
		if(stmt!=null)stmt.close();
	}
	
	public void close(Connection conn,Statement stmt,ResultSet rSet) throws SQLException{
		if(conn!=null) conn.close();
		if(stmt!=null)stmt.close();
		if(rSet!=null) rSet.close();
	}
}