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();
  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                 // TODO:  Implement a String table for i18n
  70                 pw.println("" + rowCount + " row(s) returned.");
  71                 pw.flush();
  72
  73                 return rowCount;
  74         }
  75
  76         // Returns the number of (data) rows that were output
  77         public int printCsv(PrintWriter pw) throws SQLException {
  78                 int rowCount = 0;
  79                 Column[] cols = getCols();
  80                 Row row = null;
  81
  82                 StringBuilder sb = new StringBuilder();
  83                 boolean firstCol = true;
  84                 for (int idx = 1; idx <= cols.length; ++idx) {
  85                         if (firstCol) {
  86                                 firstCol = false;
  87                         }
  88                         else {
  89                                 sb.append(",");
  90                         }
  91                         sb.append("" + cols[idx - 1].getLabel());
  92                 }
  93                 sb.append("\n");
  94                 String header = sb.toString();
  95
  96                 while (null != (row = getNext())) {
  97                         if (null != header) {
  98                                 pw.print(header);
  99                                 header = null;
 100                         }
 101
 102                         firstCol = true;
 103                         for (int idx = 1; idx <= cols.length; ++idx) {
 104                                 if (firstCol) {
 105                                         firstCol = false;
 106                                 }
 107                                 else {
 108                                         pw.print(",");
 109                                 }
 110                                 pw.print("" + row.getValue(idx));
 111                         }
 112                         pw.println("");
 113                         rowCount++;
 114                 }
 115                 pw.flush();
 116
 117                 return rowCount;
 118         }
 119
 120         // Examine and buffer up to rowBuf.length rows.
 121         // Returns the number of actual rows that were buffered (zero if no more rows are available).
 122         RowBuffer bufferRows(int[] colWidths)
 123         {
 124                 RowBuffer rowBuf = new RowBuffer();
 125
 126                 while (!rowBuf.isFull()) {
 127                         Row row = getNext();
 128                         if (null == row) {
 129                                 // No more rows available
 130                                 return rowBuf;
 131                         }
 132                         rowBuf.addRow(row);
 133
 134                         // Check whether all values in this row will fit in the current column widths
 135                         for (int colIdx = 0; colIdx < colWidths.length; ++colIdx) {
 136                                 int width = ("" + row.getValue(colIdx + 1)).length();
 137                                 if (width > colWidths[colIdx]) {
 138                                         // Widen the column to fit this value
 139                                         colWidths[colIdx] = width;
 140                                 }
 141                         }
 142                 }
 143
 144                 return rowBuf;
 145         }
 146
 147         String centrePad(String value, int desiredWidth)
 148         {
 149                 if (null == value) {
 150                         return centrePad("NULL", desiredWidth);
 151                 }
 152                 if (value.length() >= desiredWidth) {
 153                         return value;
 154                 }
 155                 int left = (desiredWidth - value.length()) / 2;
 156                 int right = desiredWidth - value.length() - left;
 157                 return repChar(' ', left) + value + repChar(' ', right);
 158         }
 159
 160         int[] initColWidthsFromColNames(Column[] cols)
 161         {
 162                 int[] widths = new int[cols.length];
 163
 164                 for (int idx = 0; idx < cols.length; ++idx) {
 165                         widths[idx] = cols[idx].getLabel().length();
 166                 }
 167
 168                 return widths;
 169         }
 170
 171         Class<?> classForSqlType(int sqlType)
 172         {
 173                 switch(sqlType)
 174                 {
 175                 case Types.BOOLEAN:
 176                         return Boolean.class;
 177                 case Types.CHAR:
 178                         return Character.class;
 179                 case Types.DATE:
 180                 case Types.TIME:
 181                 case Types.TIMESTAMP:
 182                         return java.util.Date.class;
 183                 case Types.DECIMAL:
 184                 case Types.DOUBLE:
 185                 case Types.FLOAT:
 186                 case Types.NUMERIC:
 187                 case Types.REAL:
 188                         return Double.class;
 189                 case Types.INTEGER:
 190                         return Long.class;
 191                 case Types.ROWID:
 192                 case Types.SMALLINT:
 193                         return Integer.class;
 194                 case Types.TINYINT:
 195                         return Short.class;
 196                 case Types.LONGNVARCHAR:
 197                 case Types.LONGVARCHAR:
 198                 case Types.NCHAR:
 199                 case Types.NVARCHAR:
 200                 case Types.VARCHAR:
 201                         return String.class;
 202                 }
 203                 return Object.class;
 204         }
 205
 206         String repChar(char chr, int times)
 207         {
 208                 StringBuffer sb = new StringBuffer();
 209                 for (int idx = 0; idx < times; ++idx) {
 210                         sb.append(chr);
 211                 }
 212                 return sb.toString();
 213         }
 214
 215         void writeDivider(PrintWriter pw, int[] colWidths) {
 216                 for (int idx = 0; idx < colWidths.length; ++idx) {
 217                         pw.print("+" + repChar('-', colWidths[idx]));
 218                 }
 219                 pw.println("+");
 220         }
 221
 222         void writeHeader(PrintWriter pw, Column[] cols, int[] colWidths) {
 223                 writeDivider(pw, colWidths);
 224
 225                 for (int idx = 1; idx <= cols.length; ++idx) {
 226                         Column col = cols[idx];
 227                         pw.print("|" + centrePad(col.getLabel(), colWidths[idx]));
 228                 }
 229                 pw.println("|");
 230
 231                 writeDivider(pw, colWidths);
 232         }
 233
 234         void writeRowBuffer(PrintWriter pw, RowBuffer rowBuf, int[] colWidths) {
 235
 236                 for (int rowIdx = 0; rowIdx < rowBuf.getPending(); ++rowIdx) {
 237                         Row row = rowBuf.getRow(rowIdx);
 238                         for (int colIdx = 0; colIdx < colWidths.length; ++colIdx) {
 239                                 String value = "" + row.getValue(colIdx);
 240                                 String padding = repChar(' ', colWidths[colIdx] - value.length());
 241                                 pw.print("|" + value + padding);
 242                         }
 243                         pw.println("|");
 244                 }
 245         }
 246 }