Using settings from TNSNAMES.ORA in HikariCP

Question

I am trying to migrate to HikariCP for my application connection pool. At the moment, I want to connect to an Oracle database.

Here is the code I am using :

        HikariConfig hc = new HikariConfig();
        hc.setUsername(username);
        hc.setPassword(password);
        hc.setDataSourceClassName("oracle.jdbc.pool.OracleDataSource");
        hc.addDataSourceProperty("serverName", TNS);
        hc.addDataSourceProperty("portNumber", 1521);
        hc.addDataSourceProperty("driverType", "thin");


        HikariDataSource hds = new HikariDataSource(hc);
        hds.setPoolName("Pool::"+username+"@"+dbName);

the TNS variable comes from my config, and reference a setting in the TNSNAMES.ora files present on the server.

SERVER_1 =
    (DESCRPTION = 
        (ADDRESS_LIST = 
                (ADDRESS = (PROTOCOL = TCP)(HOST = server01.intranet)(PORT = 1521))
        )
        (CONNECTION_DATA = 
                (SERVICE_NAME = SERVER1)
        )
    )

The code fails with the following stacktrace :

INFO  HikariPool-1 - Starting...
ERROR HikariPool-1 - Exception during pool initialization.
java.sql.SQLRecoverableException: Erreur d'E/S: The Network Adapter could not establish the connection
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:743)
    at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:666)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:566)
    at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSource.java:317)
    at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:241)
    at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:358)
    at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:201)
    at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:443)
    at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:514)
    at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:111)
    at com.zaxxer.hikari.HikariDataSource.<init>(HikariDataSource.java:72)
    at com.test.commonlib.config.DbConfigReader.getDataSource(DbConfigReader.java:105)
    at com.test.commonlib.config.DbConfigReader.getDataSource(DbConfigReader.java:31)
    at com.test.module1.Module1.getConnectionModule1(Module1.java:258)
    at com.test.module1.fileinjector.fd.loadConf(fd.java:583)
    at com.test.module1.fileinjector.fd.<init>(fd.java:78)
    at com.test.module1.fileinjector.fd.getInstance(fd.java:84)
    at com.test.module1.Module1.init(Module1.java:78)
    at com.test.module1.scripts.TestPerso.TestPerso.testModule1(TestPerso.java:2009)
    at com.test.module1.scripts.TestPerso.TestPerso.main(TestPerso.java:145)
Caused by: oracle.net.ns.NetException: The Network Adapter could not establish the connection
    at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:470)
    at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:506)
    at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:595)
    at oracle.net.ns.NSProtocol.connect(NSProtocol.java:230)
    at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1452)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:496)
    ... 20 more
Caused by: java.net.UnknownHostException: SERVER_1
    at java.net.Inet6AddressImpl.lookupAllHostAddr(Native Method)
    at java.net.InetAddress$2.lookupAllHostAddr(InetAddress.java:928)
    at java.net.InetAddress.getAddressesFromNameService(InetAddress.java:1323)
    at java.net.InetAddress.getAllByName0(InetAddress.java:1276)
    at java.net.InetAddress.getAllByName(InetAddress.java:1192)
    at java.net.InetAddress.getAllByName(InetAddress.java:1126)
    at oracle.net.nt.TcpNTAdapter.connect(TcpNTAdapter.java:120)
    at oracle.net.nt.ConnOption.connect(ConnOption.java:159)
    at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:428)
    ... 25 more
Exception in thread "main" com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: Erreur d'E/S: The Network Adapter could not establish the connection
    at com.zaxxer.hikari.pool.HikariPool.throwPoolInitializationException(HikariPool.java:543)
    at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:535)
    at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:111)
    at com.zaxxer.hikari.HikariDataSource.<init>(HikariDataSource.java:72)
    at com.test.commonlib.config.DbConfigReader.getDataSource(DbConfigReader.java:105)
    at com.test.commonlib.config.DbConfigReader.getDataSource(DbConfigReader.java:31)
    at com.test.module1.Module1.getConnectionModule1(Module1.java:258)
    at com.test.module1.fileinjector.fd.loadConf(fd.java:583)
    at com.test.module1.fileinjector.fd.<init>(fd.java:78)
    at com.test.module1.fileinjector.fd.getInstance(fd.java:84)
    at com.test.module1.Module1.init(Module1.java:78)
    at com.test.module1.scripts.TestPerso.TestPerso.testModule1(TestPerso.java:2009)
    at com.test.module1.scripts.TestPerso.TestPerso.main(TestPerso.java:145)
