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.sql.Timestamp;
  12 import java.util.ArrayList;
  13 import java.util.Date;
  14 import java.util.List;
  15
  16 import net.jaekl.cfb.db.Column;
  17 import net.jaekl.cfb.db.Column.Type;
  18 import net.jaekl.cfb.db.Condition;
  19 import net.jaekl.cfb.db.Row;
  20 import net.jaekl.cfb.db.Sequence;
  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                 String sql = selectSql(columns, tables, conditions);
  87                 ArrayList<Row> result = new ArrayList<Row>();
  88
  89                 try (PreparedStatement ps = con.prepareStatement(sql)) {
  90                         int index = 0;
  91                         for (Condition condition : conditions) {
  92                                 if (condition.getOperation().hasParam()) {
  93                                         index++;
  94                                         ps.setObject(index, condition.getValue());
  95                                 }
  96                         }
  97
  98                         try (ResultSet rs = ps.executeQuery()) {
  99                                 while (rs.next()) {
 100                                         Object[] values = new Object[columns.length];
 101                                         for (index = 0; index < columns.length; ++index) {
 102                                                 values[index] = rs.getObject(index + 1);
 103                                         }
 104                                         Row row = new Row(columns, values);
 105                                         result.add(row);
 106                                 }
 107                         }
 108                 }
 109
 110                 return result;
 111         }
 112
 113         // Returns the number of rows inserted
 114         public int insert(Connection con, Table table, Object[][] values) throws SQLException
 115         {
 116                 int count = 0;
 117                 int pendingValues = 0;
 118
 119                 String sql = insertSql(table);
 120
 121                 try (PreparedStatement ps = con.prepareStatement(sql))
 122                 {
 123                         for (int row = 0; row < values.length; ++row) {
 124                                 Object[] data = values[row];
 125
 126                                 assert(null != data);
 127                                 assert(data.length == table.getNumColumns());
 128
 129                                 for (int col = 0; col < data.length; ++col) {
 130                                         Object obj = data[col];
 131                                         if (obj instanceof java.util.Date) {
 132                                                 Date date = (Date)obj;
 133                                                 Timestamp ts = new Timestamp(date.getTime());
 134                                                 ps.setTimestamp(col + 1, ts);
 135                                         }
 136                                         else {
 137                                                 ps.setObject(col + 1, data[col]);
 138                                         }
 139                                         pendingValues++;
 140                                 }
 141                                 ps.addBatch();
 142
 143                                 int rowsFlushed = checkFlushBatch(ps, pendingValues, false);
 144                                 if (rowsFlushed > 0) {
 145                                         count += rowsFlushed;
 146                                         pendingValues = 0;
 147                                 }
 148                         }
 149
 150                         count += checkFlushBatch(ps, pendingValues, true);
 151                 }
 152
 153                 return count;
 154         }
 155
 156         public long nextVal(Connection con, Sequence seq) throws SQLException
 157         {
 158                 String sql = nextValSql(seq);
 159
 160                 try (PreparedStatement ps = con.prepareStatement(sql))
 161                 {
 162                         try (ResultSet rs = ps.executeQuery()) {
 163                                 if (rs.next()) {
 164                                         return rs.getLong(1);
 165                                 }
 166                         }
 167                 }
 168
 169                 throw new SQLException("No value returned for sequence:  " + sql);
 170         }
 171
 172         int checkFlushBatch(PreparedStatement ps, int pendingValues, boolean forceFlush) throws SQLException
 173         {
 174                 int count = 0;
 175
 176                 if (forceFlush || (pendingValues >= PENDING_LIMIT))
 177                 {
 178                         int[] updateCounts = ps.executeBatch();
 179                         for (int i = 0; i < updateCounts.length; ++i) {
 180                                 if (updateCounts[i] > 0) {
 181                                         count += updateCounts[i];
 182                                 }
 183                         }
 184                 }
 185
 186                 return count;
 187         }
 188
 189         String insertSql(Table table) {
 190                 StringBuilder sb = new StringBuilder("INSERT INTO ");
 191                 sb.append(table.getName())
 192                   .append(" VALUES (");
 193
 194                 for (int i = 0; i < table.getNumColumns(); ++i) {
 195                         if (i > 0) {
 196                                 sb.append(",");
 197                         }
 198                         sb.append("?");
 199                 }
 200                 sb.append(")");
 201
 202                 return sb.toString();
 203         }
 204
 205         protected String selectSql(Column[] columns, Table[] tables, Condition[] conditions)
 206         {
 207                 StringBuilder sb = new StringBuilder("SELECT ");
 208
 209                 boolean firstColumn = true;
 210                 for (Column column : columns) {
 211                         if (firstColumn) {
 212                                 firstColumn = false;
 213                         }
 214                         else {
 215                                 sb.append(", ");
 216                         }
 217                         sb.append(column.getName());
 218                 }
 219
 220                 sb.append(" FROM ");
 221
 222                 boolean firstTable = true;
 223                 for (Table table : tables) {
 224                         if (firstTable) {
 225                                 firstTable = false;
 226                         }
 227                         else {
 228                                 sb.append(", ");
 229                         }
 230                         sb.append(table.getName());
 231                 }
 232
 233                 if (null != conditions && conditions.length > 0) {
 234                         sb.append(" WHERE ");
 235
 236                         boolean firstCondition = true;
 237
 238                         for (Condition condition : conditions) {
 239                                 if (firstCondition) {
 240                                         firstCondition = false;
 241                                 }
 242                                 else {
 243                                         sb.append(" AND ");
 244                                 }
 245
 246                                 sb.append(condition.getColumn().getName())
 247                                   .append(condition.getOperation().getSql());
 248                         }
 249                 }
 250
 251                 return sb.toString();
 252         }
 253
 254         protected String typeName(Type type) {
 255                 return type.toString();
 256         }
 257
 258         protected String createColumnSql(Column column)
 259         {
 260                 String result = column.getName() + " " + typeName(column.getType());
 261                 if (column.getWidth() > 0) {
 262                         result += "(" + column.getWidth() + ")";
 263                 }
 264
 265                 if (NOT_NULL == column.getNull()) {
 266                         result += " NOT NULL";
 267                 }
 268                 else {
 269                         result += " NULL";
 270                 }
 271
 272                 return result;
 273         }
 274
 275         protected String createTableSql(Table table)
 276         {
 277                 assert(null != table);
 278                 assert(null != table.getName());
 279                 assert(table.getNumColumns() > 0);
 280
 281                 StringBuilder sb = new StringBuilder();
 282
 283                 sb.append("CREATE TABLE ")
 284                   .append(table.getName())
 285                   .append("(");
 286
 287                 for (int idx = 0; idx < table.getNumColumns(); ++idx) {
 288                         if (idx > 0) {
 289                                 sb.append(", ");
 290                         }
 291                         sb.append(createColumnSql(table.getColumn(idx)));
 292                 }
 293
 294                 sb.append(")");
 295
 296                 return sb.toString();
 297         }
 298
 299         protected String dropTableSql(Table table) {
 300                 assert(null != table);
 301                 assert(null != table.getName());
 302
 303                 return "DROP TABLE " + table.getName();
 304         }
 305
 306         protected String createSequenceSql(Sequence seq) {
 307                 assert(null != seq);
 308                 assert(null != seq.getName());
 309
 310                 return "CREATE SEQUENCE " + seq.getName();
 311         }
 312
 313         protected String dropSequenceSql(Sequence seq) {
 314                 assert(null != seq);
 315                 assert(null != seq.getName());
 316
 317                 return "DROP SEQUENCE " + seq.getName();
 318         }
 319
 320         abstract protected String nextValSql(Sequence seq);
 321 }