登录
当前位置： 首页 > 知识库问答 >
问题：

通过TNS与Oracle的连接不工作

戚侯林
2023-03-14

我有一个Spring Boot应用程序，当它以经典的方式连接到Oracle实例时，它可以很顺利地工作：

jdbc:oracle:thin:@<server name>:<port num>/<service name>

但是，当我通过存储在本地驱动器“C:\Oracle\Client11g\Network\Admin”中的TNSnames.ora转到tns连接时，有些东西不起作用，我会得到以下异常：

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

下面是我的application.properties+application-oracleprod.properties文件。请记住，当我如前所述使用SID连接时，这种方法是有效的。

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

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

@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;
    }

总之，我遵循了Oracle文档中建议的方法，并结合Spring Boot配置文件/类，但没有成功。

我也尝试过不同的路径格式：

C:/ORACLE/Client11g/network/admin/tnsnames.ora
C:\ORACLE\Client11g\network\admin
C:\\ORACLE\\Client11g\\network\\admin

我认为我做得很好，因为有了SID连接，一切都很完美，但可能缺少了一些隐藏的东西。

    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> )
      )
    )
  )

 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 "

共有1个答案

吴靖
2023-03-14

对于您的类，您基本上是在禁用自动配置，从而使application.properties的很大一部分呈现为无用。

您的oracleconfiguration中没有任何东西是不能（或可以）为您自动配置的。

注意：我假设有两件事，第一，您使用HikariCP作为连接池，第二，您使用Spring Boot1.4.x。

# 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

# 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

这应该允许Spring Boot自动创建DataSource和适当的EntityManagerFactory而不需要创建自定义配置类。

如果希望使用普通OracleDataSource而不是连接池，可以从默认的Application.Properties中删除Spring.DataSource.Type并将以下内容添加到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

其结果与您手动配置的大致相同。

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);
}
 类似资料：
  • 如何将JDBC连接到TNS Oracle

    问题内容： 我可以使用tns文件从plsql连接到数据库 现在，我想使用JDBC从Java连接到数据库。 我试过的 我搜索谷歌，我发现我必须使用此连接字符串： 我的电脑名称是 myPC 在tnsfile中写入的端口是 5151 所以我尝试了这个连接字符串 但我有这个例外 我究竟做错了什么？ 如何使用tns文件将JDBC连接到数据库？ 问题答案： 您必须设置一个名为的属性，以指向包含您的文件的文件夹

  • 无法使用tns连接到Oracle

    我使用的是Oracle 11g R2数据库。我使用Oracle SQL Developer。如果我创建了一个新连接并检查了基本连接类型并填写了我可以连接的字段。如果我选择TNS并在下拉菜单中选择我想要的，它会显示失败E/S异常：网络适配器无法建立连接。 这是tnsnames.ora。我用它连接到2个数据库，dblilly和astrea。我可以正确连接到astrea。侦听器已打开，实例已准备就绪。你

  • 如何将JDBC连接到tns oracle

    我可以使用tns文件从plsql连接到数据库 现在我想使用JDBC从我的Java连接到数据库。 我在谷歌上搜索，我发现我必须使用这个连接字符串：

  • 通过oracle钱包连接jdbc

    基于Oracle文档，我创建了一个钱包 添加凭证 在我的java应用程序中，我想通过这个钱包连接到数据库 但我不知道如何填充连接字符串。我不想使用tnsnames。谢谢

  • Oracle与JDBC的连接

  • 通过R连接Oracle 10g数据库

    我想通过R从SQL数据库导入数据。我尝试了太多次（几乎6小时）连接到服务器。我运行这些命令，它显示错误：- jdbcDriver jdbc连接 我认为是司机的问题，但我无法解决。

相关问答

通过R连接Oracle 10g数据库能够通过CMD连接，但不能连接到Oracle SQL开发人员到Oracle DBjmeter与oracle的连接问题与Oracle DB的SAP Hybris连接与oracle的jdbc连接错误

相关文章

通过类的java sql连接通过网络通过JDBC连接到MySQL通过spring-rabbitmq自动重试与代理的连接通过Node.JS连接到RedisToGo通过SSH隧道的MySQL连接

相关阅读

连接到Oracle数据库与Microsoft Excel的数据连接Oracle过程与文本文件的数据连接与数据源数据连接

相关工具

tnstns-urihandlertns-ngtns-core-modules-widgetsOracle

相关文档

Oracle 备份与恢复Windows 连接管理器管理工具包 帮助文档通过例子学 Rust通过实例学习 C 语言Windows 连接管理器 帮助文档
快捷导航： 新手教程 算法原理 架构设计 Java进阶 数据库进阶 大厂专栏 面试经验 编程笔记 编程问答 所有专题 文档资料 工具软件 电子书籍 小牛导航
在线工具： 房贷计算器 个税计算器 Linux命令查询 Json格式化 正则表达式 颜色转换 AES加解密 SHA1加密 MD5加密 毒鸡汤 字数统计 随机密码生成 进制转换 Base64编解码 励志句子
Copyright © 2019-2024 小牛知识库@xnip.cn. All Rights Reserved.