How to implement an Oracle AQ queue in Spring Boot

Question

I already found out how to create an Oracle database with AQ (Streams?) packages. I also created some queue's in Oracle (by hand). (Using PL/SQL and SQL).

However, I'm having a hard time setting up a proper connection from Spring.

The following works (using oracle.AQ java package):

private final String aqUrl = "jdbc:oracle:thin:@localhost:1521:orcl";
private final String aqUser = "queue_mut";
private final String aqPassword = "******";
private final String aqSchema = "queue_mut";
private final String aqTable = "aq_table1";
private final String aqQueue = "aq_queue1";


@Test
public void testManualAQ() throws ClassNotFoundException, SQLException, AQException {

    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection connection = DriverManager.getConnection(aqUrl, aqUser, aqPassword);
    connection.setAutoCommit(false);

    Class.forName("oracle.AQ.AQOracleDriver");
    AQSession aqSession = AQDriverManager.createAQSession(connection);
    AQQueueTable q_table = aqSession.createQueueTable(aqSchema, aqTable, new AQQueueTableProperty("RAW"));
    aqSession.createQueue(q_table, aqQueue, new AQQueueProperty());

}

(based on https://docs.oracle.com/cd/B10501_01/appdev.920/a96587/apexampl.htm)

This shows me that I can connect to Oracle and reach AQ functionality.

Now, I'm trying to create Java Configured beans in order to use the JmsTemplate.

@Resource
private JmsTemplate jmsTemplate;

@Test
public void testJmsTemplate() {
    String xmlval = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n" +
            "<product id=\"10\">\n" +
            " <description>Foo</description>\n" +
            " <price>2.05</price>\n" +
            "</product>";

    jmsTemplate.convertAndSend(aqSchema + ".jms_ws_incoming_queue", xmlval);
}

(yes, the queue already exists ;-))

With the following configuration class:

import oracle.jms.AQjmsFactory;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jms.core.JmsTemplate;

import javax.jms.ConnectionFactory;
import javax.jms.JMSException;
import javax.sql.DataSource;

@Configuration
public class OracleAQConfiguration {

    @Bean
    public DataSourceTransactionManager transactionManager(DataSource dataSource) {
        DataSourceTransactionManager manager = new DataSourceTransactionManager();
        manager.setDataSource(dataSource);
        return manager;
    }

    @Bean
    public ConnectionFactory connectionFactory(DataSource dataSource) throws JMSException {
        return AQjmsFactory.getQueueConnectionFactory(dataSource);
    }

    @Bean
    public JmsTemplate jmsTemplate(ConnectionFactory connectionFactory) {
        JmsTemplate jmsTemplate = new JmsTemplate();
        jmsTemplate.setSessionTransacted(true);
        jmsTemplate.setConnectionFactory(connectionFactory);
        return jmsTemplate;
    }
}

And with properties yml:

spring:
  datasource:
    url: jdbc:oracle:thin:@localhost:1521:orcl
    username: queue_mut
    password: ******
    driverClassName: oracle.jdbc.driver.OracleDriver

But with this I get errors which I cannot grasp:

2017-04-19 12:11:17,151  INFO my.project.QueueTest: Started QueueTest in 5.305 seconds (JVM running for 6.588)

org.springframework.jms.UncategorizedJmsException: Uncategorized exception occurred during JMS processing; nested exception is oracle.jms.AQjmsException: Error creating the db_connection; nested exception is java.lang.ClassCastException: com.sun.proxy.$Proxy102 cannot be cast to oracle.jdbc.internal.OracleConnection

    at org.springframework.jms.support.JmsUtils.convertJmsAccessException(JmsUtils.java:316)
    at org.springframework.jms.support.JmsAccessor.convertJmsAccessException(JmsAccessor.java:169)
    at org.springframework.jms.core.JmsTemplate.execute(JmsTemplate.java:487)
    at org.springframework.jms.core.JmsTemplate.send(JmsTemplate.java:570)
    at org.springframework.jms.core.JmsTemplate.convertAndSend(JmsTemplate.java:658)
    at my.project.QueueTest.testJmsTemplate(QueueTest.java:51)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:252)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:94)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:191)
    at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
    at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:51)
    at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:237)
    at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)
Caused by: oracle.jms.AQjmsException: Error creating the db_connection
    at oracle.jms.AQjmsDBConnMgr.getConnection(AQjmsDBConnMgr.java:625)
    at oracle.jms.AQjmsDBConnMgr.<init>(AQjmsDBConnMgr.java:399)
    at oracle.jms.AQjmsConnection.<init>(AQjmsConnection.java:249)
    at oracle.jms.AQjmsConnectionFactory.createConnection(AQjmsConnectionFactory.java:513)
    at org.springframework.jms.support.JmsAccessor.createConnection(JmsAccessor.java:180)
    at org.springframework.jms.core.JmsTemplate.execute(JmsTemplate.java:474)
    ... 36 more
Caused by: java.lang.ClassCastException: com.sun.proxy.$Proxy102 cannot be cast to oracle.jdbc.internal.OracleConnection
    at oracle.jms.AQjmsGeneralDBConnection.getProviderKey(AQjmsGeneralDBConnection.java:98)
    at oracle.jms.AQjmsGeneralDBConnection.<init>(AQjmsGeneralDBConnection.java:67)
    at oracle.jms.AQjmsDBConnMgr.getConnection(AQjmsDBConnMgr.java:566)
    ... 41 more

I believe that the Cast exception occurs because it is a ProxyConnection[PooledConnection[oracle.jdbc.driver.T4CConnection@40016ce1]]. But I don't know how to fix this.

Answer 1

We experienced the same exception when tried to access Oracle AQ from Spring boot. Research indicated that this exception is thrown as the Database connection pool libraries doesn't allow the access to the underlying connection required by the oracle AQ Library.(Both dbcp and tomcat connection pool libraries threw exceptions , not same but similar)

This exception was gone when we removed the Database connection pool libraries from dependencies which lead to an undesirable state of having no Database connection pooling for the entire application .

We noticed that the if we use the following method the exception is not thrown AQjmsFactory.getQueueConnectionFactory(url, info);

Perhaps connection pooling is missing with solution also, but that is limited to the component which reads from AQ . Other components in the app will have the benefits of the connection pooling

Here is the java config for the Bean definition:

@Bean
public QueueConnectionFactory connectionFactory() throws Exception {
    OracleServiceInfo serviceInfo = (OracleServiceInfo) this.cloud().getServiceInfo(NAME_PRIMARY_DS);
    Properties info = new Properties();
    String url = serviceInfo.getJdbcUrl();
    info.put("driver-name", "oracle.jdbc.OracleDriver");
    info.put("user", serviceInfo.getUserName());
    info.put("password", serviceInfo.getPassword());
    return oracle.jms.AQjmsFactory.getQueueConnectionFactory(url, info);
}

@Bean
public JmsTemplate jmsTemplate() throws Exception {
    JmsTemplate jmsTemplate = new JmsTemplate();
    jmsTemplate.setConnectionFactory(connectionFactory());
    return jmsTemplate;
}

I am not sure yet if this a good solution. But this is definitely one way to get rid of the exception discussed in the question.

Answer 2

the problem is that the AQ code expected an OracleConnection but when pooled the connection wrapped and hence it fails

Answer 3

Change the jdbc library, in my case this fixed it(if don't, try with some other versions):

<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc7</artifactId>
    <version>12.1.0.2.0</version>
</dependency>