Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

spring

jdbc

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
     }

}
like image 754
Faz Avatar asked Jan 02 '14 18:01

Faz


1 Answers

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.

like image 107
Evgeni Dimitrov Avatar answered Nov 12 '22 08:11

Evgeni Dimitrov