Spring boot web application not working with oracle wallet

Question

I am working on spring boot on both command line runner and web application. Both applications require to be implemented with oracle wallet, so I implemented the oracle wallet. The command line runner is able to connect to database using spring jdbc template using oracle datasource but same configuration is not been able to create the bean for datasource object. When same has been implemented with database user name and password, I am able to connect.

I am taking help from this post - [Connect to Oracle DB from Spring-jdbc with Oracle Wallet authentification

with code similar to,

System.setProperty("oracle.net.tns_admin", "path/to/your/tnsnames");

OracleDataSource ds = new OracleDataSource();

Properties props = new Properties();
props.put("oracle.net.wallet_location", "(source=(method=file)(method_data=(directory=path/to/your/wallet)))");
ds.setConnectionProperties( props );
ds.setURL("jdbc:oracle:thin:/@dbAlias"); //dbAlias should match what's in your tnsnames

return ds;

I have all my properties set from application.properties of boot application and I am getting null pointer exception on creating the datasource.

Any pointer or help in this regard will be much appreciated.

Albacore

Answer 1

After trying, I could figure out what we need to do when we need to include oracle wallet in spring boot.

1. In application.properties put two properties,
   A> spring.datasource.url=jdbc:oracle:thin:/@<DB_ALIAS_NAME>
   B> spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver

2. On boot runner/configuration class, 
   A> Define the dataSource bean like this,


 @Bean
   public DataSource dataSource() {
       OracleDataSource dataSource = null;
       try {
           dataSource = new OracleDataSource();
           Properties props = new Properties();
           String oracle_net_wallet_location = 
           System.getProperty("oracle.net.wallet_location");
           props.put("oracle.net.wallet_location", "(source=(method=file)(method_data=(directory="+oracle_net_wallet_location+")))");
           dataSource.setConnectionProperties(props);
           dataSource.setURL(url);
       } catch(Exception e) {
           e.printStackTrace();
       }
       return dataSource;
   }

   B> Define the jdbcTemplate bean as follows,


@Bean
   public JdbcTemplate jdbcTemplate(DataSource dataSource) {
       JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource());
       jdbcTemplate.setFetchSize(20000);
       return jdbcTemplate;
   }

3. Now we need to set jvm arguments in boot runner class like as follows,
   -Doracle.net.wallet_location=<PATH_TO_WALLET_DIR> -Doracle.net.tns_admin=<PATH_TO_WALLET_DIR>
   Note - <WALLET_DIR> should contain .sso, .p12 and .ora files. On external 
   server like tomcat, set above two variables on catalina.sh or catalina.bat 
   depending on your environment OS.

I hope this helps.
Thanks,
Sandip  

Albacore

For any multi threaded load test, always use Oracle 11.2.0.4 or up ojdbc driver, as 11.2.0.3 ojdbc driver does not have support for multi threaded access to oracle wallets. Will see some logs like PKIX error, 02002 and can not open cwallet.sso file if you are using 10.2.0.3 version of ojdbc on multithreads. – Albacore

Answer 2

add below in application properties 

spring.datasource.url=jdbc:oracle:thin:@db202007181319_medium?TNS_ADMIN=C:/wallet/Wallet_Name
spring.datasource.driverClassName=oracle.jdbc.driver.OracleDriver
spring.datasource.username=ADMIN
spring.datasource.password=yourpassword


Also pom entry as follows : - 

       <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc8</artifactId>
            <version>19.6.0.0</version>
        </dependency>

Inducement

Please add further details to expand on your answer, such as working code or documentation citations. – Dionnedionysia

Answer 3

In addition to the steps above, make sure to add these dependencies at the correct version level:

    <dependency>
        <groupId>com.oracle.database.security</groupId>
        <artifactId>osdt_cert</artifactId>
        <version>21.1.0.0</version>
    </dependency>
    <dependency>
        <groupId>com.oracle.database.security</groupId>
        <artifactId>osdt_core</artifactId>
        <version>21.1.0.0</version>
    </dependency>
    <dependency>
        <groupId>com.oracle.database.security</groupId>
        <artifactId>oraclepki</artifactId>
        <version>21.1.0.0</version>
    </dependency>

Melinite

Answer 4

This solution worked for me.

In build.gradle file you need these dependencies:

dependencies {
        // Oracle database
    runtimeOnly('com.oracle.database.jdbc:ojdbc8:21.1.0.0') {
        exclude group: 'com.oracle.database.ha', module: 'simplefan'
        exclude group: 'com.oracle.database.ha', module: 'ons' 
    }
    runtimeOnly 'com.oracle.database.jdbc:ucp:21.1.0.0'
    runtimeOnly 'com.oracle.database.security:oraclepki:21.1.0.0'   
    runtimeOnly 'com.oracle.database.security:osdt_cert:21.1.0.0'   
    runtimeOnly 'com.oracle.database.security:osdt_core:21.1.0.0'   
}

I have defined data source information inside application.properties file:

spring.datasource.url=jdbc:oracle:thin:@name_in_tnsnames?TNS_ADMIN=C:/path/to/oracle/wallet
spring.datasource.username=username
spring.datasource.password=password

Path to Oracle wallet needs to be a directory with files tnsnames.ora and keys and other files. Usually Oracle wallet is distributed as a ZIP file and in this case you need to extract files from that ZIP archive into a directory which you will reference in data source definition.

Nacred