Part 20 데이터베이스 입출력

JDBC 개요

자바는 데이터베이스와 연결해서 데이터 입출력 작업을 할 수 있도록 JDBC 라이브러리(java.sql 패키지)를 제공한다.
JDBC는 데이터베이스 관리시스템의 종류와 상관없이 동일하게 사용할 수 있는 클래스와 인터페이스로 구성되어 있다.

JDBC 인터페이스를 통해 실제로 DB와 작업하는 것은 JDBC Driver이다. JDBC Driver는 JDBC 인터페이스를 구현한 것으로, DBMS 마다 별도록 다운로드 받아 사용해야 한다.

DriverManager 클래스

JDBC Driver를 관리하며 DB와 연결해서 Connection 구현 객체를 생성

Connection 인터페이스

Statement, PreparedStatement, CallabelStatement 구현 객체를 생성하며, 트랜젝션 처리 및 DB 연결을 끊을 때 사용

Statement 인터페이스

SQL의 DDL 과 DML 을 실행할 때 사용. 주로 변경되지 않는 정적 SQL 문을 실행할 때 사용

PreparedStatement

Statement와 동일하게 SQL의 DDL, DML 문을 실행할 때 사용. 차이점은 매개변수화 된 SQL문을 사용할 수 있기 때문에 편리성과 보안성이 좋다. 그래서 Statement보다는 PreparedStatement를 주로 사용한다.

CallableStatement

DB에 저장되어 있는 프로시저와 함수를 호출할 때 사용

ResultSet

DB에서 가져온 데이터를 읽을 때 사용

데이터 저장

users 데이블에 새로운 사용자 정보를 저장하는 INSERT 문은 다음과 같다.

INSERT INTO users (userid, username, userpassword, userage, useremail ) VALUES ('winter', '한겨율', '12345', 25, 'winter@mycompany.com')

값을 ? 로 대체한 매개변수화된 INSERT 문으로 변경하면 다음과 같다.

INSERT INTO users (userid, username, userpassword, userage, useremail ) VALUES (?, ?, ?, ?, ?)

그리고 INSERT 문을 String 타입 변수 sql에 문자열로 대입한다.

String sql = new StringBuilder()
	.append("INSERT INTO users (userid, username, userpassword, userage, useremail )")
    .append("VALUES (?, ?, ?, ?, ?)")
    .toString();

매개변수화된 SQL문을 실행하려면 PreparedStatement가 필요하다.
다음과 같이 Connection 의 prepareStatement() 메소드로부터 PreparedStatement 를 얻는다.

PreparedStatement pstmt = conn.prepareStatement(sql);

그리고 ?에 들어갈 값을 지정해주는데, ?는 순서에 따라 1번부터 번호가 부여된다.
값의 타입에 따라 Setter 메소드를 선택한 후 첫 번째에는 ? 순번, 두 번째에는 값을 지정한다.

ptmst.setString(1, "winter");
ptmst.setString(2, "한겨울");
ptmst.setString(3, "12345");
ptmst.setInt(4, 23);
ptmst.setString(5, "winter@mycompany.com");

값을 지정한 후 executeUpdate() 메소드를 호출하면 SQL문이 실행되면서 users 테이블에 1개의 행이 저장된다. executeUpdate() 메소드가 리턴하는 값은 저장된 행 수인데, 정상적으로 실행되었을 경우 1을 리턴한다.

int rows = pstmt.executeUpdate();

PreparedStatement 를 더 이상 사용하지 않을 경우에는 다음과 같이 close() 메소드를 호출해서 메모리를 해제한다.

pstmst.close();

데이터 수정

boards 테이블에 저장된 게시물 중에서 bno가 1인 게시물의 btitl, bcontent, bfilename, bfiledata 를 변경하는 SQL 문은 다음과 같다.

UPDATE boards SET
	btitle='눈사람',
    bcontent='눈으로 만든 사람',
    bfilename='snowman.jpg',
    bfiledata=binaryData
WHERE bno=3

값을 ?로 대체한 매개변수화된 UPDATE 문으로 변경한다.

