I'm trying to connect Oracle database using Entity Manager method. Picking up oracle credentials from application.properties then the program is working fine I've referred this post [https://stackoverflow.com/questions/58570032/hibernate-could-not-fetch-the-sequenceinformation-from-the-database/58574009#58574009][1], but its not applicable, since I need to use Entity Manager to set up username, password, url, etc. It seems like the Entity Manager didn't pick up oracle credentials. I am getting the following error:
2021-07-14 14:15:56 INFO c.e.f.s.s.SpringBootPracticeApplication - The following profiles are active: default
2021-07-14 14:15:56 INFO o.s.b.d.restart.ChangeableUrls - The Class-Path manifest attribute in
2021-07-14 14:15:56 INFO o.s.b.d.e.DevToolsPropertyDefaultsPostProcessor - Devtools property defaults active! Set 'spring.devtools.add-properties' to 'false' to disable
2021-07-14 14:15:56 INFO o.s.b.d.e.DevToolsPropertyDefaultsPostProcessor - For additional web related logging consider setting the 'logging.level.web' property to 'DEBUG'
2021-07-14 14:15:57 INFO o.s.d.r.c.RepositoryConfigurationDelegate - Bootstrapping Spring Data JPA repositories in DEFAULT mode.
2021-07-14 14:15:57 INFO o.s.d.r.c.RepositoryConfigurationDelegate - Finished Spring Data repository scanning in 44 ms. Found 1 JPA repository interfaces.
2021-07-14 14:15:58 INFO o.s.b.w.e.tomcat.TomcatWebServer - Tomcat initialized with port(s): 8080 (http)
2021-07-14 14:15:58 INFO o.a.catalina.core.StandardService - Starting service [Tomcat]
2021-07-14 14:15:58 INFO o.a.catalina.core.StandardEngine - Starting Servlet engine: [Apache Tomcat/9.0.45]
2021-07-14 14:15:58 INFO o.a.c.c.C.[Tomcat].[localhost].[/] - Initializing Spring embedded WebApplicationContext
2021-07-14 14:15:58 INFO o.s.b.w.s.c.ServletWebServerApplicationContext - Root WebApplicationContext: initialization completed in 1780 ms
2021-07-14 14:15:58 INFO com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Starting...
2021-07-14 14:15:58 INFO com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Start completed.
2021-07-14 14:15:58 INFO o.s.b.a.h.H2ConsoleAutoConfiguration - H2 console available at '/h2-console'. Database available at 'jdbc:h2:mem:3d872e1e-07c6-4088-b9fa-19b41bd383f2'
2021-07-14 14:15:58 INFO com.zaxxer.hikari.HikariDataSource - HikariPool-2 - Starting...
2021-07-14 14:15:59 INFO com.zaxxer.hikari.HikariDataSource - HikariPool-2 - Start completed.
2021-07-14 14:15:59 INFO o.h.jpa.internal.util.LogHelper - HHH000204: Processing PersistenceUnitInfo [name: default]
2021-07-14 14:15:59 INFO org.hibernate.Version - HHH000412: Hibernate ORM core version 5.4.30.Final
2021-07-14 14:15:59 INFO o.h.annotations.common.Version - HCANN000001: Hibernate Commons Annotations {5.1.2.Final}
2021-07-14 14:15:59 INFO org.hibernate.dialect.Dialect - HHH000400: Using dialect: org.hibernate.dialect.Oracle12cDialect
2021-07-14 14:16:06 ERROR o.h.e.j.e.i.JdbcEnvironmentImpl - Could not fetch the SequenceInformation from the database
java.sql.SQLException: Numeric Overflow
at oracle.jdbc.driver.NumberCommonAccessor.throwOverflow(NumberCommonAccessor.java:4139)
at oracle.jdbc.driver.NumberCommonAccessor.getLong(NumberCommonAccessor.java:636)
at oracle.jdbc.driver.GeneratedStatement.getLong(GeneratedStatement.java:208)
at oracle.jdbc.driver.GeneratedScrollableResultSet.getLong(GeneratedScrollableResultSet.java:261)
at oracle.jdbc.driver.GeneratedResultSet.getLong(GeneratedResultSet.java:560)
at com.zaxxer.hikari.pool.HikariProxyResultSet.getLong(HikariProxyResultSet.java)
at org.hibernate.tool.schema.extract.internal.SequenceInformationExtractorLegacyImpl.resultSetMinValue(SequenceInformationExtractorLegacyImpl.java:134)
at org.hibernate.tool.schema.extract.internal.SequenceInformationExtractorLegacyImpl.extractMetadata(SequenceInformationExtractorLegacyImpl.java:60)
at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentImpl.sequenceInformationList(JdbcEnvironmentImpl.java:403)
at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentImpl.<init>(JdbcEnvironmentImpl.java:268)
at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:114)
at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:35)
at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.initiateService(StandardServiceRegistryImpl.java:101)
at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:263)
at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:237)
at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:214)
at org.hibernate.id.factory.internal.DefaultIdentifierGeneratorFactory.injectServices(DefaultIdentifierGeneratorFactory.java:152)
at org.hibernate.service.internal.AbstractServiceRegistryImpl.injectDependencies(AbstractServiceRegistryImpl.java:286)
at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:243)
at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:214)
at org.hibernate.boot.internal.InFlightMetadataCollectorImpl.<init>(InFlightMetadataCollectorImpl.java:176)
at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.complete(MetadataBuildingProcess.java:127)
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.metadata(EntityManagerFactoryBuilderImpl.java:1224)
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1255)
at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:58)
at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365)
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:409)
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:396)
at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1845)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1782)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:602)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:524)
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208)
at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1154)
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:908)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:583)
at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:144)
at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:782)
at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:774)
at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:439)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:339)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1340)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1329)
at com.sample.springbootpractice.src.SpringBootPracticeApplication.main(SpringBootPracticeApplication.java:18)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:49)
2021-07-14 14:16:06 INFO o.h.e.t.j.p.i.JtaPlatformInitiator - HHH000490: Using JtaPlatform implementation: [org.hibernate.engine.transaction.jta.platform.internal.NoJtaPlatform]
2021-07-14 14:16:07 INFO o.s.o.j.LocalContainerEntityManagerFactoryBean - Initialized JPA EntityManagerFactory for persistence unit 'default'
2021-07-14 14:16:07 INFO o.s.b.d.a.OptionalLiveReloadServer - LiveReload server is running on port 35729
2021-07-14 14:16:07 WARN o.s.b.a.o.j.JpaBaseConfiguration$JpaWebConfiguration - spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning
2021-07-14 14:16:07 INFO o.s.s.c.ThreadPoolTaskExecutor - Initializing ExecutorService 'applicationTaskExecutor'
2021-07-14 14:16:07 INFO o.s.b.a.e.web.EndpointLinksResolver - Exposing 13 endpoint(s) beneath base path '/actuator'
2021-07-14 14:16:07 INFO o.s.b.w.e.tomcat.TomcatWebServer - Tomcat started on port(s): 8080 (http) with context path ''
2021-07-14 14:16:07 INFO c.e.f.s.s.SpringBootPracticeApplication - Started SpringBootPracticeApplication in 11.49 seconds (JVM running for 13.003)
2021-07-14 14:16:08 INFO o.a.c.c.C.[Tomcat].[localhost].[/] - Initializing Spring DispatcherServlet 'dispatcherServlet'
2021-07-14 14:16:08 INFO o.s.web.servlet.DispatcherServlet - Initializing Servlet 'dispatcherServlet'
2021-07-14 14:16:08 INFO o.s.web.servlet.DispatcherServlet - Completed initialization in 2 ms
It seems like it didn't pick up the dialect property. Here is my configuration file:
package com.sample.springbootpractice.src.configuration;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.context.annotation.*;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.JpaVendorAdapter;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Properties;
@Configuration
@EnableTransactionManagement
@Profile("default")
@ConditionalOnProperty(name = "source-system", havingValue = "ebs")
@EnableJpaRepositories(
entityManagerFactoryRef = "ebsEntityManager",
transactionManagerRef = "ebsTransactionManager",
basePackages = {"com.sample.springbootpractice.src.user.repository.ebs"})
@PropertySource("classpath:application.properties")
public class EbsDataSourceConfig {
@Value("${source-system}")
private String source;
@Value("${aws-secret-name}")
private String awsSecretName;
private HashMap<String, String> getAwsSecretValues() {
HashMap<String, String> awsSecretKeyValuesMap = new HashMap<>();
System.out.println("Get values from AWS Secrets Manager for Secret : " + awsSecretName);
awsSecretKeyValuesMap.put("ebs_jdbc_url", "jdbc:oracle:thin:@...");
awsSecretKeyValuesMap.put("ebs_write_username", "xxx");
awsSecretKeyValuesMap.put("ebs_write_password", "xxx");
return awsSecretKeyValuesMap;
}
private Properties additionalProperties() {
Properties properties = new Properties();
properties.setProperty("spring.jpa.properties.hibernate.dialect",
"com.sample.springbootpractice.src.configuration.CustomOracleDialect");
return properties;
}
public DataSource getEbsOracleDataSource() {
HashMap<String, String> awsSecretKeyValuesMap = getAwsSecretValues();
String jdbcUrl, userName, userPasswd, driverClassName;
jdbcUrl = awsSecretKeyValuesMap.get("ebs_jdbc_url");
userName = awsSecretKeyValuesMap.get("ebs_write_username");
userPasswd = awsSecretKeyValuesMap.get("ebs_write_password");
driverClassName = "oracle.jdbc.OracleDriver";
System.out.println("Setting Oracle JPA");
HikariConfig jdbcConfig = new HikariConfig();
jdbcConfig.setDriverClassName(driverClassName);
jdbcConfig.setJdbcUrl(jdbcUrl);
jdbcConfig.setUsername(userName);
jdbcConfig.setPassword(userPasswd);
/** Set the SQL string that will be executed on all new connections when they are created, before they are added to the pool. */
jdbcConfig.setConnectionInitSql("SELECT 1 FROM dual");
jdbcConfig.setConnectionTestQuery("SELECT 1 FROM dual");
return new HikariDataSource(jdbcConfig);
}
@Bean(name = "ebsEntityManager")
@Primary
public LocalContainerEntityManagerFactoryBean oracleEntityManagerFactory() {
LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
em.setDataSource(getEbsOracleDataSource());
JpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
em.setJpaProperties(additionalProperties());
em.setJpaVendorAdapter(vendorAdapter);
em.setPackagesToScan("com.sample.springbootpractice.src.user.entity.ebs");
return em;
}
@Bean(name = "ebsTransactionManager")
public JpaTransactionManager transactionManager(@Qualifier("ebsEntityManager") EntityManagerFactory ebsEntityManager) {
JpaTransactionManager transactionManager = new JpaTransactionManager();
transactionManager.setEntityManagerFactory(ebsEntityManager);
return transactionManager;
}
}
Here is my CustomOracleDialect file.
package com.sample.springbootpractice.src.configuration;
import org.hibernate.dialect.Oracle12cDialect;
import org.hibernate.tool.schema.extract.internal.SequenceInformationExtractorOracleDatabaseImpl;
import org.hibernate.tool.schema.extract.spi.SequenceInformationExtractor;
import java.sql.ResultSet;
import java.sql.SQLException;
class AppSequenceInformationExtractor extends SequenceInformationExtractorOracleDatabaseImpl {
/**
* Singleton access
*/
public static final AppSequenceInformationExtractor INSTANCE = new AppSequenceInformationExtractor();
@Override
protected Long resultSetMinValue(ResultSet resultSet) throws SQLException {
return resultSet.getBigDecimal("min_value").longValue();
}
}
public class CustomOracleDialect extends Oracle12cDialect {
@Override
public SequenceInformationExtractor getSequenceInformationExtractor() {
return AppSequenceInformationExtractor.INSTANCE;
}
@Override
public String getQuerySequencesString() {
return "select * from user_sequences";
}
}
Here is my application.properties file
# logging
logging.pattern.console=%d{yyyy-MM-dd HH:mm:ss} %-5level %logger{36} - %msg%n
logging.level.org.hibernate.SQL=info
logging.level.root=INFO
logging.level.org.springframework=info
## to enable all (*) actuator endpoints ##
management.endpoints.web.exposure.include=*
# Oracle settings
spring.jpa.show-sql=false
spring.datasource.url=jdbc:oracle:thin:@xxx
spring.datasource.driverClassName=oracle.jdbc.OracleDriver
spring.datasource.username=xxx
spring.datasource.password=xxx
spring.jpa.properties.hibernate.dialect=com.sample.springbootpractice.src.configuration.CustomOracleDialect
# Issue with newer versions of Hibernate thus created custom Dialect
spring.jpa.properties.hibernate.jdbc.batch_size=30
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true
spring.jpa.properties.hibernate.batch_versioned_data=true
spring.jpa.properties.hibernate.enable_lazy_load_no_trans=true
spring.datasource.hikari.validationTimeout=50000
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.minimum-idle=2
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.idle-timeout=300000
spring.datasource.hikari.max-lifetime=1200000
spring.datasource.hikari.auto-commit=false
