Keepalive for database connections

diff --git a/src/org/cacert/gigi/database/DatabaseConnection.java b/src/org/cacert/gigi/database/DatabaseConnection.java
index c1eed41b5391d6ff18e75faf3fd5a916137aeb6b..36b702114ca56316edc961a283bb6f6d38d881f0 100644 (file)
--- a/src/org/cacert/gigi/database/DatabaseConnection.java
+++ b/src/org/cacert/gigi/database/DatabaseConnection.java
@@ -9,8 +9,10 @@ import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.util.HashMap;
 import java.util.Properties;
 import java.sql.SQLException;
 import java.util.HashMap;
 import java.util.Properties;

+import java.sql.Statement;

 
 public class DatabaseConnection {
 
 public class DatabaseConnection {

+       public static final int CONNECTION_TIMEOUT = 24 * 60 * 60;

        Connection c;
        HashMap<String, PreparedStatement> statements = new HashMap<String, PreparedStatement>();
        static Properties credentials = new Properties();
        Connection c;
        HashMap<String, PreparedStatement> statements = new HashMap<String, PreparedStatement>();
        static Properties credentials = new Properties();


@@ -21,23 +23,34 @@ public class DatabaseConnection {
                        e.printStackTrace();
                }
        }
                        e.printStackTrace();
                }
        }

+       Statement adHoc;

        public DatabaseConnection() {
                try {
                        Class.forName(credentials.getProperty("driver"));
                } catch (ClassNotFoundException e) {
                        e.printStackTrace();
                }
        public DatabaseConnection() {
                try {
                        Class.forName(credentials.getProperty("driver"));
                } catch (ClassNotFoundException e) {
                        e.printStackTrace();
                }

+               tryConnect();
+
+       }
+       private void tryConnect() {

                try {
                        c = DriverManager.getConnection(credentials.getProperty("url")
                                        + "?zeroDateTimeBehavior=convertToNull",
                                        credentials.getProperty("user"),
                                        credentials.getProperty("password"));
                try {
                        c = DriverManager.getConnection(credentials.getProperty("url")
                                        + "?zeroDateTimeBehavior=convertToNull",
                                        credentials.getProperty("user"),
                                        credentials.getProperty("password"));

+                       PreparedStatement ps = c
+                                       .prepareStatement("SET SESSION wait_timeout=?;");
+                       ps.setInt(1, CONNECTION_TIMEOUT);
+                       ps.execute();
+                       ps.close();
+                       adHoc = c.createStatement();

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

-

        }
        public PreparedStatement prepare(String query) throws SQLException {
        }
        public PreparedStatement prepare(String query) throws SQLException {

+               ensureOpen();

                PreparedStatement statement = statements.get(query);
                if (statement == null) {
                        statement = c.prepareStatement(query);
                PreparedStatement statement = statements.get(query);
                if (statement == null) {
                        statement = c.prepareStatement(query);


@@ -45,7 +58,21 @@ public class DatabaseConnection {
                }
                return statement;
        }
                }
                return statement;
        }

-
+       long lastAction = System.currentTimeMillis();
+       private void ensureOpen() {
+               if (System.currentTimeMillis() - lastAction > CONNECTION_TIMEOUT * 1000L) {
+                       try {
+                               ResultSet rs = adHoc.executeQuery("SELECT 1");
+                               rs.close();
+                               lastAction = System.currentTimeMillis();
+                               return;
+                       } catch (SQLException e) {
+                       }
+                       statements.clear();
+                       tryConnect();
+               }
+               lastAction = System.currentTimeMillis();
+       }

        public static int lastInsertId(PreparedStatement query) throws SQLException {
                ResultSet rs = query.getGeneratedKeys();
                rs.next();
        public static int lastInsertId(PreparedStatement query) throws SQLException {
                ResultSet rs = query.getGeneratedKeys();
                rs.next();