JDBCTemplate setQueryTimeout specific for each query i.e, Query Level

Question

I need to set query timeout for various ranges of queries depending on the data they pull. ie, each query will have their own timeout time.

say for instance, query A - > 10 MINUTES
query B - > 5 MINUTES

Now how do I set these different timings using the getJDBCTemplate(). When I try the below code snippet, the timeout is overidden and both the queries are timing out on the same time irrespective of the setting!

Thread t1 = new Thread(new Runnable() {
public void run() {
       getJdbcTemplate().setQueryTimeout(5);
       List t = getJdbcTemplate()
                   .query("select top 10000 * from ABC",new RowMapper<T>(){
                   ..
       });

Thread t2 = new Thread(new Runnable() {
public void run() {
       getJdbcTemplate().setQueryTimeout(10);
       List t = getJdbcTemplate()
        .query("select top 30000 * from XYZ",new RowMapper<T>() {
                        ..
       });
t1.start();
t2.start();  

In the above context, both the queries are timing out at 5th min or 10th min. Is there a way to set it up based on the query ? please suggest!

[UPDATE]

<bean id="dSource" class="com.xyz.DSource" >
    <property name="dataSource" ref="dataSource"/>
</bean>

public abstract class AbstractData  {
    private DSource dSource;
    public JdbcTemplate getJdbcTemplate(){

        ApplicationContext Ctx = ContextUtils.getApplicationContext(); 
        dSource = (DSource)Ctx.getBean("dSource");
        return dSource.getJDBCTemplate();
         }
}

public class DSource extends JdbcDaoSupport{
    public JdbcTemplate getJdbcTemplate(){
        return getJdbcTemplate();
    }
}

public Class Dao extends AbstractData{
     public void callQuery(){
        [AS already posted, t1 and t2 are 2 threads for 2 diff methods/queries using 
         getJDBCTemplate Of abstract classs]

       Thread 1 
       Thread 2
     }

}

Answer 1

Spring JdbcTemplate has method variants that accept CallableStatementCreator / PreparedStatementCreator objects as a parameter. You can use these objects to create and configure statements.

For example, you can write implementation of CallableStatementCreator to support query-level timeout as follows:

public class TimeoutCallableStatementCreator implements CallableStatementCreator {

    private final String callString;
    private final int timeout;

    public TimeoutCallableStatementCreator(String callString, int timeout) {
        Assert.notNull(callString, "Call string must not be null");
        this.callString = callString;
        this.timeout = timeout;
    }

    @Override
    public CallableStatement createCallableStatement(Connection con) throws SQLException {
        CallableStatement stmt = con.prepareCall(this.callString);
        stmt.setQueryTimeout(timeout);
        return stmt;
    }
}

Note: If you set timeout for JdbcTemplate, it overrides timeout set through CallableStatementCreator / PreparedStatementCreator.

Answer 2

The timeout is overridden probably because because your JdbcTemplate is singleton(please add its configuration). To achieve what you want you need dedicated JdbcTemplate for every class(or method).

Thread t1 = new Thread(new Runnable() {
public void run() {
       JdbcTemplate template = new JdbcTemplate(dataSource);
       template.setQueryTimeout(5);
       List t = template
                   .query("select top 10000 * from ABC",new RowMapper<T>(){
                   ..
       });

I don't think this is ideal. Probably better solution will be to use pure jdbc and set the timeout directly to the prpared statement

    Connection con = jdbcTemplate.getDataSource().getConnection()
    preparedstatement = con.prepareStatement(sql);
    preparedstatement.setQueryTimeout(theTimeout);

You have t check if Spring will close the statement and connection in this case or you will need to handle this yourself.