Study Web Development

12월 1일

이전으로

자바

17. JDBC

17-2 JDBC(Java Database Connectivity)

• 데이터베이스 연동 작업(=CRUD 작업)

CRUD	기능	SQL
Create	생성	INSERT
Read	읽기	SELECT
Update	갱신	UPDATE
Delete	삭제	DELETE

11. 새 클래스 UpdateMain 생성

package kr.s02.statement;

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

public class UpdateMain {
	public static void main(String[] args) {
		String db_driver = "oracle.jdbc.OracleDriver";
		String db_url = "jdbc:oracle:thin:@localhost:1521:xe";
		String db_id = "scott";
		String db_password = "tiger";
		
		Connection conn = null;
		Statement stmt = null;
		String sql = null;
		
		try {
			// JDBC 수행 1단계 : 드라이버 로드
			Class.forName(db_driver);
			// JDBC 수행 2단계 : Connection 객체 생성(ID, 비밀번호 인증)
			conn = DriverManager.getConnection(db_url, db_id, db_password);
			// JDBC 수행 3단계 : Statement 객체 생성
			stmt = conn.createStatement();
			
			// SQL문 작성
			sql = "UPDATE test1 SET age=90 WHERE id='sky'";
			
			// JDBC 수행 4단계 : SQL문을 실행해서 테이블의 행을 갱신
			int count = stmt.executeUpdate(sql); // 갱신 작업 후 갱신한 행의 수를 반환
			System.out.println(count + "개 행의 정보를 갱신했습니다.");
		}
		catch(Exception e) {
			e.printStackTrace();
		}
		finally {
			// 자원 정리
			if(stmt!=null) try {stmt.close();} catch(SQLException e) {}
			if(conn!=null) try {conn.close();} catch(SQLException e) {}
		}
	}
}

12. 새 클래스 DeleteMain 생성

package kr.s02.statement;

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

public class DeleteMain {
	public static void main(String[] args) {
		String db_driver = "oracle.jdbc.OracleDriver";
		String db_url = "jdbc:oracle:thin:@localhost:1521:xe";
		String db_id = "scott";
		String db_password = "tiger";
		
		Connection conn = null;
		Statement stmt = null;
		String sql = null;
		
		try {
			// JDBC 수행 1단계 : 드라이버 로드
			Class.forName(db_driver);
			// JDBC 수행 2단계 : Connection 객체 생성
			conn = DriverManager.getConnection(db_url, db_id, db_password);
			// JDBC 수행 3단계 : Statement 객체 생성
			stmt = conn.createStatement();
			
			// SQL문 작성
			sql = "DELETE FROM test1 WHERE id='star'";
			
			// JDBC 수행 4단계 : SQL문을 실행해서 테이블의 행을 삭제
			int count = stmt.executeUpdate(sql); // 삭제 작업 후 삭제한 행의 수를 반환
			System.out.println(count + "개 행을 삭제하였습니다.");
		}
		catch(Exception e) {
			e.printStackTrace();
		}
		finally {
			// 자원 정리
			if(stmt!=null) try {stmt.close();} catch(SQLException e) {}
			if(conn!=null) try {conn.close();} catch(SQLException e) {}
		}
	}
}

13. 새 클래스 DropMain 생성

package kr.s02.statement;

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

