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