Can't connect to db from spring boot app
RaghuThota opened this issue · 19 comments
I am setting below properties under application.properties file and added required jars.
spring.datasource.url=jdbc:oracle:thin:@test_high?TNS_ADMIN=Wallet_Test
spring.datasource.username=admin
spring.datasource.password=admin
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.Oracle10gDialect
spring.jpa.database-platform=org.hibernate.dialect.Oracle10gDialect
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.type=oracle.jdbc.pool.OracleDataSource
Dependencies added to pom.xml
ojdbc8.jar
ons.jar
oraclepki.jar
osdt_cert.jar
osdt_core.jar
ucp.jar
I am facing could not resolve the connect identifier "test_high" exception while running my spring boot application. Any help is appreciated..
Full stack trace
Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.
2019-11-14 21:23:45.600 ERROR 9720 --- [ main] o.s.boot.SpringApplication : Application run failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.exception.JDBCConnectionException: Unable to open JDBC Connection for DDL execution
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1778) ~[spring-beans-5.1.9.RELEASE.jar:5.1.9.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:593) ~[spring-beans-5.1.9.RELEASE.jar:5.1.9.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:515) ~[spring-beans-5.1.9.RELEASE.jar:5.1.9.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:320) ~[spring-beans-5.1.9.RELEASE.jar:5.1.9.RELEASE]
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222) ~[spring-beans-5.1.9.RELEASE.jar:5.1.9.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:318) ~[spring-beans-5.1.9.RELEASE.jar:5.1.9.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199) ~[spring-beans-5.1.9.RELEASE.jar:5.1.9.RELEASE]
at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1105) ~[spring-context-5.1.9.RELEASE.jar:5.1.9.RELEASE]
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:867) ~[spring-context-5.1.9.RELEASE.jar:5.1.9.RELEASE]
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:549) ~[spring-context-5.1.9.RELEASE.jar:5.1.9.RELEASE]
at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:141) ~[spring-boot-2.1.8.RELEASE.jar:2.1.8.RELEASE]
at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:744) [spring-boot-2.1.8.RELEASE.jar:2.1.8.RELEASE]
at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:391) [spring-boot-2.1.8.RELEASE.jar:2.1.8.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:312) [spring-boot-2.1.8.RELEASE.jar:2.1.8.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1215) [spring-boot-2.1.8.RELEASE.jar:2.1.8.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1204) [spring-boot-2.1.8.RELEASE.jar:2.1.8.RELEASE]
at com.deals.warangaldeals.WarangaldealsApplication.main(WarangaldealsApplication.java:18) [classes/:na]
Caused by: javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.exception.JDBCConnectionException: Unable to open JDBC Connection for DDL execution
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:402) ~[spring-orm-5.1.9.RELEASE.jar:5.1.9.RELEASE]
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:377) ~[spring-orm-5.1.9.RELEASE.jar:5.1.9.RELEASE]
at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341) ~[spring-orm-5.1.9.RELEASE.jar:5.1.9.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1837) ~[spring-beans-5.1.9.RELEASE.jar:5.1.9.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1774) ~[spring-beans-5.1.9.RELEASE.jar:5.1.9.RELEASE]
... 16 common frames omitted
Caused by: org.hibernate.exception.JDBCConnectionException: Unable to open JDBC Connection for DDL execution
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:115) ~[hibernate-core-5.3.11.Final.jar:5.3.11.Final]
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) ~[hibernate-core-5.3.11.Final.jar:5.3.11.Final]
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113) ~[hibernate-core-5.3.11.Final.jar:5.3.11.Final]
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99) ~[hibernate-core-5.3.11.Final.jar:5.3.11.Final]
at org.hibernate.resource.transaction.backend.jdbc.internal.DdlTransactionIsolatorNonJtaImpl.getIsolatedConnection(DdlTransactionIsolatorNonJtaImpl.java:69) ~[hibernate-core-5.3.11.Final.jar:5.3.11.Final]
at org.hibernate.tool.schema.internal.exec.ImprovedExtractionContextImpl.getJdbcConnection(ImprovedExtractionContextImpl.java:60) ~[hibernate-core-5.3.11.Final.jar:5.3.11.Final]
at org.hibernate.tool.schema.extract.internal.SequenceInformationExtractorLegacyImpl.extractMetadata(SequenceInformationExtractorLegacyImpl.java:40) ~[hibernate-core-5.3.11.Final.jar:5.3.11.Final]
at org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl.initializeSequences(DatabaseInformationImpl.java:65) ~[hibernate-core-5.3.11.Final.jar:5.3.11.Final]
at org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl.<init>(DatabaseInformationImpl.java:59) ~[hibernate-core-5.3.11.Final.jar:5.3.11.Final]
at org.hibernate.tool.schema.internal.Helper.buildDatabaseInformation(Helper.java:132) ~[hibernate-core-5.3.11.Final.jar:5.3.11.Final]
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:96) ~[hibernate-core-5.3.11.Final.jar:5.3.11.Final]
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:183) ~[hibernate-core-5.3.11.Final.jar:5.3.11.Final]
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:72) ~[hibernate-core-5.3.11.Final.jar:5.3.11.Final]
at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:310) ~[hibernate-core-5.3.11.Final.jar:5.3.11.Final]
at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:467) ~[hibernate-core-5.3.11.Final.jar:5.3.11.Final]
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:939) ~[hibernate-core-5.3.11.Final.jar:5.3.11.Final]
at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:57) ~[spring-orm-5.1.9.RELEASE.jar:5.1.9.RELEASE]
at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365) ~[spring-orm-5.1.9.RELEASE.jar:5.1.9.RELEASE]
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:390) ~[spring-orm-5.1.9.RELEASE.jar:5.1.9.RELEASE]
... 20 common frames omitted
Caused by: java.sql.SQLRecoverableException: IO Error: could not resolve the connect identifier "rampquik_high"
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:801) ~[ojdbc8-12.2.0.1.jar:18.3.0.0.0]
at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:782) ~[ojdbc8-12.2.0.1.jar:18.3.0.0.0]
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:39) ~[ojdbc8-12.2.0.1.jar:18.3.0.0.0]
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:704) ~[ojdbc8-12.2.0.1.jar:18.3.0.0.0]
at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSource.java:390) ~[ojdbc8-12.2.0.1.jar:18.3.0.0.0]
at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:279) ~[ojdbc8-12.2.0.1.jar:18.3.0.0.0]
at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:202) ~[ojdbc8-12.2.0.1.jar:18.3.0.0.0]
at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:180) ~[ojdbc8-12.2.0.1.jar:18.3.0.0.0]
at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122) ~[hibernate-core-5.3.11.Final.jar:5.3.11.Final]
at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator$ConnectionProviderJdbcConnectionAccess.obtainConnection(JdbcEnvironmentInitiator.java:180) ~[hibernate-core-5.3.11.Final.jar:5.3.11.Final]
at org.hibernate.resource.transaction.backend.jdbc.internal.DdlTransactionIsolatorNonJtaImpl.getIsolatedConnection(DdlTransactionIsolatorNonJtaImpl.java:43) ~[hibernate-core-5.3.11.Final.jar:5.3.11.Final]
... 34 common frames omitted
Caused by: oracle.net.ns.NetException: could not resolve the connect identifier "rampquik_high"
at oracle.net.resolver.NameResolver.resolveName(NameResolver.java:180) ~[ojdbc8-12.2.0.1.jar:18.3.0.0.0]
at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:490) ~[ojdbc8-12.2.0.1.jar:18.3.0.0.0]
at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:660) ~[ojdbc8-12.2.0.1.jar:18.3.0.0.0]
at oracle.net.ns.NSProtocol.connect(NSProtocol.java:287) ~[ojdbc8-12.2.0.1.jar:18.3.0.0.0]
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1481) ~[ojdbc8-12.2.0.1.jar:18.3.0.0.0]
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:540) ~[ojdbc8-12.2.0.1.jar:18.3.0.0.0]
... 44 common frames omitted
Sorry was traveling.
Can you confirm that test_high is declared in your tnsnames.ora file at the location identified by TNS_ADMIN?
There are few things that you need to check.
(a) Check where the tnsnames.ora is located where you have TNS alias "test_high"
(b) Point that location of the file as part of the connection URL. See the example below.
Linux: jdbc:oracle:thin:@dbname_alias?TNS_ADMIN=/Users/test/wallet_dbname
@Kuassim yes, test_high is declared in tnsnames.ora.
@nsundara I have placed wallet_dbname, application.properties file under src/main/resources directory and this is my url property in application.properties.
spring.datasource.url=jdbc:oracle:thin:@test_high?TNS_ADMIN=wallet_dbname.
I also have tried placing wallet_dbname under root directory. But no luck. I was able to connect from Sql Developer with the same wallet_dbname folder using test_high alias. I suspect Oracle cloud is blocking the incoming request from my spring boot application.
Also, I have tried using DateTimeStampSample class to create JDBC connection with simple java code rather than using Spring boot. I replaced host name, port number and DB name with my data base details. With this approach, I am facing Exception in thread "main" java.sql.SQLRecoverableException: IO Error: Got minus one from a read call, connect lapse 292 ms., Authentication lapse 0 ms exception. Please find the stack trace below.
private static String user = "admin";
private static String password = "admin";
private static String url = "jdbc:oracle:thin:@//hostname:1522/DB_name";
Exception in thread "main" java.sql.SQLRecoverableException: IO Error: Got minus one from a read call, connect lapse 292 ms., Authentication lapse 0 ms.
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:794)
at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:688)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:39)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:691)
at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSource.java:384)
at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:273)
at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:198)
at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:176)
at com.ojdbc.DateTimeStampSample.getConnection(DateTimeStampSample.java:185)
at com.ojdbc.DateTimeStampSample.run(DateTimeStampSample.java:65)
at com.ojdbc.DateTimeStampSample.main(DateTimeStampSample.java:61)
Caused by: java.io.IOException: Got minus one from a read call, connect lapse 292 ms., Authentication lapse 0 ms.
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:790)
... 10 more
Caused by: java.io.IOException: Got minus one from a read call, connect lapse 292 ms.
at oracle.net.ns.NSProtocolNIO.negotiateConnection(NSProtocolNIO.java:138)
at oracle.net.ns.NSProtocol.connect(NSProtocol.java:317)
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1438)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:518)
... 10 more
Caused by: oracle.net.ns.NetException: Got minus one from a read call
at oracle.net.ns.NIOHeader.readHeaderBuffer(NIOHeader.java:100)
at oracle.net.ns.NIOPacket.readNIOPacket(NIOPacket.java:252)
at oracle.net.ns.NSProtocolNIO.negotiateConnection(NSProtocolNIO.java:118)
... 13 more
I see "ojdbc8-12.2.0.1.jar" in your stack trace. You need to use a more recent version of the driver such as 19.3 (which is available on Maven) in order to use a URL of the form: spring.datasource.url=jdbc:oracle:thin:@test_high?TNS_ADMIN=wallet_dbname
So I would suggest to review your dependencies.
I have tried with 19.3 jar, But it still throwing the same exception. I don't think this is a problem with dependencies. I was able to connect from SQL developer using the same Wallet. Oracle is blocking the incoming request from my java application. I am using Oracle Cloud autonomous database. Even I was unable to establish the JDBC connection using java method as I mentioned in my previous comment. I am not moving ahead due to this error.
Can you test with the simple Java program DataSourceSample.javaand check if you are able to connect? Refer to the detailed instructions
We do have a SpringBoot example @ https://github.com/oracle/oracle-db-examples/tree/master/java/jdbc/SpringBootApp/src
Please try this one instead.
I am also facing same problem but I am able to connect when providing a fixed path of wallet folder like d:/test/wallet_test, but still facing a problem when trying to read from resource folder.
spring.datasource.url=jdbc:oracle:thin:@test_high?TNS_ADMIN=Wallet_Test
Can you test with the simple Java program DataSourceSample.javaand check if you are able to connect? Refer to the detailed instructions
sample program work but required to provide fix path for wallet folder but not possible from resource folder
I am also facing same problem but I am able to connect when providing a fixed path of wallet folder like d:/test/wallet_test, but still facing a problem when trying to read from resource folder.
spring.datasource.url=jdbc:oracle:thin:@test_high?TNS_ADMIN=Wallet_Test
I was able to load it from the resources folder, you have to copy the wallet directory to the resources folder and configure the url like this:
spring.datasource.url=jdbc:oracle:thin:@connectionname_tp?TNS_ADMIN=src/main/resources/Wallet_name
@andrepnd When I changed the path to spring.datasource.url=jdbc:oracle:thin:@connectionname_tp?TNS_ADMIN=src/main/resources/Wallet_name its working with in eclipse. But when I deploy into tomcat its not finding the connection identifier. Its failing with below exception
Caused by: java.sql.SQLRecoverableException: IO Error: could not resolve the connect identifier "test_medium"
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:854)
at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:793)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:57)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:747)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:562)
at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:136)
at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:369)
at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:198)
at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:467)
at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:541)
at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115)
at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112)
at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122)
at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator$ConnectionProviderJdbcConnectionAccess.obtainConnection(JdbcEnvironmentInitiator.java:180)
at org.hibernate.resource.transaction.backend.jdbc.internal.DdlTransactionIsolatorNonJtaImpl.getIsolatedConnection(DdlTransactionIsolatorNonJtaImpl.java:43)
... 47 more
Caused by: oracle.net.ns.NetException: could not resolve the connect identifier "test_medium"
at oracle.net.resolver.NameResolver.resolveName(NameResolver.java:182)
at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:510)
at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:682)
at oracle.net.ns.NSProtocol.connect(NSProtocol.java:309)
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1596)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:588)
... 61 more
Note that the location that is specified as TNS_ADMIN should be an absolute path. If you give relative path then the driver will not be able to find the tnsnames.ora file.
jdbc:oracle:thin:@connectionname_tp?TNS_ADMIN=src/main/resources
Can you correct the path to the absolute path and verify?
@nsundara its working with absolute path. But just hard coding the absolute path won't work after deploying into Tomcat. So, this is how I resolved it.
I am using below method to establish database connection instead of declaring in properties file. I have placed Wallet_Test folder inside /src/main/resources and reading the absolute path with System.getProperty("user.dir"). path in below method gives me an absolute path for Wallet_Test folder. If it is deployed into Tomcat, System.getProperty("user.dir") returns path like /usr/share/tomcat8/. I have copied /src/main/resources/Wallet_Test to /usr/share/tomcat8/
@Bean
public DataSource dataSource() {
DriverManagerDataSource ds = new DriverManagerDataSource();
ds.setDriverClassName(oracle.jdbc.driver.OracleDriver.class.getName());
String path = System.getProperty("user.dir").replace('\\','/')+"/src/main/resources/Wallet_Test";
ds.setUrl("jdbc:oracle:thin:@test_medium?TNS_ADMIN=" + path);
ds.setUsername("admin");
ds.setPassword("admin");
return ds;
}
Closing the issue as it is resolved
We do have a SpringBoot example @ https://github.com/oracle/oracle-db-examples/tree/master/java/jdbc/SpringBootApp/src
Please try this one instead.
This link is broken 404
We do have a SpringBoot example @ https://github.com/oracle/oracle-db-examples/tree/master/java/jdbc/SpringBootApp/src
Please try this one instead.This link is broken 404
Looks like the SpringBoot example exists, but on several other branches, like https://github.com/oracle/oracle-db-examples/tree/sqldev-ext-connection-url/java/jdbc/SpringBootApp/src, but not on master.
Sorry, the sample on that branch has not been published to the main branch as we are working on a revised version.
We do have a SpringBoot example @ https://github.com/oracle/oracle-db-examples/tree/master/java/jdbc/SpringBootApp/src
Please try this one instead.This link is broken 404
Looks like the SpringBoot example exists, but on several other branches, like https://github.com/oracle/oracle-db-examples/tree/sqldev-ext-connection-url/java/jdbc/SpringBootApp/src, but not on master.
Now found here:
https://github.com/oracle-samples/oracle-db-examples/tree/main/java/SpringBootApp