Day062

Java & Database 연동

• Eclipse > 프로젝트 우클릭 > Properties > Java Build Path > Libraries > Classpath > Add External JARs > D:\oracle\WINDOWS.X64_193000_db_home\jdbc\lib\ojdbc8.jar 파일 추가

DBConnectionManager

DB 연결을 도와주는 기능을 가진 클래스

public class DBConnectionManager {
	
	public static Connection getConnection() {
		Connection conn = null;
		try {
			
			Class.forName("oracle.jdbc.driver.OracleDriver");

			String db_url = "jdbc:oracle:thin:@localhost:1521:orcl";
			String db_id = "scott";
			String db_pw = "tiger";

			conn = DriverManager.getConnection(db_url, db_id, db_pw);

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return conn;

	}
	
	public static void close(ResultSet rs, PreparedStatement psmt, Connection conn) {
		try {
			if (rs != null) {
				rs.close();
			}
			if (psmt != null) {
				psmt.close();
			}
			if (conn != null) {
				conn.close(); // 연결 해제
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

DAO(Data Access Object)

데이터베이스에 접근해서 CRUD를 수행하는 객체를 의미한다.

public class PersonDAO {

	public void selectEmpByEmpno(int empno) {

		Connection conn = null;
		PreparedStatement psmt = null;
		ResultSet rs = null;
		try {
			conn = DBConnectionManager.getConnection();

			// 쿼리문!
			// String sql = "select * from emp where empno = 7566";
			String sql = "select empno, ename empname, job, mgr, hiredate, sal, comm, deptno"
					+ " from emp where empno = ?";

			psmt = conn.prepareStatement(sql);
			psmt.setInt(1, empno);

			rs = psmt.executeQuery(); // 쿼리를 실행!!

			// ResultSet next() 메소드는 결과 데이터에 다음행 다음행 다음행 이동.
			// return true/false -> 값이 있다 없다!
			while (rs.next()) {
				System.out.println("------------------");
				System.out.println(rs.getInt("empno"));
				System.out.println(rs.getString("empname"));
				System.out.println(rs.getString("job"));
				System.out.println(rs.getInt("mgr"));
				System.out.println(rs.getDate("hiredate"));
				System.out.println(rs.getInt("sal"));
				System.out.println(rs.getInt("comm"));
				System.out.println(rs.getInt("deptno"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBConnectionManager.close(rs, psmt, conn);
		}

	}
}