title: How to implement an Oracle AQ queue in Spring Boot?
body: <p>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).</p>
  
  <p>However, I'm having a hard time setting up a proper connection from Spring.</p>
  
  <p>The following works (using <code>oracle.AQ</code> java package):</p>
  
  <pre><code>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());
  
  }
  </code></pre>
  
  <p>(based on <a href="https://docs.oracle.com/cd/B10501_01/appdev.920/a96587/apexampl.htm" rel="nofollow noreferrer">https://docs.oracle.com/cd/B10501_01/appdev.920/a96587/apexampl.htm</a>)</p>
  
  <p>This shows me that I can connect to Oracle and reach AQ functionality.</p>
  
  <p>Now, I'm trying to create Java Configured beans in order to use the <code>JmsTemplate</code>. </p>
  
  <pre><code>@Resource
  private JmsTemplate jmsTemplate;
  
  @Test
  public void testJmsTemplate() {
      String xmlval = "&lt;?xml version=\"1.0\" encoding=\"UTF-8\"?&gt;\n" +
              "&lt;product id=\"10\"&gt;\n" +
              " &lt;description&gt;Foo&lt;/description&gt;\n" +
              " &lt;price&gt;2.05&lt;/price&gt;\n" +
              "&lt;/product&gt;";
  
      jmsTemplate.convertAndSend(aqSchema + ".jms_ws_incoming_queue", xmlval);
  }
  </code></pre>
  
  <p>(yes, the queue already exists ;-))</p>
  
  <p>With the following configuration class:</p>
  
  <pre><code>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;
      }
  }
  </code></pre>
  
  <p>And with properties yml:</p>
  
  <pre><code>spring:
    datasource:
      url: jdbc:oracle:thin:@localhost:1521:orcl
      username: queue_mut
      password: ******
      driverClassName: oracle.jdbc.driver.OracleDriver
  </code></pre>
  
  <p>But with this I get errors which I cannot grasp:</p>
  
  <pre><code>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.&lt;init&gt;(AQjmsDBConnMgr.java:399)
      at oracle.jms.AQjmsConnection.&lt;init&gt;(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.&lt;init&gt;(AQjmsGeneralDBConnection.java:67)
      at oracle.jms.AQjmsDBConnMgr.getConnection(AQjmsDBConnMgr.java:566)
      ... 41 more
  </code></pre>
  
  <p>I believe that the Cast exception occurs because it is a <code>ProxyConnection[PooledConnection[oracle.jdbc.driver.T4CConnection@40016ce1]]</code>. But I don't know how to fix this.</p>