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