20201104_28 jdbc

• Application9

package jdbc;

import java.sql.*;

public class Application9 {
	
	private static final String JDBC_URL = "jdbc:oracle:thin:@localhost:1521:XE";
	private static final String DB_USER = "system";
	private static final String DB_PASS = "oracle";
	
	public static void main(String[] args) throws ClassNotFoundException {
		// TODO Auto-generated method stub
		Class.forName("oracle.jdbc.driver.OracleDriver");
		
		try {
			final Connection conn = DriverManager.getConnection(JDBC_URL, DB_USER, DB_PASS);
			final String sql = "SELECT empno, ename, job FROM emp";
			final PreparedStatement pstmt = conn.prepareStatement(sql);
			final ResultSet rs = pstmt.executeQuery();
			
			while (rs.next()) {
				int emp = rs.getInt("empno"); //별명이 온다면 별명으로 써줘야함
				String ename = rs.getString("ename");
				String job = rs.getString("job");
				System.out.println(emp);
				System.out.println(ename);
				System.out.println(job);
				System.out.println("------------------");
				}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}

emp테이블에서 emp, ename, job읽어와서 순차적으로 출력

 

 

 

 

 

• comm이 널값인 부분을 0으로 바꿔주기

package jdbc;

import java.sql.*;

public class Application9 {
	
	private static final String JDBC_URL = "jdbc:oracle:thin:@localhost:1521:XE";
	private static final String DB_USER = "system";
	private static final String DB_PASS = "oracle";
	
	public static void main(String[] args) throws ClassNotFoundException {
		// TODO Auto-generated method stub
		Class.forName("oracle.jdbc.driver.OracleDriver");
		
		try {
			final Connection conn = DriverManager.getConnection(JDBC_URL, DB_USER, DB_PASS);
			final String sql = "UPDATE emp SET comm = 0 WHERE comm is null";
			final PreparedStatement pstmt = conn.prepareStatement(sql);
			final int affectedRows = pstmt.executeUpdate();
			System.out.println(affectedRows);
		
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}

(null)이었던 값이 0이 됨

 

 

 

 

 

 

 

• 작업종류

DQL : SELECT

DML : INSERT UPDATE DELETE

 

 

 

 

#DQL - SELECT

 

• ResultSet 결과값담음
  -cursor : 현재 읽고 있는 행. 최초에는 BOF. 마지막을 만나면 EOF(false)
  -get데이터타입(columnIndex);
     -컬럼이 정의된 순소 (1부터)
  -get데이터타입(columnLable);
     -컬럼 이름

 

#DML - INSERT UPDATE DELETE

• PreparedStatement 값 세팅 set타입(칼럼순서, 지정할 값)
    pstmt.setInt(1,10);
• executeUpdate()
    반환값은 DML결과가 반영된 행의 개수