1 package net.jaekl.cfb.db.driver;
3 // Copyright (C) 2015 Christian Jaekl
5 import static net.jaekl.cfb.db.Column.Null.NOT_NULL;
7 import java.sql.Connection;
8 import java.sql.PreparedStatement;
9 import java.sql.ResultSet;
10 import java.sql.SQLException;
11 import java.util.ArrayList;
12 import java.util.Date;
13 import java.util.List;
15 import net.jaekl.cfb.db.Column;
16 import net.jaekl.cfb.db.Column.Type;
17 import net.jaekl.cfb.db.Condition;
18 import net.jaekl.cfb.db.Row;
19 import net.jaekl.cfb.db.Sequence;
20 import net.jaekl.cfb.db.Sort;
21 import net.jaekl.cfb.db.Table;
23 public abstract class DbDriver {
24 static int PENDING_LIMIT = 1024; // Rough limit at which point we'll start a new batch for batch updates
30 // Load the JDBC driver
31 public abstract void load() throws ClassNotFoundException;
33 public abstract Connection connect(String host, int port, String dbName, String user, String pass) throws SQLException;
35 public boolean createTable(Connection con, Table table) throws SQLException
37 String sql = createTableSql(table);
38 try (PreparedStatement ps = con.prepareStatement(sql)) {
41 catch (SQLException exc) {
42 throw new SQLException("Failed to executeUpdate: " + sql, exc);
48 public void dropTable(Connection con, Table table) throws SQLException
50 String sql = dropTableSql(table);
51 try (PreparedStatement ps = con.prepareStatement(sql)) {
54 catch (SQLException exc) {
55 throw new SQLException("Failed to drop table: " + sql, exc);
59 public boolean createSequence(Connection con, Sequence seq) throws SQLException
61 String sql = createSequenceSql(seq);
62 try (PreparedStatement ps = con.prepareStatement(sql)) {
65 catch (SQLException exc) {
66 throw new SQLException("Failed to executeUpdate: " + sql, exc);
72 public void dropSequence(Connection con, Sequence seq) throws SQLException
74 String sql = dropSequenceSql(seq);
75 try (PreparedStatement ps = con.prepareStatement(sql)) {
78 catch (SQLException exc) {
79 throw new SQLException("Failed to drop sequence: " + sql, exc);
83 public List<Row> select(Connection con, Column[] columns, Table[] tables, Condition[] conditions)
86 Sort[] sorts = new Sort[0];
87 int limit = (-1); // no limit
89 return select(con, columns, tables, conditions, sorts, limit);
92 public List<Row> select(Connection con, Column[] columns, Table[] tables, Condition[] conditions, Sort[] sorts, int limit)
95 String sql = selectSql(columns, tables, conditions, sorts, limit);
96 ArrayList<Row> result = new ArrayList<Row>();
98 try (PreparedStatement ps = con.prepareStatement(sql)) {
100 for (Condition condition : conditions) {
101 if (condition.getOperation().hasParam()) {
103 ps.setObject(index, condition.getValue());
107 try (ResultSet rs = ps.executeQuery()) {
109 Object[] values = new Object[columns.length];
110 for (index = 0; index < columns.length; ++index) {
111 if (columns[index].getType().equals(Type.TIMESTAMPTZ)) {
112 long milliseconds = rs.getLong(index + 1);
113 values[index] = new java.util.Date(milliseconds);
116 values[index] = rs.getObject(index + 1);
119 Row row = new Row(columns, values);
128 // Returns the number of rows inserted
129 public int insert(Connection con, Table table, Object[][] values) throws SQLException
132 int pendingValues = 0;
134 String sql = insertSql(table);
136 try (PreparedStatement ps = con.prepareStatement(sql))
138 for (int row = 0; row < values.length; ++row) {
139 Object[] data = values[row];
141 assert(null != data);
142 assert(data.length == table.getNumColumns());
144 for (int col = 0; col < data.length; ++col) {
145 Object obj = data[col];
146 Column column = table.getColumn(col);
147 if (column.getType().equals(Type.TIMESTAMPTZ)) {
148 // Special case: because there's no good way to read a TIMESTAMPTZ from
149 // the database using JDBC, we store it as an integer (milliseconds since
150 // the epoch, 01.01.1970 00:00:00.000 UTC).
151 Date date = (Date)obj;
152 ps.setLong(col + 1, date.getTime());
155 ps.setObject(col + 1, data[col]);
161 int rowsFlushed = checkFlushBatch(ps, pendingValues, false);
162 if (rowsFlushed > 0) {
163 count += rowsFlushed;
168 count += checkFlushBatch(ps, pendingValues, true);
174 public long nextVal(Connection con, Sequence seq) throws SQLException
176 String sql = nextValSql(seq);
178 try (PreparedStatement ps = con.prepareStatement(sql))
180 try (ResultSet rs = ps.executeQuery()) {
182 return rs.getLong(1);
187 throw new SQLException("No value returned for sequence: " + sql);
190 int checkFlushBatch(PreparedStatement ps, int pendingValues, boolean forceFlush) throws SQLException
194 if (forceFlush || (pendingValues >= PENDING_LIMIT))
196 int[] updateCounts = ps.executeBatch();
197 for (int i = 0; i < updateCounts.length; ++i) {
198 if (updateCounts[i] > 0) {
199 count += updateCounts[i];
207 String insertSql(Table table) {
208 StringBuilder sb = new StringBuilder("INSERT INTO ");
209 sb.append(table.getName())
210 .append(" VALUES (");
212 for (int i = 0; i < table.getNumColumns(); ++i) {
220 return sb.toString();
223 protected String selectSql(Column[] columns, Table[] tables, Condition[] conditions, Sort[] sorts, int limit)
225 StringBuilder sb = new StringBuilder("SELECT ");
227 boolean firstColumn = true;
228 for (Column column : columns) {
235 sb.append(column.getName());
240 boolean firstTable = true;
241 for (Table table : tables) {
248 sb.append(table.getName());
251 if (null != conditions && conditions.length > 0) {
252 sb.append(" WHERE ");
254 boolean firstCondition = true;
256 for (Condition condition : conditions) {
257 if (firstCondition) {
258 firstCondition = false;
264 sb.append(condition.getColumn().getName())
265 .append(condition.getOperation().getSql());
269 if (null != sorts && sorts.length > 0) {
270 sb.append(" ORDER BY ");
272 boolean firstSort = true;
274 for (Sort sort : sorts) {
282 sb.append(sort.getColumn().getName());
284 if (sort.getDirection().equals(Sort.Direction.ASCENDING)) {
285 sb.append(" ASCENDING ");
288 sb.append(" DESCENDING ");
294 sb.append(" LIMIT " + limit + " ");
297 return sb.toString();
300 protected String typeName(Type type) {
301 // Special case: TIMESTAMPTZ stored as INTEGER (milliseconds since the epoch)
302 // Reading a TIMESTAMPTZ back from the DB, and converting it to a java.util.Date,
303 // is fraught with peril. The best way around this is to store the dates in
304 // milliseconds-since-the-epoch (01.01.1970 00:00:00.000 UTC).
305 if (Type.TIMESTAMPTZ.equals(type)) {
306 return Type.INTEGER.toString();
309 return type.toString();
312 protected String createColumnSql(Column column)
314 String result = column.getName() + " " + typeName(column.getType());
315 if (column.getWidth() > 0) {
316 result += "(" + column.getWidth() + ")";
319 if (NOT_NULL == column.getNull()) {
320 result += " NOT NULL";
329 protected String createTableSql(Table table)
331 assert(null != table);
332 assert(null != table.getName());
333 assert(table.getNumColumns() > 0);
335 StringBuilder sb = new StringBuilder();
337 sb.append("CREATE TABLE ")
338 .append(table.getName())
341 for (int idx = 0; idx < table.getNumColumns(); ++idx) {
345 sb.append(createColumnSql(table.getColumn(idx)));
350 return sb.toString();
353 protected String dropTableSql(Table table) {
354 assert(null != table);
355 assert(null != table.getName());
357 return "DROP TABLE " + table.getName();
360 protected String createSequenceSql(Sequence seq) {
362 assert(null != seq.getName());
364 return "CREATE SEQUENCE " + seq.getName();
367 protected String dropSequenceSql(Sequence seq) {
369 assert(null != seq.getName());
371 return "DROP SEQUENCE " + seq.getName();
374 abstract protected String nextValSql(Sequence seq);