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                 String sql = createTableSql(table);
  35                 try (PreparedStatement ps = con.prepareStatement(sql)) {
  36                         ps.executeUpdate();
  37                 }
  38                 catch (SQLException exc) {
  39                         throw new SQLException("Failed to executeUpdate:  " + sql, exc);
  40                 }
  41
  42                 return true;
  43         }
  44
  45         public boolean createSequence(Connection con, Sequence seq) throws SQLException
  46         {
  47                 String sql = createSequenceSql(seq);
  48                 try (PreparedStatement ps = con.prepareStatement(sql)) {
  49                         ps.executeUpdate();
  50                 }
  51                 catch (SQLException exc) {
  52                         throw new SQLException("Failed to executeUpdate:  " + sql, exc);
  53                 }
  54
  55                 return true;
  56         }
  57
  58         public List<Row> select(Connection con, Column[] columns, Table[] tables, Condition[] conditions)
  59                 throws SQLException
  60         {
  61                 String sql = selectSql(columns, tables, conditions);
  62                 ArrayList<Row> result = new ArrayList<Row>();
  63
  64                 try (PreparedStatement ps = con.prepareStatement(sql)) {
  65                         int index = 0;
  66                         for (Condition condition : conditions) {
  67                                 if (condition.getOperation().hasParam()) {
  68                                         index++;
  69                                         ps.setObject(index, condition.getValue());
  70                                 }
  71                         }
  72
  73                         try (ResultSet rs = ps.executeQuery()) {
  74                                 while (rs.next()) {
  75                                         Object[] values = new Object[columns.length];
  76                                         for (index = 0; index < columns.length; ++index) {
  77                                                 values[index] = rs.getObject(index);
  78                                         }
  79                                         Row row = new Row(columns, values);
  80                                         result.add(row);
  81                                 }
  82                         }
  83                 }
  84
  85                 return result;
  86         }
  87
  88         // Returns the number of rows inserted
  89         public int insert(Connection con, Table table, Object[][] values) throws SQLException
  90         {
  91                 int count = 0;
  92                 int pendingValues = 0;
  93
  94                 String sql = insertSql(table);
  95
  96                 try (PreparedStatement ps = con.prepareStatement(sql))
  97                 {
  98                         for (int row = 0; row < values.length; ++row) {
  99                                 Object[] data = values[row];
 100
 101                                 assert(null != data);
 102                                 assert(data.length == table.getNumColumns());
 103
 104                                 for (int col = 0; col < data.length; ++col) {
 105                                         ps.setObject(col + 1, data[col]);
 106                                         pendingValues++;
 107                                 }
 108                                 ps.addBatch();
 109
 110                                 int rowsFlushed = checkFlushBatch(ps, pendingValues, false);
 111                                 if (rowsFlushed > 0) {
 112                                         count += rowsFlushed;
 113                                         pendingValues = 0;
 114                                 }
 115                         }
 116
 117                         count += checkFlushBatch(ps, pendingValues, true);
 118                 }
 119
 120                 return count;
 121         }
 122
 123         int checkFlushBatch(PreparedStatement ps, int pendingValues, boolean forceFlush) throws SQLException
 124         {
 125                 int count = 0;
 126
 127                 if (forceFlush || (pendingValues >= PENDING_LIMIT))
 128                 {
 129                         int[] updateCounts = ps.executeBatch();
 130                         for (int i = 0; i < updateCounts.length; ++i) {
 131                                 if (updateCounts[i] > 0) {
 132                                         count += updateCounts[i];
 133                                 }
 134                         }
 135                 }
 136
 137                 return count;
 138         }
 139
 140         String insertSql(Table table) {
 141                 StringBuilder sb = new StringBuilder("INSERT INTO ");
 142                 sb.append(table.getName())
 143                   .append(" VALUES (");
 144
 145                 for (int i = 0; i < table.getNumColumns(); ++i) {
 146                         if (i > 0) {
 147                                 sb.append(",");
 148                         }
 149                         sb.append("?");
 150                 }
 151                 sb.append(")");
 152
 153                 return sb.toString();
 154         }
 155
 156         protected String selectSql(Column[] columns, Table[] tables, Condition[] conditions)
 157         {
 158                 StringBuilder sb = new StringBuilder("SELECT ");
 159
 160                 boolean firstColumn = true;
 161                 for (Column column : columns) {
 162                         if (firstColumn) {
 163                                 firstColumn = false;
 164                         }
 165                         else {
 166                                 sb.append(", ");
 167                         }
 168                         sb.append(column.getName());
 169                 }
 170
 171                 sb.append(" FROM ");
 172
 173                 boolean firstTable = true;
 174                 for (Table table : tables) {
 175                         if (firstTable) {
 176                                 firstTable = false;
 177                         }
 178                         else {
 179                                 sb.append(", ");
 180                         }
 181                         sb.append(table.getName());
 182                 }
 183
 184                 if (null != conditions && conditions.length > 0) {
 185                         sb.append(" WHERE ");
 186
 187                         boolean firstCondition = true;
 188
 189                         for (Condition condition : conditions) {
 190                                 if (firstCondition) {
 191                                         firstCondition = false;
 192                                 }
 193                                 else {
 194                                         sb.append(" AND ");
 195                                 }
 196
 197                                 sb.append(condition.getColumn().getName())
 198                                   .append(condition.getOperation().getSql());
 199                         }
 200                 }
 201
 202                 return sb.toString();
 203         }
 204
 205         protected String typeName(Type type) {
 206                 return type.toString();
 207         }
 208
 209         protected String createColumnSql(Column column)
 210         {
 211                 String result = column.getName() + " " + typeName(column.getType());
 212                 if (column.getWidth() > 0) {
 213                         result += "(" + column.getWidth() + ")";
 214                 }
 215
 216                 if (NOT_NULL == column.getNull()) {
 217                         result += " NOT NULL";
 218                 }
 219                 else {
 220                         result += " NULL";
 221                 }
 222
 223                 return result;
 224         }
 225
 226         protected String createTableSql(Table table)
 227         {
 228                 assert(null != table);
 229                 assert(null != table.getName());
 230                 assert(table.getNumColumns() > 0);
 231
 232                 StringBuilder sb = new StringBuilder();
 233
 234                 sb.append("CREATE TABLE ")
 235                   .append(table.getName())
 236                   .append("(");
 237
 238                 for (int idx = 0; idx < table.getNumColumns(); ++idx) {
 239                         if (idx > 0) {
 240                                 sb.append(", ");
 241                         }
 242                         sb.append(createColumnSql(table.getColumn(idx)));
 243                 }
 244
 245                 sb.append(")");
 246
 247                 return sb.toString();
 248         }
 249
 250         protected String createSequenceSql(Sequence seq) {
 251                 assert(null != seq);
 252                 assert(null != seq.getName());
 253
 254                 return "CREATE SEQUENCE " + seq.getName();
 255         }
 256 }