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         DbDriver() {
  23
  24         }
  25
  26         // Load the JDBC driver
  27         public abstract void load() throws ClassNotFoundException;
  28
  29         public abstract Connection connect(String host, int port, String dbName, String user, String pass) throws SQLException;
  30
  31         public boolean createTable(Connection con, Table table) throws SQLException {
  32                 String sql = createTableSql(table);
  33                 try (PreparedStatement ps = con.prepareStatement(sql)) {
  34                         ps.executeUpdate();
  35                 }
  36                 catch (SQLException exc) {
  37                         throw new SQLException("Failed to executeUpdate:  " + sql, exc);
  38                 }
  39
  40                 return true;
  41         }
  42
  43         public boolean createSequence(Connection con, Sequence seq) throws SQLException
  44         {
  45                 String sql = createSequenceSql(seq);
  46                 try (PreparedStatement ps = con.prepareStatement(sql)) {
  47                         ps.executeUpdate();
  48                 }
  49                 catch (SQLException exc) {
  50                         throw new SQLException("Failed to executeUpdate:  " + sql, exc);
  51                 }
  52
  53                 return true;
  54         }
  55
  56         public List<Row> select(Connection con, Column[] columns, Table[] tables, Condition[] conditions)
  57                 throws SQLException
  58         {
  59                 String sql = selectSql(columns, tables, conditions);
  60                 ArrayList<Row> result = new ArrayList<Row>();
  61
  62                 try (PreparedStatement ps = con.prepareStatement(sql)) {
  63                         int index = 0;
  64                         for (Condition condition : conditions) {
  65                                 if (condition.getOperation().hasParam()) {
  66                                         index++;
  67                                         ps.setObject(index, condition.getValue());
  68                                 }
  69                         }
  70
  71                         try (ResultSet rs = ps.executeQuery()) {
  72                                 while (rs.next()) {
  73                                         Object[] values = new Object[columns.length];
  74                                         for (index = 0; index < columns.length; ++index) {
  75                                                 values[index] = rs.getObject(index);
  76                                         }
  77                                         Row row = new Row(columns, values);
  78                                         result.add(row);
  79                                 }
  80                         }
  81                 }
  82
  83                 return result;
  84         }
  85
  86         protected String selectSql(Column[] columns, Table[] tables, Condition[] conditions)
  87         {
  88                 StringBuilder sb = new StringBuilder("SELECT ");
  89
  90                 boolean firstColumn = true;
  91                 for (Column column : columns) {
  92                         if (firstColumn) {
  93                                 firstColumn = false;
  94                         }
  95                         else {
  96                                 sb.append(", ");
  97                         }
  98                         sb.append(column.getName());
  99                 }
 100
 101                 sb.append(" FROM ");
 102
 103                 boolean firstTable = true;
 104                 for (Table table : tables) {
 105                         if (firstTable) {
 106                                 firstTable = false;
 107                         }
 108                         else {
 109                                 sb.append(", ");
 110                         }
 111                         sb.append(table.getName());
 112                 }
 113
 114                 if (null != conditions && conditions.length > 0) {
 115                         sb.append(" WHERE ");
 116
 117                         boolean firstCondition = true;
 118
 119                         for (Condition condition : conditions) {
 120                                 if (firstCondition) {
 121                                         firstCondition = false;
 122                                 }
 123                                 else {
 124                                         sb.append(" AND ");
 125                                 }
 126
 127                                 sb.append(condition.getColumn().getName())
 128                                   .append(condition.getOperation().getSql());
 129                         }
 130                 }
 131
 132                 return sb.toString();
 133         }
 134
 135         protected String typeName(Type type) {
 136                 return type.toString();
 137         }
 138
 139         protected String createColumnSql(Column column)
 140         {
 141                 String result = column.getName() + " " + typeName(column.getType());
 142                 if (column.getWidth() > 0) {
 143                         result += "(" + column.getWidth() + ")";
 144                 }
 145
 146                 if (NOT_NULL == column.getNull()) {
 147                         result += " NOT NULL";
 148                 }
 149                 else {
 150                         result += " NULL";
 151                 }
 152
 153                 return result;
 154         }
 155
 156         protected String createTableSql(Table table)
 157         {
 158                 assert(null != table);
 159                 assert(null != table.getName());
 160                 assert(table.getNumColumns() > 0);
 161
 162                 StringBuilder sb = new StringBuilder();
 163
 164                 sb.append("CREATE TABLE ")
 165                   .append(table.getName())
 166                   .append("(");
 167
 168                 for (int idx = 0; idx < table.getNumColumns(); ++idx) {
 169                         if (idx > 0) {
 170                                 sb.append(", ");
 171                         }
 172                         sb.append(createColumnSql(table.getColumn(idx)));
 173                 }
 174
 175                 sb.append(")");
 176
 177                 return sb.toString();
 178         }
 179
 180         protected String createSequenceSql(Sequence seq) {
 181                 assert(null != seq);
 182                 assert(null != seq.getName());
 183
 184                 return "CREATE SEQUENCE " + seq.getName();
 185         }
 186 }