[펌]JDBC정리내용

JDBC

• Java Database Programming책 정리
  
• 책 평가는 - 그냥 그래...
  
• 이것은 JDBC를 설명하기 위한 것이 아니라, 필요할 때 찾아봐서 기억을 상기 시키려는 목적으로 작성되었으므로, 이것만 봐서는 JDBC를 이해할 수 없음. 책을 읽거나 다른 JDBC관련 기초 설명을 봐야 이해할 수 있음.
  
  
• 현재 JDBC 3.0을 지원하는 드라이버는 거의 없다. 그러므로 JDBC 3.0으로 표기된 것들은 작동하지 않는다.
  
• JDBC 관련 문서 :http://java.sun.com/j2se/1.4.2/docs/guide/jdbc/
  
  

DriverManager를 이용한 Connection 획득

Class.forName("myDriver.className");Connection conn = DriverManager.getConnection( "jdbc:myprotocol:mydatabase", "id", "password");

DataSource를 이용한 Connection 획득. JDBC 3.0

Context ctx = new InitialContext();// 이 객체가 JNDI에 미리 등록되어 있어야만 한다.DataSource ds = (DataSource)ctx.lookup("jdbc/bookSampleDB");Connection con = ds.getConnection("id", "password");

ConnectionPoolDataSource를 이용한 커넥션풀에서 Connection 획득. JDBC 3.0

Context ctx = new InitialContext();DataSource ds = (DataSource)ctx.lookup("jdbc/poolconnectionbookds");Connection con = ds.getConnection("id", "password");

Statement : SQL명령문을 실행한다.

• Statement, PreparedStatement, CallableStatement 세 종류
  
  

Statement 얻기

Connection conn = ds.getConnection("id", "password");Statement stmt1 = conn.createStatement();Statement stmt2 = conn.createStatement();    ....stmt1.close();stmt2.close();

• Statement는 DBMS에 설정된 OPEN CURSORS개수만큼 생성 가능.
  
• OPEN CURSORS란 하나의 커넥션(세션)에서 수행할 수 있는 Statement 개수
  
• Statement가 OPEN CURSORS보다 많으면 "Open Cursors exceed"라는 예외 발생.
  
• 그러므로 Statement를 다 사용한 뒤에 필히 닫아주어야 한다.
  
  

Statement for Select : stmt.executeQuery(query)

Statement stmt = conn.createStatement(  ResultSet.TYPE_SCROLL_INSENSITIVE,  ResultSet.CUSOR_READ_ONLY);String query = "SELECT ...";ResultSet rs = stmt.executeQuery(query); ...stmt.close();

Statement for DML(UPDATE, DELETE, INSERT) : stmt.executeUpdate(query)

Statement stmt = conn.createStatement();int countRow = stmt.executeUpdate("UPDATE book SET ...");// countRow는 DML이 적용된 컬럼 수를 의미한다.stmt.close();

Statement for All(SELECT and DML) : stmt.execute(query)

Statement stmt = conn.createStatement();boolean returnVal = stmt.execute("이런 저런 문장...");// returnVal == true : SELECT문이 실행됐음.// returnVal == false : DML(UPDATE, DELETE, INSERT)if (returnVal) == true) { ResultSet rs = stmt.getResultSet(); ...} else { int updateCount = stmt.getUpdateCount(); ...}stmt.close();

PreparedStatement

DBMS는 가장 최근에 수행된 SQL문장을 저장하고 있다가, 똑같은 SQL이 반복하여
들어올 경우에 별다른 수행계획없이 이전의 수행계획으로 SQL를 실행한다.
이때 속도가 빨라지게 된다.
PreparedStatement는 이러한 DBMS기능을 통하여 반복적인 SQL실행에서
사용되어 어플리케이션의 성능을 향상시킨다.

String sql = "SELECT a, b, c where a = ? and b <= ?";PreparedStatement pstmt = conn.prepareStatement(sql);pstmt.setString(1, "string");pstmt.setInt(2, 300);...ResultSet rs = pstmt.executeQuery();...pstmt.close();conn.close();

• ? 부분에 적합한 데이터형 Setter(setString(), setInt(),...)를 이용해 값을 끼워넣고 SQL을 실행한다.
  
  
• 특정 데이타에 Null 값을 세팅하고자 한다면,pstmt.setNull(3, java.sql.Type.VARCHAR);
  
  

Stored Procedure : DBMS에 미리 저장된 SQL명령 집합

