DAO 리펙토리 전

package net.slipp.user;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class UserDAO {

	public Connection getConnection() {
		String url = "jdbc:mysql://localhost:3305/slipp_dev?useUnicode=true&characterEncoding=utf8";
		String id = "slipp";
		String pw = "1111"; 
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			return DriverManager.getConnection(url,id,pw);
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return null;
		}
	}

	public void addUser(User user) throws SQLException {
		String sql = "insert into USERS values(?,?,?,?)";
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(sql); 
			pstmt.setString(1, user.getUserId());
			pstmt.setString(2, user.getPassword());
			pstmt.setString(3, user.getName());
			pstmt.setString(4, user.getEmail());
			
			pstmt.executeUpdate();
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
			
			if (conn != null) {
				conn.close();
			}
		}
	}

	public User findByUserId(String userId) throws SQLException {
		String sql = "select * from USERS where userId = ?";
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(sql); 
			pstmt.setString(1, userId);
			
			rs = pstmt.executeQuery();
			
			if (!rs.next()) {
				return null;
			}
			
			return new User(
					rs.getString("userId"), 
					rs.getString("password"),
					rs.getString("name"),
					rs.getString("email"));
		} finally {
			if (rs != null) {
				rs.close();
			}
			
			if (pstmt != null) {
				pstmt.close();
			}
			
			if (conn != null) {
				conn.close();
			}
		}
		
	}

	public void removeUser(String userId) throws SQLException {
		String sql = "delete from USERS where userId = ?";
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(sql); 
			pstmt.setString(1, userId);
			
			pstmt.executeUpdate();
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
			
			if (conn != null) {
				conn.close();
			}
		}
	}

	public void updateUser(User user) throws SQLException {
		String sql = "update USERS set password = ?, name = ?, email = ? where userId = ?";
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(sql); 
			pstmt.setString(1, user.getPassword());
			pstmt.setString(2, user.getName());
			pstmt.setString(3, user.getEmail());
			pstmt.setString(4, user.getUserId());
			
			pstmt.executeUpdate();
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
			
			if (conn != null) {
				conn.close();
			}
		}
	}
	
	
	
	

}

 

리펙토리 후

abstract class JdbcTemplate

package net.slipp.support;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import net.slipp.user.User;

public abstract class JdbcTemplate {


	public static Connection getConnection() {
		String url = "jdbc:mysql://localhost:3305/slipp_dev?useUnicode=true&characterEncoding=utf8";
		String id = "slipp";
		String pw = "1111"; 
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			return DriverManager.getConnection(url,id,pw);
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return null;
		}
	}
	
	public void prepareStatementExecute(String sql)throws SQLException {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(sql); 
			
			prepareSet(pstmt);
			
			pstmt.executeUpdate();
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
			
			if (conn != null) {
				conn.close();
			}
		}	
		
	}
		
	
	public abstract void prepareSet(PreparedStatement pstmt) throws SQLException;
	
	
}

 

---

class UserDAO

package net.slipp.user;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import net.slipp.support.JdbcTemplate;

public class UserDAO {


	public void addUser(User user) throws SQLException {
		String sql = "insert into USERS values(?,?,?,?)";	
		JdbcTemplate jdbcTemplate=new JdbcTemplate() {		
			@Override
			public void prepareSet(PreparedStatement pstmt) throws SQLException {
				pstmt.setString(1, user.getUserId());			
				pstmt.setString(2, user.getPassword());
				pstmt.setString(3, user.getName());
				pstmt.setString(4, user.getEmail());
			}
		};
		jdbcTemplate.prepareStatementExecute(sql);	
		
	}

	
	
	public User findByUserId(String userId) throws SQLException {
		String sql = "select * from USERS where userId = ?";
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = JdbcTemplate.getConnection();
			pstmt = conn.prepareStatement(sql); 
			pstmt.setString(1, userId);
			
			rs = pstmt.executeQuery();
			
			if (!rs.next()) {
				return null;
			}
			
			return new User(
					rs.getString("userId"), 
					rs.getString("password"),
					rs.getString("name"),
					rs.getString("email"));
		} finally {
			if (rs != null) {
				rs.close();
			}
			
			if (pstmt != null) {
				pstmt.close();
			}
			
			if (conn != null) {
				conn.close();
			}
		}
		
	}

	public void removeUser(String userId) throws SQLException {
		String sql = "delete from USERS where userId = ?";
		
		JdbcTemplate jdbcTemplate=new JdbcTemplate() {		
			@Override
			public void prepareSet(PreparedStatement pstmt) throws SQLException {
				pstmt.setString(1, userId);		
			}
		};
		jdbcTemplate.prepareStatementExecute(sql);
	}

	
	public void updateUser(User user) throws SQLException {
		String sql = "update USERS set password = ?, name = ?, email = ? where userId = ?";
		JdbcTemplate jdbcTemplate=new JdbcTemplate() {		
			@Override
			public void prepareSet(PreparedStatement pstmt) throws SQLException {
				pstmt.setString(1, user.getPassword());
				pstmt.setString(2, user.getName());
				pstmt.setString(3, user.getEmail());
				pstmt.setString(4, user.getUserId());			
			}
		};
		jdbcTemplate.prepareStatementExecute(sql);
	}
	


}