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 Row selectExactlyOne(Connection con, Column[] columns, Table[] tables, Condition[] conditions)
  84                 throws SQLException
  85         {
  86                 Sort[] sorts = new Sort[0];
  87                 int limit = 2;
  88                 List<Row> rows = select(con, columns, tables, conditions, sorts, limit);
  89                 if (rows.size() < 1) {
  90                         throw new SQLException("Expected one result, but found none:  ", selectSql(columns, tables, conditions, sorts, limit));
  91                 }
  92                 if (rows.size() > 1) {
  93                         throw new SQLException("Expected one result, but found more than one:  " + selectSql(columns, tables, conditions, sorts, limit));
  94                 }
  95
  96                 return rows.get(0);
  97         }
  98
  99         public List<Row> select(Connection con, Column[] columns, Table[] tables, Condition[] conditions)
 100                 throws SQLException
 101         {
 102                 Sort[] sorts = new Sort[0];
 103                 int limit = (-1);       // no limit
 104
 105                 return select(con, columns, tables, conditions, sorts, limit);
 106         }
 107
 108         public List<Row> select(Connection con, Column[] columns, Table[] tables, Condition[] conditions, Sort[] sorts, int limit)
 109                 throws SQLException
 110         {
 111                 String sql = selectSql(columns, tables, conditions, sorts, limit);
 112                 ArrayList<Row> result = new ArrayList<Row>();
 113
 114                 try (PreparedStatement ps = con.prepareStatement(sql)) {
 115                         int index = 0;
 116                         for (Condition condition : conditions) {
 117                                 if (condition.getOperation().hasParam()) {
 118                                         index++;
 119                                         ps.setObject(index, condition.getValue());
 120                                 }
 121                         }
 122
 123                         try (ResultSet rs = ps.executeQuery()) {
 124                                 while (rs.next()) {
 125                                         Object[] values = new Object[columns.length];
 126                                         for (index = 0; index < columns.length; ++index) {
 127                                                 if (columns[index].getType().equals(Type.TIMESTAMPTZ)) {
 128                                                         long milliseconds = rs.getLong(index + 1);
 129                                                         values[index] = new java.util.Date(milliseconds);
 130                                                 }
 131                                                 else {
 132                                                         values[index] = rs.getObject(index + 1);
 133                                                 }
 134                                         }
 135                                         Row row = new Row(columns, values);
 136                                         result.add(row);
 137                                 }
 138                         }
 139                 }
 140
 141                 return result;
 142         }
 143
 144         // Returns the number of rows inserted
 145         public int insert(Connection con, Table table, Object[][] values) throws SQLException
 146         {
 147                 int count = 0;
 148                 int pendingValues = 0;
 149
 150                 assert( isValidInsert(table, values));
 151
 152                 String sql = insertSql(table);
 153
 154                 try (PreparedStatement ps = con.prepareStatement(sql))
 155                 {
 156                         for (int row = 0; row < values.length; ++row) {
 157                                 Object[] data = values[row];
 158
 159                                 assert(null != data);
 160                                 assert(data.length == table.getNumColumns());
 161
 162                                 for (int col = 0; col < data.length; ++col) {
 163                                         Object obj = data[col];
 164                                         Column column = table.getColumn(col);
 165                                         if (column.getType().equals(Type.TIMESTAMPTZ)) {
 166                                                 // Special case:  because there's no good way to read a TIMESTAMPTZ from
 167                                                 // the database using JDBC, we store it as an integer (milliseconds since
 168                                                 // the epoch, 01.01.1970 00:00:00.000 UTC).
 169                                                 Date date = (Date)obj;
 170                                                 ps.setLong(col + 1, date.getTime());
 171                                         }
 172                                         else {
 173                                                 ps.setObject(col + 1, data[col]);
 174                                         }
 175                                         pendingValues++;
 176                                 }
 177                                 ps.addBatch();
 178
 179                                 int rowsFlushed = checkFlushBatch(ps, pendingValues, false);
 180                                 if (rowsFlushed > 0) {
 181                                         count += rowsFlushed;
 182                                         pendingValues = 0;
 183                                 }
 184                         }
 185
 186                         count += checkFlushBatch(ps, pendingValues, true);
 187                 }
 188
 189                 return count;
 190         }
 191
 192         public long nextVal(Connection con, Sequence seq) throws SQLException
 193         {
 194                 String sql = nextValSql(seq);
 195
 196                 try (PreparedStatement ps = con.prepareStatement(sql))
 197                 {
 198                         try (ResultSet rs = ps.executeQuery()) {
 199                                 if (rs.next()) {
 200                                         return rs.getLong(1);
 201                                 }
 202                         }
 203                 }
 204
 205                 throw new SQLException("No value returned for sequence:  " + sql);
 206         }
 207
 208         int checkFlushBatch(PreparedStatement ps, int pendingValues, boolean forceFlush) throws SQLException
 209         {
 210                 int count = 0;
 211
 212                 if (forceFlush || (pendingValues >= PENDING_LIMIT))
 213                 {
 214                         int[] updateCounts = ps.executeBatch();
 215                         for (int i = 0; i < updateCounts.length; ++i) {
 216                                 if (updateCounts[i] > 0) {
 217                                         count += updateCounts[i];
 218                                 }
 219                         }
 220                 }
 221
 222                 return count;
 223         }
 224
 225         String insertSql(Table table) {
 226                 StringBuilder sb = new StringBuilder("INSERT INTO ");
 227                 sb.append(table.getName())
 228                   .append(" VALUES (");
 229
 230                 for (int i = 0; i < table.getNumColumns(); ++i) {
 231                         if (i > 0) {
 232                                 sb.append(",");
 233                         }
 234                         sb.append("?");
 235                 }
 236                 sb.append(")");
 237
 238                 return sb.toString();
 239         }
 240
 241         protected String selectSql(Column[] columns, Table[] tables, Condition[] conditions, Sort[] sorts, int limit)
 242         {
 243                 StringBuilder sb = new StringBuilder("SELECT ");
 244
 245                 boolean firstColumn = true;
 246                 for (Column column : columns) {
 247                         if (firstColumn) {
 248                                 firstColumn = false;
 249                         }
 250                         else {
 251                                 sb.append(", ");
 252                         }
 253                         sb.append(column.getName());
 254                 }
 255
 256                 sb.append(" FROM ");
 257
 258                 boolean firstTable = true;
 259                 for (Table table : tables) {
 260                         if (firstTable) {
 261                                 firstTable = false;
 262                         }
 263                         else {
 264                                 sb.append(", ");
 265                         }
 266                         sb.append(table.getName());
 267                 }
 268
 269                 if (null != conditions && conditions.length > 0) {
 270                         sb.append(" WHERE ");
 271
 272                         boolean firstCondition = true;
 273
 274                         for (Condition condition : conditions) {
 275                                 if (firstCondition) {
 276                                         firstCondition = false;
 277                                 }
 278                                 else {
 279                                         sb.append(" AND ");
 280                                 }
 281
 282                                 sb.append(condition.getColumn().getName())
 283                                   .append(condition.getOperation().getSql());
 284                         }
 285                 }
 286
 287                 if (null != sorts && sorts.length > 0) {
 288                         sb.append(" ORDER BY ");
 289
 290                         boolean firstSort = true;
 291
 292                         for (Sort sort : sorts) {
 293                                 if (firstSort) {
 294                                         firstSort = false;
 295                                 }
 296                                 else {
 297                                         sb.append(", ");
 298                                 }
 299
 300                                 sb.append(sort.getColumn().getName());
 301
 302                                 if (sort.getDirection().equals(Sort.Direction.ASCENDING)) {
 303                                         sb.append(" ASCENDING ");
 304                                 }
 305                                 else {
 306                                         sb.append(" DESCENDING ");
 307                                 }
 308                         }
 309                 }
 310
 311                 if (limit > 0) {
 312                         sb.append(" LIMIT " + limit + " ");
 313                 }
 314
 315                 return sb.toString();
 316         }
 317
 318         protected String typeName(Type type) {
 319                 // Special case:  TIMESTAMPTZ stored as INTEGER (milliseconds since the epoch)
 320                 // Reading a TIMESTAMPTZ back from the DB, and converting it to a java.util.Date,
 321                 // is fraught with peril.  The best way around this is to store the dates in
 322                 // milliseconds-since-the-epoch (01.01.1970 00:00:00.000 UTC).
 323                 if (Type.TIMESTAMPTZ.equals(type)) {
 324                         return Type.INTEGER.toString();
 325                 }
 326
 327                 return type.toString();
 328         }
 329
 330         protected String createColumnSql(Column column)
 331         {
 332                 String result = column.getName() + " " + typeName(column.getType());
 333                 if (column.getWidth() > 0) {
 334                         result += "(" + column.getWidth() + ")";
 335                 }
 336
 337                 if (NOT_NULL == column.getNull()) {
 338                         result += " NOT NULL";
 339                 }
 340                 else {
 341                         result += " NULL";
 342                 }
 343
 344                 return result;
 345         }
 346
 347         protected String createTableSql(Table table)
 348         {
 349                 assert(null != table);
 350                 assert(null != table.getName());
 351                 assert(table.getNumColumns() > 0);
 352
 353                 StringBuilder sb = new StringBuilder();
 354
 355                 sb.append("CREATE TABLE ")
 356                   .append(table.getName())
 357                   .append("(");
 358
 359                 for (int idx = 0; idx < table.getNumColumns(); ++idx) {
 360                         if (idx > 0) {
 361                                 sb.append(", ");
 362                         }
 363                         sb.append(createColumnSql(table.getColumn(idx)));
 364                 }
 365
 366                 sb.append(")");
 367
 368                 return sb.toString();
 369         }
 370
 371         protected String dropTableSql(Table table) {
 372                 assert(null != table);
 373                 assert(null != table.getName());
 374
 375                 return "DROP TABLE " + table.getName();
 376         }
 377
 378         protected String createSequenceSql(Sequence seq) {
 379                 assert(null != seq);
 380                 assert(null != seq.getName());
 381
 382                 return "CREATE SEQUENCE " + seq.getName();
 383         }
 384
 385         protected String dropSequenceSql(Sequence seq) {
 386                 assert(null != seq);
 387                 assert(null != seq.getName());
 388
 389                 return "DROP SEQUENCE " + seq.getName();
 390         }
 391
 392         abstract protected String nextValSql(Sequence seq);
 393
 394         boolean isValidInsert(Table table, Object[][] values)
 395         {
 396                 if (null == table) return false;
 397                 if (null == values) return false;
 398
 399                 for (Object[] rowValues : values) {
 400                         if (rowValues.length != table.getNumColumns()) {
 401                                 return false;
 402                         }
 403                         for (int idx = 0; idx < rowValues.length; ++idx) {
 404
 405                         }
 406                 }
 407
 408                 return true;
 409         }
 410 }