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

   1 package net.jaekl.cfb.db.driver;
   2
   3 // Copyright (C) 2015 Christian Jaekl
   4
   5 import static net.jaekl.cfb.db.Column.Null.NOT_NULL;
   6
   7 import java.sql.Connection;
   8 import java.sql.PreparedStatement;
   9 import java.sql.ResultSet;
  10 import java.sql.SQLException;
  11 import java.util.ArrayList;
  12 import java.util.Date;
  13 import java.util.List;
  14
  15 import net.jaekl.cfb.db.Column;
  16 import net.jaekl.cfb.db.Column.Type;
  17 import net.jaekl.cfb.db.Condition;
  18 import net.jaekl.cfb.db.Row;
  19 import net.jaekl.cfb.db.Sequence;
  20 import net.jaekl.cfb.db.Sort;
  21 import net.jaekl.cfb.db.Table;
  22
  23 public abstract class DbDriver {
  24         static int PENDING_LIMIT = 1024;        // Rough limit at which point we'll start a new batch for batch updates
  25
  26         DbDriver() {
  27
  28         }
  29
  30         // Load the JDBC driver
  31         public abstract void load() throws ClassNotFoundException;
  32
  33         public abstract Connection connect(String host, int port, String dbName, String user, String pass) throws SQLException;
  34
  35         public boolean createTable(Connection con, Table table) throws SQLException
  36         {
  37                 String sql = createTableSql(table);
  38                 try (PreparedStatement ps = con.prepareStatement(sql)) {
  39                         ps.executeUpdate();
  40                 }
  41                 catch (SQLException exc) {
  42                         throw new SQLException("Failed to executeUpdate:  " + sql, exc);
  43                 }
  44
  45                 return true;
  46         }
  47
  48         public void dropTable(Connection con, Table table) throws SQLException
  49         {
  50                 String sql = dropTableSql(table);
  51                 try (PreparedStatement ps = con.prepareStatement(sql)) {
  52                         ps.executeUpdate();
  53                 }
  54                 catch (SQLException exc) {
  55                         throw new SQLException("Failed to drop table:  " + sql, exc);
  56                 }
  57         }
  58
  59         public boolean createSequence(Connection con, Sequence seq) throws SQLException
  60         {
  61                 String sql = createSequenceSql(seq);
  62                 try (PreparedStatement ps = con.prepareStatement(sql)) {
  63                         ps.executeUpdate();
  64                 }
  65                 catch (SQLException exc) {
  66                         throw new SQLException("Failed to executeUpdate:  " + sql, exc);
  67                 }
  68
  69                 return true;
  70         }
  71
  72         public void dropSequence(Connection con, Sequence seq) throws SQLException
  73         {
  74                 String sql = dropSequenceSql(seq);
  75                 try (PreparedStatement ps = con.prepareStatement(sql)) {
  76                         ps.executeUpdate();
  77                 }
  78                 catch (SQLException exc) {
  79                         throw new SQLException("Failed to drop sequence:  " + sql, exc);
  80                 }
  81         }
  82
  83         public List<Row> select(Connection con, Column[] columns, Table[] tables, Condition[] conditions)
  84                 throws SQLException
  85         {
  86                 Sort[] sorts = new Sort[0];
  87                 int limit = (-1);       // no limit
  88
  89                 return select(con, columns, tables, conditions, sorts, limit);
  90         }
  91
  92         public List<Row> select(Connection con, Column[] columns, Table[] tables, Condition[] conditions, Sort[] sorts, int limit)
  93                 throws SQLException
  94         {
  95                 String sql = selectSql(columns, tables, conditions, sorts, limit);
  96                 ArrayList<Row> result = new ArrayList<Row>();
  97
  98                 try (PreparedStatement ps = con.prepareStatement(sql)) {
  99                         int index = 0;
 100                         for (Condition condition : conditions) {
 101                                 if (condition.getOperation().hasParam()) {
 102                                         index++;
 103                                         ps.setObject(index, condition.getValue());
 104                                 }
 105                         }
 106
 107                         try (ResultSet rs = ps.executeQuery()) {
 108                                 while (rs.next()) {
 109                                         Object[] values = new Object[columns.length];
 110                                         for (index = 0; index < columns.length; ++index) {
 111                                                 if (columns[index].getType().equals(Type.TIMESTAMPTZ)) {
 112                                                         long milliseconds = rs.getLong(index + 1);
 113                                                         values[index] = new java.util.Date(milliseconds);
 114                                                 }
 115                                                 else {
 116                                                         values[index] = rs.getObject(index + 1);
 117                                                 }
 118                                         }
 119                                         Row row = new Row(columns, values);
 120                                         result.add(row);
 121                                 }
 122                         }
 123                 }
 124
 125                 return result;
 126         }
 127
 128         // Returns the number of rows inserted
 129         public int insert(Connection con, Table table, Object[][] values) throws SQLException
 130         {
 131                 int count = 0;
 132                 int pendingValues = 0;
 133
 134                 String sql = insertSql(table);
 135
 136                 try (PreparedStatement ps = con.prepareStatement(sql))
 137                 {
 138                         for (int row = 0; row < values.length; ++row) {
 139                                 Object[] data = values[row];
 140
 141                                 assert(null != data);
 142                                 assert(data.length == table.getNumColumns());
 143
 144                                 for (int col = 0; col < data.length; ++col) {
 145                                         Object obj = data[col];
 146                                         Column column = table.getColumn(col);
 147                                         if (column.getType().equals(Type.TIMESTAMPTZ)) {
 148                                                 // Special case:  because there's no good way to read a TIMESTAMPTZ from
 149                                                 // the database using JDBC, we store it as an integer (milliseconds since
 150                                                 // the epoch, 01.01.1970 00:00:00.000 UTC).
 151                                                 Date date = (Date)obj;
 152                                                 ps.setLong(col + 1, date.getTime());
 153                                         }
 154                                         else {
 155                                                 ps.setObject(col + 1, data[col]);
 156                                         }
 157                                         pendingValues++;
 158                                 }
 159                                 ps.addBatch();
 160
 161                                 int rowsFlushed = checkFlushBatch(ps, pendingValues, false);
 162                                 if (rowsFlushed > 0) {
 163                                         count += rowsFlushed;
 164                                         pendingValues = 0;
 165                                 }
 166                         }
 167
 168                         count += checkFlushBatch(ps, pendingValues, true);
 169                 }
 170
 171                 return count;
 172         }
 173
 174         public long nextVal(Connection con, Sequence seq) throws SQLException
 175         {
 176                 String sql = nextValSql(seq);
 177
 178                 try (PreparedStatement ps = con.prepareStatement(sql))
 179                 {
 180                         try (ResultSet rs = ps.executeQuery()) {
 181                                 if (rs.next()) {
 182                                         return rs.getLong(1);
 183                                 }
 184                         }
 185                 }
 186
 187                 throw new SQLException("No value returned for sequence:  " + sql);
 188         }
 189
 190         int checkFlushBatch(PreparedStatement ps, int pendingValues, boolean forceFlush) throws SQLException
 191         {
 192                 int count = 0;
 193
 194                 if (forceFlush || (pendingValues >= PENDING_LIMIT))
 195                 {
 196                         int[] updateCounts = ps.executeBatch();
 197                         for (int i = 0; i < updateCounts.length; ++i) {
 198                                 if (updateCounts[i] > 0) {
 199                                         count += updateCounts[i];
 200                                 }
 201                         }
 202                 }
 203
 204                 return count;
 205         }
 206
 207         String insertSql(Table table) {
 208                 StringBuilder sb = new StringBuilder("INSERT INTO ");
 209                 sb.append(table.getName())
 210                   .append(" VALUES (");
 211
 212                 for (int i = 0; i < table.getNumColumns(); ++i) {
 213                         if (i > 0) {
 214                                 sb.append(",");
 215                         }
 216                         sb.append("?");
 217                 }
 218                 sb.append(")");
 219
 220                 return sb.toString();
 221         }
 222
 223         protected String selectSql(Column[] columns, Table[] tables, Condition[] conditions, Sort[] sorts, int limit)
 224         {
 225                 StringBuilder sb = new StringBuilder("SELECT ");
 226
 227                 boolean firstColumn = true;
 228                 for (Column column : columns) {
 229                         if (firstColumn) {
 230                                 firstColumn = false;
 231                         }
 232                         else {
 233                                 sb.append(", ");
 234                         }
 235                         sb.append(column.getName());
 236                 }
 237
 238                 sb.append(" FROM ");
 239
 240                 boolean firstTable = true;
 241                 for (Table table : tables) {
 242                         if (firstTable) {
 243                                 firstTable = false;
 244                         }
 245                         else {
 246                                 sb.append(", ");
 247                         }
 248                         sb.append(table.getName());
 249                 }
 250
 251                 if (null != conditions && conditions.length > 0) {
 252                         sb.append(" WHERE ");
 253
 254                         boolean firstCondition = true;
 255
 256                         for (Condition condition : conditions) {
 257                                 if (firstCondition) {
 258                                         firstCondition = false;
 259                                 }
 260                                 else {
 261                                         sb.append(" AND ");
 262                                 }
 263
 264                                 sb.append(condition.getColumn().getName())
 265                                   .append(condition.getOperation().getSql());
 266                         }
 267                 }
 268
 269                 if (null != sorts && sorts.length > 0) {
 270                         sb.append(" ORDER BY ");
 271
 272                         boolean firstSort = true;
 273
 274                         for (Sort sort : sorts) {
 275                                 if (firstSort) {
 276                                         firstSort = false;
 277                                 }
 278                                 else {
 279                                         sb.append(", ");
 280                                 }
 281
 282                                 sb.append(sort.getColumn().getName());
 283
 284                                 if (sort.getDirection().equals(Sort.Direction.ASCENDING)) {
 285                                         sb.append(" ASCENDING ");
 286                                 }
 287                                 else {
 288                                         sb.append(" DESCENDING ");
 289                                 }
 290                         }
 291                 }
 292
 293                 if (limit > 0) {
 294                         sb.append(" LIMIT " + limit + " ");
 295                 }
 296
 297                 return sb.toString();
 298         }
 299
 300         protected String typeName(Type type) {
 301                 // Special case:  TIMESTAMPTZ stored as INTEGER (milliseconds since the epoch)
 302                 // Reading a TIMESTAMPTZ back from the DB, and converting it to a java.util.Date,
 303                 // is fraught with peril.  The best way around this is to store the dates in
 304                 // milliseconds-since-the-epoch (01.01.1970 00:00:00.000 UTC).
 305                 if (Type.TIMESTAMPTZ.equals(type)) {
 306                         return Type.INTEGER.toString();
 307                 }
 308
 309                 return type.toString();
 310         }
 311
 312         protected String createColumnSql(Column column)
 313         {
 314                 String result = column.getName() + " " + typeName(column.getType());
 315                 if (column.getWidth() > 0) {
 316                         result += "(" + column.getWidth() + ")";
 317                 }
 318
 319                 if (NOT_NULL == column.getNull()) {
 320                         result += " NOT NULL";
 321                 }
 322                 else {
 323                         result += " NULL";
 324                 }
 325
 326                 return result;
 327         }
 328
 329         protected String createTableSql(Table table)
 330         {
 331                 assert(null != table);
 332                 assert(null != table.getName());
 333                 assert(table.getNumColumns() > 0);
 334
 335                 StringBuilder sb = new StringBuilder();
 336
 337                 sb.append("CREATE TABLE ")
 338                   .append(table.getName())
 339                   .append("(");
 340
 341                 for (int idx = 0; idx < table.getNumColumns(); ++idx) {
 342                         if (idx > 0) {
 343                                 sb.append(", ");
 344                         }
 345                         sb.append(createColumnSql(table.getColumn(idx)));
 346                 }
 347
 348                 sb.append(")");
 349
 350                 return sb.toString();
 351         }
 352
 353         protected String dropTableSql(Table table) {
 354                 assert(null != table);
 355                 assert(null != table.getName());
 356
 357                 return "DROP TABLE " + table.getName();
 358         }
 359
 360         protected String createSequenceSql(Sequence seq) {
 361                 assert(null != seq);
 362                 assert(null != seq.getName());
 363
 364                 return "CREATE SEQUENCE " + seq.getName();
 365         }
 366
 367         protected String dropSequenceSql(Sequence seq) {
 368                 assert(null != seq);
 369                 assert(null != seq.getName());
 370
 371                 return "DROP SEQUENCE " + seq.getName();
 372         }
 373
 374         abstract protected String nextValSql(Sequence seq);
 375 }