25. JAVA JDBC (Java Database Connectivity) - 3

1. 동적 커서 정의

- JDBC 2.0 부터는 ResultSet 의 커서가 원하는 위치대로 이동 가능한 커서.

(이용하려면 stmt, pstmt 생성시 아래의 방법으로 옵션을 줌.)

 

2. 방법

con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,

ResultSet.CONCUR_UPDATABLE);

Statement createStatement(int resultSetType,int resultSetConcurrency)

throws SQLException

 

(1) resultSetType

1) ResultSet.TYPE_FORWARD_ONLY,

2) ResultSet.TYPE_SCROLL_INSENSITIVE,

3) ResultSet.TYPE_SCROLL_SENSITIVE

 

(2) resultSetConcurrency

1) ResultSet.CONCUR_READ_ONLY

2) ResultSet.CONCUR_UPDATABLE */

 

3. 주요 ResultSet 의 커서 이동 메소드

(1) rs.next(); //커서를 한칸씩 내림

(2) rs.previous(); //커서를 한칸씩 올림

(3) rs.beforeFirst(); //커서를 BOF에 위치

(4) rs.afterLast(); // 커서를 EOF에 위치

---

import java.sql.*;

class C {
	Connection con;
	String url = "jdbc:oracle:thin:@61.81.98.62:1521:JAVA";	// localhost or 127.0.0.1
	Statement stmt;
	ResultSet rs;
	ResultSetMetaData rsmd;

	C() {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			con = DriverManager.getConnection(url, "scott", "tiger");
			// 커서의 기본값: 	TYPE_FORWARD_ONLY
			stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
			select();
			System.out.print("\n\n");
			reverse();
			closeAll();
		} catch(ClassNotFoundException cnfe) {
			System.out.println(cnfe.toString());
		} catch(SQLException se) {
			System.out.println(se.toString());
		}
	}

	void select() {
		try {
			String sql = "select * from JDBCT";
			rs = stmt.executeQuery(sql);
			rsmd = rs.getMetaData();
			int colCnt = rsmd.getColumnCount();
			for(int i=1; i<=colCnt; i++) {
				String colName = rsmd.getColumnName(i);
				System.out.print(colName+"\t");
			}
			System.out.println("\n-------------------------");
			// cursor => BOF -> EOF
			while(rs.next()) {
				int no = rs.getInt(1);
				String name = rs.getString(2);
				String addr = rs.getString(3);
				System.out.println(no + "\t" + name + "\t" + addr);
			}
		}  catch(SQLException se){
			System.out.println(se.toString());
		}
	}

	void reverse() {
		try {
			String sql = "select * from JDBCT";
			rs = stmt.executeQuery(sql);
			rsmd = rs.getMetaData();
			int colCnt = rsmd.getColumnCount();
			for(int i=1; i<=colCnt; i++) {
				String colName = rsmd.getColumnName(i);
				System.out.print(colName+"\t");
			}
			System.out.println("\n-------------------------");
			// cursor => EOF -> BOF
			rs.afterLast();
			while(rs.previous()) {
				int no = rs.getInt(1);
				String name = rs.getString(2);
				String addr = rs.getString(3);
				System.out.println(no + "\t" + name + "\t" + addr);
			}
		}  catch(SQLException se){
			System.out.println(se.toString());
		}
	}

	void closeAll() {
		try {
			if(stmt != null) stmt.close();
			if(con != null) con.close();
		} catch(SQLException se) {}
	}

	public static void main(String[] args) {
		new C();
	}
}