X-Git-Url: http://jaekl.net/gitweb/?a=blobdiff_plain;f=prod%2Fnet%2Fjaekl%2Fcfb%2Fdb%2Fdriver%2FDbDriver.java;h=b8d1ee6bf5f67935a882d62d57667a82cd81a3b6;hb=a1378c84c773511e4ffe99fb419da67af188aff7;hp=525ca7049d3444545a717a9788e51595b29697f3;hpb=3c10b6100c6035a65ce37dea846b027135289f67;p=cfb.git diff --git a/prod/net/jaekl/cfb/db/driver/DbDriver.java b/prod/net/jaekl/cfb/db/driver/DbDriver.java index 525ca70..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,9 +16,12 @@ 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 { + static int PENDING_LIMIT = 1024; // Rough limit at which point we'll start a new batch for batch updates + DbDriver() { } @@ -28,7 +31,10 @@ 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 abstract long nextVal(Connection con, Sequence seq) throws SQLException; + + public boolean createTable(Connection con, Table table) throws SQLException + { String sql = createTableSql(table); try (PreparedStatement ps = con.prepareStatement(sql)) { ps.executeUpdate(); @@ -40,6 +46,17 @@ public abstract class DbDriver { 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); @@ -53,18 +70,55 @@ public abstract class DbDriver { 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 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()); } } @@ -72,18 +126,99 @@ 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); + 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; } - protected String selectSql(Column[] columns, Table[] tables, Condition[] conditions) + // Returns the number of rows inserted + public int insert(Connection con, Table table, Object[][] values) throws SQLException + { + int count = 0; + int pendingValues = 0; + + assert( isValidInsert(table, values)); + + String sql = insertSql(table); + + try (PreparedStatement ps = con.prepareStatement(sql)) + { + for (int row = 0; row < values.length; ++row) { + Object[] data = values[row]; + + assert(null != data); + assert(data.length == table.getNumColumns()); + + 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(); + + int rowsFlushed = checkFlushBatch(ps, pendingValues, false); + if (rowsFlushed > 0) { + count += rowsFlushed; + pendingValues = 0; + } + } + + count += checkFlushBatch(ps, pendingValues, true); + } + + return count; + } + + int checkFlushBatch(PreparedStatement ps, int pendingValues, boolean forceFlush) throws SQLException + { + int count = 0; + + if (forceFlush || (pendingValues >= PENDING_LIMIT)) + { + int[] updateCounts = ps.executeBatch(); + for (int i = 0; i < updateCounts.length; ++i) { + if (updateCounts[i] > 0) { + count += updateCounts[i]; + } + } + } + + return count; + } + + String insertSql(Table table) { + StringBuilder sb = new StringBuilder("INSERT INTO "); + sb.append(table.getName()) + .append(" VALUES ("); + + for (int i = 0; i < table.getNumColumns(); ++i) { + if (i > 0) { + sb.append(","); + } + sb.append("?"); + } + sb.append(")"); + + return sb.toString(); + } + + protected String selectSql(Column[] columns, Table[] tables, Condition[] conditions, Sort[] sorts, int limit) { StringBuilder sb = new StringBuilder("SELECT "); @@ -128,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(); } @@ -177,10 +348,52 @@ public abstract class DbDriver { 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(); + } + + 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); + } + } }