CODEWITHSUNDEEP
javamultithreadingspringjdbc
erik-sn
erik-sn

Reputation: 2600

Multithreading with Spring JDBC

I am having a connection issue with Spring JDBC and an SQL 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.

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.

And the catch is, when I run this method using a single threaded version it works perfectly fine. So I believe the actual URL /user/pass/driver setup is ok. I am new to multithreading so I think there is a flaw somewhere in my implementation.

I am using Spring JDBC with Apache Tomcat JNDI connection pooling:

Java Multithreading: 

public List<Item> getSetPoints(List<Item> 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);
    }
}

DAO:

@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) -> {
                item.setPiDate(rs.getString("time"));
                item.setPiValue(rs.getString("value"));
                return item;
            });
        } catch (Exception e) {
           System.out.println(e);
        }
    }
}

Spring DAO Container: 

<jee:jndi-lookup jndi-name="jdbc/PI" id="pijdbc"
                 expected-type="javax.sql.DataSource">
</jee:jndi-lookup>

JNDI Configuration:

 <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**"
/>

Stacktrace when error occurs: 

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

error : java.sql.SQLException: [Orb.Channel] The channel is not registered on server, is unique to the particular driver I am using.

After some research it essentially means that the connection to the server was dropped - however, looking at the logs on the SQL server indicate it was not dropped on the server side.

Upvotes: 3

Views: 4170

Answers (1)

Zim-Zam O&#39;Pootertoot
Zim-Zam O&#39;Pootertoot

Reputation: 18148

Try creating a fresh NamedParameterJdbcTemplate within the retrieveSetPoint method to see if this eliminates any problems you're having with timeouts

@Component("PIDAO")
public class PIDAO {

    private DataSource jdbc;

    @Resource(name="pijdbc")
    public void setPiDataSource(DataSource jdbc) {
        this.jdbc = 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 (new NamedParameterJdbcTemplate(jdbc)).queryForObject(sql, params, (rs, rowNum) -> {
                item.setPiDate(rs.getString("time"));
                item.setPiValue(rs.getString("value"));
                return item;
            });
        } catch (Exception e) {
           System.out.println(e);
        }
    }
}

Alternatively, you can reuse the NamedParameterJdbcTemplates and refresh them when they time out; this can benefit from explicit pooling, e.g.

private final int poolSize = 10;

public Collection<Item> getSetPoints(List<Item> items) {
    ExecutorService executorService = Executors.newFixedThreadPool(poolSize);
    Queue<Item> queue = new ConcurrentLinkedQueue<>();
    queue.addAll(items);
    Collection<Item> output = new ConcurrentLinkedQueue<>();
    for(int i = 0; i < poolSize; i++) {
        executorService.submit(new ProcessItem(queue, output);
    }
    return output;
} 

class ProcessItem implements Runnable {
    private final Queue<Item> queue;
    private final Collection<Item> output;
    private NamedParameterJdbcTemplate jdbc;

    public ProcessItem(Queue<Item> queue, Collection<Item> output) {
        this.queue = queue;
        this.output = output;
        this.jdbc = piDAO.getNamedJdbcTemplate();
    }

    public void run() {
        Item item = null;
        while((item = queue.poll()) != null) {
            try {
                output.add(piDAO.retrieveSetPoint(item, jdbc));
            } catch(SQLException e) {
                this.jdbc = piDAO.getNamedJdbcTemplate();
                output.add(piDAO.retrieveSetPoint(item, jdbc));
            }
        }
    }
}

@Component("PIDAO")
public class PIDAO {

    private DataSource jdbc;

    @Resource(name="pijdbc")
    public void setPiDataSource(DataSource jdbc) {
        this.jdbc = jdbc;
    }

    public NamedParameterJdbcTemplate getNamedJdbcTemplate() {
        return new NamedParameterJdbcTemplate(jdbc);
    }

    public Item retrieveSetPoint(Item item, NamedParameterJdbcTemplate template) throws SQLException {
        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";
        return template.queryForObject(sql, params, (rs, rowNum) -> {
            item.setPiDate(rs.getString("time"));
            item.setPiValue(rs.getString("value"));
            return item;
        });
    }
}

Upvotes: 1

Related Questions