public class DropMain {
	public static void main(String[] args) {
		String db_driver = "oracle.jdbc.OracleDriver";
		String db_url = "jdbc:oracle:thin:@localhost:1521:xe";
		String db_id = "scott";
		String db_password = "tiger";
		
		Connection conn = null;
		Statement stmt = null;
		String sql = null;
		
		try {
            // JDBC 수행 1단계 : 드라이버 로드
			Class.forName(db_driver);
            // JDBC 수행 2단계 : Connection 객체 생성
			conn = DriverManager.getConnection(db_url, db_id, db_password);
            // JDBC 수행 3단계 : Statement 객체 생성
			stmt = conn.createStatement();
			
			// SQL문 작성
			sql = "DROP TABLE test1";
			
			// JDBC 수행 4단계 : SQL문을 실행해서 데이터베이스에서 테이블을 삭제
			stmt.executeUpdate(sql); // executeUpdate() 메서드는 접근한 행의 수를 반환하며, 테이블을 생성하거나 삭제할 때는 0을 반환
			System.out.println("테이블이 정상적으로 삭제되었습니다.");
		}
		catch(Exception e) {
			e.printStackTrace();
		}
		finally {
			if(stmt!=null) try {stmt.close();} catch(SQLException e) {}
			if(conn!=null) try {conn.close();} catch(SQLException e) {}
		}
	}
}

PreparedStatement

• java.sql 패키지에 포함된 인터페이스로, Statement를 상속받음
• SQL문에 바인드 변수 ?를 사용하고, 쿼리 실행 전에 setString(), setInt() 등 setter 메서드를 이용하여 값을 바인딩
  • 바인드 변수는 SQL 자료형을 갖는 값 자리에만 사용 가능(=테이블명, 컬럼명 등은 동적 처리 불가)
    • 패턴 검색시 사용되는 가변 문자 %, _는 setString() 메서드의 인자로 전달되거나, SQL문에서 CONCATE() 함수 혹은 연결 연산자 ||를 통해 바인드 변수와 연결되어야 함
  • setter 메서드는 값을 전달받는 컬럼의 자료형과 호환 가능한 자료형을 지정
• Soft Parsing
  • 쿼리를 반복 실행할 때, PreparedStatement는 parse 단계를 생략(=DBMS 캐시에 준비되어 있는 쿼리 실행 계획을 재사용)
    • DBMS의 쿼리 실행 단계: parse, bind, execute, fetch
    • Oracle Database와 연동하는 경우, PreparedStatement가 Statement보다 빠른 실행 속도와 낮은 CPU 점유율 제공
  • SQL Injection 예방
    • parse 단계를 건너뛰므로, 사용자가 문자열로 가장한 SQL문을 입력하더라도 SQL문으로 인식하지 않음
  • 문자열 내에 특수문자 ' 단독 사용 가능

1. 새 패키지 kr.s03.preparedstatement 생성하고 새 클래스 DBUtil 생성

package kr.s03.preparedstatement;

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

public class DBUtil {
	private static final String DB_DRIVER = "oracle.jdbc.OracleDriver";
	private static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:xe";
	private static final String DB_ID = "scott";
	private static final String DB_PASSWORD = "tiger";

	// Connection 객체 생성 및 반환
	public static Connection getConnection() throws ClassNotFoundException, SQLException {
		// JDBC 수행 1단계 : 드라이버 로드
		Class.forName(DB_DRIVER);
		// JDBC 수행 2단계 : Connection 객체 생성
		return DriverManager.getConnection(DB_URL, DB_ID, DB_PASSWORD);
	}
	
	// 자원 정리
	public static void executeClose(ResultSet rs, PreparedStatement pstmt, Connection conn) {
		if(rs!=null) try {rs.close();} catch(SQLException e) {}
		if(pstmt!=null) try {pstmt.close();} catch(SQLException e) {}
		if(conn!=null) try {conn.close();} catch(SQLException e) {}
	}
}

2. 새 클래스 SelectMain 생성

package kr.s03.preparedstatement;

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

