JDBC

JDBC-ODBC Bridge

JDBC-ODBC Bridge[1]는 ODBC를 통해서 JDBC 인터페이스로 데이터베이스를 사용하는 방법이다. Java에서 RDB를 엑세스하려면 JDBC를 이용해야 하는데 ODBC로 제공하고 있는 것이 있다면 이것을 JDBC 인터페이스로 접근할 수 있도록 JDBC-ODBC Bridge를 제공하고 있다.

ODBC 드라이버 만들기

ODBC 드라이버는 ODBC를 참고하여 만든다. 이 예에서는 mybank 드라이버를 만들었다.

JDBC-ODBC Bridge 예제

교과서에 나오는 예제 테이블 중 Customer와 Branch, 두 개의 테이블을 생성하고 이 테이블에 레코드를 삽입하는 예제이다.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
public class Banking {
    public static void main(String[] argv) {
        // Load the JDBC-ODBC bridge driver
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        } catch (ClassNotFoundException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
 
        // setup the properties
        java.util.Properties prop = new java.util.Properties();
        //prop.put("charSet", "Big5");
        //prop.put("user", username);
        //prop.put("password", password);
 
        // Connect to the database
        try {
            Connection con = DriverManager.getConnection("jdbc:odbc:mybank", prop);
 
            createSchema(con);
 
            insertCustomers(con);
            insertBranches(con);
 
            retrieveBranches(con);
 
            dropTables(con);
 
            con.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
 
    }
 
    private static void createSchema(Connection con) throws SQLException {
        String customer_schema = "CREATE TABLE customer ("
                                  + "customer_name CHAR(20), "
                                  + "customer_street VARCHAR(30), "
                                  + "customer_city VARCHAR(20), "
                                  + "PRIMARY KEY(customer_name));";
 
        String branch_schema   = "CREATE TABLE branch ("
                                  + "branch_name CHAR(15), "
                                  + "branch_city VARCHAR(30), "
                                  + "assets INTEGER, "
                                  + "PRIMARY KEY(branch_name));";
 
        Statement stmt = con.createStatement();
        stmt.execute(customer_schema);
        stmt.execute(branch_schema);
        stmt.close();
    }
 
    /**
     * Customer 테이블에 레코드를 삽입한다.
     */
    private static void insertCustomers(Connection con) throws SQLException {
        String[] queries = { "INSERT INTO customer VALUES('Adams', 'Spring', 'Pittsfield');",
                             "INSERT INTO customer VALUES('Brooks', 'Senator', 'Brooklyn');",
                             "INSERT INTO customer VALUES('Curry', 'North', 'Rye');",
                             "INSERT INTO customer VALUES('Glenn', 'Sand Hill', 'Woodside');",
                             "INSERT INTO customer VALUES('Green', 'Walnut', 'Stamford');",
                             "INSERT INTO customer VALUES('Hayes', 'Main', 'Harrison');",
                             "INSERT INTO customer VALUES('Johnson', 'Alma', 'Palo Alto');",
                             "INSERT INTO customer VALUES('Jones', 'MAIN', 'Harrison');",
                             "INSERT INTO customer VALUES('Lindsay', 'Park', 'Pittsfield');",
                             "INSERT INTO customer VALUES('Smith', 'North', 'Rye');",
                             "INSERT INTO customer VALUES('Turner', 'Puthnam', 'Stamford');",
                             "INSERT INTO customer VALUES('Williams', 'Nassau', 'Princeton');" };
 
        con.setAutoCommit(false);
        Statement stmt = con.createStatement();
        for(String query : queries) {
            stmt.executeUpdate(query);
        }
        stmt.close();
        con.commit();
        con.setAutoCommit(true);
    }
 
    private static void insertBranches(Connection con) throws SQLException {
        String[][] records = {  { "Brighton", "Brooklyn", "7100000", },
                                { "Downtown", "Brooklyn", "9000000", },
                                { "Mianus", "Horseneck", "400000", },
                                { "North Town", "Rye", "3700000", },
                                { "Perryridge", "Horseneck", "1700000", },
                                { "Pownal", "Bennington", "300000", },
                                { "Redwood", "Palo Alto", "2100000", },
                                { "Round Hill", "Horseneck", "8000000" } };
 
        con.setAutoCommit(false);
        PreparedStatement stmt = con.prepareStatement("INSERT INTO branch VALUES(?, ?, ?);");
 
        for(String[] record : records) {
            stmt.setString(1, record[0]);
            stmt.setString(2, record[1]);
            stmt.setInt(3, Integer.parseInt(record[2]));
            stmt.execute();
        }
 
        stmt.close();
        con.setAutoCommit(true);
    }
 
    private static void retrieveBranches(Connection con) throws SQLException {
        Statement stmt = con.createStatement();
        ResultSet rset = stmt.executeQuery("SELECT * FROM branch;");
        while(rset.next()) {
            System.out.println(rset.getString(1) + "\t" 
                             + rset.getString("branch_city")
                             + "\t" + rset.getInt(3));
        }
        stmt.close();
    }
 
    private static void dropTables(Connection con) throws SQLException {
        Statement stmt = con.createStatement();
        stmt.execute("DROP TABLE customer;");
        stmt.execute("DROP TABLE branch;");
        stmt.close();
    }
}