Activate JDBC: return auto generated keys.

[gigi.git] / src / org / cacert / gigi / database / DatabaseConnection.java

diff --git a/src/org/cacert/gigi/database/DatabaseConnection.java b/src/org/cacert/gigi/database/DatabaseConnection.java
index 6d505d864cc65d01afffa3a8d5fe45f1fce40e95..1c7962299b7151662d09e9b238513631728c0ff5 100644 (file)
--- a/src/org/cacert/gigi/database/DatabaseConnection.java
+++ b/src/org/cacert/gigi/database/DatabaseConnection.java
@@ -1,48 +1,77 @@
 package org.cacert.gigi.database;
 
-import java.io.FileInputStream;
-import java.io.IOException;
 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.PreparedStatement;
+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();
-       static {
-               try {
-                       credentials.load(new FileInputStream("config/sql.properties"));
-               } catch (IOException e) {
-                       e.printStackTrace();
-               }
-       }
+       private static Properties credentials;
+       Statement adHoc;
        public DatabaseConnection() {
                try {
-                       Class.forName(credentials.getProperty("driver"));
+                       Class.forName(credentials.getProperty("sql.driver"));
                } catch (ClassNotFoundException e) {
                        e.printStackTrace();
                }
+               tryConnect();
+
+       }
+       private void tryConnect() {
                try {
-                       c = DriverManager.getConnection(credentials.getProperty("url"),
-                                       credentials.getProperty("user"),
-                                       credentials.getProperty("password"));
+                       c = DriverManager.getConnection(credentials.getProperty("sql.url")
+                                       + "?zeroDateTimeBehavior=convertToNull",
+                                       credentials.getProperty("sql.user"),
+                                       credentials.getProperty("sql.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);
+                       statement = c.prepareStatement(query,
+                                       Statement.RETURN_GENERATED_KEYS);
                        statements.put(query, 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();
+               int id = rs.getInt(1);
+               rs.close();
+               return id;
+       }
        static ThreadLocal<DatabaseConnection> instances = new ThreadLocal<DatabaseConnection>() {
                @Override
                protected DatabaseConnection initialValue() {
@@ -52,4 +81,27 @@ public class DatabaseConnection {
        public static DatabaseConnection getInstance() {
                return instances.get();
        }
+       public static void init(Properties conf) {
+               if (credentials != null) {
+                       throw new Error("Re-initiaizing is forbidden.");
+               }
+               credentials = conf;
+       }
+       public void beginTransaction() throws SQLException {
+               c.setAutoCommit(false);
+       }
+       public void commitTransaction() throws SQLException {
+               c.commit();
+               c.setAutoCommit(true);
+       }
+       public void quitTransaction() {
+               try {
+                       if (!c.getAutoCommit()) {
+                               c.rollback();
+                               c.setAutoCommit(true);
+                       }
+               } catch (SQLException e) {
+                       e.printStackTrace();
+               }
+       }
 }