src/org/cacert/gigi/database/DatabaseConnection.java

   1 package org.cacert.gigi.database;
   2
   3 import java.sql.Connection;
   4 import java.sql.DriverManager;
   5 import java.sql.PreparedStatement;
   6 import java.sql.ResultSet;
   7 import java.sql.SQLException;
   8 import java.util.HashMap;
   9 import java.util.Properties;
  10 import java.sql.Statement;
  11
  12 public class DatabaseConnection {
  13
  14     public static final int CONNECTION_TIMEOUT = 24 * 60 * 60;
  15
  16     Connection c;
  17
  18     HashMap<String, PreparedStatement> statements = new HashMap<String, PreparedStatement>();
  19
  20     private static Properties credentials;
  21
  22     Statement adHoc;
  23
  24     public DatabaseConnection() {
  25         try {
  26             Class.forName(credentials.getProperty("sql.driver"));
  27         } catch (ClassNotFoundException e) {
  28             e.printStackTrace();
  29         }
  30         tryConnect();
  31
  32     }
  33
  34     private void tryConnect() {
  35         try {
  36             c = DriverManager.getConnection(credentials.getProperty("sql.url") + "?zeroDateTimeBehavior=convertToNull", credentials.getProperty("sql.user"), credentials.getProperty("sql.password"));
  37             PreparedStatement ps = c.prepareStatement("SET SESSION wait_timeout=?;");
  38             ps.setInt(1, CONNECTION_TIMEOUT);
  39             ps.execute();
  40             ps.close();
  41             adHoc = c.createStatement();
  42         } catch (SQLException e) {
  43             e.printStackTrace();
  44         }
  45     }
  46
  47     public PreparedStatement prepare(String query) throws SQLException {
  48         ensureOpen();
  49         PreparedStatement statement = statements.get(query);
  50         if (statement == null) {
  51             statement = c.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
  52             statements.put(query, statement);
  53         }
  54         return statement;
  55     }
  56
  57     long lastAction = System.currentTimeMillis();
  58
  59     private void ensureOpen() {
  60         if (System.currentTimeMillis() - lastAction > CONNECTION_TIMEOUT * 1000L) {
  61             try {
  62                 ResultSet rs = adHoc.executeQuery("SELECT 1");
  63                 rs.close();
  64                 lastAction = System.currentTimeMillis();
  65                 return;
  66             } catch (SQLException e) {
  67             }
  68             statements.clear();
  69             tryConnect();
  70         }
  71         lastAction = System.currentTimeMillis();
  72     }
  73
  74     public static int lastInsertId(PreparedStatement query) throws SQLException {
  75         ResultSet rs = query.getGeneratedKeys();
  76         rs.next();
  77         int id = rs.getInt(1);
  78         rs.close();
  79         return id;
  80     }
  81
  82     static ThreadLocal<DatabaseConnection> instances = new ThreadLocal<DatabaseConnection>() {
  83
  84         @Override
  85         protected DatabaseConnection initialValue() {
  86             return new DatabaseConnection();
  87         }
  88     };
  89
  90     public static DatabaseConnection getInstance() {
  91         return instances.get();
  92     }
  93
  94     public static boolean isInited() {
  95         return credentials != null;
  96     }
  97
  98     public static void init(Properties conf) {
  99         if (credentials != null) {
 100             throw new Error("Re-initiaizing is forbidden.");
 101         }
 102         credentials = conf;
 103     }
 104
 105     public void beginTransaction() throws SQLException {
 106         c.setAutoCommit(false);
 107     }
 108
 109     public void commitTransaction() throws SQLException {
 110         c.commit();
 111         c.setAutoCommit(true);
 112     }
 113
 114     public void quitTransaction() {
 115         try {
 116             if ( !c.getAutoCommit()) {
 117                 c.rollback();
 118                 c.setAutoCommit(true);
 119             }
 120         } catch (SQLException e) {
 121             e.printStackTrace();
 122         }
 123     }
 124 }