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

   1 package org.cacert.gigi.database;
   2
   3 import java.io.FileInputStream;
   4 import java.io.IOException;
   5 import java.sql.Connection;
   6 import java.sql.DriverManager;
   7 import java.sql.PreparedStatement;
   8 import java.sql.ResultSet;
   9 import java.sql.SQLException;
  10 import java.util.HashMap;
  11 import java.util.Properties;
  12 import java.sql.Statement;
  13
  14 public class DatabaseConnection {
  15         public static final int CONNECTION_TIMEOUT = 24 * 60 * 60;
  16         Connection c;
  17         HashMap<String, PreparedStatement> statements = new HashMap<String, PreparedStatement>();
  18         static Properties credentials = new Properties();
  19         static {
  20                 try {
  21                         credentials.load(new FileInputStream("config/sql.properties"));
  22                 } catch (IOException e) {
  23                         e.printStackTrace();
  24                 }
  25         }
  26         Statement adHoc;
  27         public DatabaseConnection() {
  28                 try {
  29                         Class.forName(credentials.getProperty("driver"));
  30                 } catch (ClassNotFoundException e) {
  31                         e.printStackTrace();
  32                 }
  33                 tryConnect();
  34
  35         }
  36         private void tryConnect() {
  37                 try {
  38                         c = DriverManager.getConnection(credentials.getProperty("url")
  39                                         + "?zeroDateTimeBehavior=convertToNull",
  40                                         credentials.getProperty("user"),
  41                                         credentials.getProperty("password"));
  42                         PreparedStatement ps = c
  43                                         .prepareStatement("SET SESSION wait_timeout=?;");
  44                         ps.setInt(1, CONNECTION_TIMEOUT);
  45                         ps.execute();
  46                         ps.close();
  47                         adHoc = c.createStatement();
  48                 } catch (SQLException e) {
  49                         e.printStackTrace();
  50                 }
  51         }
  52         public PreparedStatement prepare(String query) throws SQLException {
  53                 ensureOpen();
  54                 PreparedStatement statement = statements.get(query);
  55                 if (statement == null) {
  56                         statement = c.prepareStatement(query);
  57                         statements.put(query, statement);
  58                 }
  59                 return statement;
  60         }
  61         long lastAction = System.currentTimeMillis();
  62         private void ensureOpen() {
  63                 if (System.currentTimeMillis() - lastAction > CONNECTION_TIMEOUT * 1000L) {
  64                         try {
  65                                 ResultSet rs = adHoc.executeQuery("SELECT 1");
  66                                 rs.close();
  67                                 lastAction = System.currentTimeMillis();
  68                                 return;
  69                         } catch (SQLException e) {
  70                         }
  71                         statements.clear();
  72                         tryConnect();
  73                 }
  74                 lastAction = System.currentTimeMillis();
  75         }
  76         public static int lastInsertId(PreparedStatement query) throws SQLException {
  77                 ResultSet rs = query.getGeneratedKeys();
  78                 rs.next();
  79                 int id = rs.getInt(1);
  80                 rs.close();
  81                 return id;
  82         }
  83         static ThreadLocal<DatabaseConnection> instances = new ThreadLocal<DatabaseConnection>() {
  84                 @Override
  85                 protected DatabaseConnection initialValue() {
  86                         return new DatabaseConnection();
  87                 }
  88         };
  89         public static DatabaseConnection getInstance() {
  90                 return instances.get();
  91         }
  92 }