4. JDBC - 트랜잭션 처리 & properties파일에서 정보읽어오기.

 

1. 트랜잭션 처리

 

1) 트랜잭션:분리되어서는 안되는 연속적으로 처리되어야 할 작업 단위.

 

2) JDBC에서의 트랜잭션 처리
  JDBC에서는 자동커밋이 기본으로 설정되어 있음. 트랜잭션 처리를 하려면 Connection객체에서 설정한다.
 

    Connection con= ...
     con.setAutoCommit(false);//자동커밋해제
     ...
     con.commit();//커밋하기
     또는
     con.rollback();//작업취소하기

 

 

 

 

2. 테스트

1) 테이블

 

members 테이블

 drop table MEMBERS cascade constraints;

 

 CREATE TABLE MEMBERS(

    ID VARCHAR2(10) PRIMARY KEY,

    PWD VARCHAR2(10),

    EMAIL VARCHAR2(15),

    PHONE VARCHAR2(20),

    REGDATE DATE);

 

2) 자바

 

ex> members테이블에 insert하기. auto commit이 true아님.

Test03.java

package test01.jdbc;

 

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

class MyJdbc03{
    public MyJdbc03() {
        Connection con = null;
        Statement stmt = null;

 

        try{
            Class.forName("oracle.jdbc.OracleDriver");
            String url = "jdbc:oracle:thin:@localhost:1521:xe";
            con = DriverManager.getConnection(url, "scott", "tiger");

 

            System.out.println("dbms 접속성공");

 

            stmt = con.createStatement();
           
            con.setAutoCommit(false);
           
            String sql  = "INSERT INTO MEMBERS VALUES ('hong1','1','song1','000', sysdate )";
            String sql1 = "INSERT INTO MEMBERS VALUES ('lee1','1','song1@fjd','000', sysdate )";
           
            stmt.executeUpdate(sql);
            stmt.executeUpdate(sql1);
           
            con.commit();
            System.out.println("데이터 추가 성공");
           
        }catch(ClassNotFoundException ce){
            System.out.println(ce.getMessage());
        }catch(SQLException e){
            System.out.println(e.getMessage());
            try{
                con.rollback();
                System.out.println("오류로 인해서 작업이 취소되었습니다.");
            }catch(SQLException se){
                System.out.println(se.getMessage());
            }
        }finally{
            try{
                if(stmt!=null) stmt.close();
                if(con!=null) con.close();
            }catch(SQLException se){
               
            }
        }
    }
}
public class Test03 {
    public static void main(String[] args) {
        new MyJdbc03();
    }
}

 

 

EX>

ID,PWD,E-MAIL,포인트 를 입력받아 DB에 저장하고, 조회하기. (transaction 처리)

 

테이블, sequence 생성.

point 테이블

CREATE TABLE POINT(

NUM NUMBER PRIMARY KEY,

POINT NUMBER(5),

ID VARCHAR2(10) REFERENCES MEMBERS(ID)

);

 

 

CREATE SEQUENCE POINT_SEQ;

 

 

Test04.java

package test01.jdbc;

 

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

import test.db.DBConnection;

 

class MyJdbc04{
    Connection con;
    public MyJdbc04(){
        try{
            con=DBConnection.getCon();
            con.setAutoCommit(false);

            System.out.println("DB접속 성공!");
        }catch(SQLException se){
            System.out.println(se.getMessage());
        }
    }
    public void disConnect(){
        try{
            if(con!=null) con.close();
        }catch(SQLException se){
            System.out.println(se.getMessage());
        }
    }
    public void insert(){
        Scanner scan = new Scanner(System.in);

        System.out.println("회원아이디");
        String id = scan.next();
        System.out.println("회원비밀번호");
        String pwd = scan.next();
        System.out.println("회원이메일");
        String email = scan.next();
        System.out.println("회원전화번호");
        String phone = scan.next();

        System.out.println("적립할 포인트점수");
        int point = scan.nextInt();

 

        String sql1 = "insert into members values (?,?,?,?,sysdate)";
        String sql2 = "insert into point values (point_seq.nextval,?,?)";

 

        PreparedStatement pstmt1 = null;
        PreparedStatement pstmt2 = null;

        try{
            pstmt1 = con.prepareStatement(sql1);
            pstmt2 = con.prepareStatement(sql2);

            pstmt1.setString(1, id);
            pstmt1.setString(2, pwd);
            pstmt1.setString(3, email);
            pstmt1.setString(4, phone);
            pstmt1.executeUpdate();

 

            pstmt2.setInt(1, point);
            pstmt2.setString(2, id);
            pstmt2.executeUpdate();

 

            con.commit();
            System.out.println("회원가입이 성공적으로 이루어 졌습니다.");

        }catch(SQLException e){
            System.out.println(e.getMessage());
            try{
                con.rollback();
                System.out.println("오류가 발생되어 회원가입에 실패했습니다.");
            }catch(SQLException s){
                System.out.println(s.getMessage());
            }
        }
    }
    public void showList(){
        PreparedStatement pstmt =null;
        ResultSet rs = null;
       
        String sql = "select m.id  as id, m.pwd  as pwd, m.email  as email, p.point as point from members m, point p where m.id = p.id";
        try{
            pstmt = con.prepareStatement(sql);
            rs = pstmt.executeQuery();
           
            while(rs.next()){
                System.out.println( "id: " + rs.getString("id"));
                System.out.println( "pwd: " + rs.getString("pwd"));
                System.out.println( "email: " + rs.getString("email"));
                System.out.println( "email: " + rs.getInt("point"));
            }
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }finally{
            try{
                if(pstmt!=null) pstmt.close();
                if(rs!=null) rs.close();
            }catch(SQLException e){
                System.out.println(e.getMessage());
            }
        }
    }
}
public class Test04 {
    public static void main(String[] args) {
        MyJdbc04 mj = new MyJdbc04();

        mj.insert();
        mj.showList();
        mj.disConnect();
    }
}

 

 

 

 

properties파일에서 정보읽기

EX> db.properties파일에서 접속 정보 읽어와서 DB접속하기.

 

db.properties

## Database Connect Info

url=jdbc:oracle:thin:@127.0.0.1:1521:xe

user=hr

pwd=hr

 

Test05_Properties.java

package test01.jdbc;

 

import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

import test.db.DBConnection;

 

class MyJdbc05{
    Connection con = null;
    public MyJdbc05(){
        Properties prop = new Properties();
        Connection con = null;
        try{
            Reader reader = new FileReader("db.properties");
            prop.load(reader);
            String url = prop.getProperty("url");
            String user = prop.getProperty("user");
            String pwd = prop.getProperty("pwd");
           
            System.out.println("url:" + url + " user:" + user + " pwd:" + pwd);
           
            Class.forName("oracle.jdbc.OracleDriver");
            con = DriverManager.getConnection(url, user, pwd);
            System.out.println("dbms 접속성공");
            System.out.println("con:" + con);
        } catch(SQLException se){
           
        } catch (ClassNotFoundException ce) {
            // TODO: handle exception
        } catch( FileNotFoundException fe){
           
        } catch( IOException ie){
           
        }
       
    }
}
public class Test05_Properties {
    public static void main(String[] args) {
        MyJdbc05 jm = new MyJdbc05();
    }
}