import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
+import java.sql.Timestamp;
import java.util.ArrayList;
+import java.util.Date;
import java.util.List;
import net.jaekl.cfb.db.Column;
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() {
}
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 List<Row> select(Connection con, Column[] columns, Table[] tables, Condition[] conditions)
throws SQLException
{
while (rs.next()) {
Object[] values = new Object[columns.length];
for (index = 0; index < columns.length; ++index) {
- values[index] = rs.getObject(index);
+ values[index] = rs.getObject(index + 1);
}
Row row = new Row(columns, values);
result.add(row);
return result;
}
+ // Returns the number of rows inserted
+ public int insert(Connection con, Table table, Object[][] values) throws SQLException
+ {
+ int count = 0;
+ int pendingValues = 0;
+
+ 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 col = 0; col < data.length; ++col) {
+ Object obj = data[col];
+ if (obj instanceof java.util.Date) {
+ Date date = (Date)obj;
+ Timestamp ts = new Timestamp(date.getTime());
+ ps.setTimestamp(col + 1, ts);
+ }
+ else {
+ ps.setObject(col + 1, data[col]);
+ }
+ pendingValues++;
+ }
+ ps.addBatch();
+
+ int rowsFlushed = checkFlushBatch(ps, pendingValues, false);
+ if (rowsFlushed > 0) {
+ count += rowsFlushed;
+ pendingValues = 0;
+ }
+ }
+
+ count += checkFlushBatch(ps, pendingValues, true);
+ }
+
+ 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;
+
+ 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)
{
StringBuilder sb = new StringBuilder("SELECT ");
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);
}