时间戳

首页 > 解决方案 > 如何使用 JDBC Thin Driver 和 Oracle Wallet 连接到 Oracle Cloud DDBB

java - 如何使用 JDBC Thin Driver 和 Oracle Wallet 连接到 Oracle Cloud DDBB

问题描述

我正在按照指南使用 IDE IntelliJ 从 Java 应用程序连接到 Oracle 云数据库。

我满足先决条件，因为：

推荐的 BD_URL 永远不会起作用。我总是得到：java.net.UnknownHostException 和 oracle.net.ns.NetException：网络适配器无法建立连接

DB_URL= "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(HOST=testgerard_high)(PORT=1521)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=testgerard_high)))"

然后我在Oracle支持中发现可以将它添加到钱包目录中，但是同样的问题。

DB_URL= "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(HOST=testgerard_high)(PORT=1521)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=testgerard_high))(SECURITY = (MY_WALLET_DIRECTORY = src\\Wallet_testGerard)))"

如果我使用适用于我的设置的 18.3 JDBC 驱动程序切换到连接字符串，则会收到错误消息：连接字符串格式无效，有效格式为：“host:port:sid”

DB_URL="jdbc:oracle:thin:@testgerard_high?TNS_ADMIN=src\\Wallet_testGerard";

最后，我在这里看到了一种从 BD_URL 通知钱包文件夹的方法，因此我不会收到无效格式异常：

System.setProperty("oracle.net.tns_admin","src\\Wallet_testGerard");

现在它正在尝试连接，但在 60 秒后失败，出现以下异常：sun.security.provider.certpath.SunCertPathBuilderException：无法找到请求目标的有效证书路径

我改编了一个 oracle 示例，这是我的代码：

import java.sql.SQLException;
import java.sql.DatabaseMetaData; 
import oracle.jdbc.pool.OracleDataSource;
import oracle.jdbc.OracleConnection;


public class OracleDataSourceSample
{
    final static String DB_URL="jdbc:oracle:thin:@testgerard_high";
    //final static String DB_URL="jdbc:oracle:thin:@testgerard_high?TNS_ADMIN=src\\Wallet_testGerard";
    //final static String DB_URL= "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(HOST=testgerard_high)(PORT=1521)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=testgerard_high))(SECURITY = (MY_WALLET_DIRECTORY = src\\Wallet_testGerard)))";

    final static String DB_USER = "hr";
    final static String DB_PASSWORD = "hr";

    public static void main (String args[]) throws SQLException, ClassNotFoundException {

        System.setProperty("oracle.net.tns_admin","src\\Wallet_testGerard");
        Class.forName("oracle.jdbc.driver.OracleDriver");
        OracleDataSource ods = new OracleDataSource();
        ods.setURL(DB_URL);
        ods.setUser(DB_USER);
        ods.setPassword(DB_PASSWORD);

        // With AutoCloseable, the connection is closed automatically.
        try (OracleConnection connection = (OracleConnection)
                ods.getConnection()) {
            // Get the JDBC driver name and version
            DatabaseMetaData dbmd = connection.getMetaData();
            System.out.println("Driver Name: " + dbmd.getDriverName());
            System.out.println("Driver Version: " +
                    dbmd.getDriverVersion());
            System.out.println("Database Username is: " +
                    connection.getUserName());
        }
    }
}

标签： javaoraclejdbc

解决方案

使用独立的 jdk 8 和 jdk 14 以及 Intellij 社区版进行测试（首次运行防火墙阻止了 intellij）。

    import java.io.IOException;
    import java.io.InputStream;
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.Properties;
    import oracle.jdbc.pool.OracleDataSource;
    import oracle.jdbc.OracleConnection;
    import java.sql.DatabaseMetaData;
    
    
    public class SalesConnection
    {
        final static String DB_URL="jdbc:oracle:thin:@oci_adw_high";
        final static String DB_USER = "xxxx";
        final static String DB_PASSWORD = "xxxxx";
    
        public static void main (String args[]) throws SQLException, ClassNotFoundException {
    
    
             System.setProperty("oracle.net.tns_admin","C:\\app\\oracle\\product\\19\\dbhome_1\\network\\admin");
             System.setProperty("oracle.jdbc.fanEnabled","false");
    
            Class.forName("oracle.jdbc.driver.OracleDriver");
            OracleDataSource ods = new OracleDataSource();
            ods.setURL(DB_URL);
            ods.setUser(DB_USER);
            ods.setPassword(DB_PASSWORD);
    
            // With AutoCloseable, the connection is closed automatically.
            try (OracleConnection connection = (OracleConnection)
                    ods.getConnection()) {
                // Get the JDBC driver name and version
                DatabaseMetaData dbmd = connection.getMetaData();
                System.out.println("Driver Name: " + dbmd.getDriverName());
                System.out.println("Driver Version: " +
                        dbmd.getDriverVersion());
                System.out.println("Database Username is: " +
                        connection.getUserName());
                printSales(connection);
            }
        }
    
        public static void printSales(Connection connection) throws SQLException {
            // Statement and ResultSet are AutoCloseable and closed automatically.
            try (Statement statement = connection.createStatement()) {
              try (ResultSet resultSet = statement
                  .executeQuery("select /* Java Console */PROD_ID, CUST_ID from sales fetch first 10 rows only ")) {
                System.out.println("PROD_ID" + "  " + "CUST_ID");
                System.out.println("---------------------");
                while (resultSet.next())
                  System.out.println(resultSet.getString(1) + " "
                      + resultSet.getString(2) + " ");
              }
            }
          }
}

尝试运行此代码更改表和列名

此代码的来源

编辑： 从命令提示符编译和执行

javac -cp "C:\ojdbc8-full\*;" SalesConnection.java

java -cp "C:\ojdbc8-full\*;" SalesConnection

推荐阅读