// Copyright (C) 2015 Christian Jaekl
-import static net.jaekl.cfb.db.Column.Null.*;
+import static net.jaekl.cfb.db.Column.Null.NOT_NULL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
+import java.util.Date;
import java.util.List;
import net.jaekl.cfb.db.Column;
import net.jaekl.cfb.db.Condition;
import net.jaekl.cfb.db.Row;
import net.jaekl.cfb.db.Sequence;
+import net.jaekl.cfb.db.Sort;
import net.jaekl.cfb.db.Table;
public abstract class DbDriver {
public abstract Connection connect(String host, int port, String dbName, String user, String pass) throws SQLException;
- public boolean createTable(Connection con, Table table) throws SQLException {
+ public boolean createTable(Connection con, Table table) throws SQLException
+ {
String sql = createTableSql(table);
try (PreparedStatement ps = con.prepareStatement(sql)) {
ps.executeUpdate();
return true;
}
+ public void dropTable(Connection con, Table table) throws SQLException
+ {
+ String sql = dropTableSql(table);
+ try (PreparedStatement ps = con.prepareStatement(sql)) {
+ ps.executeUpdate();
+ }
+ catch (SQLException exc) {
+ throw new SQLException("Failed to drop table: " + sql, exc);
+ }
+ }
+
public boolean createSequence(Connection con, Sequence seq) throws SQLException
{
String sql = createSequenceSql(seq);
return true;
}
+ public void dropSequence(Connection con, Sequence seq) throws SQLException
+ {
+ String sql = dropSequenceSql(seq);
+ try (PreparedStatement ps = con.prepareStatement(sql)) {
+ ps.executeUpdate();
+ }
+ catch (SQLException exc) {
+ throw new SQLException("Failed to drop sequence: " + sql, exc);
+ }
+ }
+
+ public Row selectExactlyOne(Connection con, Column[] columns, Table[] tables, Condition[] conditions)
+ throws SQLException
+ {
+ Sort[] sorts = new Sort[0];
+ int limit = 2;
+ List<Row> rows = select(con, columns, tables, conditions, sorts, limit);
+ if (rows.size() < 1) {
+ throw new SQLException("Expected one result, but found none: ", selectSql(columns, tables, conditions, sorts, limit));
+ }
+ if (rows.size() > 1) {
+ throw new SQLException("Expected one result, but found more than one: " + selectSql(columns, tables, conditions, sorts, limit));
+ }
+
+ return rows.get(0);
+ }
+
public List<Row> select(Connection con, Column[] columns, Table[] tables, Condition[] conditions)
throws SQLException
{
- String sql = selectSql(columns, tables, conditions);
+ Sort[] sorts = new Sort[0];
+ int limit = (-1); // no limit
+
+ return select(con, columns, tables, conditions, sorts, limit);
+ }
+
+ public List<Row> select(Connection con, Column[] columns, Table[] tables, Condition[] conditions, Sort[] sorts, int limit)
+ throws SQLException
+ {
+ String sql = selectSql(columns, tables, conditions, sorts, limit);
ArrayList<Row> result = new ArrayList<Row>();
try (PreparedStatement ps = con.prepareStatement(sql)) {
while (rs.next()) {
Object[] values = new Object[columns.length];
for (index = 0; index < columns.length; ++index) {
- values[index] = rs.getObject(index);
+ if (columns[index].getType().equals(Type.TIMESTAMPTZ)) {
+ long milliseconds = rs.getLong(index + 1);
+ values[index] = new java.util.Date(milliseconds);
+ }
+ else {
+ values[index] = rs.getObject(index + 1);
+ }
}
Row row = new Row(columns, values);
result.add(row);
int count = 0;
int pendingValues = 0;
+ assert( isValidInsert(table, values));
+
String sql = insertSql(table);
try (PreparedStatement ps = con.prepareStatement(sql))
assert(data.length == table.getNumColumns());
for (int col = 0; col < data.length; ++col) {
- ps.setObject(col + 1, data[col]);
+ Object obj = data[col];
+ Column column = table.getColumn(col);
+ if (column.getType().equals(Type.TIMESTAMPTZ)) {
+ // Special case: because there's no good way to read a TIMESTAMPTZ from
+ // the database using JDBC, we store it as an integer (milliseconds since
+ // the epoch, 01.01.1970 00:00:00.000 UTC).
+ Date date = (Date)obj;
+ ps.setLong(col + 1, date.getTime());
+ }
+ else {
+ ps.setObject(col + 1, data[col]);
+ }
pendingValues++;
}
ps.addBatch();
return count;
}
+ public long nextVal(Connection con, Sequence seq) throws SQLException
+ {
+ String sql = nextValSql(seq);
+
+ try (PreparedStatement ps = con.prepareStatement(sql))
+ {
+ try (ResultSet rs = ps.executeQuery()) {
+ if (rs.next()) {
+ return rs.getLong(1);
+ }
+ }
+ }
+
+ throw new SQLException("No value returned for sequence: " + sql);
+ }
+
int checkFlushBatch(PreparedStatement ps, int pendingValues, boolean forceFlush) throws SQLException
{
int count = 0;
return sb.toString();
}
- protected String selectSql(Column[] columns, Table[] tables, Condition[] conditions)
+ protected String selectSql(Column[] columns, Table[] tables, Condition[] conditions, Sort[] sorts, int limit)
{
StringBuilder sb = new StringBuilder("SELECT ");
.append(condition.getOperation().getSql());
}
}
+
+ if (null != sorts && sorts.length > 0) {
+ sb.append(" ORDER BY ");
+
+ boolean firstSort = true;
+
+ for (Sort sort : sorts) {
+ if (firstSort) {
+ firstSort = false;
+ }
+ else {
+ sb.append(", ");
+ }
+
+ sb.append(sort.getColumn().getName());
+
+ if (sort.getDirection().equals(Sort.Direction.ASCENDING)) {
+ sb.append(" ASCENDING ");
+ }
+ else {
+ sb.append(" DESCENDING ");
+ }
+ }
+ }
+
+ if (limit > 0) {
+ sb.append(" LIMIT " + limit + " ");
+ }
return sb.toString();
}
protected String typeName(Type type) {
+ // Special case: TIMESTAMPTZ stored as INTEGER (milliseconds since the epoch)
+ // Reading a TIMESTAMPTZ back from the DB, and converting it to a java.util.Date,
+ // is fraught with peril. The best way around this is to store the dates in
+ // milliseconds-since-the-epoch (01.01.1970 00:00:00.000 UTC).
+ if (Type.TIMESTAMPTZ.equals(type)) {
+ return Type.INTEGER.toString();
+ }
+
return type.toString();
}
return sb.toString();
}
+ protected String dropTableSql(Table table) {
+ assert(null != table);
+ assert(null != table.getName());
+
+ return "DROP TABLE " + table.getName();
+ }
+
protected String createSequenceSql(Sequence seq) {
assert(null != seq);
assert(null != seq.getName());
return "CREATE SEQUENCE " + seq.getName();
}
+
+ protected String dropSequenceSql(Sequence seq) {
+ assert(null != seq);
+ assert(null != seq.getName());
+
+ return "DROP SEQUENCE " + seq.getName();
+ }
+
+ abstract protected String nextValSql(Sequence seq);
+
+ boolean isValidInsert(Table table, Object[][] values)
+ {
+ if (null == table) return false;
+ if (null == values) return false;
+
+ for (Object[] rowValues : values) {
+ if (rowValues.length != table.getNumColumns()) {
+ return false;
+ }
+ for (int idx = 0; idx < rowValues.length; ++idx) {
+
+ }
+ }
+
+ return true;
+ }
}
projects / cfb.git / blobdiff