X-Git-Url: http://jaekl.net/gitweb/?p=cfb.git;a=blobdiff_plain;f=prod%2Fnet%2Fjaekl%2Fcfb%2Fdb%2Fdriver%2FDbDriver.java;h=eea3e22c44c8d89f8fa79d21a52089b87e05555b;hp=f85db06ca3345f66f7e9732f8fa3c75db1e2ca18;hb=538190e8467a555615fbaf1ada3eed44631e10b4;hpb=358d80a86ac7c79cd57b81a4f1708da80db2f0ec

diff --git a/prod/net/jaekl/cfb/db/driver/DbDriver.java b/prod/net/jaekl/cfb/db/driver/DbDriver.java
index f85db06..eea3e22 100644
--- 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();
 	}