From dad47827334a0732966cc197dfe1e3ac303fb694 Mon Sep 17 00:00:00 2001 From: Chris Jaekl Date: Sun, 12 Jun 2016 20:01:17 +0900 Subject: [PATCH] Refactor tabular output for eventual re-use printing table metadata. Note that this is not yet complete; tabular output is currently broken. --- src/main/java/net/jaekl/squelch/sql/Row.java | 17 ++ .../java/net/jaekl/squelch/stmt/Select.java | 59 +---- .../java/net/jaekl/squelch/stmt/Tabular.java | 246 ++++++++++++++++++ .../jaekl/squelch/stmt/TabularResultSet.java | 38 +++ .../java/net/jaekl/squelch/SquelchTest.java | 2 +- .../squelch/{db => sql}/ConnectionMock.java | 3 +- .../{db => sql}/DatabaseMetaDataMock.java | 3 +- .../{db => sql}/PreparedStatementMock.java | 2 +- .../{db => sql}/ResultSetMetaDataMock.java | 2 +- .../squelch/{db => sql}/ResultSetMock.java | 36 ++- .../net/jaekl/squelch/stmt/TabularTest.java | 94 +++++++ 11 files changed, 430 insertions(+), 72 deletions(-) create mode 100644 src/main/java/net/jaekl/squelch/sql/Row.java create mode 100644 src/main/java/net/jaekl/squelch/stmt/Tabular.java create mode 100644 src/main/java/net/jaekl/squelch/stmt/TabularResultSet.java rename src/test/java/net/jaekl/squelch/{db => sql}/ConnectionMock.java (99%) rename src/test/java/net/jaekl/squelch/{db => sql}/DatabaseMetaDataMock.java (99%) rename src/test/java/net/jaekl/squelch/{db => sql}/PreparedStatementMock.java (99%) rename src/test/java/net/jaekl/squelch/{db => sql}/ResultSetMetaDataMock.java (99%) rename src/test/java/net/jaekl/squelch/{db => sql}/ResultSetMock.java (97%) create mode 100644 src/test/java/net/jaekl/squelch/stmt/TabularTest.java diff --git a/src/main/java/net/jaekl/squelch/sql/Row.java b/src/main/java/net/jaekl/squelch/sql/Row.java new file mode 100644 index 0000000..0496d23 --- /dev/null +++ b/src/main/java/net/jaekl/squelch/sql/Row.java @@ -0,0 +1,17 @@ +package net.jaekl.squelch.sql; + +public class Row { + private Object[] m_args; + + public Row(int numCols) { + m_args = new Object[numCols]; + } + + public void setValue(int idx, Object value) { + m_args[idx - 1] = value; + } + + public Object getValue(int idx) { + return m_args[idx-1]; + } +} diff --git a/src/main/java/net/jaekl/squelch/stmt/Select.java b/src/main/java/net/jaekl/squelch/stmt/Select.java index 088181a..afad262 100644 --- a/src/main/java/net/jaekl/squelch/stmt/Select.java +++ b/src/main/java/net/jaekl/squelch/stmt/Select.java @@ -5,12 +5,9 @@ import java.io.PrintWriter; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; -import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.util.Locale; -import net.jaekl.squelch.sql.Column; - public class Select extends Query { @Override @@ -38,57 +35,13 @@ public class Select extends Query { } return rowCount; } - - private Column[] getColumns(ResultSetMetaData metaData) - throws SQLException, ClassNotFoundException - { - int colCount = metaData.getColumnCount(); - - Column cols[] = new Column[colCount]; - for (int idx = 1; idx <= colCount; ++idx) { - String label = metaData.getColumnLabel(idx); - Class clazz = Class.forName(metaData.getColumnTypeName(idx)); - int width = metaData.getColumnDisplaySize(idx); - cols[idx] = new Column(label, clazz, width); - } - - return cols; - } - + private int printFormatted(PrintWriter pw, ResultSet rs) throws IOException, SQLException { - try { - ResultSetMetaData metaData = rs.getMetaData(); - int rowCount = 0; - Column[] cols = getColumns(metaData); - StringBuilder sb = new StringBuilder(); - sb.append("----------------------------------\n"); - for (int idx = 1; idx <= cols.length; ++idx) { - sb.append("|" + cols[idx - 1].getLabel()); - } - sb.append("|\n"); - String header = sb.toString(); - - while (rs.next()) { - if (null != header) { - pw.print(header); - header = null; - } - - for (int idx = 1; idx <= cols.length; ++idx) { - pw.print("|" + rs.getObject(idx)); - } - pw.println("|"); - } - if (rowCount > 0) { - pw.println("----------------------------------"); - } - pw.println("" + rowCount + " row(s) returned."); - - return rowCount; - } - catch (ClassNotFoundException exc) { - throw new SQLException(exc); - } + TabularResultSet trs = new TabularResultSet(rs); + int rowCount = trs.printTable(pw); + pw.flush(); + + return rowCount; } } diff --git a/src/main/java/net/jaekl/squelch/stmt/Tabular.java b/src/main/java/net/jaekl/squelch/stmt/Tabular.java new file mode 100644 index 0000000..03c403e --- /dev/null +++ b/src/main/java/net/jaekl/squelch/stmt/Tabular.java @@ -0,0 +1,246 @@ +package net.jaekl.squelch.stmt; + +import java.io.PrintWriter; +import java.sql.SQLException; +import java.sql.Types; + +import net.jaekl.squelch.sql.Column; +import net.jaekl.squelch.sql.Row; + +// Copyright (C) 2016 by Chris Jaekl +// +// Tabular: wrapper around a set of tabular data (such as a ResultSet or table's MetaData). +// Includes routines to output the data as a human-friendly table, or as CSV. + +abstract public class Tabular { + private static class RowBuffer { + private final int ROW_BUF_SIZE = 50; + + private Row[] m_rowBuf; + private int m_pending; + + public RowBuffer() { + m_rowBuf = new Row[ROW_BUF_SIZE]; + m_pending = 0; + } + + public void addRow(Row row) { + assert (m_pending < m_rowBuf.length); + m_rowBuf[m_pending] = row; + m_pending++; + } + public int getPending() { return m_pending; } + public Row getRow(int idx) { + assert (idx >= 0 && idx < m_pending); + return m_rowBuf[idx]; + } + public boolean isFull() { return m_pending >= m_rowBuf.length; } + } + + abstract Column[] getCols() throws SQLException; + abstract Row getNext(); + + // Returns the number of (data) rows that were output + public int printTable(PrintWriter pw) throws SQLException { + int rowCount = 0; + Column[] cols = getCols(); + RowBuffer rowBuf; + + // Initialize colWidths to the widths of the column names + int[] colWidths = initColWidthsFromColNames(cols); + + // Examine and buffer up to ROW_BUF_SIZE rows, adjusting (widening) colWidths where needed + rowBuf = bufferRows(colWidths); + + int pending = rowBuf.getPending(); + if (pending > 0) { + writeHeader(pw, cols, colWidths); + writeRowBuffer(pw, rowBuf, colWidths); + rowCount = rowBuf.getPending(); + } + + while (pending > 0) { + rowBuf = bufferRows(colWidths); + writeRowBuffer(pw, rowBuf, colWidths); + pending = rowBuf.getPending(); + rowCount += pending; + } + + // TODO: Implement a String table for i18n + pw.println("" + rowCount + " row(s) returned."); + pw.flush(); + + return rowCount; + } + + // Returns the number of (data) rows that were output + public int printCsv(PrintWriter pw) throws SQLException { + int rowCount = 0; + Column[] cols = getCols(); + Row row = null; + + StringBuilder sb = new StringBuilder(); + boolean firstCol = true; + for (int idx = 1; idx <= cols.length; ++idx) { + if (firstCol) { + firstCol = false; + } + else { + sb.append(","); + } + sb.append("" + cols[idx - 1].getLabel()); + } + sb.append("\n"); + String header = sb.toString(); + + while (null != (row = getNext())) { + if (null != header) { + pw.print(header); + header = null; + } + + firstCol = true; + for (int idx = 1; idx <= cols.length; ++idx) { + if (firstCol) { + firstCol = false; + } + else { + pw.print(","); + } + pw.print("" + row.getValue(idx)); + } + pw.println(""); + rowCount++; + } + pw.flush(); + + return rowCount; + } + + // Examine and buffer up to rowBuf.length rows. + // Returns the number of actual rows that were buffered (zero if no more rows are available). + RowBuffer bufferRows(int[] colWidths) + { + RowBuffer rowBuf = new RowBuffer(); + + while (!rowBuf.isFull()) { + Row row = getNext(); + if (null == row) { + // No more rows available + return rowBuf; + } + rowBuf.addRow(row); + + // Check whether all values in this row will fit in the current column widths + for (int colIdx = 0; colIdx < colWidths.length; ++colIdx) { + int width = ("" + row.getValue(colIdx + 1)).length(); + if (width > colWidths[colIdx]) { + // Widen the column to fit this value + colWidths[colIdx] = width; + } + } + } + + return rowBuf; + } + + String centrePad(String value, int desiredWidth) + { + if (null == value) { + return centrePad("NULL", desiredWidth); + } + if (value.length() >= desiredWidth) { + return value; + } + int left = (desiredWidth - value.length()) / 2; + int right = desiredWidth - value.length() - left; + return repChar(' ', left) + value + repChar(' ', right); + } + + int[] initColWidthsFromColNames(Column[] cols) + { + int[] widths = new int[cols.length]; + + for (int idx = 0; idx < cols.length; ++idx) { + widths[idx] = cols[idx].getLabel().length(); + } + + return widths; + } + + Class classForSqlType(int sqlType) + { + switch(sqlType) + { + case Types.BOOLEAN: + return Boolean.class; + case Types.CHAR: + return Character.class; + case Types.DATE: + case Types.TIME: + case Types.TIMESTAMP: + return java.util.Date.class; + case Types.DECIMAL: + case Types.DOUBLE: + case Types.FLOAT: + case Types.NUMERIC: + case Types.REAL: + return Double.class; + case Types.INTEGER: + return Long.class; + case Types.ROWID: + case Types.SMALLINT: + return Integer.class; + case Types.TINYINT: + return Short.class; + case Types.LONGNVARCHAR: + case Types.LONGVARCHAR: + case Types.NCHAR: + case Types.NVARCHAR: + case Types.VARCHAR: + return String.class; + } + return Object.class; + } + + String repChar(char chr, int times) + { + StringBuffer sb = new StringBuffer(); + for (int idx = 0; idx < times; ++idx) { + sb.append(chr); + } + return sb.toString(); + } + + void writeDivider(PrintWriter pw, int[] colWidths) { + for (int idx = 0; idx < colWidths.length; ++idx) { + pw.print("+" + repChar('-', colWidths[idx])); + } + pw.println("+"); + } + + void writeHeader(PrintWriter pw, Column[] cols, int[] colWidths) { + writeDivider(pw, colWidths); + + for (int idx = 1; idx <= cols.length; ++idx) { + Column col = cols[idx]; + pw.print("|" + centrePad(col.getLabel(), colWidths[idx])); + } + pw.println("|"); + + writeDivider(pw, colWidths); + } + + void writeRowBuffer(PrintWriter pw, RowBuffer rowBuf, int[] colWidths) { + + for (int rowIdx = 0; rowIdx < rowBuf.getPending(); ++rowIdx) { + Row row = rowBuf.getRow(rowIdx); + for (int colIdx = 0; colIdx < colWidths.length; ++colIdx) { + String value = "" + row.getValue(colIdx); + String padding = repChar(' ', colWidths[colIdx] - value.length()); + pw.print("|" + value + padding); + } + pw.println("|"); + } + } +} diff --git a/src/main/java/net/jaekl/squelch/stmt/TabularResultSet.java b/src/main/java/net/jaekl/squelch/stmt/TabularResultSet.java new file mode 100644 index 0000000..92da23d --- /dev/null +++ b/src/main/java/net/jaekl/squelch/stmt/TabularResultSet.java @@ -0,0 +1,38 @@ +package net.jaekl.squelch.stmt; + +import java.sql.ResultSet; +import java.sql.ResultSetMetaData; +import java.sql.SQLException; + +import net.jaekl.squelch.sql.Column; +import net.jaekl.squelch.sql.Row; + +public class TabularResultSet extends Tabular { + private ResultSet m_resultSet; + + public TabularResultSet(ResultSet resultSet) { + m_resultSet = resultSet; + } + + @Override + Column[] getCols() throws SQLException { + ResultSetMetaData metaData = m_resultSet.getMetaData(); + int colCount = metaData.getColumnCount(); + + Column cols[] = new Column[colCount]; + for (int idx = 1; idx <= colCount; ++idx) { + String label = metaData.getColumnLabel(idx); + Class clazz = classForSqlType(metaData.getColumnType(idx)); + int width = metaData.getColumnDisplaySize(idx); + cols[idx - 1] = new Column(label, clazz, width); + } + + return cols; + } + + @Override + Row getNext() { + // TODO Auto-generated method stub + return null; + } +} diff --git a/src/test/java/net/jaekl/squelch/SquelchTest.java b/src/test/java/net/jaekl/squelch/SquelchTest.java index fc12402..be533d8 100644 --- a/src/test/java/net/jaekl/squelch/SquelchTest.java +++ b/src/test/java/net/jaekl/squelch/SquelchTest.java @@ -13,12 +13,12 @@ import java.nio.charset.StandardCharsets; import java.sql.Connection; import java.sql.SQLException; -import net.jaekl.squelch.db.ConnectionMock; import net.jaekl.squelch.db.DbDriver; import net.jaekl.squelch.db.MsSqlDriver; import net.jaekl.squelch.db.MySqlDriver; import net.jaekl.squelch.db.OracleDriver; import net.jaekl.squelch.db.PostgresqlDriver; +import net.jaekl.squelch.sql.ConnectionMock; import net.jaekl.squelch.util.ConsoleInputMock; import org.junit.Test; diff --git a/src/test/java/net/jaekl/squelch/db/ConnectionMock.java b/src/test/java/net/jaekl/squelch/sql/ConnectionMock.java similarity index 99% rename from src/test/java/net/jaekl/squelch/db/ConnectionMock.java rename to src/test/java/net/jaekl/squelch/sql/ConnectionMock.java index e242e00..22b686f 100644 --- a/src/test/java/net/jaekl/squelch/db/ConnectionMock.java +++ b/src/test/java/net/jaekl/squelch/sql/ConnectionMock.java @@ -1,4 +1,4 @@ -package net.jaekl.squelch.db; +package net.jaekl.squelch.sql; import java.sql.Array; import java.sql.Blob; @@ -20,6 +20,7 @@ import java.util.Map; import java.util.Properties; import java.util.concurrent.Executor; + public class ConnectionMock implements Connection { private ArrayList m_executedQueries; diff --git a/src/test/java/net/jaekl/squelch/db/DatabaseMetaDataMock.java b/src/test/java/net/jaekl/squelch/sql/DatabaseMetaDataMock.java similarity index 99% rename from src/test/java/net/jaekl/squelch/db/DatabaseMetaDataMock.java rename to src/test/java/net/jaekl/squelch/sql/DatabaseMetaDataMock.java index 8f9abcd..b8ecded 100644 --- a/src/test/java/net/jaekl/squelch/db/DatabaseMetaDataMock.java +++ b/src/test/java/net/jaekl/squelch/sql/DatabaseMetaDataMock.java @@ -1,4 +1,4 @@ -package net.jaekl.squelch.db; +package net.jaekl.squelch.sql; import java.sql.Connection; import java.sql.DatabaseMetaData; @@ -6,7 +6,6 @@ import java.sql.ResultSet; import java.sql.RowIdLifetime; import java.sql.SQLException; -import net.jaekl.squelch.db.ResultSetMock; public class DatabaseMetaDataMock implements DatabaseMetaData { diff --git a/src/test/java/net/jaekl/squelch/db/PreparedStatementMock.java b/src/test/java/net/jaekl/squelch/sql/PreparedStatementMock.java similarity index 99% rename from src/test/java/net/jaekl/squelch/db/PreparedStatementMock.java rename to src/test/java/net/jaekl/squelch/sql/PreparedStatementMock.java index 9967728..c4c33a2 100644 --- a/src/test/java/net/jaekl/squelch/db/PreparedStatementMock.java +++ b/src/test/java/net/jaekl/squelch/sql/PreparedStatementMock.java @@ -1,4 +1,4 @@ -package net.jaekl.squelch.db; +package net.jaekl.squelch.sql; import java.io.InputStream; import java.io.Reader; diff --git a/src/test/java/net/jaekl/squelch/db/ResultSetMetaDataMock.java b/src/test/java/net/jaekl/squelch/sql/ResultSetMetaDataMock.java similarity index 99% rename from src/test/java/net/jaekl/squelch/db/ResultSetMetaDataMock.java rename to src/test/java/net/jaekl/squelch/sql/ResultSetMetaDataMock.java index f1969ec..402aabb 100644 --- a/src/test/java/net/jaekl/squelch/db/ResultSetMetaDataMock.java +++ b/src/test/java/net/jaekl/squelch/sql/ResultSetMetaDataMock.java @@ -1,4 +1,4 @@ -package net.jaekl.squelch.db; +package net.jaekl.squelch.sql; import java.sql.ResultSetMetaData; import java.sql.SQLException; diff --git a/src/test/java/net/jaekl/squelch/db/ResultSetMock.java b/src/test/java/net/jaekl/squelch/sql/ResultSetMock.java similarity index 97% rename from src/test/java/net/jaekl/squelch/db/ResultSetMock.java rename to src/test/java/net/jaekl/squelch/sql/ResultSetMock.java index 5c7be5d..b398e24 100644 --- a/src/test/java/net/jaekl/squelch/db/ResultSetMock.java +++ b/src/test/java/net/jaekl/squelch/sql/ResultSetMock.java @@ -1,4 +1,4 @@ -package net.jaekl.squelch.db; +package net.jaekl.squelch.sql; import java.io.InputStream; import java.io.Reader; @@ -19,14 +19,24 @@ import java.sql.SQLXML; import java.sql.Statement; import java.sql.Time; import java.sql.Timestamp; +import java.util.ArrayList; import java.util.Calendar; import java.util.Map; + public class ResultSetMock implements ResultSet { private ResultSetMetaDataMock m_metaDataMock; + private ArrayList m_rows; + private int m_rowNum; public ResultSetMock() { m_metaDataMock = new ResultSetMetaDataMock(); + m_rows = new ArrayList(); + m_rowNum = -1; + } + + public void mock_addRow(Row row) { + m_rows.add(row); } @Override @@ -319,9 +329,9 @@ public class ResultSetMock implements ResultSet { } @Override - public long getLong(int arg0) throws SQLException { - throw new UnsupportedOperationException("Not yet implemented."); - + public long getLong(int idx) throws SQLException { + Row row = m_rows.get(m_rowNum); + return (Long)(row.getValue(idx)); } @Override @@ -372,9 +382,9 @@ public class ResultSetMock implements ResultSet { } @Override - public Object getObject(int arg0) throws SQLException { - throw new UnsupportedOperationException("Not yet implemented."); - + public Object getObject(int idx) throws SQLException { + Row row = m_rows.get(m_rowNum); + return row.getValue(idx); } @Override @@ -470,9 +480,9 @@ public class ResultSetMock implements ResultSet { } @Override - public String getString(int arg0) throws SQLException { - throw new UnsupportedOperationException("Not yet implemented."); - + public String getString(int idx) throws SQLException { + Row row = m_rows.get(m_rowNum); + return (String)(row.getValue(idx)); } @Override @@ -482,9 +492,9 @@ public class ResultSetMock implements ResultSet { } @Override - public Time getTime(int arg0) throws SQLException { - throw new UnsupportedOperationException("Not yet implemented."); - + public Time getTime(int idx) throws SQLException { + Row row = m_rows.get(m_rowNum); + return new Time(((java.util.Date)row.getValue(idx)).getTime()); } @Override diff --git a/src/test/java/net/jaekl/squelch/stmt/TabularTest.java b/src/test/java/net/jaekl/squelch/stmt/TabularTest.java new file mode 100644 index 0000000..6b5462d --- /dev/null +++ b/src/test/java/net/jaekl/squelch/stmt/TabularTest.java @@ -0,0 +1,94 @@ +package net.jaekl.squelch.stmt; + +import static org.junit.Assert.*; + +import java.sql.SQLException; +import java.sql.Types; + +import net.jaekl.squelch.sql.Column; +import net.jaekl.squelch.sql.Row; + +import org.junit.Test; + +public class TabularTest { + private static class TabularMock extends Tabular { + @Override + Column[] getCols() throws SQLException { + return null; + } + + @Override + Row getNext() { + return null; + } + } + + @Test + public void test_classForSqlType() { + Object[][] data = { + { Types.ARRAY, Object.class }, + { Types.BIGINT, Object.class }, + { Types.BINARY, Object.class }, + { Types.BIT, Object.class }, + { Types.BLOB, Object.class }, + { Types.BOOLEAN, Boolean.class }, + { Types.CHAR, Character.class }, + { Types.CLOB, Object.class }, + { Types.DATALINK, Object.class }, + { Types.DATE, java.util.Date.class }, + { Types.DECIMAL, Double.class }, + { Types.DISTINCT, Object.class }, + { Types.DOUBLE, Double.class }, + { Types.FLOAT, Double.class }, + { Types.INTEGER, Long.class }, + { Types.JAVA_OBJECT, Object.class }, + { Types.LONGNVARCHAR, String.class }, + { Types.LONGVARBINARY, Object.class }, + { Types.NCHAR, String.class }, + { Types.NCLOB, Object.class }, + { Types.NULL, Object.class }, + { Types.NUMERIC, Double.class }, + { Types.NVARCHAR, String.class }, + { Types.OTHER, Object.class }, + { Types.REAL, Double.class }, + { Types.REF, Object.class }, + { Types.ROWID, Integer.class }, + { Types.SMALLINT, Integer.class }, + { Types.SQLXML, Object.class }, + { Types.STRUCT, Object.class }, + { Types.TIME, java.util.Date.class }, + { Types.TIMESTAMP, java.util.Date.class }, + { Types.TINYINT, Short.class }, + { Types.VARBINARY, Object.class }, + { Types.VARCHAR, String.class } + }; + Tabular tabular = new TabularMock(); + + for (Object[] datum : data) { + Class expected = (Class)datum[1]; + Class actual = tabular.classForSqlType((int) datum[0]); + assertEquals(expected, actual); + } + } + + @Test + public void test_repChar() { + Tabular tabular = new TabularMock(); + + assertEquals("", tabular.repChar(' ', 0)); + assertEquals(" ", tabular.repChar(' ', 4)); + assertEquals("###", tabular.repChar('#', 3)); + assertEquals("------", tabular.repChar('-', 6)); + } + + @Test + public void test_centrePad() { + Tabular tabular = new TabularMock(); + + assertEquals("Vestibule", tabular.centrePad("Vestibule", 2)); + assertEquals(" Fred ", tabular.centrePad("Fred", 8)); + assertEquals("NULL", tabular.centrePad(null, 0)); + assertEquals(" NULL ", tabular.centrePad(null, 8)); + assertEquals(" Wilma ", tabular.centrePad("Wilma", 12)); + } +} -- 2.30.2