UPDATE boards SET
	btitle=?,
    bcontent=?,
    bfilename=?,
    bfiledata=?
WHERE bno=?

String 타입 변수 sql에 매개변수화된 UPDATE 문을 저장한다.

String sql = new StringBuilder()
	.append("UPDATE boards SET ")
    .append("btitle=?,")
    .append("bcontent=?,")
    .append("bfilename=?,")
    .append("bfiledata=?")
    .append("WHERE bno=?")
    .toString();

매개변수화된 UPDATE 문을 실행하기 위해 다음과 같이 prepareStatement() 메소드로부터 PreparedStatement 를 얻고, ?에 해당하는 값을 지정한다.

PreparedStatement pstmt = conn.prepareStatement(sql);
ptmst.setString(1, "눈사람");
ptmst.setString(2, "눈으로 만든 사람");
ptmst.setString(3, "snowman.jpg");
ptmst.setBlob(4, new FileInputStream("src/ch20/oracle/sec07/snowman.jpg"));
ptmst.setInt(5, 3);

값을 모두 지정하였다면 UPDATE 문을 실행하기 위해 executeUpdate() 메소드를 호출한다.
성공적으로 실행되면 수정된 행의 수가 리턴된다. 0이 리턴되면 조건에 맞는 행이 없어 수정된 내용이 없음을 의미한다.

int rows = pstmt.executeUpdate();

데이터 삭제

boards 테이블에서 bwriter가 winter인 모든 게시물을 삭제하는 DELETE 문은 다음과 같다.

DELETE FROM boards WHERE bwriter='winter'

조건절의 값을 ?로 대체한 매개변수화된 DELETE 문으로 변경한다.

DELETE FROM boards WHERE bwriter=?

매개변수화된 DELETE 문을 String 타입 변수 sql 에 대입한다.

String sql = "DELETE FROM boards WHERE bwriter=?";

매개변수화된 DELETE 문을 실행하기 위해 prepareStatement() 메소드로부터 PreparedStatement 를 얻고 ?에 값을 지정한 후, executeUpdate로 SQL문을 실행한다. 리턴값은 삭제된 행 수이다.

String sql = "DELETE FROM boards WHERE bwriter=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
ptmst.setString(1, "winter");
int rows = pstmt.executeUpdate();

데이터 읽기

PreparedStatement 를 생성할 때 SQL 문이 INSERT, UPDATE, DELETE 일 경우에는 executeUpdate() 메소드를 호출하지만, 데이터를 가져오는 SELETE 문일 경우에는 executeQuery() 메소드를 호출해야 한다.
executeQuery() 메소드는 가져온 데이터를 ResultSet에 저장하고 리턴한다.

ResultSet rs = pstmt.executeQuery();

ResultSet 구조

ResultSet은 SELECT 문에 기술된 컬럼으로 구성된 행의 집합이다.

ResultSet의 특징은 커서가있는 행의 데이터만 읽을 수 있다는 것이다. 여기서 커서는 행을 가리키는 포인터를 말한다.
ResultSet에는 실제 가져온 데이터 행의 앞과 뒤에 beforeFirst 행과 afterLast 행이 붙는데, 최초 커서는 beforeFirst를 가리킨다. 따라서 첫 번째 데이터 행인 first 행을 읽으려면 커서를 이동시켜야 한다. 이때 next() 메소드를 사용한다.

boolean result = rs.next();

next() 메소드는 커서를 다음 행으로 이동시키는데, 이동한 행에 데이터가 있으면 true, 없으면 false를 리턴한다.

만약 SELECT 문으로 가져온 데이터 행이 없다면 beforeFirst 행과 afterLast 행이 붙어 있기 때문에 첫 번째 next() 결과는 false가 된다.

• 1개의 데이터 행만 가져올 경우

ResultSet rc = pstmt.executeQuery();
if(rs.next()) {
	//첫번째 데이터 행 처리
} else {
	//afterLast 행으로 이동했을 경우
}

• n개의 데이터 행을 가져올 경우

