20201103_27 A와 B 돈인출, review

• DELETE

        try (final Connection conn = DriverManager.getConnection(JDBC_URL, DB_USER, DB_PASS)) {
        	final String sql = "DELETE FROM jdbc_example WHERE id <=2";
        	final PreparedStatement pstmt = conn.prepareStatement(sql);
        	final int affectedRows	= pstmt.executeUpdate();
        	System.out.println(affectedRows);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

id가 2이하인것 삭제

결과값 2 (영향받은 행)

 

SQL결과)

• 실행 단계
  1. SQL 구문 분석
  2. 컴파일
  3. 실행
  
  statement는 1~3단계 매번 실행
  prepared statement는 1~3단계 캐시 이후에 생략 

---

 

JAVA)

package ko.or.iei;

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

import oracle.jdbc.driver.OracleSQLException;

public class Application7 {
    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 = "Rudtm23.";

    public static void main(String[] args) throws ClassNotFoundException {
        // 1. 클래스 로드
        Class.forName("oracle.jdbc.driver.OracleDriver");

        // 2. 데이터베이스에 접속
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(JDBC_URL, DB_USER, DB_PASS);
            conn.setAutoCommit(false);

            String sql1 = "UPDATE bank_account SET money = 0 WHERE username = 'A'";
            String sql2 = "UPDATE bank_account SET money = 1000 WHERE username = 'B'";

            // A의 계좌를 0으로
            final PreparedStatement pstmt1 = conn.prepareStatement(sql1);
            final int affectedRows1 = pstmt1.executeUpdate();
            System.out.println("1: " + affectedRows1);

            if (System.currentTimeMillis() > 0) {
                throw new SQLException("롤백 테스트");
            }

            // B의 계좌를 1000으로
            final PreparedStatement pstmt2 = conn.prepareStatement(sql2);
            final int affectedRows2 = pstmt2.executeUpdate();
            System.out.println("2: " + affectedRows2);

            conn.commit();
            conn.setAutoCommit(true);
        } catch (SQLException throwables) {
            if (conn != null) {
                try {
                    System.out.println("제발 롤백");
                    conn.rollback();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            throwables.printStackTrace();
        } finally {
            close(conn);
        }
    }

    private static void close(AutoCloseable closeable) {
        try {
            closeable.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
       }
}
       

 

 

 

 

throw new SQLException("롤백 테스트");

이부분을 주석처리하면 잘된다

 

 

 

 

 

 

SQL)

DROP TABLE bank_account CASCADE CONSTRAINTS;
CREATE TABLE bank_account(
    id NUMBER PRIMARY KEY,
    username VARCHAR2(128) NOT NULL,
    money NUMBER DEFAULT 0
);
SELECT * FROM bank_account;

--초기화
INSERT INTO bank_account VALUES(1, 'A', 1000);
INSERT INTO bank_account VALUES(2, 'B', 0);

--계좌복구
UPDATE bank_account SET money = 1000 WHERE username = 'A';
UPDATE bank_account SET money = 0 WHERE username = 'B';

COMMIT;
ROLLBACK;

---

복습

package kr.or.iei;

import java.sql.*;

public class Application8 {

    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 {
        // 1. 드라이버 클래스 로드
        Class.forName("oracle.jdbc.driver.OracleDriver");

        // 2. 커넥션을 얻는다 - DB에 접속(세션)
        try (final Connection conn = DriverManager.getConnection(JDBC_URL, DB_USER, DB_PASS)) {
            // 3. 데이터베이스 작업
                // 1. executeQuery - ResultSet
            test1(conn);
                // 2. executeUpdate - int (영향 받은 행의 수)
            test2(conn);
                // 3. 트랜잭션 제어
            test3(conn);
            // 4. 연결 종료
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    private static void test3(Connection conn) throws SQLException {
        conn.setAutoCommit(false);  // auto commit 설정. true면 켬.
        conn.commit();  // commit - 물리적인 저장소에 반영됨
        conn.rollback();    // rollback - 반영할 내용들이 취소됨
        conn.getAutoCommit();   // 현재 auto commit 설정 상태를 나타냄. true면 켜진 상태.
    }

    private static void test2(Connection conn) throws SQLException {
        // 1. 파라미터 세팅 - setXXXXX(파라미터번호, 값)
        // 2. executeUpdate의 결과는 영향 받은 행의 개수를 나타냄
        final String sql = "INSERT INTO bank_account VALUES (?, ?, ?)";
        final PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setLong(1, 10);
        pstmt.setString(2, "chwon");
        pstmt.setLong(3, 10000);
        final int affectedRows = pstmt.executeUpdate();
        System.out.println(affectedRows);
    }

    private static void test1(Connection conn) throws SQLException {
        // 1. prepared statement
        // 2. executeQuery로 SELECT문 실행
        // 3. 그 결과는 ResultSet임
        // 4. cursor
        final String sql = "SELECT * FROM bank_account";
        final PreparedStatement pstmt = conn.prepareStatement(sql);
        final ResultSet resultSet = pstmt.executeQuery();

        while(resultSet.next()) {
            final int id = resultSet.getInt("id");
            final String username = resultSet.getString("username");
            final int money = resultSet.getInt("money");
            System.out.println(id);
            System.out.println(username);
            System.out.println(money);
            System.out.println("---------------------");
        }
    }
}