HSQLDB and Tomcat ServletContextListener

[Feris Thia <fe...@gmail.com>]

Dear All,

 I'm using HSQLDB as embedded in-process mode for my web application. I used
 to schedule every 1 minutes to check on the HSQLDB database. I open the db
 when context initialized and close it when context destroyed. But when the
 context reloaded I always get Java.lang.NullPointerException when trying to
 execute SQL statement.

 What is wrong ?? It looks like everytime the context is destroyed... the
 hsqldb gets lock.

 The snippet code like below :
 =======================

 import javax.servlet.ServletContext;
 import javax.servlet.ServletContextListener;
 import javax.servlet.ServletContextEvent;

 import java.nio.channels.ClosedByInterruptException;
 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.util.Random;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;

 public class ContextStartUp implements ServletContextListener {

     private ServletContext context = null;
     MyThread objThread = null;

     ResultSet rs = null;
     Connection conn = null;

     public void openDB()
     {

         try {
             conn = DriverManager.getConnection("jdbc:hsqldb:file:" +
 context.getRealPath("/data/testdb") + ";ifexists=true;shutdown=true;", "sa",
 "phidmspassword");

         } catch (SQLException e) {
             e.printStackTrace();
         }
         catch(Exception e)
         {
             e.printStackTrace();
         }
     }


     public void closeDB()
     {
         Statement stmt = null;
         try {
             if(conn!=null)
             {
                 stmt = conn.createStatement();
                 stmt.execute("SHUTDOWN COMPACT");
                 stmt.close();
                 conn.close();
                 stmt = null;
                 conn = null;
             }
         } catch (SQLException e) {
             e.printStackTrace();
         }
     }

     public class MyThread implements Runnable {

         int nomor = 0;

         Thread currentthread = null;

         public MyThread() {
             if (currentthread == null) {
                 currentthread = new Thread(this);
                 currentthread.setPriority(new Random().nextInt(5) + 1);
                 currentthread.start();
             }
         }

         public void run() {
             Statement stmt = null;

             String sqltodo = "";
             Thread myThread = Thread.currentThread();

             while (myThread == currentthread) {
                 try {
                     stmt = conn.createStatement();

                     if (stmt.execute("A select statement...... "))
                     {
                         rs = stmt.getResultSet();

                         if (rs.next()) {
                             ..........................
                         }
                     }


                        sqltodo = Update queries ......;

                         stmt.executeUpdate(sqltodo);
                         stmt.execute("SHUTDOWN");
                         stmt.close();
                     }
                     else
                     {
                         stmt.close();
                     }
                 } catch (SQLException e) {
                     e.printStackTrace();
                 } catch (Exception e) {
                     e.printStackTrace();
                 } finally {
                     stmt = null;
                 }

                 try {
                     Thread.sleep(100000);
                 } catch (InterruptedException e) {
                 }
             }
         }

     }

     public void initThread() {
         openDB();
         objThread = new MyThread();
     }

     public void stopThread() {
         objThread = null;
         closeDB();
     }

     public void contextDestroyed(ServletContextEvent event) {
         context = null;
         stopThread();
     }

     public void contextInitialized(ServletContextEvent event) {
         try {
             Class.forName("org.hsqldb.jdbcDriver").newInstance();
         } catch (InstantiationException e) {
             e.printStackTrace();
         } catch (IllegalAccessException e) {
             e.printStackTrace();
         } catch (ClassNotFoundException e) {
             e.printStackTrace();
         }

         context = event.getServletContext();

         initThread();
     }
 }

 ==============================================================


-- 
Regards,
Feris
PT. Putera Handal Indotama
JL. KH. Moh. Mansyur No. 11 Blok B.8-12
Telp. +62-21-631 6688 (Hunting)
Fax. +62-21-6330211
Jakarta (10140) - INDONESIA