Partial implemenation of load-Analysis code.

[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 f85db06ca3345f66f7e9732f8fa3c75db1e2ca18..eea3e22c44c8d89f8fa79d21a52089b87e05555b 100644 (file)
--- a/prod/net/jaekl/cfb/db/driver/DbDriver.java
+++ b/prod/net/jaekl/cfb/db/driver/DbDriver.java
@@ -2,13 +2,12 @@ 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;
 import java.sql.ResultSet;
 import java.sql.SQLException;
-import java.sql.Timestamp;
 import java.util.ArrayList;
 import java.util.Date;
 import java.util.List;
@@ -18,6 +17,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 {
@@ -83,7 +83,16 @@ public abstract class DbDriver {
        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)) {
@@ -99,7 +108,13 @@ 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);
@@ -128,10 +143,13 @@ public abstract class DbDriver {
                                
                                for (int col = 0; col < data.length; ++col) {
                                        Object obj = data[col];
-                                       if (obj instanceof java.util.Date) {
+                                       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;
-                                               Timestamp ts = new Timestamp(date.getTime());
-                                               ps.setTimestamp(col + 1, ts);
+                                               ps.setLong(col + 1, date.getTime());
                                        }
                                        else {
                                                ps.setObject(col + 1, data[col]);
@@ -202,7 +220,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 ");
                
@@ -247,11 +265,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(" 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();
        }