deepfree's archive

Linux Shell에서 JDK를 통해서 ojdbc11.jar등을 로드하여 직접 Connection을 생성하고 간단한 executeQuery를 실행해보는 방법입니다

# 실행하는 방법 (classpath에 ojdbc11.jar를 포함하여 Main.java 실행)
$ java -cp ojdbc11.jar Main.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

/**
 * Oracle JDBC 드라이버 로드 및 DB 접속 테스트
 * <p>java -cp ojdbc11.jar Main.java</p>
 */
public class Main {

    public static void main(String[] args) {
        //JDBC 드라이버 로드 테스트
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            System.out.println("oracle.jdbc.driver.OracleDriver Found!");
        } catch (ClassNotFoundException e) {
            System.out.println("Oracle JDBC Driver not found.");
            e.printStackTrace();
            //return;
        }
        try {
            Class.forName("oracle.jdbc.OracleDriver");
            System.out.println("oracle.jdbc.OracleDriver Found!");
        } catch (ClassNotFoundException e) {
            System.out.println("Oracle JDBC Driver not found.");
            e.printStackTrace();
            //return;
        }

        //접속테스트할 DB URL들
        // jdbc:oracle:thin:@(DESCRIPTION=(RETRY_COUNT=20)(RETRY_DELAY=3)(ADDRESS=(PROTOCOL=tcp)(HOST=X.X.X.X)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XXXXXX))(SECURITY=(SSL_SERVER_DN_MATCH=yes)))
        // jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(FAILOVER=yes)(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=X.X.X.X)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=X.X.X.X)(PORT=1521)))(CONNECT_DATA=(FAILOVER_MODE=(TYPE=select)(METHOD=basic))(SERVER=dedicated)(SERVICE_NAME=XXXXXX)))
        List<String> urls = List.of(
                "jdbc:oracle:thin:@(...)",
                "jdbc:oracle:thin:@(...)");
        for (String url : urls) {
            testConnectAndQuery(url);
        }
    }

    /**
     * 주어진 URL을 사용하여 Oracle 데이터베이스에 연결하고 간단한 쿼리를 실행합니다.
     * @param url 데이터베이스에 연결하기 위한 JDBC URL
     */
    private static void testConnectAndQuery(String url) {
        System.out.println("Try Connect... " + url);
        try (Connection connection = DriverManager.getConnection(
                url,
                "USERNAME",
                "PASSWORD")) {
            System.out.println("Connected to Oracle database!");
            //쿼리실행
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery("SELECT sysdate FROM dual");
            if (resultSet.next()) {
                System.out.println("SELECT sysdate FROM dual => " + resultSet.getString(1));
            }
            resultSet.close();
            statement.close();
        } catch (SQLException e) {
            System.out.println("Connection failed.");
            e.printStackTrace();
        }
    }
}