Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Add new Data Source to Spring Boot 2.6.2 JdbcTemplate

Davil MiladJun 3 2024 — edited Jun 5 2024

I have an existing spring Boot 2.6.2 microservice that we want to add a call to a new data source. This is being Deployed to Openliberty 23.0.0.10 with Java 8. I created a new DAO:

public class iSeriesDatabaseDao {
      @Autowired
      private JdbcTemplate jdbcTemplate;
      public String getPhoneNumber(String number)
                                   throws SSOServiceException {
               try {
                      String sqlString = String.join(" ",
                           "SELECT phone AS SPECIALITY, (wrkph1 CONCAT wrkph2 CONCAT wrkph3) AS PHONE FROM PRVMAS where PROVNO = '%s'");
                      String getPhone = String.format(sqlString,number);
                      return String.valueOf(jdbcTemplate.queryForList(getPhone));
                  } catch (Exception e) {
                        throw new SSOServiceException("Error in the query the 
                  configuration table: " + e.getMessage());
                 }
             }

I have updated my server.xml file for my website apkbeb to account for the new JDBC driver and data source connection.

             <jdbcDriver id="DB2iSeries">
                            <library name="DB2iToolboxLib">
                                          <fileset dir="${wlp.user.dir}/shared/resources" includes="jt400.jar" />
                            </library>
             </jdbcDriver>
<dataSource jdbcDriverRef="DB2iSeries" jndiName="jdbc/iSeriesDataSource">
   <properties serverName="${env.DB_ISERIES_URL}" password="${env.DB_ISERIES_PWD}" user="${env.DB_USERID}" />
</dataSource>

I also updated my application.properties file too.

spring.datasource.jndi-name=jdbc/PostgresSQLDataSource
spring.datasource.jndi-name-iSeries=jdbc/iSeriesDataSource

I am getting the following error message when I test my code.

[ERROR   ] Connection error:
SSL error: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
[ERROR   ] Failed to create a ConnectionPoolDataSource from PostgreSQL JDBC Driver 42.1.4 for user_xxx at jdbc:postgresql://Dev.xxx.com:3306/xxx?prepareThreshold=5&preparedStatementCacheQueries=256&preparedStatementCacheSizeMiB=5&databaseMetadataCacheFields=65536&databaseMetadataCacheFieldsMiB=5&defaultRowFetchSize=0&binaryTransfer=true&readOnly=false&binaryTransferEnable=&binaryTransferDisable=&unknownLength=2147483647&logUnclosedConnections=false&disableColumnSanitiser=false&ssl=true&tcpKeepAlive=false&loginTimeout=0&connectTimeout=10&socketTimeout=0&cancelSignalTimeout=10&receiveBufferSize=-1&sendBufferSize=-1&ApplicationName=PostgreSQL JDBC Driver&useSpnego=false&gsslib=auto&sspiServiceClass=POSTGRES&allowEncodingChanges=false&targetServerType=any&loadBalanceHosts=false&hostRecheckSeconds=10&preferQueryMode=extended&autosave=never&reWriteBatchedInserts=false: org.postgresql.util.PSQLException: SSL error: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
             at org.postgresql.ssl.MakeSSL.convert(MakeSSL.java:67)
             at org.postgresql.core.v3.ConnectionFactoryImpl.enableSSL(ConnectionFactoryImpl.java:359)
             at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:148)
             at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
             at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:194)
             at org.postgresql.Driver.makeConnection(Driver.java:450)

This references the PostgreSQL database which is an existing connection, not my new connection. The PostgreSQL connection is working fine. What additional changes are needed to set up the new iSeries connection?

Comments

Add EVENT_10842=15

You can also set this as an environment variable with the same name & value.

Sheeraz Majeed May 10 2024 — edited on May 10 2024

Hi, Thanks for reply.

But still no trace or log files to check used oci functions

Works for me.

cjones@cjones-mac:~$ rm -rf $HOME/instantclient/log/diag/clients && mkdir -p $HOME/instantclient/log/diag/clients
cjones@cjones-mac:~$ export EVENT_10842=15
cjones@cjones-mac:~$ sqlplus cj/cj@localhost/orclpdb1

...

SQL> select * from dual;

D
-
X

SQL> exit

And then a file like /Users/cjones/instantclient/log/diag/clients/user_cjones/host_nnnnnn_nn/trace/ora_nnn_nnnnn.trc will contain lines like:

# 2024-06-06 07:26:54.520 # Thread ID 8547126272 # Entry - OCIServerAttach(srvhp = 0x128030068, errhp = 0x13580a410, dblink_hash = H:0x9bf0f8e9ee161fdf, mode = OCI_DEFAULT(000000000), dblink = localhost/orclpdb1, dblink_len = 18 );
1 - 3

Post Details

Added on Jun 3 2024
0 comments
248 views