org.kawanfw.sql.api.util.ResultSetPrinter Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of aceql-http Show documentation
Show all versions of aceql-http Show documentation
AceQL HTTP is a framework of REST like http APIs that allow to access to remote SQL databases over http from any device that supports http.
AceQL HTTP is provided with four client SDK:
- The AceQL C# Client SDK allows to wrap the HTTP APIs using Microsoft SQL Server like calls in their code, just like they would for a local database.
- The AceQL Java Client SDK allows to wrap the HTTP APIs using JDBC calls in their code, just like they would for a local database.
- The AceQL Python Client SDK allows SQL calls to be encoded with standard unmodified DB-API 2.0 syntax
/*
* Copyright (c)2022 KawanSoft S.A.S. All rights reserved.
*
* Use of this software is governed by the Business Source License included
* in the LICENSE.TXT file in the project's root directory.
*
* Change Date: 2026-11-01
*
* On the date above, in accordance with the Business Source License, use
* of this software will be governed by version 2.0 of the Apache License.
*/
package org.kawanfw.sql.api.util;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.PrintStream;
import java.io.StringReader;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import org.kawanfw.sql.transport.TransportConverter;
import org.kawanfw.sql.util.FrameworkDebug;
/**
*
* Tool to display the content of any JDBC ResultSet on a print
* stream.
* (Code extracted/adapted from
* Oreilly JDBC
* book).
*
* Limitations:
*
* - Text/CLOB columns display is limited to the first line of text.
* - Binary/BLOB columns are not displayed.
*
*
* Example on our sampledb test database
* sampledb:
*
*
*
* 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 | customer_title | fname | lname | addressline | town | zipcode | phone
* ----------------------------------------------------------------------------------------------------------
* 1 | Sir | John | Smith_1 | 1, César Avenue | JavaLand_1 | 145 | 1-12345678
* 2 | Sir | John | Smith_2 | 2, César Avenue | JavaLand_2 | 245 | 2-12345678
* 3 | Sir | John | Smith_3 | 3, César Avenue | JavaLand_3 | 345 | 3-12345678
* 4 | Sir | John | Smith_4 | 4, César Avenue | JavaLand_4 | 445 | 4-12345678
* 5 | Sir | John | Smith_5 | 5, César Avenue | JavaLand_5 | 545 | 5-12345678
* (5 rows)
*
* customer_id;customer_title;fname;lname;addressline;town;zipcode;phone
* 1;Sir;John;Smith_1;1, César Avenue;JavaLand_1;145;1-12345678
* 2;Sir;John;Smith_2;2, César Avenue;JavaLand_2;245;2-12345678
* 3;Sir;John;Smith_3;3, César Avenue;JavaLand_3;345;3-12345678
* 4;Sir;John;Smith_4;4, César Avenue;JavaLand_4;445;4-12345678
* 5;Sir;John;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 = FrameworkDebug.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 ConcurrentHashMap();
/** 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 formatted
* 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 = getFirstLineOfText(strNoTrim);
str = str.trim();
debug("str:" + str);
int colType = meta.getColumnType(i);
if (str.startsWith(TransportConverter.KAWANFW_BYTES_STREAM_FILE)) {
if (isBinaryColumn(colType) || colType == Types.BIGINT) {
str = getBinaryDataMessage();
}
}
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);
}
}
}
}
/**
* @return the binary data message depending on android
*/
public String getBinaryDataMessage() {
return "(binary data)";
}
/**
* Prints the first line of a text
*
* @param str the text
* @return the first line of the text
* @throws SQLException
*/
private String getFirstLineOfText(String str) throws SQLException {
BufferedReader bufferedReader = new BufferedReader(new StringReader(str));
String line = null;
try {
line = bufferedReader.readLine();
} catch (IOException e) {
throw new SQLException(e);
}
return line;
}
/**
* 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 colTypeName = meta.getColumnTypeName(i);
int colType = meta.getColumnType(i);
debug("");
// int size = meta.getColumnDisplaySize(i);
int size = columnWidth.get(i);
debug("Column Label : " + label);
debug("Column size : " + size);
debug("Column type : " + colType);
debug("Column type name : " + colTypeName);
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 colType = meta.getColumnType(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 strCSV = strNoTrim.trim();
String str = getFirstLineOfText(strNoTrim);
str = str.trim();
if (str.startsWith(TransportConverter.KAWANFW_BYTES_STREAM_FILE)) {
if (isBinaryColumn(colType) || colType == Types.BIGINT) {
str = getBinaryDataMessage();
}
}
// 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, colType);
// 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);
}
}
/**
* Returns true if the column is a binary type
*
* @param columnType the sql column type
* @param columnName the sql column name
* @param columnTable the table name of the column
* @return true if it's a binary type
*/
private static boolean isBinaryColumn(int columnType) {
if (columnType == Types.BINARY || columnType == Types.VARBINARY || columnType == Types.LONGVARBINARY
|| columnType == Types.BLOB) {
return true;
} else {
return false;
}
}
/**
* Returns true if column is numeric
*
* @param colType the meta column type
* @return true if column is numeric
*/
private static boolean isColumnNumeric(int colType) {
if (colType == Types.TINYINT || colType == Types.SMALLINT || colType == Types.INTEGER || colType == Types.BIGINT
|| colType == Types.FLOAT || colType == Types.REAL || colType == Types.NUMERIC
|| colType == Types.DECIMAL) {
return true;
} else {
return false;
}
}
/**
* Fills the value of the result set column with surrounding blanks.
*
* @param size the size to fill with blanks
* @param str the column value
* @param colType
* @return the string filled with blank
*/
private String fillValueWithBlanks(int size, String str, int colType) {
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) {
if (isColumnNumeric(colType)) {
str = " " + str; // blanks appended left because of
// numbers
} else {
str = str + " ";
}
}
}
}
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) {
System.out.println(s);
}
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy