Appendix A. tbJDBC 예제

내용 목차

A.1. JdbcTest.class

본 장에서는 tbJDBC를 이용하여 작성한 기본 프로그램의 전체 소스 코드를 설명한다.

다음은 tbJDBC를 이용하여 JdbcTest 클래스 파일을 작성한 프로그램 소스 코드이다.

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

public class JdbcTest
{
    Connection conn;

    public static void main(String[] args) throws Exception
    {
        JdbcTest test = new JdbcTest();

        test.connect();                  
        test.executeStatement();             
        test.executePreparedStatement();     
        test.executeCallableStatement();     
        test.disconnect();                   
    }
  
    private void connect() throws ClassNotFoundException
    {
        Class.forName("com.tmax.tibero.jdbc.TbDriver");

        try {
            conn = DriverManager.getConnection(
                    "jdbc:tibero:thin:@localhost:8629:tibero", "tibero", "tmax");
        } catch (SQLException e) {
            System.out.println("connection failure!");
            System.exit(-1);
        }
        System.out.println("Connection success!");
    }

    private void executeStatement() throws SQLException
    {
        String dropTable   = "drop table emp";
        String createTable = "create table emp (id   number, "+
                             " name varchar(20), salary number)";
        String InsertTable = "insert into emp values(1000, 'Park', 5000)";

        Statement stmt = conn.createStatement();

        try {
            stmt.executeUpdate(dropTable);
         } catch(SQLException e) {
           // if there is not the table
         }

         stmt.executeUpdate(createTable);    
         stmt.executeUpdate(InsertTable);

         stmt.close();
    }

    private void executePreparedStatement() throws SQLException
    {
        PreparedStatement pstmt = conn
        .prepareStatement("select name from emp where id = ?");

        pstmt.setString(1, "1000");

        ResultSet rs = pstmt.executeQuery();

        while (rs.next()) {
            System.out.println(rs.getString(1));
        }
        pstmt.close();
    }

    private void executeCallableStatement() throws SQLException
    {
        String callSQL = " CREATE PROCEDURE testProc "+
                         " (ID_VAL IN NUMBER, SAL_VAL IN OUT NUMBER) as " + 
                         " BEGIN" +
                         "   update emp" +
                         "      set salary = SAL_VAL" +
                         "    where id = ID_VAL;" + 
                         "   select salary into SAL_VAL" +
                         "     from emp" +
                         "    where id = ID_VAl;" +
                         " END;";

        String dropProc = "DROP PROCEDURE testProc";

        Statement stmt = conn.createStatement();

        try {
            stmt.executeUpdate(dropProc);
        } catch(SQLException e) {
            // if there is not the procedure
        }
        
        stmt.executeUpdate(callSQL);

        CallableStatement cstmt = conn.prepareCall("{call testProc(?, ?)}");
        cstmt.setInt(1, 1000);
        cstmt.setInt(2, 7000);
        cstmt.registerOutParameter(2, Types.INTEGER);
        cstmt.executeUpdate();

        int salary = cstmt.getInt(2);
        System.out.println(salary);

        stmt.close();
        cstmt.close();
    }

    private void disconnect() throws SQLException
    {
        if (conn != null)    
            conn.close();
    }
}