src/main/java/net/jaekl/squelch/stmt/Tabular.java

   1 package net.jaekl.squelch.stmt;
   2
   3 import java.io.PrintWriter;
   4 import java.sql.SQLException;
   5 import java.sql.Types;
   6
   7 import net.jaekl.squelch.sql.Column;
   8 import net.jaekl.squelch.sql.Row;
   9
  10 // Copyright (C) 2016 by Chris Jaekl
  11 //
  12 // Tabular:  wrapper around a set of tabular data (such as a ResultSet or table's MetaData).
  13 // Includes routines to output the data as a human-friendly table, or as CSV.
  14
  15 abstract public class Tabular {
  16         private static class RowBuffer {
  17                 private final int ROW_BUF_SIZE = 50;
  18
  19                 private Row[] m_rowBuf;
  20                 private int m_pending;
  21
  22                 public RowBuffer() {
  23                         m_rowBuf = new Row[ROW_BUF_SIZE];
  24                         m_pending = 0;
  25                 }
  26
  27                 public void addRow(Row row) {
  28                         assert (m_pending < m_rowBuf.length);
  29                         m_rowBuf[m_pending] = row;
  30                         m_pending++;
  31                 }
  32                 public int getPending() { return m_pending; }
  33                 public Row getRow(int idx) {
  34                         assert (idx >= 0 && idx < m_pending);
  35                         return m_rowBuf[idx];
  36                 }
  37                 public boolean isFull() { return m_pending >= m_rowBuf.length; }
  38         }
  39
  40         abstract Column[] getCols() throws SQLException;
  41         abstract Row getNext() throws SQLException;
  42
  43         // Returns the number of (data) rows that were output
  44         public int printTable(PrintWriter pw) throws SQLException {
  45                 int rowCount = 0;
  46                 Column[] cols = getCols();
  47                 RowBuffer rowBuf;
  48
  49                 // Initialize colWidths to the widths of the column names
  50                 int[] colWidths = initColWidthsFromColNames(cols);
  51
  52                 // Examine and buffer up to ROW_BUF_SIZE rows, adjusting (widening) colWidths where needed
  53                 rowBuf = bufferRows(colWidths);
  54
  55                 int pending = rowBuf.getPending();
  56                 if (pending > 0) {
  57                         writeHeader(pw, cols, colWidths);
  58                         writeRowBuffer(pw, rowBuf, colWidths);
  59                         rowCount = rowBuf.getPending();
  60                 }
  61
  62                 while (pending > 0) {
  63                         rowBuf = bufferRows(colWidths);
  64                         writeRowBuffer(pw, rowBuf, colWidths);
  65                         pending = rowBuf.getPending();
  66                         rowCount += pending;
  67                 }
  68
  69                 if (rowCount > 0) {
  70                         writeDivider(pw, colWidths);
  71                 }
  72
  73                 // TODO:  Implement a String table for i18n
  74                 pw.println("" + rowCount + " row(s) returned.");
  75                 pw.flush();
  76
  77                 return rowCount;
  78         }
  79
  80         // Returns the number of (data) rows that were output
  81         public int printCsv(PrintWriter pw) throws SQLException {
  82                 int rowCount = 0;
  83                 Column[] cols = getCols();
  84                 Row row = null;
  85
  86                 StringBuilder sb = new StringBuilder();
  87                 boolean firstCol = true;
  88                 for (int idx = 1; idx <= cols.length; ++idx) {
  89                         if (firstCol) {
  90                                 firstCol = false;
  91                         }
  92                         else {
  93                                 sb.append(",");
  94                         }
  95                         sb.append("" + cols[idx - 1].getLabel());
  96                 }
  97                 sb.append("\n");
  98                 String header = sb.toString();
  99
 100                 while (null != (row = getNext())) {
 101                         if (null != header) {
 102                                 pw.print(header);
 103                                 header = null;
 104                         }
 105
 106                         firstCol = true;
 107                         for (int idx = 1; idx <= cols.length; ++idx) {
 108                                 if (firstCol) {
 109                                         firstCol = false;
 110                                 }
 111                                 else {
 112                                         pw.print(",");
 113                                 }
 114                                 pw.print("" + row.getValue(idx));
 115                         }
 116                         pw.println("");
 117                         rowCount++;
 118                 }
 119                 pw.flush();
 120
 121                 return rowCount;
 122         }
 123
 124         // Examine and buffer up to rowBuf.length rows.
 125         // Returns the number of actual rows that were buffered (zero if no more rows are available).
 126         RowBuffer bufferRows(int[] colWidths) throws SQLException
 127         {
 128                 RowBuffer rowBuf = new RowBuffer();
 129
 130                 while (!rowBuf.isFull()) {
 131                         Row row = getNext();
 132                         if (null == row) {
 133                                 // No more rows available
 134                                 return rowBuf;
 135                         }
 136                         rowBuf.addRow(row);
 137
 138                         // Check whether all values in this row will fit in the current column widths
 139                         for (int colIdx = 0; colIdx < colWidths.length; ++colIdx) {
 140                                 int width = ("" + row.getValue(colIdx + 1)).length();
 141                                 if (width > colWidths[colIdx]) {
 142                                         // Widen the column to fit this value
 143                                         colWidths[colIdx] = width;
 144                                 }
 145                         }
 146                 }
 147
 148                 return rowBuf;
 149         }
 150
 151         String centrePad(String value, int desiredWidth)
 152         {
 153                 if (null == value) {
 154                         return centrePad("NULL", desiredWidth);
 155                 }
 156                 if (value.length() >= desiredWidth) {
 157                         return value;
 158                 }
 159                 int left = (desiredWidth - value.length()) / 2;
 160                 int right = desiredWidth - value.length() - left;
 161                 return repChar(' ', left) + value + repChar(' ', right);
 162         }
 163
 164         int[] initColWidthsFromColNames(Column[] cols)
 165         {
 166                 int[] widths = new int[cols.length];
 167
 168                 for (int idx = 0; idx < cols.length; ++idx) {
 169                         widths[idx] = cols[idx].getLabel().length();
 170                 }
 171
 172                 return widths;
 173         }
 174
 175         Class<?> classForSqlType(int sqlType)
 176         {
 177                 switch(sqlType)
 178                 {
 179                 case Types.BOOLEAN:
 180                         return Boolean.class;
 181                 case Types.CHAR:
 182                         return Character.class;
 183                 case Types.DATE:
 184                 case Types.TIME:
 185                 case Types.TIMESTAMP:
 186                         return java.util.Date.class;
 187                 case Types.DECIMAL:
 188                 case Types.DOUBLE:
 189                 case Types.FLOAT:
 190                 case Types.NUMERIC:
 191                 case Types.REAL:
 192                         return Double.class;
 193                 case Types.INTEGER:
 194                         return Long.class;
 195                 case Types.ROWID:
 196                 case Types.SMALLINT:
 197                         return Integer.class;
 198                 case Types.TINYINT:
 199                         return Short.class;
 200                 case Types.LONGNVARCHAR:
 201                 case Types.LONGVARCHAR:
 202                 case Types.NCHAR:
 203                 case Types.NVARCHAR:
 204                 case Types.VARCHAR:
 205                         return String.class;
 206                 }
 207                 return Object.class;
 208         }
 209
 210         String repChar(char chr, int times)
 211         {
 212                 StringBuffer sb = new StringBuffer();
 213                 for (int idx = 0; idx < times; ++idx) {
 214                         sb.append(chr);
 215                 }
 216                 return sb.toString();
 217         }
 218
 219         void writeDivider(PrintWriter pw, int[] colWidths) {
 220                 for (int idx = 0; idx < colWidths.length; ++idx) {
 221                         pw.print("+" + repChar('-', colWidths[idx]));
 222                 }
 223                 pw.println("+");
 224         }
 225
 226         void writeHeader(PrintWriter pw, Column[] cols, int[] colWidths) {
 227                 writeDivider(pw, colWidths);
 228
 229                 for (int idx = 0; idx < cols.length; ++idx) {
 230                         Column col = cols[idx];
 231                         pw.print("|" + centrePad(col.getLabel(), colWidths[idx]));
 232                 }
 233                 pw.println("|");
 234
 235                 writeDivider(pw, colWidths);
 236         }
 237
 238         void writeRowBuffer(PrintWriter pw, RowBuffer rowBuf, int[] colWidths) {
 239
 240                 for (int rowIdx = 0; rowIdx < rowBuf.getPending(); ++rowIdx) {
 241                         Row row = rowBuf.getRow(rowIdx);
 242                         for (int colIdx = 0; colIdx < colWidths.length; ++colIdx) {
 243                                 String value = "" + row.getValue(colIdx + 1);
 244                                 String padding = repChar(' ', colWidths[colIdx] - value.length());
 245                                 pw.print("|" + value + padding);
 246                         }
 247                         pw.println("|");
 248                 }
 249         }
 250 }