src-main.org.awakefw.sql.api.util.ResultSetPrinter Maven / Gradle / Ivy
/*
* Awake File: Easy file upload & download through HTTP with Java
* Awake SQL: Remote JDBC access through HTTP.
* Copyright (C) 2012, Kawan Softwares S.A.S.
* (http://www.awakeframework.org). All rights reserved.
*
* Awake File/SQL is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or (at your option) any later version.
*
* Awake File/SQL is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
* 02110-1301 USA
*
* Any modifications to this file must keep this entire header
* intact.
*/
//Last Updates:
// 21 avr. 2011 15:03:49 Nicolas de Pomereu
// 21/04/11 19:40 NDP - ResultSetPrinter: fix bug on null value
// 22/04/11 16:35 NDP - ResultSetPrinter: better format of result lines (match max row length)
// 29/04/11 17:35 NDP - ResultSetPrinter: values are right-justified (for numeric)
// 04/05/11 17:45 NDP - ResultSetPrinter: add format for CSV option
// 05/05/11 11:45 NDP - ResultSetPrinter: remove Timestamp special formatting for 00:00:00.0
// 27/11/11 20:35 NDP : ResultSetPrinter: clean Javadoc & new method name
// 06/12/11 15:45 NDP : ResultSetPrinter: new method name: print()
// 07/12/11 18:35 NDP : ResultSetPrinter: clean javadoc
// 28/03/12 13:05 NDP : ResultSetPrinter: clean javadoc
package org.awakefw.sql.api.util;
import java.io.PrintStream;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import org.awakefw.file.api.util.AwakeDebug;
import org.awakefw.file.util.AwakeLogger;
/**
*
* Tool to display the content of any JDBC ResultSet on a print
* stream.
* (Code extracted/adapted from Oreilly JDBC book).
*
* Note that header columns are left justified and all result columns are right
* justified.
*
* Example on our test database awake_example:
*
*
* String sql = "select * from customer limit 5";
* PreparedStatement preparedStatement = connection.prepareStatement(sql);
* ResultSet rs = preparedStatement.executeQuery();
*
* // Print the result set on System.out:
* ResultSetPrinter resultSetPrinter = new ResultSetPrinter(rs, System.out);
* resultSetPrinter.print();
*
* rs.first();
* System.out.println();
*
* // Print the result set on System.out with CSV format:
* resultSetPrinter = new ResultSetPrinter(rs, System.out, true);
* resultSetPrinter.print();
*
* preparedStatement.close();
* rs.close();
*
*
Will produce the following output:
*
*
* customer_id | title | fname | lname | addressline | town | zipcode | phone
* ---------------------------------------------------------------------------------------------
* 1 | Sir | NULL | Smith_1 | 1, César Avenue | JavaLand_1 | 145 | 1-12345678
* 2 | Sir | NULL | Smith_2 | 2, César Avenue | JavaLand_2 | 245 | 2-12345678
* 3 | Sir | NULL | Smith_3 | 3, César Avenue | JavaLand_3 | 345 | 3-12345678
* 4 | Sir | NULL | Smith_4 | 4, César Avenue | JavaLand_4 | 445 | 4-12345678
* 5 | Sir | NULL | Smith_5 | 5, César Avenue | JavaLand_5 | 545 | 5-12345678
* (5 rows)
*
* customer_id;title;fname;lname;addressline;town;zipcode;phone
* 1;Sir;NULL;Smith_1;1, César Avenue;JavaLand_1;145;1-12345678
* 2;Sir;NULL;Smith_2;2, César Avenue;JavaLand_2;245;2-12345678
* 3;Sir;NULL;Smith_3;3, César Avenue;JavaLand_3;345;3-12345678
* 4;Sir;NULL;Smith_4;4, César Avenue;JavaLand_4;445;4-12345678
* 5;Sir;NULL;Smith_5;5, César Avenue;JavaLand_5;545;5-12345678
*
*
*
*
*
* @author Nicolas de Pomereu
* @since 1.0
*/
public class ResultSetPrinter {
/** Debug Value */
private static boolean DEBUG = AwakeDebug.isSet(ResultSetPrinter.class);
/** Universal and clean line separator */
private static String CR_LF = System.getProperty("line.separator");
/**
* The map than contains per column number (starts at 1) the maximum
* required length
*/
private Map columnWidth = new HashMap();
/** The Result Set to process */
private ResultSet resultSet = null;
/** The print stream to use */
private PrintStream printStream = null;
/**
* if true, result will be formatted for CSV format with ";" separator
* instead of "|"
*/
private boolean formatForCSV = false;
/**
* Constructor to use to display a human readable or a CSV formated ResultSet
*
* @param resultSet
* the result set to display
* @param printStream
* the print stream to use for display
* @param formatForCSV
* if true, result will be formatted for CSV format with ";"
* separator instead of "|"
*/
public ResultSetPrinter(ResultSet resultSet, PrintStream printStream,
boolean formatForCSV) {
if (resultSet == null) {
throw new IllegalArgumentException("resultSet can not be null!");
}
if (printStream == null) {
throw new IllegalArgumentException("printStream can not be null!");
}
this.resultSet = resultSet;
this.printStream = printStream;
this.formatForCSV = formatForCSV;
}
/**
* Constructor to use to display a human readable ResultSet.
*
* @param resultSet
* the result set to display
* @param printStream
* the print stream to use for display
*/
public ResultSetPrinter(ResultSet resultSet, PrintStream printStream) {
this(resultSet, printStream, false);
}
/**
* Computes the column max width.
*/
private void computeColumnMaxWidth() throws SQLException {
ResultSetMetaData meta = resultSet.getMetaData();
int cols = meta.getColumnCount();
for (int i = 1; i <= cols; i++) {
String label = meta.getColumnLabel(i);
label = label.trim();
// int size = meta.getColumnDisplaySize(i);
int length = label.length();
columnWidth.put(i, length + 2);
}
// Formatter chaque ligne de l?ensemble r?sultat et l?ajouter.
while (resultSet.next())
{
// Formatter chaque colonne de la ligne.
for (int i = 1; i <= cols; i++) {
Object value = resultSet.getObject(i);
String strNoTrim = null;
if (resultSet.wasNull()) {
strNoTrim = "NULL";
} else {
if (value == null) {
strNoTrim = "";
} else {
strNoTrim = value.toString();
}
}
// Trim, because of some SQL (Postgres,...) behavior
String str = strNoTrim.trim();
int length = str.length();
/*
if (columnWidth.get(i) == null) {
columnWidth.put(i, length + 2);
} else {
int storedLength = columnWidth.get(i);
if (length > storedLength) {
columnWidth.put(i, length + 2);
}
}
*/
if (length + 2 > columnWidth.get(i))
{
columnWidth.put(i, length + 2);
}
}
}
}
/**
* Prints the Result Set.
*/
public void print() throws SQLException {
resultSet.beforeFirst();
computeColumnMaxWidth(); // Compute the column max width
resultSet.beforeFirst(); // reset the read Result Set
ResultSetMetaData meta = resultSet.getMetaData();
StringBuffer bar = new StringBuffer();
StringBuffer buffer = new StringBuffer();
int cols = meta.getColumnCount();
int row_count = 0;
int i, width = 0;
// Prepare header of each column
// Display will look like:
// --------------------------------------
// | Column one | Column two |
// --------------------------------------
// | Value line 1 | Value line 1 |
// --------------------------------------
// Create bar who length is total of all columns
for (i = 1; i <= cols; i++) {
// width += meta.getColumnDisplaySize(i);
width += columnWidth.get(i);
}
width += 1 + cols;
for (i = 0; i < width - 2; i++) {
bar.append('-');
}
// bar.append(CR_LF);
// buffer.append(bar.toString() + CR_LF + "|");
// buffer.append(bar.toString() + CR_LF);
// After firt libe bare: display column titles
for (i = 1; i <= cols; i++) {
String label = meta.getColumnLabel(i);
String colType = meta.getColumnTypeName(i);
debug("");
debug(label);
debug(colType);
// int size = meta.getColumnDisplaySize(i);
int size = columnWidth.get(i);
// debug("Column Label: " + label);
// debug("Column size : " + size);
if (!formatForCSV) {
label = fillHeaderWithBlanks(label, size);
// Add column headers to buffer
buffer.append(label);
if (i < cols) {
buffer.append("|");
}
} else {
buffer.append(label);
if (i < cols) {
buffer.append(";");
}
}
}
// Display underbar
if (!formatForCSV) {
buffer.append(CR_LF + bar.toString());
}
String bufferheader = buffer.toString();
printStream.println(bufferheader);
// Formatter chaque ligne de l?ensemble r?sultat et l?ajouter.
while (resultSet.next()) {
buffer = new StringBuffer();
row_count++;
// buffer.append('|');
// Formatter chaque colonne de la ligne.
for (i = 1; i <= cols; i++) {
Object value = resultSet.getObject(i);
// int size = meta.getColumnDisplaySize(i);
int size = columnWidth.get(i);
String strNoTrim = null;
if (resultSet.wasNull()) {
strNoTrim = "NULL";
} else {
if (value == null) {
strNoTrim = "";
} else {
strNoTrim = value.toString();
}
}
// Trim, because of some SQL (Postgres,...) behavior
String str = strNoTrim.trim();
// NDP: Commented, don't know why it remained?
// if (meta.getColumnType(i) == Types.TIMESTAMP)
// {
// // Fix for timestamp display, because size is limited to
// first 14:
// str = str.substring(2);
// }
if (!formatForCSV) {
str = fillValueWithBlanks(size, str);
// buffer.append(str + "|");
buffer.append(str);
if (i < cols) {
buffer.append("|");
}
} else {
str = str.replace(';', ',');
buffer.append(str);
if (i < cols) {
buffer.append(";");
}
}
}
String bufferLine = buffer.toString();
printStream.println(bufferLine);
}
if (!formatForCSV) {
String query_trailer = new String();
query_trailer = "(" + row_count + " rows)";
printStream.println(query_trailer);
}
}
/**
* Fills the value of the result set column with surrounding blanks.
*
* @param size
* the size to fill with blanks
* @param str
* the column value
* @return the string filled with blank
*/
private String fillValueWithBlanks(int size, String str) {
StringBuffer filler = new StringBuffer();
if (str.length() > size) {
str = str.substring(0, size);
}
if (str.length() < size) {
int j, x;
// x = (size-str.length())/2;
x = 1;
for (j = 0; j < x; j++) {
filler.append(" ");
}
str = filler + str + filler;
if (str.length() > size) {
str = str.substring(0, size);
} else {
while (str.length() < size) {
str = " " + str; // blanks appended left because of numbers
// align need
}
}
}
return str;
}
/**
* Fills the header with surrounding blanks.
*
* @param label
* the header label
* @param size
* the size to fill with blanks
* @return the header filled with surrounding blanks
*/
private String fillHeaderWithBlanks(String label, int size) {
StringBuffer filler = new StringBuffer();
int x;
// Si le titre est plus long que la largeur de la colonne,
// tronquer son contenu.
if (label.length() > size) {
label = label.substring(0, size);
}
// Si le titre est moins long que la largeur de la colonne,
// ajouter des espaces.
if (label.length() < size) {
int j;
// x = (size-label.length())/2;
x = 1;
for (j = 0; j < x; j++) {
filler.append(' ');
}
label = filler + label + filler;
if (label.length() > size) {
label = label.substring(0, size);
} else {
while (label.length() < size) {
label += " ";
}
}
}
return label;
}
private static void debug(String s) {
if (DEBUG)
AwakeLogger.log(s);
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy