posted 22 years ago
I see some picky people here. I just want to confirm this with you guys. Anyway you are helping me. The type in Oracle is NUMBER(8). I used rs.getLong(j+1). It works for other similar result set, but it fails this one.
This is the code:
package com.ubsw.fusion.data;
import java.util.List;
import java.util.HashSet;
import java.util.HashMap;
import java.util.Map;
import java.util.Set;
import java.util.Iterator;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import com.ubsw.dqe.businessobjects.Timestamp;
import com.ubsw.dqe.businessobjects.Table;
import com.ubsw.dqe.businessobjects.Identifier;
import com.ubsw.dqe.businessobjects.Descriptor;
import com.ubsw.dqe.businessobjects.TableColumn;
import com.ubsw.dqe.exception.InvalidDataFormatException;
import com.ubsw.dqe.exception.InvalidDataTypeException;
import com.ubsw.dqe.exception.ColumnCountException;
import com.ubsw.dqe.util.LongArrayList;
import com.ubsw.dqe.util.StringArrayList;
import com.ubsw.dqe.util.DoubleArrayList;
import com.ubsw.dqe.util.TimestampArrayList;
import com.ubsw.dqe.util.DataType;
import com.ubsw.fusion.data.metadata.FieldAddress;
import com.ubsw.fusion.data.metadata.Cache;
import com.ubs.ejb.framework.logging.LogService;
public class ResultSetToTable
{
private static final LogService log = new LogService(ResultSetToTable.class.getName());
public static Table execute(int[] colTypes, Descriptor[] colDescs, ResultSet rs) throws SQLException
{
return ResultSetToTable.execute(colTypes, colDescs, rs, false);
}
public static Table execute(int[] colTypes, Descriptor[] colDescs, ResultSet rs, boolean onlyOneRow) throws SQLException
{
for (int i=0; i<colTypes.length; i++)>
{
System.out.println(i + " " +colTypes[i]);
System.out.println(i + " " +colDescs[i]);
}
log.logDebug("starting conversion");
Table table = new Table(0);
try
{
log.logDebug("getting result set metadata object");
ResultSetMetaData rsmd = rs.getMetaData();
log.logDebug("getting result set col count");
int columnCount = rsmd.getColumnCount();
if ((columnCount != colDescs.length) | | (columnCount != colTypes.length))
{
log.logError("Column count mismatch: Got " + columnCount + ", expecting " + colTypes.length + ".");
return table;
}
// Used to hold XXXArrayList for each column
Object[] colData = new Object[columnCount];
// Column name for each columns
String[] colNames = new String[columnCount];
log.logDebug("initializing table metadata and columns");
// Initialize the table metadata and columns
for (int j = 0; j < columnCount; j++)
{
log.logDebug("getting column name");
// get the name of the column
colNames[j] = rsmd.getColumnName(j+1);
// based on the type of the column, create the array list
switch (colTypes[j])
{
case DataType.DOUBLE:
DoubleArrayList dal = new DoubleArrayList();
colData[j] = dal;
break;
case DataType.STRING:
StringArrayList sal = new StringArrayList();
colData[j] = sal;
break;
case DataType.INTEGER:
case DataType.LONG:
LongArrayList lal = new LongArrayList();
colData[j] = lal;
break;
case DataType.yyyyMMDD:
case DataType.yyyyMMDDHHmmss:
case DataType.yyyyMMDDHHmmssSSS:
TimestampArrayList tal = new TimestampArrayList();
colData[j] = tal;
break;
default:
throw new InvalidDataTypeException();
}
}
log.logDebug("getting column data");
String tmpStr;
int numRows = 0;
for (int i=0; rs.next() != false; i++)
{
numRows++;
for (int j=0; j<columnCount; j++) // NOTE: result set is 1 based so have to add one>
{
switch (colTypes[j])
{
case DataType.DOUBLE:
System.out.println(colTypes[j]+" " +j);
((DoubleArrayList)colData[j]).add(rs.getDouble(j+1));
break;
case DataType.yyyyMMDD:
System.out.println(colTypes[j]+" " +j);
((TimestampArrayList)colData[j]).add(new Timestamp(rs.getLong(j+1) * 1000000000L));
break;
case DataType.STRING:
System.out.println(colTypes[j]+" " +j);
tmpStr = rs.getString(j+1);
if (tmpStr == null)
{
tmpStr = "";
}
((StringArrayList)colData[j]).add(tmpStr);
break;
case DataType.yyyyMMDDHHmmss:
System.out.println(colTypes[j]+" " +j);
((TimestampArrayList)colData[j]).add(new Timestamp(rs.getLong(j+1) * 1000L));
break;
case DataType.INTEGER:
case DataType.LONG:
System.out.println(colTypes[j]+" " +j);
((LongArrayList)colData[j]).add(rs.getLong(j+1));
break;
case DataType.yyyyMMDDHHmmssSSS:
System.out.println(colTypes[j]+" " +j);
((TimestampArrayList)colData[j]).add(new Timestamp(rs.getLong(j+1)));
break;
default:
throw new InvalidDataTypeException();
}
}
if (onlyOneRow)
{
break;
}
}
if (log.isDebugEnabled()) { log.logDebug("Got " + numRows + " rows."); }
// now build a table and put the date column at the 0 index.
log.logDebug("Building Table Object");
table = buildTable(colTypes, colNames, colDescs, colData);
log.logDebug("done building Table Object");
}
catch(SQLException e)
{
log.logError("Error while processing result set into table.");
throw e;
}
catch(InvalidDataTypeException e)
{
log.logError("Caught InvalidDataTypeException (returning empty table): " + e.getMessage());
return table = new Table(0);
}
catch(InvalidDataFormatException e)
{
log.logError("Caught InvalidDataFormatException (returning empty table): " + e.getMessage());
return table = new Table(0);
}
catch(ColumnCountException e)
{
log.logError("Caught ColumnCountException (returning empty table): " + e.getMessage());
return table = new Table(0);
}
return table;
}
private static Table buildTable(int[] colTypes, String[] colNames,
Descriptor[] colDescs, Object[] colData)
throws InvalidDataTypeException, ColumnCountException, InvalidDataFormatException
{
Descriptor colDesc;
TableColumn tc;
LongArrayList lal;
StringArrayList sal;
DoubleArrayList dal;
TimestampArrayList tal;
log.logDebug("creating table instance");
// Construct table instance
Table table = new Table(colData.length);
// Construct and add table column instances
for (int k=0; k<colData.length; k++)>
{
log.logDebug("creating column");
switch (colTypes[k])
{
case DataType.DOUBLE:
// get the array of data
dal = (DoubleArrayList)colData[k];
double[] doubleArray = dal.toArray();
// create the table column and add it to the table
tc = new TableColumn(doubleArray, colDescs[k]);
table.addColumn(colNames[k], tc);
break;
case DataType.STRING:
// get the array of data
sal = (StringArrayList)colData[k];
String[] stringArray = sal.toArray();
// create the table column and add it to the table
tc = new TableColumn(stringArray, colDescs[k]);
table.addColumn(colNames[k], tc);
break;
case DataType.INTEGER:
case DataType.LONG:
// get the array of data
lal = (LongArrayList)colData[k];
long[] longArray = lal.toArray();
// create the table column and add it to the table
tc = new TableColumn(longArray, colDescs[k]);
table.addColumn(colNames[k], tc);
break;
case DataType.yyyyMMDD:
case DataType.yyyyMMDDHHmmss:
case DataType.yyyyMMDDHHmmssSSS:
// get the array of data
tal = (TimestampArrayList)colData[k];
Timestamp[] timestampArray = tal.toArray();
// create the table column and add it to the table
tc = new TableColumn(timestampArray, colDescs[k]);
table.addColumn(colNames[k], tc);
break;
default:
throw new InvalidDataTypeException();
}
}
log.logDebug("done");
return table;
}
}
Also the related class:
package com.ubsw.dqe.util;
import com.ubsw.dqe.exception.InvalidDataTypeException;
public class DataType
{
private static final int min = 0;
public static final int STRING = 0;
public static final int yyyyMMDD = 1;
public static final int yyyyMMDDHHmmss = 2;
public static final int yyyyMMDDHHmmssSSS = 3;
public static final int TIMESTAMP = 4;
public static final int INTEGER = 5;
public static final int LONG = 6;
public static final int DOUBLE = 7;
private static final int max = 7;
public static boolean isValidDataType(int type)
{
boolean validFlag = false;
if ((type >= min) & (type <= max))
{
validFlag = true;
}
return validFlag;
}
public static int getTypeFromString(String strType) throws InvalidDataTypeException
{
if (strType.equals("STRING"))
{
return DataType.STRING;
}
else if (strType.equals("yyyyMMDD"))
{
return DataType.yyyyMMDD;
}
else if (strType.equals("yyyyMMDDHHmmss"))
{
return DataType.yyyyMMDDHHmmss;
}
else if (strType.equals("yyyyMMDDHHmmssSSS"))
{
return DataType.yyyyMMDDHHmmssSSS;
}
else if (strType.equals("TIMESTAMP"))
{
return DataType.TIMESTAMP;
}
else if (strType.equals("INTEGER"))
{
return DataType.INTEGER;
}
else if (strType.equals("LONG"))
{
return DataType.LONG;
}
else if (strType.equals("DOUBLE"))
{
return DataType.DOUBLE;
}
else
{
throw new InvalidDataTypeException();
}
}
public static String getStringFromType(int type) throws InvalidDataTypeException
{
String s = null;
if (isValidDataType(type))
{
if (type == DataType.STRING)
{
s = new String("STRING");
}
else if (type == DataType.yyyyMMDD)
{
s = new String("yyyyMMDD");
}
else if (type == DataType.yyyyMMDDHHmmss)
{
s = new String("yyyyMMDDHHmmss");
}
else if (type == DataType.yyyyMMDDHHmmssSSS)
{
s = new String("yyyyMMDDHHmmssSSS");
}
else if (type == DataType.TIMESTAMP)
{
s = new String("TIMESTAMP");
}
else if (type == DataType.INTEGER)
{
s = new String("INTEGER");
}
else if (type == DataType.LONG)
{
s = new String("LONG");
}
else if (type == DataType.DOUBLE)
{
s = new String("DOUBLE");
}
}
else
{
throw new InvalidDataTypeException();
}
return s;
}
}
Thanks for any help
Jun Hong<br />SCJP, SCJD, SCWCD, SCEA<br />IBM Certified Systems Expert(V4.0)