JDBC

ojdbc6.jar 파일 lib폴더에 적재

1. DriverManager : 드라이버를 Java에 로딩

Class.forName("드라이버");

 

2. Connection : Java와 DB를 연결 하기위한 객체

Connection con = Drivermanager.getConnection(dburl, id, pwd);

 

3. Statement : 쿼리 전송 객체 (명령 : SQL사용)

Statement stmt = con.createStatement();

stmt.execute(“query”);         //any SQL
stmt.executeQuery(“query”);    //SELECT	[return : select문 수행 결과(resultSet)]
stmt.executeUpdate(“query”);   //INSERT, UPDATE, DELETE [return : 적용된 행갯수(int)]

 

4. ResultSet : 결과를 담을 객체 (Select문 일때만 사용)

ResultSet rs = stmt.executeQuery("SQL Select문")

 

- Select문

Class.forName("oracle.jdbc.driver.OracleDriver");	// 오라클 드라이버
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;

try {  
    String url = "jdbc:oracle:thin:@localhost:1521:xe";	// 오라클xe
    String user = "scott";
    String password = "1111";
    
    conn = DriverManager.getConnection(url, user, password);
    stmt = conn.createStatement();
    String sql = "SELECT * FROM student"	// studno, name, sex, birthday, tel
    rs = stmt.executeQuery(sql);
    
    if (rs != null) {
	    while (rs.next()) {
       		 out.print(rs.getInt(1));		// rs.getInt("studno")
           	 out.print(rs.getString(2));		// rs.getString("name")
           	 out.print(rs.getString(3));		// rs.getString("sex")
           	 out.print(rs.getString(4));		// rs.getString("birthday")
           	 out.print(rs.getString(5));		// rs.getString("tel")
        }
    }

} catch (Exception e) {
    e.printStackTrace();
} finally {
    try {
         if (rs != null) rs.close();
         if (stmt != null) stmt.close();
         if (conn != null) conn.close();
    } catch (Exception e2) {
        e2.printStackTrace();
    }
}

- UPDATE문

Class.forName("oracle.jdbc.driver.OracleDriver");	// 오라클 드라이버
Connection conn = null;
Statement stmt = null;

try {  
    String url = "jdbc:oracle:thin:@localhost:1521:xe";	// 오라클xe
    String user = "scott";
    String password = "1111";
    
    conn = DriverManager.getConnection(url, user, password);
    stmt = conn.createStatement();
    String sql = "UPDATE book SET book_loc ...."
    stmt.executeUpdate(sql);

} catch (Exception e) {
    e.printStackTrace();
} finally {
    try {
         if (rs != null) rs.close();
         if (stmt != null) stmt.close();
         if (conn != null) conn.close();
    } catch (Exception e2) {
        e2.printStackTrace();
    }
}

→ 열은 순서의 반대로 닫아줘야함

 

★ 보안상의 문제로 Statement 보단 PreparedStatement를 사용함

 

★ PreparedStatement : 쿼리문을 먼저 준비 한 다음에 객체에 넣을 수 있도록 해줌, ?를 사용하여 자리를 배치함

 PreparedStatement pstmt = conn.prepareStatement("sql문");

 ResultSet rs = stmt.executeQuery();

Class.forName("oracle.jdbc.driver.OracleDriver");	// 오라클 드라이버
Connection conn = null;
Statement stmt = null;

try {
    String url = "jdbc:oracle:thin:@localhost:1521:xe";	// 오라클xe
    String user = "scott";
    String password = "1111";
    
    conn = DriverManager.getConnection(url, user, password);
    String sql = "UPDATE book SET book_loc = ? WHERE book_name = ?"
    pstmt = conn.prepareStatement(sql);
    pstmt.setString(1, "01-000-123");	// 첫번째 ?
    pstmt.setString(2, "roma");	// 두번째 ?
    pstmt.executeUpdate();

} catch (Exception e) {
    e.printStackTrace();
} finally {
    try {
         if (rs != null) rs.close();
         if (stmt != null) stmt.close();
         if (conn != null) conn.close();
    } catch (Exception e2) {
        e2.printStackTrace();
    }
}