title: Multithreading with Spring JDBC
body: <p>I am having a connection issue with Spring <code>JDBC</code> and an <code>SQL</code> database. The problem is that on the first try, my method creates n amount of threads, they query the database, and no issues occur. If I immediately run the method again, same thing - no issues. Note I am not restarting the application between tries.</p>
  
  <p>The issue occurs when I wait for a few minutes before running the application again - so I assume there is a timeout issue somewhere, or threads are being abandoned.</p>
  
  <p>And the catch is, when I run this method using a single threaded version it works perfectly fine. So I believe the actual URL <code>/user/pass/driver</code> setup is ok. I am new to multithreading so I think there is a flaw somewhere in my implementation.</p>
  
  <p>I am using Spring <code>JDBC</code> with Apache Tomcat <code>JNDI</code> connection pooling:</p>
  
  <p><strong>Java Multithreading:</strong> </p>
  
  <pre><code>public List&lt;Item&gt; getSetPoints(List&lt;Item&gt; items) {
      ExecutorService executorService = Executors.newFixedThreadPool(10);
      for(Item item: items) {
          executorService.submit(new ProcessItem(item));
      }
  
      executorService.shutdown();
  } 
  
  class ProcessItem implements Runnable {
      private Item item;
  
      public ProcessItem(Item item) {
          this.item = item;
      }
  
      public void run() {
          Item newItem = piDAO.retrieveSetPoint(item);
      }
  }
  </code></pre>
  
  <p>DAO:</p>
  
  <pre><code>@Component("PIDAO")
  public class PIDAO {
  
      private NamedParameterJdbcTemplate jdbc;
  
      @Resource(name="pijdbc")
      public void setPiDataSource(DataSource jdbc) {
          this.jdbc = new NamedParameterJdbcTemplate(jdbc);
      }
  
      public Item retrieveSetPoint(Item item) {
          MapSqlParameterSource params = new MapSqlParameterSource();
          params.addValue("tag", item.getTagName());
  
          String sql = "SELECT TOP 1  time, value, status FROM piarchive.picomp2 WHERE tag = :tag AND status=0 AND questionable = false ORDER BY time DESC";
          try {
              return jdbc.queryForObject(sql, params, (rs, rowNum) -&gt; {
                  item.setPiDate(rs.getString("time"));
                  item.setPiValue(rs.getString("value"));
                  return item;
              });
          } catch (Exception e) {
             System.out.println(e);
          }
      }
  }
  </code></pre>
  
  <p>Spring DAO Container: </p>
  
  <pre><code>&lt;jee:jndi-lookup jndi-name="jdbc/PI" id="pijdbc"
                   expected-type="javax.sql.DataSource"&gt;
  &lt;/jee:jndi-lookup&gt;
  </code></pre>
  
  <p>JNDI Configuration:</p>
  
  <pre><code> &lt;Resource
      name="jdbc/PI"
      auth="Container"
      type="javax.sql.DataSource"
      maxTotal ="25"
      maxIdle="30"
      maxWaitMillis ="10000"
      driverClassName="com.osisoft.jdbc.Driver"
      url="**Valid URL**"
      username="**Valid Username**"
      password="**Valid Password**"
  /&gt;
  </code></pre>
  
  <p>Stacktrace when error occurs: </p>
  
  <pre><code>org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [SELECT TOP 1  time, value, status FROM piarchive.picomp2 WHERE tag = ? AND status=0 AND questionable = false ORDER BY time DESC]; SQL state [null]; error code [0]; [Orb.Channel] The channel is not registered on server.; nested exception is java.sql.SQLException: [Orb.Channel] The channel is not registered on server.
  at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)
  at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
  at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
  at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:645)
  at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:680)
  at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:707)
  at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:757)
  at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForObject(NamedParameterJdbcTemplate.java:211)
  at btv.app.dao.PIDAO.retrieveSetPoint(PIDAO.java:36)
  at btv.app.service.PiService$ProcessItem.run(PiService.java:91)
  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)
  Caused by: java.sql.SQLException: [Orb.Channel] The channel is not registered on server.
  at com.osisoft.jdbc.PreparedStatementImpl.executeQuery(PreparedStatementImpl.java:167)
  at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:82)
  at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:82)
  at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:688)
  at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:629)
  ... 11 more
  </code></pre>
  
  <p><code>error : java.sql.SQLException: [Orb.Channel] The channel is not registered on server</code>, is unique to the particular driver I am using. </p>
  
  <p>After some research it essentially means that the connection to the server was dropped - however, looking at the logs on the <code>SQL</code> server indicate it was not dropped on the server side. </p>