From 49b403316469afb4f62c5b60401667640746af37 Mon Sep 17 00:00:00 2001
From: =?utf8?q?Felix=20D=C3=B6rre?= <felix@dogcraft.de>
Date: Tue, 24 Jun 2014 23:05:53 +0200
Subject: [PATCH] Keepalive for database connections

---
 .../gigi/database/DatabaseConnection.java     | 31 +++++++++++++++++--
 1 file changed, 29 insertions(+), 2 deletions(-)

diff --git a/src/org/cacert/gigi/database/DatabaseConnection.java b/src/org/cacert/gigi/database/DatabaseConnection.java
index c1eed41b..36b70211 100644
--- 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.Statement;
 
 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();
@@ -21,23 +23,34 @@ public class DatabaseConnection {
 			e.printStackTrace();
 		}
 	}
+	Statement adHoc;
 	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"));
+			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();
 		}
-
 	}
 	public PreparedStatement prepare(String query) throws SQLException {
+		ensureOpen();
 		PreparedStatement statement = statements.get(query);
 		if (statement == null) {
 			statement = c.prepareStatement(query);
@@ -45,7 +58,21 @@ public class DatabaseConnection {
 		}
 		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();
-- 
2.39.2