public class SelectMain {
	public static void main(String[] args) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		
		try {
			// JDBC 수행 1단계 : 드라이버 로드
			// JDBC 수행 2단계 : Connection 객체 생성
			conn = DBUtil.getConnection();
			
			// SQL문 작성
			sql = "SELECT * FROM test1";
			
			// JDBC 수행 3단계 : PreparedStatement 객체 생성
			pstmt = conn.prepareStatement(sql);
					
			// JDBC 수행 4단계 : SQL문을 실행해서 테이블에 반영하고 결과 집합을 ResultSet에 담아서 반환
			rs = pstmt.executeQuery();
			
			System.out.println("ID\t나이");
			while(rs.next()) {
				System.out.print(rs.getString("id") + "\t");
				System.out.println(rs.getInt("age"));
			}
		}
		catch(Exception e) {
			e.printStackTrace();
		}
		finally {
			// 자원 정리
			DBUtil.executeClose(rs, pstmt, conn);
		}
	}
}

3. 새 클래스 InsertMain 생성

package kr.s03.preparedstatement;

import java.sql.Connection;
import java.sql.PreparedStatement;

public class InsertMain {
	public static void main(String[] args) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		
		try {
			// JDBC 수행 1단계 : 드라이버 로드
			// JDBC 수행 2단계 : Connection 객체 생성
			conn = DBUtil.getConnection();
			
			// SQL문 작성
			// sql = "INSERT INTO test1 VALUES ('star', 50)"; // (Statement 객체 사용시와 마찬가지로) SQL문에 데이터를 직접 입력하는 방식도 가능하지만, 직접 입력 방식은 사용자로부터 데이터를 입력받는 경우 해킹 위험이 있으므로 권장하지 않음
			sql = "INSERT INTO test1 VALUES (?, ?)"; // 데이터를 넣을 자리에 바인드 변수 ?를 명시
			
			// JDBC 수행 3단계 : PreparedStatement 객체 생성
			pstmt = conn.prepareStatement(sql);
			// ?에 데이터를 바인딩
			pstmt.setString(1, "st'ar"); // 첫 번째 ?에 데이터를 전달; 자료형은 데이터를 저장할 컬럼(=첫 번째 컬럼=id)의 자료형을 따름
			pstmt.setInt(2, 50); // 두 번째 ?에 데이터를 전달; 자료형은 데이터를 저장할 컬럼(=두 번째 컬럼=age)의 자료형을 따름
			
			// JDBC 수행 4단계 : SQL문을 실행해서 테이블에 행을 삽입
			int count = pstmt.executeUpdate(); // 삽입 작업 후 삽입한 행의 수를 반환
			System.out.println(count + "개 행을 삽입했습니다.");
		}
		catch(Exception e) {
			e.printStackTrace();
		}
		finally {
			// 자원 정리
			DBUtil.executeClose(null, pstmt, conn);
		}
	}
}

4. 새 클래스 UpdateMain 생성

package kr.s03.preparedstatement;

import java.sql.Connection;
import java.sql.PreparedStatement;

public class UpdateMain {
	public static void main(String[] args) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		
		try {
			// JDBC 수행 1단계 : 드라이버 로드
			// JDBC 수행 2단계 : Connection 객체 생성
			conn = DBUtil.getConnection();
			
			// SQL문 작성
			sql = "UPDATE test1 SET age=? WHERE id=?"; // 데이터를 넣을 자리에 바인드 변수 ?를 명시
			
			// JDBC 수행 3단계 : PreparedStatement 객체 생성
			pstmt = conn.prepareStatement(sql);
			// ?에 데이터 바인딩
			pstmt.setInt(1, 19); // 첫 번째 ?에 데이터를 전달; 자료형은 데이터를 저장할 컬럼(=age)의 자료형을 따름
			pstmt.setString(2, "star"); // 두 번째 ?에 데이터를 전달; 자료형은 데이터를 저장할 컬럼(=id)의 자료형을 따름
			
			// JDBC 수행 4단계 : SQL문을 실행해서 테이블의 행을 갱신
			int count = pstmt.executeUpdate(); // 갱신 작업 후 갱신한 행의 수를 반환
			System.out.println(count + "개 행의 정보를 갱신했습니다.");			
		}
		catch(Exception e) {
			e.printStackTrace();
		}
		finally {
			// 자원 정리
			DBUtil.executeClose(null, pstmt, conn);
		}
	}
}

