Connect to an external Oracle database using JDBC

Question

I've setup a Spring project that access a remote Oracle database but only if I run this Spring project on my local Tomcat. If I deploy it to CF either as JavaWeb or Spring project I always get the same error message:

root cause

org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Cannot open connection org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:3 ....

This is my config:

<bean id="dataSource" class="oracle.jdbc.pool.OracleDataSource">
    <property name="driverType">
        <value>oracle.jdbc.driver.OracleDriver</value>
    </property>
    <property name="URL">
        <value>${jdbc.url}</value>
    </property>
    <property name="user">
        <value>${jdbc.username}</value>
    </property>
    <property name="password">
        <value>${jdbc.password}</value>
    </property>
</bean>

<bean id="entityManagerFactory"
      class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" p:dataSource-ref="dataSource">
    <property name="persistenceXmlLocation" value="classpath:META-INF/persistence.xml"/>
    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" p:showSql="true"
              p:generateDdl="true">
        </bean>
    </property>
    <property name="jpaProperties">
        <value>
            hibernate.ejb.naming_strategy=org.hibernate.cfg.ImprovedNamingStrategy
            hibernate.dialect=${hibernate.dialect}
            hibernate.hbm2ddl.auto=${hibernate.hbm2ddl.auto}
        </value>
    </property>
</bean>

I've tried to use oracle.jdbc.OracleDriver instead of oracle.jdbc.driver.OracleDriver. I've tried to use <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"..., with corresponding property names. Also I've tried:

<bean id="dataSource" class="org.springframework.jdbc.datasource.SingleConnectionDataSource">
    <property name="driverClassName">
        <value>${database.driverClassName}</value>
    </property>
    <property name="url">
        <value>${jdbc.url}</value>
    </property>
    <property name="username">
        <value>${jdbc.username}</value>
    </property>
    <property name="password">
        <value>${jdbc.password}</value>
    </property>
    <property name="suppressClose" value="true"/>
    <property name="autoCommit" value="true"/>
</bean>

and

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName">
        <value>${database.driverClassName}</value>
    </property>
    <property name="url">
        <value>${jdbc.url}</value>
    </property>
    <property name="username">
        <value>${jdbc.username}</value>
    </property>
    <property name="password">
        <value>${jdbc.password}</value>
    </property>
</bean>

Nothing worked, I get always the same error.

I've tried to use HSQL with this config

and these jdbc.properties:

# HSQL
database.driverClassName = org.hsqldb.jdbcDriver
#jdbc.url = jdbc:hsqldb:file:target/zktodo2test.dat
jdbc.url = jdbc:hsqldb:file:zktodo2test.dat
jdbc.username = sa
jdbc.password =

hibernate.dialect = org.hibernate.dialect.HSQLDialect
hibernate.hbm2ddl.auto = update

...and it worked but when using these jdbc.properties:

# Oracle Credentials
database.driverClassName = oracle.jdbc.OracleDriver
jdbc.url = jdbc:oracle:thin:@dev.example.com:1521:dev
jdbc.username = epvin
jdbc.password = my_password

hibernate.dialect = org.hibernate.dialect.OracleDialect
hibernate.hbm2ddl.auto = update

I get the error mentioned above.

Did you confirm by printing the data source properties with log level for spring package set to debug, to see if the login/password are replaced properly? — Vikdor
– Vikdor, Commented Nov 9, 2012 at 8:29
In your case always deploy the app as JavaWeb instead of Spring, for the latter will re-write the datasource properties. You should be aware of the doc here: docs.cloudfoundry.com/frameworks/java/spring/spring.html. As Vikdor asked, it would be a good idea to print out the values of these properties. — William Gu
– William Gu, Commented Nov 9, 2012 at 9:29
I've replaced ${jdbc.values} by actual values. So the values should be correct. Also I've added the missing directives from the page William gave me. Still the same error. — Alex
– Alex, Commented Nov 9, 2012 at 14:29
Hey, replacing with the actual value will not help. The property values will be re-configured by CF. To be more specific, you might want to read the doc of this section: docs.cloudfoundry.com/frameworks/java/spring/… — William Gu
– William Gu, Commented Nov 12, 2012 at 10:15

Alex · Accepted Answer · 2012-11-12 07:06:08Z

2

It is a weird Oracle-specific authentication issue. Authenticating to a MySQL service running on the same server works fine.

answered Nov 12, 2012 at 7:06

Alex

8,5739 gold badges51 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

William Gu Over a year ago

Hey, in that case would you try deploying to your local server and connect to the same Orcacle data source to have a test?

Collectives™ on Stack Overflow

Connect to an external Oracle database using JDBC

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related