prod/net/jaekl/cfb/db/driver/SqliteDriver.java

   1 package net.jaekl.cfb.db.driver;
   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
   9 import net.jaekl.cfb.db.Sequence;
  10
  11 public class SqliteDriver extends DbDriver {
  12         static final long SEQ_NO_VALUE = (-1);
  13         static final long SEQ_INIT_VALUE = 0;
  14
  15         @Override
  16         public void load() throws ClassNotFoundException {
  17                 Class.forName("org.sqlite.JDBC");
  18         }
  19
  20         @Override
  21         public Connection connect(String host, int port, String dbName, String user, String pass)
  22                 throws SQLException
  23         {
  24                 String url = "jdbc:sqlite:" + dbName;
  25                 return DriverManager.getConnection(url);
  26         }
  27
  28         @Override
  29         public long nextVal(Connection con, Sequence seq) throws SQLException
  30         {
  31                 final String sqlRead = "SELECT value FROM " + seq.getName();
  32                 final String sqlIncr = "UPDATE " + seq.getName() + " SET value=? WHERE value=?";
  33                 long value = SEQ_NO_VALUE;
  34
  35                 boolean auto = con.getAutoCommit();
  36                 try (
  37                                 PreparedStatement psRead = con.prepareStatement(sqlRead);
  38                                 PreparedStatement psIncr = con.prepareStatement(sqlIncr);
  39                         )
  40                 {
  41                         con.setAutoCommit(false);
  42
  43                         for (int retryCount = 10; retryCount > 0; retryCount--) {
  44                                 try ( ResultSet rs = psRead.executeQuery() )
  45                                 {
  46                                         if (rs.next()) {
  47                                                 value = rs.getLong(1);
  48                                         }
  49                                 }
  50
  51                                 if (value >= SEQ_INIT_VALUE) {
  52                                         psIncr.setLong(1, value + 1);
  53                                         psIncr.setLong(2, value);
  54                                         int count = psIncr.executeUpdate();
  55                                         if (1 == count) {
  56                                                 return (value + 1);
  57                                         }
  58                                 }
  59                         }
  60                 }
  61                 finally {
  62                         con.setAutoCommit(auto);
  63                 }
  64
  65                 throw new SQLException("Unable to get next value for sequence " + seq.getName() + ".  Retry count exceeded.");
  66         }
  67
  68         @Override
  69         public boolean createSequence(Connection con, Sequence seq)
  70                 throws SQLException
  71         {
  72                 String sqlCreate = "CREATE TABLE " + seq.getName() + " ( value INTEGER )";
  73                 String sqlInsert = "INSERT INTO " + seq.getName() + " VALUES ( 0 )";
  74
  75                 executeUpdate(con, sqlCreate);
  76                 executeUpdate(con, sqlInsert);
  77
  78                 return true;
  79         }
  80 }