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