5. 새 클래스 DeleteMain 생성

package kr.s03.preparedstatement;

import java.sql.Connection;
import java.sql.PreparedStatement;

public class DeleteMain {
	public static void main(String[] args) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		
		try {
			// JDBC 수행 1단계 : 드라이버 로드
			// JDBC 수행 2단계 : Connection 객체 생성
			conn = DBUtil.getConnection();
			
			// SQL문 작성
			sql = "DELETE FROM test1 WHERE id=?";
			
			// JDBC 수행 3단계 : PreparedStatement 객체 생성
			pstmt = conn.prepareStatement(sql);
			// ?에 데이터 바인딩
			pstmt.setString(1, "star");
			
			// JDBC 수행 4단계 : SQL문을 실행해서 테이블의 행을 삭제
			int count = pstmt.executeUpdate(); // 삭제 작업 후 삭제한 행의 수를 반환
			System.out.println(count + "개 행을 삭제하였습니다.");
		}
		catch(Exception e) {
			e.printStackTrace();
		}
		finally {
			// 자원 정리
			DBUtil.executeClose(null, pstmt, conn);
		}
	}
}

6. 새 패키지 kr.s04.preparedstatement 생성
7. 패키지를 오른쪽 클릭하여 New-Other... 선택 후, SQL Development-SQL File 선택하여 table.sql 생성

CREATE TABLE test2(
	num NUMBER PRIMARY KEY,
	title VARCHAR2(30) NOT NULL,
	name VARCHAR2(30) NOT NULL,
	memo VARCHAR2(4000) NOT NULL,
	email VARCHAR2(30),
	reg_date DATE NOT NULL
);
CREATE SEQUENCE test2_seq;

8. 새 클래스 InsertMemo 생성

package kr.s04.preparedstatement;

import kr.s03.preparedstatement.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.io.BufferedReader;
import java.io.InputStreamReader;

public class InsertMemo {
	public static void main(String[] args) {
		BufferedReader br = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		String title, name, memo, email; // try 블럭에서만 사용하는 변수이므로 try 블럭 안에서 선언해도 무방
		
		try {
			br = new BufferedReader(new InputStreamReader(System.in));
			System.out.println("메모를 입력합니다.");
			System.out.print("제목 > ");
			title = br.readLine();
			System.out.print("이름 > ");
			name = br.readLine();
			System.out.print("메모 > ");
			memo = br.readLine();
			System.out.print("이메일 > ");
			email = br.readLine();
			
			// JDBC 수행 1,2단계
			conn = DBUtil.getConnection();
			
			// SQL문 작성
			sql = "INSERT INTO test2 (num, title, name, memo, email, reg_date) " // 긴 문자열은 가독성을 위해 분리 후 + 연산자로 연결
				+ "VALUES(test2_seq.NEXTVAL, ?, ?, ?, ?, SYSDATE)"; // 시퀀스의 다음 값 반환과 현재 날짜 반환은 명령어로 처리되어야 하므로 바인드 변수 사용 불가
			
			// JDBC 수행 3단계 : PreparedStatement 객체 생성
			pstmt = conn.prepareStatement(sql);
			// ?에 데이터를 바인딩
			pstmt.setString(1, title);
			pstmt.setString(2, name);
			pstmt.setString(3, memo);
			pstmt.setString(4, email);
			
			// JDBC 수행 4단계 : SQL문을 실행해서 테이블에 행을 삽입
			int count = pstmt.executeUpdate();
			System.out.println(count + "개 행을 삽입하였습니다.");
		}
		catch(Exception e) {
			e.printStackTrace();
		}
		finally {
			// 자원 정리
			DBUtil.executeClose(null, pstmt, conn);
			if(br!=null) try {br.close();} catch(Exception e) {} // 콘솔로부터 입력받는 경우에는 생략 가능
		}
	}
}

