I have a Spring Boot app that works smoothly when it connects to an Oracle instance in the classic way:
jdbc:oracle:thin:@<server name>:<port num>/<service name>
However, when I turn to a connection via tns through a tnsnames.ora stored in my local drive "C:\ORACLE\Client11g\network\admin" something doesn't work and I get this exception:
Caused by: org.hibernate.service.spi.ServiceException: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment]
at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:267)
at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:231)
at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:210)
at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.configure(JdbcServicesImpl.java:51)
at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:94)
at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:240)
at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:210)
at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.handleTypes(MetadataBuildingProcess.java:352)
at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.complete(MetadataBuildingProcess.java:111)
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.metadata(EntityManagerFactoryBuilderImpl.java:858)
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:885)
at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:60)
at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:353)
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:373)
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:362)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1642)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1579)
... 36 more
Caused by: org.hibernate.HibernateException: Access to DialectResolutionInfo cannot be null when 'hibernate.dialect' not set
at org.hibernate.engine.jdbc.dialect.internal.DialectFactoryImpl.determineDialect(DialectFactoryImpl.java:100)
at org.hibernate.engine.jdbc.dialect.internal.DialectFactoryImpl.buildDialect(DialectFactoryImpl.java:54)
at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:137)
at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:35)
at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.initiateService(StandardServiceRegistryImpl.java:88)
at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:257)
... 52 more
10:37:32,201 ERROR [org.jboss.msc.service.fail] (ServerService Thread Pool -- 381) MSC000001: Failed to start service jboss.undertow.deployment.default-server.default-host./my_service: org.jboss.msc.service.StartException in service jboss.undertow.deployment.default-server.default-host./my_service: java.lang.RuntimeException: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [app/persistence/OracleConfiguration.class]: Invocation of init method failed; nested exception is org.hibernate.service.spi.ServiceException: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment]
at org.wildfly.extension.undertow.deployment.UndertowDeploymentService$1.run(UndertowDeploymentService.java:85)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
at org.jboss.threads.JBossThread.run(JBossThread.java:320)
Caused by: java.lang.RuntimeException: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [app/persistence/OracleConfiguration.class]: Invocation of init method failed; nested exception is org.hibernate.service.spi.ServiceException: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment]
at io.undertow.servlet.core.DeploymentManagerImpl.deploy(DeploymentManagerImpl.java:236)
at org.wildfly.extension.undertow.deployment.UndertowDeploymentService.startContext(UndertowDeploymentService.java:100)
at org.wildfly.extension.undertow.deployment.UndertowDeploymentService$1.run(UndertowDeploymentService.java:82)
... 6 more
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [app/persistence/OracleConfiguration.class]: Invocation of init method failed; nested exception is org.hibernate.service.spi.ServiceException: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1583)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:553)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:482)
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197)
at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1081)
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:856)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:542)
at org.springframework.boot.context.embedded.EmbeddedWebApplicationContext.refresh(EmbeddedWebApplicationContext.java:122)
at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:761)
at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:371)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:315)
at org.springframework.boot.web.support.SpringBootServletInitializer.run(SpringBootServletInitializer.java:151)
at org.springframework.boot.web.support.SpringBootServletInitializer.createRootApplicationContext(SpringBootServletInitializer.java:131)
at org.springframework.boot.web.support.SpringBootServletInitializer.onStartup(SpringBootServletInitializer.java:86)
at org.springframework.web.SpringServletContainerInitializer.onStartup(SpringServletContainerInitializer.java:169)
at io.undertow.servlet.core.DeploymentManagerImpl$1.call(DeploymentManagerImpl.java:186)
at io.undertow.servlet.core.DeploymentManagerImpl$1.call(DeploymentManagerImpl.java:171)
at io.undertow.servlet.core.ServletRequestContextThreadSetupAction$1.call(ServletRequestContextThreadSetupAction.java:42)
at io.undertow.servlet.core.ContextClassLoaderSetupAction$1.call(ContextClassLoaderSetupAction.java:43)
at io.undertow.servlet.api.LegacyThreadSetupActionWrapper$1.call(LegacyThreadSetupActionWrapper.java:44)
at io.undertow.servlet.api.LegacyThreadSetupActionWrapper$1.call(LegacyThreadSetupActionWrapper.java:44)
at io.undertow.servlet.api.LegacyThreadSetupActionWrapper$1.call(LegacyThreadSetupActionWrapper.java:44)
at io.undertow.servlet.api.LegacyThreadSetupActionWrapper$1.call(LegacyThreadSetupActionWrapper.java:44)
at io.undertow.servlet.api.LegacyThreadSetupActionWrapper$1.call(LegacyThreadSetupActionWrapper.java:44)
at io.undertow.servlet.api.LegacyThreadSetupActionWrapper$1.call(LegacyThreadSetupActionWrapper.java:44)
at io.undertow.servlet.core.DeploymentManagerImpl.deploy(DeploymentManagerImpl.java:234)
... 8 more
The following is my application.properties + application-oracleprod.properties files. Keep in mind that this approach works when I use a SID connection as previously said.
application.properties:
spring.profiles.active=oracleprod
spring.jpa.database-platform=org.hibernate.dialect.Oracle10gDialect
spring.jpa.hibernate.ddl-auto=validate
hibernate.show_sql=true
hibernate.hbm2ddl.auto=update
hibernate.generate_statistics=true
application-oracleprod.properties (here the magic should happen due to this property "oracle.net.tns_admin" which sets the "tnsnames.ora" folder plus the link "@MY_SERVICE" which is obviously present in the "tnsnames.ora" file, but I really don't see what Oracle does under the hood):
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
#/tnsnames.ora
oracle.net.tns_admin=C:/ORACLE/Client11g/network/admin
oracle.username=my_user
oracle.password=xyz
oracle.url=jdbc:oracle:thin:@MY_SERVICE
The Configuration class is clean and straightforward:
@Configuration
@ConfigurationProperties(prefix="oracle" )
public class OracleConfiguration {
@NotNull
private String username;
@NotNull
private String password;
@NotNull
private String url;
/*@Value("${oracle.net.tns_admin}")
private String tns;*/
public void setUsername(String username) {
this.username = username;
}
public void setPassword(String password) {
this.password = password;
}
public void setUrl(String url) {
this.url = url;
}
/*public void setTns(String tns) {
this.tns = tns;
}*/
@Bean
DataSource dataSource() throws SQLException {
OracleDataSource dataSource = new OracleDataSource(); // we use oracle data source API but it could be mysql
dataSource.setURL(this.url);
dataSource.setUser(this.username);
dataSource.setPassword(this.password);
dataSource.setImplicitCachingEnabled(true);
dataSource.setFastConnectionFailoverEnabled(true);
return dataSource;
}
@Bean
public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
try {
em.setDataSource(dataSource());
} catch (SQLException e) {
e.printStackTrace();
}
em.setPackagesToScan(new String[] { "app.persistence.entity" }); // put the name of entity classes package
JpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
em.setJpaVendorAdapter(vendorAdapter);
//em.setJpaProperties(additionalProperties()); // this properties are set by "application-oracle.properties" file
return em;
}
@Bean
public PlatformTransactionManager transactionManager(EntityManagerFactory emf) {
JpaTransactionManager transactionManager = new JpaTransactionManager();
transactionManager.setEntityManagerFactory(emf);
return transactionManager;
}
To summarize, I have followed the approach suggested in the Oracle documentation in combination with Spring Boot configuration files/class with no luck.
I have also tried different path formats:
C:/ORACLE/Client11g/network/admin/tnsnames.ora
C:\ORACLE\Client11g\network\admin
C:\\ORACLE\\Client11g\\network\\admin
I think I'm doing well because with a SID connection everything is perfect but probably something hidden is missing.
UPDATE
It turned out the db url is not correct, at least for a TNS connection. The thing is we have something like multiple hosts and not only a single one:
MY_SERVICE =
(DESCRIPTION_LIST =
(LOAD_BALANCE = off)
(FAILOVER = on)
(DESCRIPTION =
(CONNECT_TIMEOUT = 5)
(TRANSPORT_CONNECT_TIMEOUT = 3)
(RETRY_COUNT = 3)
(ADDRESS_LIST =
(LOAD_BALANCE = on)
(ADDRESS = (PROTOCOL = TCP)(HOST = <ip_1> )(PORT = 1621))
(ADDRESS = (PROTOCOL = TCP)(HOST = <ip_2> )(PORT = 1621))
)
(CONNECT_DATA =
(SERVICE_NAME = <my_service_name>)
)
)
(DESCRIPTION =
(CONNECT_TIMEOUT = 5)
(TRANSPORT_CONNECT_TIMEOUT = 3)
(RETRY_COUNT = 3)
(ADDRESS_LIST =
(LOAD_BALANCE = on)
(ADDRESS = (PROTOCOL = TCP)(HOST = <ip_3> )(PORT = 1621))
(ADDRESS = (PROTOCOL = TCP)(HOST = <ip_4> )(PORT = 1621))
)
(CONNECT_DATA =
(SERVICE_NAME = <my_service_name> )
)
)
)
And I got:
javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory
Caused by: java.lang.RuntimeException: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaAutoConfiguration.class]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaAutoConfiguration.class]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory
Caused by: javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory
Caused by: org.hibernate.exception.JDBCConnectionException: Unable to open JDBC Connection for DDL execution
Caused by: java.sql.SQLRecoverableException: Errore di I/O: Unknown host specified
Caused by: oracle.net.ns.NetException: Unknown host specified "
Again, I'm a little bit confused how to tell Spring how to read this TNS configuration.
Any idea?
With your class you are basically disabling auto-configuration and thus rendering, a large part, of your application.properties useless.
There is nothing in your OracleConfiguration that isn't (or can be) automatically configured for you.
Note: I'm assuming 2 things, first you are using HikariCP as the connection pool and second you are using Spring Boot 1.4.x.
So for starters remove the OracleConfiguration class and fix the application.properties accordingly.
# DataSource
spring.datasource.type=com.zaxxer.hikari.HikariDataSource
#JPA
spring.jpa.hibernate.ddl-auto=validate
#Hibernate
spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.generate_statistics=true
You don't need the spring.datasource.driver-class-name as that will be derived from the URL.
In your application-oracle.properties (or whatever profile name you use) set the following
# DataSource
spring.datasource.username=my_user
spring.datasource.password=xyz
spring.datasource.url=jdbc:oracle:thin:@MY_SERVICE
spring.datasource.hikari.dataSourceClassName=oracle.jdbc.pool.OracleDataSource
spring.datasource.hikari.dataSourceProperties.implicitCachingEnabled=true
spring.datasource.hikari.dataSourceProperties.fastConnectionFailoverEnabled=true
#JPA
spring.jpa.database-platform=org.hibernate.dialect.Oracle10gDialect
This configures the oracle specific JDBC properties.
The spring.datasource.hikari.dataSourceProperties are needed to set the additional properties in the OracleDataSource.
This should allow Spring Boot to automatically create the DataSource and appropriate EntityManagerFactory without you needing to create a custom configuration class at all.
OracleDataSource)If you want to use the plain OracleDataSource instead of a connection pool you could remove the spring.datasource.type from the default application.properties and add the following to the application-oracle.properties.
# DataSource
spring.datasource.type=oracle.jdbc.pool.OracleDataSource
spring.datasource.username=my_user
spring.datasource.password=xyz
spring.datasource.url=jdbc:oracle:thin:@MY_SERVICE
Which results in more or less the same as you manually configured.
As you want to use TNS you also need to set a system property named oracle.net.tns_admin. (See this question).
So in your application initializer (if not set on your app server) you need to set it manually.
private static void determineAndSetTnsHome() {
String tnsAdmin = System.getenv("TNS_ADMIN");
if (tnsAdmin == null) {
String oracleHome = System.getenv("ORACLE_HOME");
if (oracleHome == null) {
return; //failed to find any useful env variables
}
tnsAdmin = oracleHome + File.separatorChar + "network" + File.separatorChar + "admin";
}
System.setProperty("oracle.net.tns_admin", tnsAdmin);
}
Call this method from your main and onStartup or configure method. This assumes you have the ORACLE_HOME or TNS_ADMIN variable set in your environment.