ResultSet rc = pstmt.executeQuery();
while(rs.next()) {
	//last 행까지 이동하면서 데이터 행 처리
} 
//afterLast 행으로 이동했을 경우

SELECT 문에 따라 ResultSet에는 많은 데이터 행이 저장될 수 있기 때문에 ResultSet을 더 이상 사용하지 않는다면 close() 메소드를 호출해서 ResultSet이 사용한 메모리를 해제하는 것이 좋다.

rs.close();

트랜젝션 처리

트랜젝션은 기능 처리의 최소 단위를 말한다. 하나의 기능은 여러 가지 소작업들로 구성된다.
최소 단위하는 것은 이 소작업들을 분리할 수 없으며, 전체를 하나로 본다는 개념이다. 트랜잭션은 소작업들이 모두 성공하거나 실패해야 한다.

DB는 트랜잭션을 처리하기 위해 커밋과 롤백을 제공한다. 커밋은 내부 작업을 모두 성공 처리하고, 롤백은 실행 전으로 돌아간다는 의미에서 모두 실패 처리한다.

JDBC에서는 INSERT와 UPDATE, DELETE 문을 실행할 때마다 자동 커밋이 일어난다.
이 기능은 계좌이체와 같이 두 가지 UPDATE 문을 실행할 때 문제가 된다. 출금 작업이 성공되면 바로 커밋이 되기 때문에 입금 작업의 성공 여부와 상관없이 출금 작업만 별도 처리된다.

따라서 JDBC 에서 트랜잭션을 코드로 제어하려면 자동 커밋 기능을 꺼야 한다.
자동 커밋 설정 여부는 Connection 의 setAutoCommit() 메소드로 할 수 있다.

conn.setAutoCommit(false);

자동 커밋 기능이 꺼지면, 다음과 같이 커밋과 롤백을 제어할 수 있다.

conn.commit();
conn.rollback();

트랜잭션을 위한 일반적인 코드 작성 패턴은 다음과 같다.

Connection conn = null;
try {
	//트랜잭션 시작
    //자동 커밋 기능 끄기
    conn.setAutoCommit(false);
    //소작업 처리
    //...
    //...
    //커밋 -> 모두 성공 처리
    conn.commit();
    //트랜젝션 종료
} catch(Exception e) {
	try {
    	//롤백 -> 모두 실패 처리
        conn.rollback();
    } catch (SQLException e1) {}
} finally {
	if(conn != null) {
    	try {
        	//원래대로 자동 커밋 기능 켜기
        	conn.setAutoCommit(true);
            //연결 끊기
            conn.close();
        } catch(SQLException e) {}
    }
}

문제

1. JDBC에 대한 설명으로 틀린 것은 무엇입니까?
   ➊ java.sql에서 제공하는 표준 라이브러리를 말한다.
   ➋ DBMS의 종류와 상관없이 사용할 수 있는 클래스와 인터페이스로 구성되어 있다.
   ➌ JDBC 인터페이스들을 구현한 것이 JDBC Driver이다.
   ➍ JDBC Driver는 DBMS의 종류와 상관없이 동일한 것을 사용할 수 있다.

• 답 : ➍

2. JDBC가 DB와 연결할 때 필요한 정보가 아닌 것은 무엇입니까?
   ➊ DBMS가 설치된 컴퓨터의 IP 주소와 Port 번호가 필요하다.
   ➋ DBMS에 생성된 DB의 이름과 사용자 및 비밀번호가 필요하다.
   ➌ DB에 생성된 테이블 이름을 알아야 한다.
   ➍ DBMS별로 제공되는 JDBC Driver 클래스 이름을 알아야 한다.

• 답 : ➌

3. JDBC로 SQL 실행 결과를 얻기 위한 코드 작성 순서는? ( ) -> ( ) -> ( ) -> ( )
   ➊ DriverManager부터 Connection을 얻는다.
   ➋ Class.forName() 메소드를 이용해서 JDBC Driver 클래스를 로딩한다.
   ➌ ResultSet에서 SQL 실행 결과를 얻는다.
   ➍ PreparedStatement를 얻고 SQL 문을 실행한다.

