Announcement Announcement Module
Collapse
No announcement yet.
Spring JDBC 2.5.6 + SimpleJdbcCall + Remote Oracle DB Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Spring JDBC 2.5.6 + SimpleJdbcCall + Remote Oracle DB

    Hello,

    We are having a problem with using Spring JDBC 2.5.6 to access an Oracle database when the database is physically placed in another part of the country. We have tested using a database in the same sub-net as the java web app running the queries and that works fine.

    Our setup is:
    Spring JDBC 2.5.6
    Data source: org.apache.commons.dbcp.BasicDataSource
    Oracle DB: 10.2.0.2.0
    Method: SimpleJdbcCall using JdbcTemplate to execute stored procedures and stored functions in the database.

    Problem:
    When the database is located in the same sub-net as the web app everything is ok.

    When the database is located far away in "the cloud" the database sometimes hangs when we execute a query. The hang can last for several minutes and sometimes even hours! the last entry in the spring debug log when the hang occurs is: Fetching JDBC Connection from DataSource

    There does not seem to be a specific pattern of when the database hangs. Sometimes it happens 10 minutes after deploy, sometimes after 2 hours.


    Does anyone have any idea how we can solve or work around the problem?


    From application-context.xml:
    <bean id="oracleDataSource" destroy-method="close"
    class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="oracle.jdbc.OracleDriver" />
    <property name="url" value="${jdbc.oracleUrl}" />
    <property name="username" value="${jdbc.oracleUserName}" />
    <property name="password" value="${jdbc.oraclePassword}" />
    <property name="validationQuery" value="SELECT 1 FROM DUAL" />
    </bean>

    Best regards
    Tobias

  • #2
    Update

    I have now changed the configuration of the BasicDataSource to:

    Code:
    <bean id="oracleDataSource" destroy-method="close"
        class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="oracle.jdbc.OracleDriver" />
        <property name="url" value="${jdbc.oracleUrl}" />
        <property name="username" value="${jdbc.oracleUserName}" />
        <property name="password" value="${jdbc.oraclePassword}" />
        <property name="validationQuery" value="SELECT 1 FROM DUAL" />
        <property name="defaultAutoCommit" value="true"/>
        <property name="maxActive" value="20"/>
        <property name="maxIdle" value="20"/>
        <property name="maxWait" value="3000"/>
        <property name="testOnBorrow" value="true"/>
        <property name="testWhileIdle" value="true"/>
        <property name="timeBetweenEvictionRunsMillis" value="300000"/>
    </bean>
    It seams to work better now. However I'm not sure I've solved the problem or if I'm just "hiding it". Maybe it is still there but harder to reproduce?

    Does anyone have some thoughts on this?

    /Tobias

    Comment

    Working...
    X