Caused by: java.sql.SQLRecoverableException: Erreur d'E/S: The Network Adapter could not establish the connection
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:743)
    at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:666)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:566)
    at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSource.java:317)
    at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:241)
    at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:358)
    at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:201)
    at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:443)
    at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:514)
    ... 11 more
Caused by: oracle.net.ns.NetException: The Network Adapter could not establish the connection
    at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:470)
    at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:506)
    at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:595)
    at oracle.net.ns.NSProtocol.connect(NSProtocol.java:230)
    at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1452)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:496)
    ... 20 more
Caused by: java.net.UnknownHostException: SERVER_1
    at java.net.Inet6AddressImpl.lookupAllHostAddr(Native Method)
    at java.net.InetAddress$2.lookupAllHostAddr(InetAddress.java:928)
    at java.net.InetAddress.getAddressesFromNameService(InetAddress.java:1323)
    at java.net.InetAddress.getAllByName0(InetAddress.java:1276)
    at java.net.InetAddress.getAllByName(InetAddress.java:1192)
    at java.net.InetAddress.getAllByName(InetAddress.java:1126)
    at oracle.net.nt.TcpNTAdapter.connect(TcpNTAdapter.java:120)
    at oracle.net.nt.ConnOption.connect(ConnOption.java:159)
    at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:428)
    ... 25 more

Does anyone got any luck to make it work on Oracle with the TNS NAME ?

Looks like it wants an actual hostname, instead of the name defining the TNSNAMES entry. Try setting `TNS` to `server01.hostname` instead of `SERVER_1`. — schtever, Jul 24 '17 at 17:10
Thing is all those informations are already stored in the tnsnames.ora which contains all the information for all my database servers. The point of this file is to avoid writing manually the IP or the dns entry — Shade, Jul 25 '17 at 07:35
For thin driver set sysprop oracle.net.tns_admin -- dupe https://stackoverflow.com/questions/19536162/how-to-connect-jdbc-to-tns-oracle and https://stackoverflow.com/questions/14194750/java-jdbc-how-to-connect-to-oracle-using-tnsnames-ora — dave_thompson_085, Jul 25 '17 at 11:04

DNL · Answer 1 · 2017-11-26T20:58:41.117

1

Set the "oracle.net.tns_admin" system property in order to specify a directory location of TNSNAMES.ORA file.

System.setProperty("oracle.net.tns_admin", "C:\\Development\\TNS_ADMIN");

If you have TNS_ADMIN system variable set you can do it in following way

System.setProperty("oracle.net.tns_admin", System.getenv("TNS_ADMIN"));

edited Nov 26 '17 at 20:58

answered Nov 26 '17 at 18:32

DNL

35
7

score 0 · Accepted Answer · answered Jul 25 '17 at 09:46

0

You can work with JDBC URL instead:

dataSource.setDriverClassName("oracle.jdbc.driver.OracleDriver");
dataSource.setJdbcUrl("jdbc:oracle:thin:@server01.intranet:1521/SERVER1")

answered Jul 25 '17 at 09:46

Ori Marko

56,308
23
131
233

It does work, even if I simply put hc.setJdbcUrl("jdbc:oracle:thin:@SERVER_1"). Somehow with this way I can use the settings from tnsnames.ora, if not from the Datasource configuration – Shade Jul 26 '17 at 11:34
So it's a better solution? you accept this suggestion? – Ori Marko Jul 26 '17 at 11:40
I accept it because I don't have any other solution and it does indeed work. However I wish I could use a datasource without the jdbc url written directly. I opened an issue on github, who knows :) – Shade Jul 27 '17 at 12:44
can you share the link to the issue? – Ori Marko Jul 27 '17 at 12:45
See also this solution that my help https://stackoverflow.com/questions/19536162/how-to-connect-jdbc-to-tns-oracle/28592684 – Ori Marko Jul 27 '17 at 12:48

Using settings from TNSNAMES.ORA in HikariCP

2 Answers2