• 답 : ➋ -> ➊ -> ➍ -> ➌

4. PreparedStatement에 대한 설명으로 틀린 것은 무엇입니까?
   ➊ 매개변수화된 SQL 문을 사용할 수 있다.
   ➋ INSERT, UPDATE, DELETE 문은 executeUpdate() 메소드로 실행한다.
   ➌ SELECT 문은 executeQuery() 메소드로 실행한다.
   ➍ 매개변수화된 SQL 문의 ? 순번은 0번부터 시작한다.

• 답 : ➍

5. ResultSet에 대한 설명으로 틀린 것은 무엇입니까?
   ➊ ResultSet은 executeQuery의 리턴값이다.
   ➋ next() 메소드로 afterLast로 이동할 때 true를 리턴한다.
   ➌ ResultSet은 한 번에 하나의 행만 읽을 수 있다.
   ➍ ResultSet은 다음 행으로 커서를 이동할 때 next() 메소드를 사용한다.

• 답 : ➋

6. 프로시저와 함수를 실행하는 방법으로 틀린 것은 무엇입니까?
   ➊ CallableStatement를 이용한다.
   ➋ 프로시저 호출 문자열로 "{ call 프로시저명(?, ?, …) }"을 사용한다.
   ➌ 함수 호출 문자열로 "{ ? = 함수명(?, ?, …) }"을 사용한다.
   ➍ 리턴값인 ?을 지정할 때에는 registerOutParameter() 메소드를 이용한다.

• 답 : ➌

7. 트랜잭션에 대한 설명으로 틀린 것은 무엇입니까?
   ➊ 기능 처리의 최소 단위를 말한다.
   ➋ 커밋(commit)은 내부 작업을 모두 성공 처리한다.
   ➌ 롤백(rollback)은 내부 작업 중에서 성공한 작업까지 되돌린다.
   ➍ 트랜잭션을 코드로 제어하려면 setAutoCommit(false) 메소드를 먼저 호출해야 한다.

• 답 : ➌

8. 20장 12절에서 구현한 게시판에서 다음 내용과 같이 새 사용자를 가입하는 기능을 추가해보세요.
   
   
   
   

• 답 :

import java.util.Date;
import lombok.Data;
@Data
public class Board {
	private int bno;
	private String btitle;
	private String bcontent;
	private String bwriter;
	private Date bdate;
}

