JDBC2

public class DeptDTO {
	private int deptno;
	private String dname;
	private String loc;

public class DBUtil {
	//byte code가 로딩시 단 한번 실행 보장, 이름도 없어서 호출 불가
	//모든 웹상의 user들이 공유하는 자원, 단 한번만 서버 실행시에 실행되면 됨
	private static Properties pro = new Properties();
	private static Properties sqlAll = new Properties();
	
	static {
		try {
			sqlAll.load(new FileInputStream("allsql.properties"));
			pro.load(new FileInputStream("dbinfo.properties"));
			
			Class.forName(pro.getProperty("driver"));
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("해당 driver가 없습니다.");
		}
	}
	
	//요청시마다 새로운 connection 객체 생성해서 반환하는 메소드
	//URL, ID, PW
	public static Connection getConnection() throws SQLException {
		return DriverManager.getConnection(
				pro.getProperty("url"), pro.getProperty("id"), pro.getProperty("pw"));
	}
	
	public static Properties getSqlAll() {
		return sqlAll;
	}

public static ArrayList<DeptDTO> deptAll() throws SQLException{
	
	Connection con = null;
	Statement stmt = null;
	ResultSet rset = null;
	ArrayList<DeptDTO> datas = null;
	
	try {
		con = DBUtil.getConnection();
		stmt = con.createStatement();
		rset = stmt.executeQuery(DBUtil.getSqlAll().getProperty("dept.All"));
			
		datas = new ArrayList<>();
		while (rset.next()) {
			datas.add(new DeptDTO(rset.getInt("deptno"),
														rset.getString("dname"),
														rset.getString("loc")));
		}
	} finally {
		DBUtil.close(con, stmt, rset);
	}
	return datas;
}

public static DeptDTO getDept(int deptno) throws SQLException{
	Connection con = null;
	PreparedStatement pstmt = null;
	ResultSet rset = null;
	
	try {
		con = DBUtil.getConnection();
		pstmt = con.prepareStatement(DBUtil.getSqlAll().getProperty("dept.getDept"));
		
		pstmt.setInt(1, deptno);
		
		rset = pstmt.executeQuery();
		if(rset.next()) {
			return new DeptDTO(rset.getInt("deptno"), rset.getString("dname"),rset.getString("loc"));
		}
	} finally {
		DBUtil.close(con, pstmt, rset);
	}
	return null;
}

static boolean insert(DeptDTO newDept) throws SQLException{
	
	Connection con = null;
	PreparedStatement pstmt = null;

	try {
		con = DBUtil.getConnection();
		pstmt = con.prepareStatement(DBUtil.getSqlAll().getProperty("dept.insert"));

		pstmt.setInt(1, newDept.getDeptno());
		pstmt.setString(2, newDept.getDname());
		pstmt.setString(3, newDept.getLoc());

		if (pstmt.executeUpdate() != 0) {
			return true;
		}
	} finally {
		DBUtil.close(con, pstmt);
	}
	return false;
}

public static boolean update(int deptno, String newloc) throws SQLException{
	
	Connection con = null;
	PreparedStatement pstmt = null;
	
	try {
		con = DBUtil.getConnection();
		pstmt = con.prepareStatement(DBUtil.getSqlAll().getProperty("dept.update"));

		pstmt.setString(1, newloc);
		pstmt.setInt(2, deptno);

		int result = pstmt.executeUpdate();
		if (result != 0) {
			return true;
		}
	} finally {
		DBUtil.close(con, pstmt);
	}
	return false;
}

public static boolean delete(int deptno) throws SQLException{
		
		Connection con = null;
		PreparedStatement pstmt = null;

		try {
			con = DBUtil.getConnection();
			pstmt = con.prepareStatement(DBUtil.getSqlAll().getProperty("dept.delete"));

			pstmt.setInt(1, deptno);
			if (pstmt.executeUpdate() != 0) {
				return true;
			}
		} finally {
			DBUtil.close(con, pstmt);
		}
		return false;
}