如何使用JDBC瘦驱动和Oracle钱包连接到Oracle Cloud DDBB

Question

我正在遵循使用IDE IntelliJ从java应用程序连接到Oracle云数据库的指南。

我满足先决条件，因为：

• 我在Oracle云服务中有一个数据库
• 我下载了钱包，并将文件放在工作区的src目录中。
• 我使用的是最后一个JDK 14
• 我正在使用OJDBC8.jar
• 并且我还下载了oraclepki、osdt_cert和osdt_core JAR，所有这些JAR都作为java库添加到我的测试项目中

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.3JDBC驱动程序切换到一个连接字符串，它应该适用于我的设置，那么我会得到错误：无效的连接字符串格式，有效的格式是：“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");

我修改了一个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());
        }
    }
}

Answer 1

使用独立的jdk 8和jdk 14以及Intellij Community edition（第一次运行防火墙阻止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