import lombok.Data;
@Data
public class User {
	private String userId;
	private String userName;정답
	private String userPassword;
	private int userAge;
	private String userEmail;
}

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class BoardExample {
	private Scanner scanner = new Scanner(System.in);
	private Connection conn;
	public BoardExample() {
 		try {
 			Class.forName("oracle.jdbc.OracleDriver");
 			conn = DriverManager.getConnection(
 				"jdbc:oracle:thin:@localhost:1521/orcl",
 				"java",
 				"oracle"
 			);
 		} catch(Exception e) {
 			e.printStackTrace();
 			exit();
 		}
	}

	public void list() {
		System.out.println();
 		System.out.println("[게시물 목록]"); 
		System.out.println("-------------------------------------------------------------------");
 		System.out.printf("%-6s%-12s%-16s%-40s\n", "no", "writer", "date", "title");
 		System.out.println("-------------------------------------------------------------------");
 		try {
 			String sql = "" +
 			"SELECT bno, btitle, bcontent, bwriter, bdate " +
 			"FROM boards " +
 			"ORDER BY bno DESC";
 			PreparedStatement pstmt = conn.prepareStatement(sql);
 			ResultSet rs = pstmt.executeQuery();
 			while(rs.next()) { 
 				Board board = new Board();
 				board.setBno(rs.getInt("bno"));
 				board.setBtitle(rs.getString("btitle"));
 				board.setBcontent(rs.getString("bcontent"));
 				board.setBwriter(rs.getString("bwriter"));
 				board.setBdate(rs.getDate("bdate"));
 				System.out.printf("%-6s%-12s%-16s%-40s \n",
 				board.getBno(),
 				board.getBwriter(),
 				board.getBdate(),
 				board.getBtitle());
 			}
 			rs.close();
 			pstmt.close();
 		} catch(SQLException e) {
 			e.printStackTrace();
 			exit();
 		}
 		mainMenu();
	}

	public void mainMenu() {
 		System.out.println();
 		System.out.println("-------------------------------------------------------------------");
 		System.out.println("메인 메뉴: 1.Create | 2.Read | 3.Clear | 4.Join | 5.Exit");
 		System.out.print("메뉴 선택: ");정답
 		String menuNo = scanner.nextLine();
 		System.out.println();
 		switch(menuNo) {
 			case "1" -> create();
 			case "2" -> read();
 			case "3" -> clear();
 			case "4" -> join();
 			case "5" -> exit();
 		}
	}

	public void create() {
		Board board = new Board();
 		System.out.println("[새 게시물 입력]");
 		System.out.print("제목: ");
 		board.setBtitle(scanner.nextLine());
 		System.out.print("내용: ");
 		board.setBcontent(scanner.nextLine());
 		System.out.print("작성자: ");
 		board.setBwriter(scanner.nextLine());
 		System.out.println("-------------------------------------------------------------------");
 		System.out.println("보조 메뉴: 1.Ok | 2.Cancel");
 		System.out.print("메뉴 선택: ");
 		String menuNo = scanner.nextLine();
 		if(menuNo.equals("1")) {
 			try {
 				String sql = "" +
 					"INSERT INTO boards (bno, btitle, bcontent, bwriter, bdate) " +
 					"VALUES (SEQ_BNO.NEXTVAL, ?, ?, ?, SYSDATE)";
 				PreparedStatement pstmt = conn.prepareStatement(sql);
 				pstmt.setString(1, board.getBtitle());
 				pstmt.setString(2, board.getBcontent());
 				pstmt.setString(3, board.getBwriter());
 				pstmt.executeUpdate();
 				pstmt.close();
 			} catch (Exception e) {정답
 				e.printStackTrace();
 				exit();
 			}
 		}
 		list();
	}

	public void read() {
		System.out.println("[게시물 읽기]");
 		System.out.print("bno: ");
 		int bno = Integer.parseInt(scanner.nextLine());
 		try {
 			String sql = "" +
 				"SELECT bno, btitle, bcontent, bwriter, bdate " +
 				"FROM boards " +
 				"WHERE bno= ?";
 			PreparedStatement pstmt = conn.prepareStatement(sql);
 			pstmt.setInt(1, bno);
 			ResultSet rs = pstmt.executeQuery();
 			if(rs.next()) {
 				Board board = new Board();
 				board.setBno(rs.getInt("bno"));
 				board.setBtitle(rs.getString("btitle"));
 				board.setBcontent(rs.getString("bcontent"));
 				board.setBwriter(rs.getString("bwriter"));
 				board.setBdate(rs.getDate("bdate"));
 				System.out.println("#############");
 				System.out.println("번호: " + board.getBno());
 				System.out.println("제목: " + board.getBtitle());
 				System.out.println("내용: " + board.getBcontent());
 				System.out.println("작성자: " + board.getBwriter());
 				System.out.println("날짜: " + board.getBdate());
	 	 	 	System.out.println("--------------------------------------------------------------");
 				System.out.println("보조 메뉴: 1.Update | 2.Delete | 3.List");
 				System.out.print("메뉴 선택: ");
 				String menuNo = scanner.nextLine();정답
 				System.out.println();
 				if(menuNo.equals("1")) {
 					update(board);
 				} else if(menuNo.equals("2")) {
 					delete(board);
 				}
 			}
 			rs.close();
 			pstmt.close();
 		} catch (Exception e) {
 			e.printStackTrace();
 			exit();
 		}
 		list();
	}

	public void update(Board board) {
		System.out.println("[수정 내용 입력]");
 		System.out.print("제목: ");
 		board.setBtitle(scanner.nextLine());
 		System.out.print("내용: ");
 		board.setBcontent(scanner.nextLine());
 		System.out.print("작성자: ");
 		board.setBwriter(scanner.nextLine());
 		System.out.println("-------------------------------------------------------------------");
 		System.out.println("보조 메뉴: 1.Ok | 2.Cancel");
 		System.out.print("메뉴 선택: ");
 		String menuNo = scanner.nextLine();
 		if(menuNo.equals("1")) {
 			try {
 				String sql = "" +
 					"UPDATE boards SET btitle= ?, bcontent= ?, bwriter= ? " +
 					"WHERE bno= ?";
 				PreparedStatement pstmt = conn.prepareStatement(sql);
 				pstmt.setString(1, board.getBtitle());정답
 				pstmt.setString(2, board.getBcontent());
 				pstmt.setString(3, board.getBwriter());
 				pstmt.setInt(4, board.getBno());
 				pstmt.executeUpdate();
 				pstmt.close();
 			} catch (Exception e) {
 				e.printStackTrace();
 				exit();
 			}
 		}
 		list();
	}

	public void delete(Board board) {
 		try {
 			String sql = "DELETE FROM boards WHERE bno= ?";
 			PreparedStatement pstmt = conn.prepareStatement(sql);
 			pstmt.setInt(1, board.getBno());
 			pstmt.executeUpdate();
 			pstmt.close();
 		} catch (Exception e) {
 			e.printStackTrace();
 			exit();
 		}
 		list();
	}

	public void clear() {
 		System.out.println("[게시물 전체 삭제]");
 		System.out.println("-------------------------------------------------------------------");
 		System.out.println("보조 메뉴: 1.Ok | 2.Cancel");
 		System.out.print("메뉴 선택: ");
 		String menuNo = scanner.nextLine();
 		if(menuNo.equals("1")) {
 			try {
 				String sql = "TRUNCATE TABLE boards";정답
 				PreparedStatement pstmt = conn.prepareStatement(sql);
 				pstmt.executeUpdate();
 				pstmt.close();
 			} catch (Exception e) {
	 			e.printStackTrace();
 				exit();
 			}
 		}
 		list();
	}

	public void join() {
		User user = new User();
 		System.out.println("[새 사용자 입력]");
 		System.out.print("아이디: ");
 		user.setUserId(scanner.nextLine());
 		System.out.print("이름: ");
 		user.setUserName(scanner.nextLine());
 		System.out.print("비밀번호: ");
 		user.setUserPassword(scanner.nextLine());
 		System.out.print("나이: ");
 		user.setUserAge(Integer.parseInt(scanner.nextLine()));
 		System.out.print("이메일: ");
 		user.setUserEmail(scanner.nextLine()); 
 		System.out.println("-------------------------------------------------------------------");
 		System.out.println("보조 메뉴: 1.Ok | 2.Cancel");
 		System.out.print("메뉴 선택: ");
 		String menuNo = scanner.nextLine();
 		if(menuNo.equals("1")) {
 			try {
 				String sql = "" +
 					"INSERT INTO users (userid, username, userpassword, userage, useremail) " +
 					"VALUES (?, ?, ?, ?, ?)";
 				PreparedStatement pstmt = conn.prepareStatement(sql);
 				pstmt.setString(1, user.getUserId());정답
 				pstmt.setString(2, user.getUserName());
 				pstmt.setString(3, user.getUserPassword());
 				pstmt.setInt(4, user.getUserAge());
 				pstmt.setString(5, user.getUserEmail());
 				pstmt.executeUpdate();
 				pstmt.close();
 			} catch (Exception e) {
 				e.printStackTrace();
 				exit();
 			}
 		}
 		list();
	}

	public void exit() {
 		if(conn != null) {
 			try {
 				conn.close();
 			} catch (SQLException e) {
 			}
 		}
 		System.out.println("** 게시판 종료 **");
 		System.exit(0);
	}

	public static void main(String[] args) {
 		BoardExample boardExample = new BoardExample();
 		boardExample.list();
	}
}