A few changes:

[cfb.git] / prod / net / jaekl / cfb / db / driver / DbDriver.java

diff --git a/prod/net/jaekl/cfb/db/driver/DbDriver.java b/prod/net/jaekl/cfb/db/driver/DbDriver.java
index 525ca7049d3444545a717a9788e51595b29697f3..ecfe2a7eae6bd48e4eef3d0160e0380a09a3274d 100644 (file)
--- a/prod/net/jaekl/cfb/db/driver/DbDriver.java
+++ b/prod/net/jaekl/cfb/db/driver/DbDriver.java
@@ -19,6 +19,8 @@ import net.jaekl.cfb.db.Sequence;
 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 +30,8 @@ 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();
@@ -40,6 +43,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,6 +67,17 @@ 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 List<Row> select(Connection con, Column[] columns, Table[] tables, Condition[] conditions)
                throws SQLException
        {
@@ -72,7 +97,7 @@ 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);
+                                               values[index] = rs.getObject(index + 1);
                                        }
                                        Row row = new Row(columns, values);
                                        result.add(row);
@@ -83,6 +108,90 @@ public abstract class DbDriver {
                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) {
+                                       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 ");
@@ -177,10 +286,26 @@ 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();
+       }
+       
+       abstract protected String nextValSql(Sequence seq);
 }