9. 새 클래스 SelectListMemo 생성

package kr.s04.preparedstatement;

import kr.s03.preparedstatement.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class SelectListMemo {
	public static void main(String[] args) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		
		try {
			// JDBC 수행 1,2단계
			conn = DBUtil.getConnection();
			
			// SQL문 작성
			sql = "SELECT * FROM test2 ORDER BY num DESC"; // 최신글이 상단에 오게끔 정렬; reg_date도 이용 가능하지만, 일반적으로 목록에서는 PRIMARY KEY를 이용하여 정렬
			
			// JDBC 수행 3단계 : PreparedStatement 객체 생성
			pstmt = conn.prepareStatement(sql);
			
			// JDBC 수행 4단계 : SQL문을 실행해서 테이블로부터 결과 집합을 얻고 ResultSet에 담아서 반환
			rs = pstmt.executeQuery();
			
			System.out.println("번호\t제목\t작성자\t작성일"); // 내용 및 이메일은 상세 페이지에서만 조회하고, 목록에서는 생략
			while(rs.next()) {
				System.out.print(rs.getInt("num") + "\t");
				System.out.print(rs.getString("title") + "\t");
				System.out.print(rs.getString("name") + "\t");
				System.out.println(rs.getDate("reg_date")); // 컬럼의 자료형이 DATE인 경우, getDate() 메서드와 getString() 메서드 모두 사용 가능
			}
		}
		catch(Exception e) {
			e.printStackTrace();
		}
		finally {
			// 자원 정리
			DBUtil.executeClose(rs, pstmt, conn);
		}
	}
}

10. 새 클래스 SelectDetailMemo 생성

package kr.s04.preparedstatement;

import kr.s03.preparedstatement.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.io.BufferedReader;
import java.io.InputStreamReader;

public class SelectDetailMemo {
	public static void main(String[] args) {
		BufferedReader br = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		
		try {
			br = new BufferedReader(new InputStreamReader(System.in));
			System.out.println("메모 상세 페이지를 조회합니다.");
			System.out.print("메모 번호 > ");
			int num = Integer.parseInt(br.readLine());
			
			// JDBC 수행 1,2단계
			conn = DBUtil.getConnection();
			
			// SQL문 작성
			sql = "SELECT * FROM test2 WHERE num=?";
					
			// JDBC 수행 3단계 : PreparedStatement 객체 생성
			pstmt = conn.prepareStatement(sql);
			// ?에 데이터를 바인딩
			pstmt.setInt(1, num); // 상세 페이지를 조회하기 위해서는 테이블에 PRIMARY KEY가 지정되어 있어야 함
			
			// JDBC 수행 4단계 : SQL문을 실행해서 테이블로부터 한 개의 레코드를 얻어 ResultSet에 담아서 반환
			rs = pstmt.executeQuery();
			
			if(rs.next()) { // 입력한 PRIMARY KEY 값을 가진 행이 있는 경우; ResultSet 커서의 초기 위치는 첫 번째 행 전으로, next() 메서드를 1회 실행해야 커서가 첫 번째 행으로 이동하게 됨
				System.out.println("번호 : " + rs.getInt("num")); // getString()도 사용 가능
				System.out.println("제목 : " + rs.getString("title"));
				System.out.println("작성자 : " + rs.getString("name"));
				System.out.println("내용 : " + rs.getString("memo"));
				System.out.println("이메일 : " + rs.getString("email"));
				System.out.println("작성일 : " + rs.getDate("reg_date"));
			}
			else { // 입력한 PRIMARY KEY 값을 가진 행이 없는 경우
				System.out.println("검색된 데이터가 없습니다.");
			}
		}
		catch(Exception e) {
			e.printStackTrace();
		}
		finally {
			// 자원 정리
			if(br!=null) try {br.close();} catch(Exception e) {}
			DBUtil.executeClose(rs, pstmt, conn);
		}
	}
}

다음으로