• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 561
  • Last Modified:

How to kill idle connection after some time in JDBC?

I want to develop a JDBC application software to connect client to database for database query. Users may keep the connection open but do not do anything, after the connection is idle(no activity from the user) for 1 hour(for example), How can I use JDBC to keep track of idle time and disconnect the user from database ?

 Thanks a lot.
0
xxdxxd
Asked:
xxdxxd
2 Solutions
 
girionisCommented:
 There is no need to do it. Opening/closing conenctions to the database requires mroe resources than actually conserving these connections and reuse them when necessary. If you want to implement a smart connection pooling solution take a look here:

http://www.webdevelopersjournal.com/columns/connection_pool.html
0
 
xxdxxdAuthor Commented:

  I think my question is not the same as connection pooling problem. Basically, I do not want to allow the users to keep the connection while  they go for lunch or forget to close the connection when their tasks are done. If the connection is idle for some time, the java application will force the connection to close.
 
  How can I do that?
0
 
GumBCommented:
I think you are missing the point of girionis suggestion.  If you use a pool, your clients won't be getting a connection and hanging on to it for any period of time at all.  When they press a button, for example, they momentarily grab a connection from the pool which is given back to the pool as soon as the query or update or whatever finishes executing ...no one hangs on to a connection for any length of time at all.

The issue here is a design one ...you are giving each client thier own connection, which they hang on to until they logout or otherwise disappear.  With a pool, your connections are grabbed from the pool, used for a single (or possibly a couple) of statements ...then returned to the pool.  Do you see the difference?

ie. (where adminPool is the connection pool object)

public String doStuff() {

  StringBuffer tables = new StringBuffer();
  try {
    // Grab a connection from the pool...
    Connection connection = adminPool.getConnection();
    // Use it...
    DBResults results = DatabaseUtilities.getQueryResults(
      connection, createQueryString(2), false
    );
    // Release the connection back to the pool...
    adminPool.free(connection);
    tables.append(results.toHTMLTable("dark","off"));
  } catch(Exception e) {
    tables.append("Error: ").append(e.toString());
  }
  return tables.toString();
}
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Tommy BraasCommented:
Ok, to help you solve the problem immediately at hand, maybe the following will help:

import java.util.*;

class CustomObservable extends Observable
{
   public update()
   {
      setChanged();
      notifyObservers();
   }
}

public class TimeOutThread extends Thread
{
   CustomObservable observers;

   long timeOutInMillis;
   long sleepInterval = 1000;
   long counter = 0;

   boolean stopped = false;

   public TimeOutThread()
   {
      observers = new CustomObservable();
   }

   public TimeOutThread(Observer anObserver)
   {
      this();
      addObserver(anObserver);
   }

   public void addObserver(Observer anObserver)
   {
      observers.addObserver(anObserver);
   }

   public void deleteObserver(Observer anObserver)
   {
      observers.deleteObserver(anObserver);
   }

   public void run()
   {
      while( !stopped )
      {
         try
         {
            Thread.sleep(sleepInterval);
            counter += sleepInterval;
            if( counter >= timeOutInMillis )
            {
               observers.update();
            }
         }
         catch(InterruptedException ie)
         {
            counter = 0;
         }
      }
   }
 

   public void setSleepInterval(long sleepIntervalInMillis)
   {
      this.sleepInterval = sleepIntervalInMillis;
   }

   public void setSleepInterval(long sleepIntervalInMillis)
   {
      this.sleepInterval = sleepIntervalInMillis;
   }

   public void reset()
   {
      try
      {
         Thread.sleep(sleepInterval/4);
         interrupt();
      }
      catch(InterruptedException ie)
      {
      }
   }

   public void stop()
   {
      stopped = true;
   }
}

You need to implement the 'Observer' interface in the class you would want to observe the TimeOutThread. The 'Observer' interface declares one method, 'update', which will be called by the timer thread when the time out occurs.
0
 
tutranCommented:
If you application is a web application, you may want to have a UserProfile that has a "conn" as a member variable and implement
javax.servlet.http.HttpSessionBindingListener and specify two methods.  This way, when session is invalidated, the connection will be closed by web container.  If your app is not a web app, then you want to bootstrap conn.close() somewhere similar:

public void valueBound(HttpSessionBindingEvent e) {
}

public void valueUnbound(HttpSessionBindingEvent ev) {
    try {
       conn.close()
    } catch (Exception e) {
    }
}

0
 
CleanupPingCommented:
xxdxxd:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
TimYatesCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

Split between girionis and tutran.

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

TimYates
EE Cleanup Volunteer
0
 
girionisCommented:
 FIne by me :)
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now