(교육자료)JDBC 사용하기 (오라클 11g 사용)

012345678910111213141516171819

 

DB_EX01.java

package dbtest; import java.sql.*; public class DB_EX01 { public static void main(String[] args){ Connection con = null; Statement stmt = null; String url = "jdbc:oracle:thin:@localhost:1521:xe"; String id = ""; //DB계정 String pass = ""; //DB비밀번호 try{ Class.forName("oracle.jdbc.driver.OracleDriver"); //jdbc 드라이버를 메모리에 로드~ con = DriverManager.getConnection(url, id, pass); //ODB와 연결 con 연결된 객체 System.out.println("DB접속 성공!!!"); stmt = con.createStatement(); StringBuffer sql = new StringBuffer(); sql.append("INSERT INTO DEPARTMENT "); sql.append("VALUES (203, '제어계측공학과', 200, '7호관')"); System.out.println(sql.toString()); int result = stmt.executeUpdate(sql.toString()); //변경 된 행의 갯수 반환 System.out.println(result + "개의 행이 추가되었습니다."); } catch(ClassNotFoundException e){ e.printStackTrace(); } catch(SQLException e){ e.printStackTrace(); } finally{ try{ if(con != null) con.close(); if(stmt != null) stmt.close(); } catch(SQLException e){ e.printStackTrace(); } } } }

DB_EX02.java

package dbtest; import java.sql.*; public class DB_EX02 { public static void main(String[] args) { StringBuffer sql = new StringBuffer(); sql.append("update department "); sql.append("set dname = '전자계산학과' "); sql.append("where deptno = 203 "); Connection con = null; Statement stmt = null; try { Class.forName("oracle.jdbc.driver.OracleDriver"); con = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:xe", "", ""); stmt = con.createStatement(); int i = stmt.executeUpdate(sql.toString()); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { try { if (con != null) con.close(); if (stmt != null) stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } } }

DB_EX03.java

package dbtest; import java.sql.*; public class DB_EX03 { public static void main(String[] args){ StringBuffer sql = new StringBuffer(); sql.append("delete department "); sql.append("where dname = '전자계산학과' "); Connection con = null; Statement stmt = null; try{ Class.forName("oracle.jdbc.driver.OracleDriver"); con = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:xe", "", ""); stmt = con.createStatement(); //update문 int n = stmt.executeUpdate(sql.toString()); //sql문을 실행하고 갱신된 갯수를 i에 저장 System.out.println(n + "개의 행이 삭제되었습니다."); } catch(ClassNotFoundException e){ e.printStackTrace(); } catch(SQLException e){ e.printStackTrace(); } finally{ try{ if(con != null) con.close(); if(stmt != null) stmt.close(); } catch(SQLException e){ e.printStackTrace(); } } } }

DB_EX04.java

package dbtest; import java.sql.*; public class DB_EX04 { public static void main(String[] args){ StringBuffer sql = new StringBuffer(); sql.append("select deptno, dname, college, loc "); sql.append("from department "); Connection con = null; Statement stmt = null; ResultSet rs = null; try{ Class.forName("oracle.jdbc.driver.OracleDriver"); con = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:xe", "", ""); stmt = con.createStatement(); rs = stmt.executeQuery(sql.toString()); //쿼리가 select일 때는 excuteUpdate()를 사용한다. while(rs.next()){ int i = rs.getInt(1); String s1 = rs.getString(2); int j = rs.getInt("College"); String s2 = rs.getString("loc"); System.out.println(i+"\t"+s1+"\t"+j+"\t"+s2); } } catch(ClassNotFoundException e){ e.printStackTrace(); } catch(SQLException e){ e.printStackTrace(); } finally{ try{ if(con != null) con.close(); if(stmt != null) stmt.close(); if(rs != null) rs.close(); } catch(SQLException e){ e.printStackTrace(); } } } }

DB_EX05.java

package dbtest; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; public class DB_EX05 { public static void main(String[] args){ StringBuffer sql = new StringBuffer(); sql.append("insert into professor "); sql.append("values (?, ?, ?, ?, ?, sysdate, ?, ?)"); Connection con = null; PreparedStatement pstmt = null; try{ con = ConnectionUtil.getConnection(); pstmt = con.prepareStatement(sql.toString()); pstmt.setInt(1, 9920); pstmt.setString(2, "홍길동"); pstmt.setString(3, "gildong"); pstmt.setString(4, "전임교수"); pstmt.setInt(5, 450); pstmt.setInt(6, 40); pstmt.setInt(7, 203); int n = pstmt.executeUpdate(); System.out.println(n + "개의 행이 추가되었습니다."); } catch(SQLException e){ e.printStackTrace(); } finally{ try{ if(con != null) con.close(); if(pstmt != null) pstmt.close(); }catch(SQLException e){ } } } }

 

[DB_EX05.java PreparedStatement 객체 사용 설명]

 쿼리 문자열에 물음표(?) 부분은 바인딩 문자라고 함

 preparedStatement 객체에 쿼리문자열을 미리 지정하는 것을 볼 수 있다.

 그 후 쿼리에 사용되는 자료형에 맞춰 setInt 또는 setString등을 통해

 ?에 해당되는 부분에 사용할 값을 지정한다.

 ?는 1번 부터 순서번호가 지정되어 있음.

 PreparedStatement객체는 설정된 쿼리문자열을 내부적으로 재사용한다. 

위와 같은 쿼리문자열이 전송되게 된다.

동일한 쿼리를 재사용한다면 바인딩 변수 부분의 값만 변경하면되고 쿼리문자열은 재사용 된다.

 

DB_EX06.java

package dbtest; import java.sql.*; public class DB_EX06 { public static void main(String[] args){ StringBuffer sql = new StringBuffer(); sql.append("update professor "); sql.append("set sal = ? "); sql.append("where name = ? "); Connection con = null; PreparedStatement pstmt = null; try{ con = ConnectionUtil.getConnection(); pstmt = con.prepareStatement(sql.toString()); pstmt.setInt(1, 500); pstmt.setString(2, "홍길동"); int n = pstmt.executeUpdate(); System.out.println(n + "개의 행이 수정되었습니다."); } catch(SQLException e){ e.printStackTrace(); } finally{ try{ if(con != null) con.close(); if(pstmt != null) pstmt.close(); }catch(SQLException e){ e.printStackTrace(); } } } }

DB_EX07.java

package dbtest; import java.sql.*; public class DB_EX07 { public static void main(String[] args){ StringBuffer sql = new StringBuffer(); sql.append("select a.name, a.profno, a.position, b.dname "); sql.append("from professor a, department b "); sql.append("where a.deptno = b.deptno "); sql.append("and a.deptno = ? "); //System.out.println(sql.toString()); Connection con = null; PreparedStatement pstmt = null; ResultSet rs = null; try{ con = ConnectionUtil.getConnection(); pstmt = con.prepareStatement(sql.toString()); pstmt.setInt(1, 203); //System.out.println(sql.toString()); rs = pstmt.executeQuery(); while(rs.next()){ System.out.print(rs.getString("name") + "\t"); System.out.print(rs.getInt("profno") + "\t"); System.out.print(rs.getString("dname") + "\t"); System.out.println(rs.getString("position") + "\t"); } } catch(SQLException e){ e.printStackTrace(); } finally{ try{ if(con != null) con.close(); if(pstmt != null) pstmt.close(); } catch(SQLException e){ e.printStackTrace(); } } } }

DB_EX08.java

package dbtest; import java.sql.*; public class DB_EX08 { public static void main(String[] args){ StringBuffer sql1 = new StringBuffer(); sql1.append("insert into department "); sql1.append("values (?, ?, ?, ?) "); StringBuffer sql2 = new StringBuffer(); sql2.append("update department "); sql2.append("set dname = ?, loc = ? "); sql2.append("where deptno = ?"); Connection con = null; PreparedStatement pstmt = null; try{ //트랜잭션 1번 작업 시작 con = ConnectionUtil.getConnection(); con.setAutoCommit(false); pstmt = con.prepareStatement(sql1.toString()); pstmt.setInt(1, 255); pstmt.setString(2, "핵물리학과"); pstmt.setInt(3, 200); pstmt.setString(4, "9호관"); pstmt.executeUpdate(); //1번 작업 종료 -> 2번 작업 시작 pstmt = con.prepareStatement(sql2.toString()); pstmt.setString(1, "생명공학과"); pstmt.setString(2, "8호관"); pstmt.setInt(3, 255); pstmt.executeUpdate(); //2번 작업 종료, 정상적으로 실행된 경우 DB에 반영한다. con.commit(); System.out.println("DB 커밋 완료!"); } catch(SQLException e){ try{ con.rollback(); System.out.println("DB 작업 실패!"); } catch(SQLException e1){ e1.printStackTrace(); } } finally{ try{ if(con != null) con.close(); if(pstmt != null) pstmt.close(); } catch(SQLException e){ e.printStackTrace(); } } } }

DB_EX09.java

package dbtest; import java.sql.*; import java.util.*; public class DB_EX09 { public static void main(String[] args) throws Exception{ Scanner in = new Scanner(System.in); //키보드 입력 스트림 생성 System.out.print("SQL 입력 : "); String sql = in.nextLine(); Connection con = ConnectionUtil.getConnection(); PreparedStatement pstmt = con.prepareStatement(sql); ResultSet rs = pstmt.executeQuery(); ResultSetMetaData rsmd = rs.getMetaData(); System.out.println("컬럼 갯수 : " + rsmd.getColumnCount()); for(int i = 1; i < rsmd.getColumnCount(); i++){ System.out.print(rsmd.getColumnName(i) + "\t"); } System.out.println(); for(int i = 1; i < rsmd.getColumnCount(); i++){ System.out.print(rsmd.getColumnType(i) + "\t"); } System.out.println(); System.out.println(); int col = rsmd.getColumnCount(); while(rs.next()){ for(int num = 1; num <= col; num++){ int type = rsmd.getColumnType(num); switch(type){ case Types.NUMERIC : System.out.println(rs.getInt(num) + "\t"); break; case Types.VARCHAR : System.out.println(rs.getString(num)+ "\t"); break; case Types.DATE : System.out.println(rs.getDate(num) + "\t"); break; } } System.out.println(); } rs.close(); pstmt.close(); con.close(); } }

DB_EX10.java

package dbtest; import java.io.FileInputStream; import java.io.IOException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.Properties; public class DB_EX10 { public static void main(String[] args) throws IOException, SQLException{ Properties pro = new Properties(); pro.load(new FileInputStream("src/properties/department.properties")); Connection con = null; PreparedStatement pstmt = null; try{ con = ConnectionUtil.getConnection(); pstmt = con.prepareStatement(pro.getProperty("department_insert")); pstmt.setInt(1, 150); pstmt.setString(2, "산업공학과"); pstmt.setInt(3, 200); pstmt.setString(4, "5호관"); int n = pstmt.executeUpdate(); System.out.println(n + "개의 행이 추가되었습니다."); } catch(SQLException e){ e.printStackTrace(); } finally{ try{ if(con != null) con.close(); if(pstmt != null) pstmt.close(); } catch(SQLException e){ e.printStackTrace(); } } } }