/*
* This program is free software; you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation; either version 2 of the License, or
* (at your option) any later version.
*
* This program 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 General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program; if not, write to the Free Software
* Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
*/
/*
* ResultSetHelper.java
* Copyright (C) 2005 University of Waikato, Hamilton, New Zealand
*
*/
package weka.gui.sql;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Types;
import java.util.Vector;
/**
* Represents an extended JTable, containing a table model based on a ResultSet
* and the corresponding query.
*
* @author FracPete (fracpete at waikato dot ac dot nz)
* @version $Revision: 7037 $
*/
public class ResultSetHelper {
/** the resultset to work on. */
protected ResultSet m_ResultSet;
/** whether we initialized. */
protected boolean m_Initialized = false;
/** the maximum number of rows to retrieve. */
protected int m_MaxRows = 0;
/** the number of columns. */
protected int m_ColumnCount = 0;
/** the number of rows. */
protected int m_RowCount = 0;
/** the column names. */
protected String[] m_ColumnNames = null;
/** whether a column is numeric. */
protected boolean[] m_NumericColumns = null;
/** the class for each column. */
protected Class[] m_ColumnClasses = null;
/**
* initializes the helper, with unlimited number of rows.
*
* @param rs the resultset to work on
*/
public ResultSetHelper(ResultSet rs) {
this(rs, 0);
}
/**
* initializes the helper, with the given maximum number of rows (less than
* 1 means unlimited).
*
* @param rs the resultset to work on
* @param max the maximum number of rows to retrieve
*/
public ResultSetHelper(ResultSet rs, int max) {
super();
m_ResultSet = rs;
m_MaxRows = max;
}
/**
* initializes, i.e. reads the data, etc.
*/
protected void initialize() {
ResultSetMetaData meta;
int i;
if (m_Initialized)
return;
try {
meta = m_ResultSet.getMetaData();
// columns names
m_ColumnNames = new String[meta.getColumnCount()];
for (i = 1; i <= meta.getColumnCount(); i++)
m_ColumnNames[i - 1] = meta.getColumnLabel(i);
// numeric columns
m_NumericColumns = new boolean[meta.getColumnCount()];
for (i = 1; i <= meta.getColumnCount(); i++)
m_NumericColumns[i - 1] = typeIsNumeric(meta.getColumnType(i));
// column classes
m_ColumnClasses = new Class[meta.getColumnCount()];
for (i = 1; i <= meta.getColumnCount(); i++) {
try {
m_ColumnClasses[i - 1] = typeToClass(meta.getColumnType(i));
}
catch (Exception ex) {
m_ColumnClasses[i - 1] = String.class;
}
}
// dimensions
m_ColumnCount = meta.getColumnCount();
// if the JDBC driver doesn't support scrolling we can't determine
// the row count here
if (m_ResultSet.getType() == ResultSet.TYPE_FORWARD_ONLY) {
m_RowCount = -1;
}
else {
m_RowCount = 0;
m_ResultSet.first();
if (m_MaxRows > 0) {
try {
m_ResultSet.absolute(m_MaxRows);
m_RowCount = m_ResultSet.getRow();
}
catch (Exception ex) {
// ignore it
}
}
else {
m_ResultSet.last();
m_RowCount = m_ResultSet.getRow();
}
// sometimes, e.g. with a "desc <table>", we can't use absolute(int)
// and getRow()???
try {
if ( (m_RowCount == 0) && (m_ResultSet.first()) ) {
m_RowCount = 1;
while (m_ResultSet.next()) {
m_RowCount++;
if (m_ResultSet.getRow() == m_MaxRows)
break;
};
}
}
catch (Exception e) {
// ignore it
}
}
m_Initialized = true;
}
catch (Exception ex) {
// ignore it
}
}
/**
* the underlying resultset.
*
* @return the resultset
*/
public ResultSet getResultSet() {
return m_ResultSet;
}
/**
* returns the number of columns in the resultset.
*
* @return the number of columns
*/
public int getColumnCount() {
initialize();
return m_ColumnCount;
}
/**
* returns the number of rows in the resultset. If -1 then the number of
* rows couldn't be determined, i.e., the cursors aren't scrollable.
*
* @return the number of rows, -1 if it wasn't possible to
* determine
*/
public int getRowCount() {
initialize();
return m_RowCount;
}
/**
* returns an array with the names of the columns in the resultset.
*
* @return the column names
*/
public String[] getColumnNames() {
initialize();
return m_ColumnNames;
}
/**
* returns an array that indicates whether a column is numeric or nor.
*
* @return the numeric columns
*/
public boolean[] getNumericColumns() {
initialize();
return m_NumericColumns;
}
/**
* returns the classes for the columns.
*
* @return the column classes
*/
public Class[] getColumnClasses() {
initialize();
return m_ColumnClasses;
}
/**
* whether a limit on the rows to retrieve was set.
*
* @return true if there's a limit
*/
public boolean hasMaxRows() {
return (m_MaxRows > 0);
}
/**
* the maximum number of rows to retrieve, less than 1 means unlimited.
*
* @return the maximum number of rows
*/
public int getMaxRows() {
return m_MaxRows;
}
/**
* returns an 2-dimensional array with the content of the resultset, the first
* dimension is the row, the second the column (i.e., getCells()[y][x]).
* Note: the data is not cached! It is always retrieved anew.
*
* @return the data
*/
public Object[][] getCells() {
int i;
int n;
Vector<Object[]> result;
Object[] row;
int rowCount;
boolean proceed;
initialize();
result = new Vector<Object[]>();
try {
// do know the number of rows?
rowCount = getRowCount();
if (rowCount == -1) {
rowCount = getMaxRows();
proceed = m_ResultSet.next();
}
else {
proceed = m_ResultSet.first();
}
if (proceed) {
i = 0;
while (true) {
row = new Object[getColumnCount()];
result.add(row);
for (n = 0; n < getColumnCount(); n++) {
try {
// to get around byte arrays when using getObject(int)
if (getColumnClasses()[n] == String.class)
row[n] = m_ResultSet.getString(n + 1);
else
row[n] = m_ResultSet.getObject(n + 1);
}
catch (Exception e) {
row[n] = null;
}
}
// get next row, if possible
if (i == rowCount - 1) {
break;
}
else {
// no more rows -> exit
if (!m_ResultSet.next())
break;
}
i++;
}
}
}
catch (Exception e) {
e.printStackTrace();
}
return result.toArray(new Object[result.size()][getColumnCount()]);
}
/**
* Returns the class associated with a SQL type.
*
* @param type the SQL type
* @return the Java class corresponding with the type
*/
public static Class typeToClass(int type) {
Class result;
switch (type) {
case Types.BIGINT :
result = Long.class;
break;
case Types.BINARY:
result = String.class;
break;
case Types.BIT:
result = Boolean.class;
break;
case Types.CHAR:
result = Character.class;
break;
case Types.DATE:
result = java.sql.Date.class;
break;
case Types.DECIMAL:
result = Double.class;
break;
case Types.DOUBLE:
result = Double.class;
break;
case Types.FLOAT:
result = Float.class;
break;
case Types.INTEGER:
result = Integer.class;
break;
case Types.LONGVARBINARY:
result = String.class;
break;
case Types.LONGVARCHAR:
result = String.class;
break;
case Types.NULL:
result = String.class;
break;
case Types.NUMERIC:
result = Double.class;
break;
case Types.OTHER:
result = String.class;
break;
case Types.REAL:
result = Double.class;
break;
case Types.SMALLINT:
result = Short.class;
break;
case Types.TIME:
result = java.sql.Time.class;
break;
case Types.TIMESTAMP:
result = java.sql.Timestamp.class;
break;
case Types.TINYINT:
result = Short.class;
break;
case Types.VARBINARY:
result = String.class;
break;
case Types.VARCHAR:
result = String.class;
break;
default:
result = null;
}
return result;
}
/**
* returns whether the SQL type is numeric (and therefore the justification
* should be right).
*
* @param type the SQL type
* @return whether the given type is numeric
*/
public static boolean typeIsNumeric(int type) {
boolean result;
switch (type) {
case Types.BIGINT :
result = true;
break;
case Types.BINARY:
result = false;
case Types.BIT:
result = false;
break;
case Types.CHAR:
result = false;
break;
case Types.DATE:
result = false;
break;
case Types.DECIMAL:
result = true;
break;
case Types.DOUBLE:
result = true;
break;
case Types.FLOAT:
result = true;
break;
case Types.INTEGER:
result = true;
break;
case Types.LONGVARBINARY:
result = false;
break;
case Types.LONGVARCHAR:
result = false;
break;
case Types.NULL:
result = false;
break;
case Types.NUMERIC:
result = true;
break;
case Types.OTHER:
result = false;
break;
case Types.REAL:
result = true;
break;
case Types.SMALLINT:
result = true;
break;
case Types.TIME:
result = false;
break;
case Types.TIMESTAMP:
result = true;
break;
case Types.TINYINT:
result = true;
break;
case Types.VARBINARY:
result = false;
break;
case Types.VARCHAR:
result = false;
break;
default:
result = false;
}
return result;
}
}