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.*;
   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.Table;
  20
  21 public abstract class DbDriver {
  22         static int PENDING_LIMIT = 1024;        // Rough limit at which point we'll start a new batch for batch updates
  23
  24         DbDriver() {
  25
  26         }
  27
  28         // Load the JDBC driver
  29         public abstract void load() throws ClassNotFoundException;
  30
  31         public abstract Connection connect(String host, int port, String dbName, String user, String pass) throws SQLException;
  32
  33         public boolean createTable(Connection con, Table table) throws SQLException
  34         {
  35                 String sql = createTableSql(table);
  36                 try (PreparedStatement ps = con.prepareStatement(sql)) {
  37                         ps.executeUpdate();
  38                 }
  39                 catch (SQLException exc) {
  40                         throw new SQLException("Failed to executeUpdate:  " + sql, exc);
  41                 }
  42
  43                 return true;
  44         }
  45
  46         public void dropTable(Connection con, Table table) throws SQLException
  47         {
  48                 String sql = dropTableSql(table);
  49                 try (PreparedStatement ps = con.prepareStatement(sql)) {
  50                         ps.executeUpdate();
  51                 }
  52                 catch (SQLException exc) {
  53                         throw new SQLException("Failed to drop table:  " + sql, exc);
  54                 }
  55         }
  56
  57         public boolean createSequence(Connection con, Sequence seq) throws SQLException
  58         {
  59                 String sql = createSequenceSql(seq);
  60                 try (PreparedStatement ps = con.prepareStatement(sql)) {
  61                         ps.executeUpdate();
  62                 }
  63                 catch (SQLException exc) {
  64                         throw new SQLException("Failed to executeUpdate:  " + sql, exc);
  65                 }
  66
  67                 return true;
  68         }
  69
  70         public void dropSequence(Connection con, Sequence seq) throws SQLException
  71         {
  72                 String sql = dropSequenceSql(seq);
  73                 try (PreparedStatement ps = con.prepareStatement(sql)) {
  74                         ps.executeUpdate();
  75                 }
  76                 catch (SQLException exc) {
  77                         throw new SQLException("Failed to drop sequence:  " + sql, exc);
  78                 }
  79         }
  80
  81         public List<Row> select(Connection con, Column[] columns, Table[] tables, Condition[] conditions)
  82                 throws SQLException
  83         {
  84                 String sql = selectSql(columns, tables, conditions);
  85                 ArrayList<Row> result = new ArrayList<Row>();
  86
  87                 try (PreparedStatement ps = con.prepareStatement(sql)) {
  88                         int index = 0;
  89                         for (Condition condition : conditions) {
  90                                 if (condition.getOperation().hasParam()) {
  91                                         index++;
  92                                         ps.setObject(index, condition.getValue());
  93                                 }
  94                         }
  95
  96                         try (ResultSet rs = ps.executeQuery()) {
  97                                 while (rs.next()) {
  98                                         Object[] values = new Object[columns.length];
  99                                         for (index = 0; index < columns.length; ++index) {
 100                                                 values[index] = rs.getObject(index + 1);
 101                                         }
 102                                         Row row = new Row(columns, values);
 103                                         result.add(row);
 104                                 }
 105                         }
 106                 }
 107
 108                 return result;
 109         }
 110
 111         // Returns the number of rows inserted
 112         public int insert(Connection con, Table table, Object[][] values) throws SQLException
 113         {
 114                 int count = 0;
 115                 int pendingValues = 0;
 116
 117                 String sql = insertSql(table);
 118
 119                 try (PreparedStatement ps = con.prepareStatement(sql))
 120                 {
 121                         for (int row = 0; row < values.length; ++row) {
 122                                 Object[] data = values[row];
 123
 124                                 assert(null != data);
 125                                 assert(data.length == table.getNumColumns());
 126
 127                                 for (int col = 0; col < data.length; ++col) {
 128                                         ps.setObject(col + 1, data[col]);
 129                                         pendingValues++;
 130                                 }
 131                                 ps.addBatch();
 132
 133                                 int rowsFlushed = checkFlushBatch(ps, pendingValues, false);
 134                                 if (rowsFlushed > 0) {
 135                                         count += rowsFlushed;
 136                                         pendingValues = 0;
 137                                 }
 138                         }
 139
 140                         count += checkFlushBatch(ps, pendingValues, true);
 141                 }
 142
 143                 return count;
 144         }
 145
 146         public long nextVal(Connection con, Sequence seq) throws SQLException
 147         {
 148                 String sql = nextValSql(seq);
 149
 150                 try (PreparedStatement ps = con.prepareStatement(sql))
 151                 {
 152                         try (ResultSet rs = ps.executeQuery()) {
 153                                 if (rs.next()) {
 154                                         return rs.getLong(1);
 155                                 }
 156                         }
 157                 }
 158
 159                 throw new SQLException("No value returned for sequence:  " + sql);
 160         }
 161
 162         int checkFlushBatch(PreparedStatement ps, int pendingValues, boolean forceFlush) throws SQLException
 163         {
 164                 int count = 0;
 165
 166                 if (forceFlush || (pendingValues >= PENDING_LIMIT))
 167                 {
 168                         int[] updateCounts = ps.executeBatch();
 169                         for (int i = 0; i < updateCounts.length; ++i) {
 170                                 if (updateCounts[i] > 0) {
 171                                         count += updateCounts[i];
 172                                 }
 173                         }
 174                 }
 175
 176                 return count;
 177         }
 178
 179         String insertSql(Table table) {
 180                 StringBuilder sb = new StringBuilder("INSERT INTO ");
 181                 sb.append(table.getName())
 182                   .append(" VALUES (");
 183
 184                 for (int i = 0; i < table.getNumColumns(); ++i) {
 185                         if (i > 0) {
 186                                 sb.append(",");
 187                         }
 188                         sb.append("?");
 189                 }
 190                 sb.append(")");
 191
 192                 return sb.toString();
 193         }
 194
 195         protected String selectSql(Column[] columns, Table[] tables, Condition[] conditions)
 196         {
 197                 StringBuilder sb = new StringBuilder("SELECT ");
 198
 199                 boolean firstColumn = true;
 200                 for (Column column : columns) {
 201                         if (firstColumn) {
 202                                 firstColumn = false;
 203                         }
 204                         else {
 205                                 sb.append(", ");
 206                         }
 207                         sb.append(column.getName());
 208                 }
 209
 210                 sb.append(" FROM ");
 211
 212                 boolean firstTable = true;
 213                 for (Table table : tables) {
 214                         if (firstTable) {
 215                                 firstTable = false;
 216                         }
 217                         else {
 218                                 sb.append(", ");
 219                         }
 220                         sb.append(table.getName());
 221                 }
 222
 223                 if (null != conditions && conditions.length > 0) {
 224                         sb.append(" WHERE ");
 225
 226                         boolean firstCondition = true;
 227
 228                         for (Condition condition : conditions) {
 229                                 if (firstCondition) {
 230                                         firstCondition = false;
 231                                 }
 232                                 else {
 233                                         sb.append(" AND ");
 234                                 }
 235
 236                                 sb.append(condition.getColumn().getName())
 237                                   .append(condition.getOperation().getSql());
 238                         }
 239                 }
 240
 241                 return sb.toString();
 242         }
 243
 244         protected String typeName(Type type) {
 245                 return type.toString();
 246         }
 247
 248         protected String createColumnSql(Column column)
 249         {
 250                 String result = column.getName() + " " + typeName(column.getType());
 251                 if (column.getWidth() > 0) {
 252                         result += "(" + column.getWidth() + ")";
 253                 }
 254
 255                 if (NOT_NULL == column.getNull()) {
 256                         result += " NOT NULL";
 257                 }
 258                 else {
 259                         result += " NULL";
 260                 }
 261
 262                 return result;
 263         }
 264
 265         protected String createTableSql(Table table)
 266         {
 267                 assert(null != table);
 268                 assert(null != table.getName());
 269                 assert(table.getNumColumns() > 0);
 270
 271                 StringBuilder sb = new StringBuilder();
 272
 273                 sb.append("CREATE TABLE ")
 274                   .append(table.getName())
 275                   .append("(");
 276
 277                 for (int idx = 0; idx < table.getNumColumns(); ++idx) {
 278                         if (idx > 0) {
 279                                 sb.append(", ");
 280                         }
 281                         sb.append(createColumnSql(table.getColumn(idx)));
 282                 }
 283
 284                 sb.append(")");
 285
 286                 return sb.toString();
 287         }
 288
 289         protected String dropTableSql(Table table) {
 290                 assert(null != table);
 291                 assert(null != table.getName());
 292
 293                 return "DROP TABLE " + table.getName();
 294         }
 295
 296         protected String createSequenceSql(Sequence seq) {
 297                 assert(null != seq);
 298                 assert(null != seq.getName());
 299
 300                 return "CREATE SEQUENCE " + seq.getName();
 301         }
 302
 303         protected String dropSequenceSql(Sequence seq) {
 304                 assert(null != seq);
 305                 assert(null != seq.getName());
 306
 307                 return "DROP SEQUENCE " + seq.getName();
 308         }
 309
 310         abstract protected String nextValSql(Sequence seq);
 311 }