DatabaseMetaData dmd = conn.getMetaData();if (dmd.supportsStoredProcedure()) { // 스토어드 프로시져 지원한다.} else { // 스토어드 프로시져 지원 안 한다.}

스토어드 프로시저는 성능을 향상시킬수 있고, 보안성이 높으나,
코딩 규칙이 제대로 규정되지 않고 문서화가 안돼 있으면 유지보수성이
떨어지게 된다.

CallableStatement : DBMS의 Stored Procedure호출에 사용된다

CallableStatement cstmt = conn.prepareCall("{CALL functionname(?,?,?)}");cstmt.setString(1, "아범노트북");cstmt.setInt(2, 1800000);cstmt.setNull(3, java.sql.Type.VARCHAR);cstmt.execute();

CallableStatement도 execute()의 리턴값이 true이면 ResultSet이 있는것이고,
false이면 DML을 실행하여 ResultSet이 없는 것이다.

스토어드 프로시져의 리턴값은 execute()하기전에 미리 리턴받을 값의 데이터형을
지정해 줘야 한다.

cstmt.registerOutParameter(1, Types.VARCHAR);cstmt.registerOutParameter(2, Types.INTEGER); ... execute() 후에 리턴값 얻기 ...String s = cstmt.getString(1);int i = cstmt.getInt(2);

등과 같은 방식으로 얻는다.

CallableStatement 또한 Statement와 PreparedStatement처럼 execute(),
executeQuery(), executeUpdate() 메소드를 호출할 수 있다.

ResultSet : SELECT의 결과를 저장하는 객체

• ResultSet Type : 결과값 집합의 검색 방향에 대한 기능 정의
  
  • TYPE_FORWARD_ONLY : 기본값. 아래 방향으로만 진행.
    
  • TYPE_SCROLL_INSENSITIVE : 위 아래로 진행. 다른 동작에 의해 값이 바뀌었을 경우 바뀐 값 인식 안함.
    
  • TYPE_SCROLL_SENSITIVE : 위 아래로 진행. 다른 동작에 의해 값이 바뀌었을 경우 바뀐 값 인식.
    
• 병행성 : ResultSet 내의 결과값 집합에 대한 Update와 같은 조작이 가능한가?
  
  • CONCUR_READ_ONLY : 기본값. 읽기만 가능.
    
  • CONCUR_UPDATABLE : 값 수정 가능.
    
• 유지성 : ResultSet 객체를 계속 유지할 것인가?
  
  • HOLD_CURSORS_OVER_COMMIT : 커밋후에도 ResultSet 객체를 유지한다.
    
  • CLOSE_CURSORS_AT_COMMIT : 커밋후에는 ResultSet 객체를 닫는다. 성능향상.
    
    

Connection.createStatement()메소드를 통해 지정한다.

DBMS가 지원하는 ResultSet 형태 정보 알아내기

DatabaseMetaData dbmd = con.getMetaData();

• String dbmd.getDatabaseProductName() : 데이타베이스 제품 이름
  
• boolean dbmd.supportsResultSetType() : ResultSet Type 지원 여부
  
• boolean dbmd.supportsResultSetConcurrency() : 병행성 지원 여부
  
• int dbmd.getResultSetHoldability() : JDBC 3.0. 유지성 지원 여부
  
  

Cursor

• ResultSet 데이타의 각 행을 가리킨다.
  
• 최초 커서의 위치는Before the first row이다.
  
• Cursor가 가리키는 위치를 이동하면서 각 행의 값을 조회하고 조작할 수 있다.
  
  

메소드	리턴값	비고
next()	boolean	가장 많이 쓰임. 아래 방향으로 한 행씩 이동. 더이상 행이 존재하지 않을 때(After the last row)는 false. TYPE_FORWARD_ONLY 일 경우에는 next() 메소드만 정상적으로 수행된다.
previous()	boolean	위 방향으로 이동. 더이상 위에 행이 없을 때(Before the first row)는 false.
last()	boolean	마지막 행. false이면 현재 ResultSet에 아무 값도 없다는 뜻.
first()	void	첫번째 행. false이면 현재 ResultSet에 아무 값도 없다는 뜻.
beforeFirst()	void	Before the first row로 이동
afterLast()	void	After the last row로 이동
relative(int i)	boolean	현재 위치에서 i 만큼 이동. i가 음수이면 위로, 양수이면 아래로.
absolute(int i)	boolean	양수일 경우에는 Before the first row를 기준으로, 음수일 경우에는 After the first row를 기준으로 하여 지정된 행으로 이동. 각 행은1혹은-1부터 시작한다.

값 얻기

• rs.getXXX(1번부터시작하는컬럼순서)혹은rs.getXXX("컬럼이름")을 이용해 값을 가져온다.
  
• 전형적인 ResultSet 사용 패턴
  
  

  while (rs.next()) {  String bookname = rs.getString(1);  int bookPrice = rs.getInt(2);  System.out.println("책 이름 : " + bookname + ", 값 : " + bookPrice);}

  
  

ResultSetMetaData

• 쿼리 결과로 얻은 ResultSet 객체로 부터 각 컬럼들에 대한 정보를 얻을 수 있다.
  
  

  ResultSetMetaData rsmd = rs.getMetaData();

• java.sql.ResultSetMetaDataAPI Doc 참조
  
  

ResultSet에서 행 삭제

• ResultSet이CONCUR_UPDATABLE속성일 경우에만 가능하다.
  
• 삭제할 행으로 이동한 뒤에
  
  

  rs.deleteRow();

• rs.rowDeleted(): 삭제된 행인가?
  
• DatabaseMetaData.othersDeletesAreVisible(int ResultSetType): 다른 ResultSet에서 삭제된 행을 인식할 수 있는가?
  
  

ResultSet에서 새로운 행 삽입

• ResultSet이CONCUR_UPDATABLE속성일 경우에만 가능하다.
  
• 행을 삽입할 가상 위치로 이동
  
  

  rs.moveToInsertRow();

• 삽입할 값 설정. 컬럼 번호 이용.
  
  

  rs.updateString(1, "한솔");rs.updateInt(2, 50000000);

• 삽입 완료하고 기존 위치로 이동
  
  

  rs.insertRow(); // 실제 삽입rs.moveToCurrentRow(); // 기존 위치로 이동

• rs.insertRow()를 실행하기 전에rs.moveToCurrentRow()를 실행하면 모든 설정값을 잃고 삽입은 실패하게 된다.
  
• 만약 NULL 일 수 없는 컬럼을 빼먹고 설정하지 않으면 Exception이 발생한다. NULL일 수 있는 값은 생략하면 NULL로 설정된다.
  
• Default값이 있을 경우에는 생략하면 기본값이 들어가게 된다.
  
  

ResultSet에서 컬럼 수정

• ResultSet이CONCUR_UPDATABLE속성일 경우에만 가능하다.
  
• 수정할 컬럼이 있는 행으로 이동한다.
  
• 값을 수정하고 적용한다.
  
  

  // 수정rs.updateInt("컬럼이름", 1700000);// 수정된 값 실제로 적용.rs.updateRow();

• rs.updateRow()를 하기전에 행을 이동하면 수정사항이 반영되지 않는다.
  
• TYPE_SCROLL_SENSITIVE가 아니라면 수정 사항을 현재 ResultSet에서 확인할 수 없다. 그러나 실제 DBMS에는 반영되어 있다.
  
  

Batch 업데이트

• 여러 SQL문을 일괄 실행한다.
  
• AutoCommit을 끄고, 일괄 실행할 SQL문을 추가하고, 성공하면 Commit하고 실패하면 RollBack한다.
  
  

Statement 배치 실행하기

try { con.setAutoCommit(false); Statement stmt = con.createStatement(); stmt.addBatch("SQL 문1"); stmt.addBatch("SQL 문2");   ....     // 각 업데이트가 몇개의 행에 영향을 끼쳤는가? int [] uprowcount = stmt.executeBatch(); // SQL문장 실행 con.commit();} catch (BatchUpdateException be) { // 예외 발성 전까지 성공적으로 수행된 문장 개수 int updatedCount = be.getUpdateCounts().length; // 각 업데이트가 몇개의 행에 영향을 끼쳤는가? int [] updateCount = be.getUpdateCounts();}

• statement.addBatch("SQL문장"): 배치 실행할 SQL 문장 추가
  
• statement.executeBatch(): 배치 실행
  
• batchUpdateException.getUpdateCounts(): 배치 실행된 각각의 업데이트가 영향을 끼친 행의 개수에 대한 int 배열
  
• 배치 작업 내의 작업 리스트는 Update Count를 리턴하는 SQL문이어야만 한다. SELECT같은 ResultSet을 반환하는 문장은 사용할 수 없다.
  
  

PreparedStatement 배치 실행하기

• Statement와 거의 동일하다.
  
  

  PreparedStatement pstmt = con.prepareStatement("....");pstmt.setString(1,"ABC");pstmt.setInt(2,2000);pstmt.addBatch(); // 지금까지 입력된 데이터를 배치 작업에 추가pstmt.setString(1, "DEF");pstmt.setInt(2, 3000);pstmt.addBatch();  ....int [] uprowcount = pstmt.executeBatch();

  
  

CallableStatement 배치 실행하기

• PreparedStatement와 동일하지만, OUT 파라미터나 INOUT 파라미터 작업을 한다면 배치 작업을 할 수 없다.
  
  

트랜잭션(Transaction)

• Connection.setAutoCommit(true/false): 자동으로 커밋할 것인가 여부. 기본값 true.
  
• Connection.commit(): 커밋
  
• Connection.rollback(): 롤백
  
  

트랜잭션 고립 레벨

• 트랜잭션에서 병행 작업시 발생하는 문제점
  
  • dirty reads : 다른 트랜잭션에서 커밋되지 않은 변경내용을 볼 수 있다.
    
  • nonrepeatable reads : 최초에 읽기 작업 후, 다른 트랜잭션이 데이터를 변경 시키고, 그 다음 읽기 작업이 변경된 사항을 읽어들여 최초의 읽기와 두 번째 읽기 작업의 결과 불일치.
    
  • phantom reads : nonrepeatable reads에 where 조건 추가.
    
• 고립 레벨 종류
  
  • TRANSACTION_NONE : 트랜잭션 지원 안함.
    
  • TRANSACTION_READ_UNCOMMITTED : 커밋되지 않은 데이터도 볼 수 있음.
    
  • TRANSACTION_READ_COMMITTED : 커밋된 데이터만 볼 수 있음.
    
  • TRANSACTION_REPEATABLE_READ : dirty reads와 nonrepeatable reads 방지. phantom reads는 방지 못함.
    
  • TRANSACTION_SERIALIZABLE : dirty/nonrepeatable/phantom reads 모두 방지.
    
• Connection.setTransactionIsolation(int level): 고립 레벨 지정. 커넥션을 닫거나 재지정할 때 까지 유지.
  
• DatabaseMetaData.surpportsTransactionIsolationLevel(int level): 고립 레벨 지원 여부 확인.
  
  

SavePoints : JDBC 3.0

• SavePoints 는 트랜잭션 작업 사이에 SavePoint를 정의해 두고 지정된 SavePoint로부터의 작업들을 커밋이나 롤백할 수 있도록 한다.
  
• DatabaseMetaData.supportsSavePoints(): SavePoints 지원 여부.
  
• Savepoint sp1 = con.setSavepoint("SP1"): SavePoint 지정.
  
• con.rollback(sp1): 지정된 SavePoint까지 롤백.
  
  

JDBC 3.0 새로운 데이타 타입

• SQL99의 새로운 데이터 타입들
  
• Blob/Clob 읽기
  
  

  Blob b1 = rs.getBlob(3);Clob c1 = rs.getClob(4);

• Blob/Clob 저장하기
  
  

  PreparedStatement pstmt = conn.preparedStatement(   "INSERT INTO PRODUCT (image, etc) VALUES (?,?));pstmt.setBlob(1, blobimage);pstmt.setClob(2, clobdescription);

• Array
  
  

  java.sql.Array ar = rs.getArray("arraycolumn");...pstmt.setArray(1, ar);// 업데이트rs.updateArray("arrayColumn", ar);

• Ref : 레퍼런스. 실제 데이터에 대한 포인터 역할.
  
  

  Ref ref = rs.getRedf("refcolumn");Address addr = (Address)ref.getObject();

• Datalink : 외부 데이터에 대한 참조 링크
  
  

  java.net.URL addurl = rs.getURL("urlColumn");

  
  

사용자 정의 데이터 타입

• 데이터베이스에서 사용자 정의 타입을 만들고, 그것을 자바에서SQLData인터페이스를 구현한 자바 클래스와 매핑할 수 있다.
  
• SQLData 구현
  
  

  import java.sql.*;public class ProductType implements SQLData { public int p_num; public String p_name;  public String sqlType = "product_type";  public String getSQLTypeName() {  return sqlType; } public void readSQL(SQLInput stream, String type) throws SQLException {  this.sqlType = type;  p_num = stream.readInt();  p_name = stream.readString(); } public void writeSQL(SQLOutput stream) throws SQLException {  stream　　　　　.write　　　　　Int(p_num);  stream　　　　　.write　　　　　String(p_name); }}

• 구현을 Connection의 TypeMap에 등록
  
  

  java.util.Map typeMap = conn.getTypeMap();map.put("SchemaName.PRODUCT_TYPE", Class.forName("ProductType"));

• 읽기
  
  

  ProductType pt = (ProductType)rs.getObject(1);

• Connection에 새로운 타입 맵 추가
  
  

  Hashtable newMap = new Hashtable(); ....conn.setTypeMap(newMap);

• 수정
  
  

  //먼저 ResultSet에서 클래스 인스턴스를 얻고, 수정 사항을 수정한 뒤에ProductType upproduct = (ProductType)rs.getObject(1); ...pstmt.setObject(1, upproduct);