JAVA_36_ExecuteQuery_JDBC(오라클)

• 수행결과로 ResultSet 객체의 값을 반환
• SELECT 구문을 수행할 때 사용

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class OraSelect1 {

	public static void main(String[] args) throws SQLException {
		Scanner sc = new Scanner(System.in);
		System.out.println("부서코드를 입력하세요");
		int deptno = sc.nextInt();
		
		String driver = "oracle.jdbc.driver.OracleDriver";
		String url    = "jdbc:oracle:thin:@localhost:1521:xe";
		String sql = "Select dname, loc FROM Dept Where deptno=" + deptno;
		                                                  //deptno가 PK라서 한 행만 나온다.
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		  // 받을 값이 있다고하면  ResultSet으로 반드시 받아야 한다.
		
		System.out.println("sql-> " + sql);
		
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url,"scott","tiger");
			stmt = conn.createStatement();  // stmt
			rs   = stmt.executeQuery(sql);  // Select 문장은 executeQuery를 써야함
			
			if (rs.next()) {
				String dname = rs.getString("dname"); // rs.getString(1) <-- dname
				String loc   = rs.getString(2);       // rs.getString(2) <-- loc
				                         //위에 "loc"이라고 적어도 된다.
				System.out.println("부서번호 :" + deptno);
				System.out.println("부서명 :" + dname);
				System.out.println("위치 :" + loc);
			} else {
				System.out.println("자료가 없습니다.");
			}
		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			if( rs != null) rs.close();
			if( stmt != null) stmt.close();
			if( conn != null) conn.close();
		}
		sc.close();
	}
}

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class OraSelect2 {

	public static void main(String[] args) throws SQLException {
		
		String driver = "oracle.jdbc.driver.OracleDriver";
		String url    = "jdbc:oracle:thin:@localhost:1521:xe";
		String sql    = "Select * FROM EMP";
		
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		System.out.println("사원 명단");
		System.out.println("사원코드\t 사원명 \t 업무\t\t 급여\t 일자");
		System.out.println("==========================================");
		
		
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url,"scott","tiger");
			stmt = conn.createStatement();  // stmt
			rs   = stmt.executeQuery(sql);  
			
			// rs row
			if (rs.next()) {
				do {
					int empno = rs.getInt(1); // rs.getString(1) <-- dname
					String ename   = rs.getString(2);       // rs.getString(2) <-- loc
					String job   = rs.getString(3);       // rs.getString(2) <-- loc
					int sal   = rs.getInt("SAL");       // rs.getString(2) <-- loc
					Date date   = rs.getDate(5);       // rs.getString(2) <-- loc
					if (job.length() > 7)
						System.out.printf("%d \t%s\t%s\t%d\t%TF\n",empno, ename, job, sal,date);
					                                       //날짜는 %TF이다.
					else System.out.printf("%d \t%s\t%s\t\t%d\t%TF\n",empno, ename, job, sal,date);
					
				} while (rs.next());	
			}
			else {
				System.out.println("date No");
			}
		}  catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			if( rs != null) rs.close();
			if( stmt != null) stmt.close();
			if( conn != null) conn.close();
		}
	}
}