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