X-Git-Url: http://jaekl.net/gitweb/?a=blobdiff_plain;f=prod%2Fnet%2Fjaekl%2Fcfb%2Fdb%2Fdriver%2FDbDriver.java;h=b8d1ee6bf5f67935a882d62d57667a82cd81a3b6;hb=a1378c84c773511e4ffe99fb419da67af188aff7;hp=ecfe2a7eae6bd48e4eef3d0160e0380a09a3274d;hpb=58107a0cbb49652e7772ce80fb73d2c027590eb1;p=cfb.git diff --git a/prod/net/jaekl/cfb/db/driver/DbDriver.java b/prod/net/jaekl/cfb/db/driver/DbDriver.java index ecfe2a7..b8d1ee6 100644 --- a/prod/net/jaekl/cfb/db/driver/DbDriver.java +++ b/prod/net/jaekl/cfb/db/driver/DbDriver.java @@ -2,7 +2,7 @@ package net.jaekl.cfb.db.driver; // 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; @@ -16,6 +16,7 @@ import net.jaekl.cfb.db.Column.Type; 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 { @@ -30,6 +31,8 @@ public abstract class DbDriver { public abstract Connection connect(String host, int port, String dbName, String user, String pass) throws SQLException; + public abstract long nextVal(Connection con, Sequence seq) throws SQLException; + public boolean createTable(Connection con, Table table) throws SQLException { String sql = createTableSql(table); @@ -78,18 +81,44 @@ public abstract class DbDriver { } } + public Row selectExactlyOne(Connection con, Column[] columns, Table[] tables, Condition[] conditions) + throws SQLException + { + Sort[] sorts = new Sort[0]; + int limit = 2; + List 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 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 select(Connection con, Column[] columns, Table[] tables, Condition[] conditions, Sort[] sorts, int limit) + throws SQLException + { + String sql = selectSql(columns, tables, conditions, sorts, limit); ArrayList result = new ArrayList(); try (PreparedStatement ps = con.prepareStatement(sql)) { int index = 0; for (Condition condition : conditions) { if (condition.getOperation().hasParam()) { + Column column = condition.getColumn(); index++; - ps.setObject(index, condition.getValue()); + column.setObject(ps, index, condition.getValue()); } } @@ -97,13 +126,22 @@ public abstract class DbDriver { while (rs.next()) { Object[] values = new Object[columns.length]; for (index = 0; index < columns.length; ++index) { - values[index] = rs.getObject(index + 1); + 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); } } } + catch (SQLException se) { + throw new SQLException("Error with SQL: " + sql, se); + } return result; } @@ -114,6 +152,8 @@ public abstract class DbDriver { int count = 0; int pendingValues = 0; + assert( isValidInsert(table, values)); + String sql = insertSql(table); try (PreparedStatement ps = con.prepareStatement(sql)) @@ -124,8 +164,10 @@ public abstract class DbDriver { assert(null != data); assert(data.length == table.getNumColumns()); - for (int col = 0; col < data.length; ++col) { - ps.setObject(col + 1, data[col]); + for (int idx = 0; idx < data.length; ++idx) { + Object obj = data[idx]; + Column column = table.getColumn(idx); + column.setObject(ps, idx + 1, obj); pendingValues++; } ps.addBatch(); @@ -143,22 +185,6 @@ public abstract class DbDriver { 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; @@ -192,7 +218,7 @@ public abstract class DbDriver { 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 "); @@ -237,11 +263,47 @@ public abstract class DbDriver { .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(" ASC "); + } + else { + sb.append(" DESC "); + } + } + } + + 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(); } @@ -307,5 +369,31 @@ public abstract class DbDriver { 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; + } + + int executeUpdate(Connection con, String sql) throws SQLException + { + try (PreparedStatement ps = con.prepareStatement(sql)) + { + return ps.executeUpdate(); + } + catch (SQLException exc) { + throw new SQLException("Failed to executeUpdate: " + sql, exc); + } + } }