[40일] JDBC (9) - 한 행 조회

package jdbc0922;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Test02_selectOne {

	public static void main(String[] args) {
		// sungjuk 테이블에서 한 행 가져오기

		int sno = 42;
		
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			String url = "jdbc:oracle:thin:@localhost:1521:xe";
			String user = "system";
			String password = "1234";
			String driver = "oracle.jdbc.driver.OracleDriver";
			Class.forName(driver);
			con = DriverManager.getConnection(url, user, password);				
			System.out.println("오라클 DB 서버 연결 성공!!");
			
			StringBuilder sql = new StringBuilder();
			sql.append(" SELECT sno, uname, kor, eng, mat, addr, tot, aver, wdate ");
			sql.append(" FROM sungjuk ");
			sql.append(" WHERE sno=? ");
			
			pstmt = con.prepareStatement(sql.toString());
			pstmt.setInt(1, sno);
			
			rs = pstmt.executeQuery();

			if(rs.next()) {
				System.out.println("자료있음~~");
			} else {
				System.out.println("자료없음!!");
			}//if end 
			
		} catch (Exception e) {
			System.out.println("오라클 DB 연결 실패 : " + e);
		} finally { //자원반납(순서주의)
			try {
				if(rs!=null) {rs.close();}
			} catch (Exception e) {}
			
			try {
				if(pstmt!=null) {pstmt.close();}
			} catch (Exception e) {}
			
			try {
				if(con!=null) {con.close();}
			} catch (Exception e) {}
		}//end
		
		System.out.println("END");
		
	}//main() end
}//class end

모든 칼럼을 조회할 때 select * 을 사용해도 된다. 하지만 * 사용할 경우 다음에 일부 칼럼을 조회하거나 다른 사람이 보게 될 경우 어떤 칼럼이 있는지 알 수 없다. 따라서 어떤 칼럼이 있는지 알 수 있게 명시적으로 작성하는 것이 좋다.

 

package jdbc0922;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Test02_selectOne {

	public static void main(String[] args) {
		// sungjuk 테이블에서 한 행 가져오기

		int sno = 42;
		
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			String url = "jdbc:oracle:thin:@localhost:1521:xe";
			String user = "system";
			String password = "1234";
			String driver = "oracle.jdbc.driver.OracleDriver";
			Class.forName(driver);
			con = DriverManager.getConnection(url, user, password);				
			System.out.println("오라클 DB 서버 연결 성공!!");
			
			StringBuilder sql = new StringBuilder();
			sql.append(" SELECT sno, uname, kor, eng, mat, tot, aver, addr, wdate ");
			sql.append(" FROM sungjuk ");
			sql.append(" WHERE sno=? ");
			
			pstmt = con.prepareStatement(sql.toString());
			pstmt.setInt(1, sno);
			
			rs = pstmt.executeQuery();

			if(rs.next()) {
				System.out.println("자료있음~~");
				
				//1)칼럼순서 접근
				System.out.println(rs.getInt(1)); //1번. sno칼럼
				System.out.println(rs.getString(2)); //2번칼럼. uname
				System.out.println(rs.getInt(3)); //3번칼럼. kor
				System.out.println(rs.getInt(4)); //4번칼럼. eng
				System.out.println(rs.getInt(5)); //5번칼럼. mat
				System.out.println(rs.getInt(6)); //6번칼럼. tot
				System.out.println(rs.getInt(7)); //7번칼럼. aver
				System.out.println(rs.getString(8)); //8번칼럼. addr
				System.out.println(rs.getString(9)); //9번칼럼. wdate
				
				//2)칼럼명 접근
				System.out.println(rs.getInt("sno")); //1번. sno칼럼
				System.out.println(rs.getString("uname")); //2번칼럼. uname
				System.out.println(rs.getInt("kor")); //3번칼럼. kor
				System.out.println(rs.getInt("eng")); //4번칼럼. eng
				System.out.println(rs.getInt("mat")); //5번칼럼. mat
				System.out.println(rs.getInt("tot")); //6번칼럼. tot
				System.out.println(rs.getInt("aver")); //7번칼럼. aver
				System.out.println(rs.getString("addr")); //8번칼럼. addr
				System.out.println(rs.getString("wdate")); //9번칼럼. wdate
				
			} else {
				System.out.println("자료없음!!");
			}//if end 
			
		} catch (Exception e) {
			System.out.println("오라클 DB 연결 실패 : " + e);
		} finally { //자원반납(순서주의)
			try {
				if(rs!=null) {rs.close();}
			} catch (Exception e) {}
			
			try {
				if(pstmt!=null) {pstmt.close();}
			} catch (Exception e) {}
			
			try {
				if(con!=null) {con.close();}
			} catch (Exception e) {}
		}//end
		
		System.out.println("END");
		
	}//main() end
}//class end