[자바] 29일차 - JDBC (트랜젝션 처리, PreparedStatement, ResultSetMetaData)

 

2012/09/06 - [Dev. 640시간 뭉개기/강의내용정리] - [자바] 29일차 - JDBC (트랜젝션 처리, PreparedStatement, ResultSetMetaData)

 

1. PreparedStatement

 

package pstmt;

 

import java.sql.*;

 

import conn.*;

 

public class TestPstmt1 {

 

    public static void main(String[] args) {

        new TestPstmt1().execute();

       

    }

 

    public void execute() {

        Connection conn = null;

        PreparedStatement pstmt = null;

        ResultSet rs = null;

       

        try {

            conn = MyConn.getConnection();

            pstmt = conn.prepareStatement (

                    "select * from dept where lower(dname) like lower('%'||?||'%')"

                    );

            // setString에 의해서 'a'로 바뀐다.

            pstmt.setString(1, "a");

            rs = pstmt.executeQuery();

            while (rs.next()) {

                System.out.println(rs.getString(1) + ", " +

                                rs.getString(2) + ", " + rs.getString(3) );

            }

        } catch (SQLException e) {

           

            e.printStackTrace();

        }finally {

            try { if(rs != null) rs.close(); }

            catch (SQLException e ) { e.printStackTrace(); }

            try { if(pstmt != null) pstmt.close(); }

            catch (SQLException e ) { e.printStackTrace(); }

            try { if(conn != null) conn.close(); }

            catch (SQLException e ) { e.printStackTrace(); }

        }

    }

}

 

 

package pstmt;

 

import java.sql.*;

import java.util.*;

 

import conn.*;

 

public class TestPstmt2 {

 

    public static void main(String[] args) {

        new TestPstmt2().execute();

    }

 

    public void execute() {

        Scanner sc = new Scanner(System.in);

        System.out.print("부서번호 > ");

        String deptno = sc.next();

       

        System.out.print("부서명 > ");

        String dname = sc.next();

       

        System.out.print("부서위치 > ");

        String loc = sc.next();

       

        //PreparedStatement 를 사용해서 Dept 테이블에 insert하라

       

        Connection conn = null;

        PreparedStatement pstmt = null;

       

        try {

            conn = MyConn.getConnection();

            pstmt = conn.prepareStatement("insert into dept(deptno,dname,loc) values (?,?,?)");

           

            pstmt.setString(1, deptno);

            pstmt.setString(2, dname);

            pstmt.setString(3, loc);

           

            int result = pstmt.executeUpdate();

            System.out.println(result + "행이 입력되었습니다.");

           

           

        } catch (SQLException e) {

            System.out.println(e.getMessage());

        } finally {

            try { if(pstmt!=null) pstmt.close(); } catch (SQLException e) {e.printStackTrace();}

            try { if(conn!=null) conn.close(); } catch (SQLException e) {e.printStackTrace();}

        }

       

    }

 

}

 

 

 

 PreparedStatement VS Statement
     똑같은 쿼리문에 값만 바꿔서 반복적으로 해야 되는 경우 : PreparedStatement
     다른 쿼리문을 번갈아가면서 써야하는 경우 : Statement

 

 

2. 트랜젝션 (Transaction )

       
         Atomicity(원자성)   : 실행이 될거면 다 되던가 아예 안되던가 (쿼리문 2개 이상 실행시)
         Consistency(지속성) : 처리중일때 항상 일관된 데이터를 처리할 수 있어야 한다.
         Isolation(독립성)   : 다른 트랜젝션과 별개로 하나의 트렌젝션은 독립적으로 처리되어야 한
         Durability(내구성)  : 트랜젝션이 처리된 이후에는 계속 존재해야 한다.

 

package tran;

 

import java.sql.*;

 

import conn.*;

 

public class TestTran {

 

    public static void main(String[] args) {

        new TestTran().execute();

    }

 

    public void execute() {

Connection conn = null;

        Statement stmt = null;

        try {

            conn = MyConn.getConnection();

           

            // 자동커밋불가로 변경

            conn.setAutoCommit(false);

           

            stmt = conn.createStatement();

            stmt.execute("insert into dept values(11, '11', '11')");

            stmt.execute("update dept set dname='22' where deptno=11");

            conn.commit(); // close() 할 때 자동 commit되지만 오류 발생할 수 있으므로 반드시 명시한다.

           

        } catch (SQLException e) {

            e.printStackTrace();

           

            // 예외가 발생했을때 되돌린다.

            try { conn.rollback(); }

            catch (SQLException e1) { e1.printStackTrace(); }

           

        } finally {

            try { if(stmt != null) stmt.close(); }

            catch (SQLException e) { e.printStackTrace(); }

            try { if(conn != null) conn.close(); }

            catch (SQLException e) { e.printStackTrace(); }

        }

    }

 

}

 

 

 

 

3. ResultSetMetaData

: ResultSet에 대한 상세 정보를 가진다.

 

package rsmd;

 

import java.sql.*;

 

import conn.*;

 

public class TestRsmd {

 

    public static void main(String[] args) {

        new TestRsmd().execute();

    }

 

    public void execute() {

        Connection conn = null;

        Statement stmt = null;

        ResultSet rs = null;

       

        try {

            conn = MyConn.getConnection();

            stmt = conn.createStatement();

            rs = stmt.executeQuery("select * from emp");

           

            // ResultSetMetaData : ResultSet에 대한 상세 정보

            ResultSetMetaData rsmd = rs.getMetaData();

            int count = rsmd.getColumnCount();

            System.out.println(count);

            for (int i=1; i < count+1; i++) {

                // 컬럼의 별칭을 가져온다.(별칭 없으면 원래 이름)

                String label = rsmd.getColumnLabel(i);

                String name = rsmd.getColumnName(i);

                System.out.print(label+" ");

//                System.out.print(name+" ");

            }

            System.out.println();

            while (rs.next()) {

                for (int i = 1; i <=count; i++) {

                    System.out.print(rs.getString(i) + " ");

                }

                System.out.println();

            }

           

        } catch (SQLException e) {

            e.printStackTrace();

        } finally {

            try { if(rs != null) rs.close(); }

            catch(SQLException e) { e.printStackTrace(); }

            try { if(stmt != null) stmt.close(); }

            catch(SQLException e) { e.printStackTrace(); }

            try { if(conn != null) conn.close(); }

            catch(SQLException e) { e.printStackTrace(); }

        }

    }

 

}