Create Datasource from JDBC Resultset

[Anubrata Chowdhury]

Can any one give me some sample code on converting a JDBC Resultset into a CategoryDatasource? I have tried to use the following code in a servlet but always I end up ArrayIndexOutOfBoundsException.

Where am I doing wrong ?

public CategoryDataSource createCategoryDataSource() {
String driver= "oracle.jdbc.driver.OracleDriver";
String dbURL="jdbc:oracle:thin:@localhost:1521:ORCL";
String login="SCOTT" ;
String password="TIGER" ;
Connection conn ;
Statement stmt=null;
Statement stmt1=null;
int colCount = 0 ;
int i = 0 ;
int j = 0 ;
Number [][] data = new Integer[][]{};
try {
Class.forName(driver);
conn=DriverManager.getConnection dbURL,login,password);
stmt1=conn.createStatement();
stmt = conn.createStatement();
ResultSet rs1 = stmt1.executeQuery("SELECT COUNT(DISTINCT CAT_ID) LCOUNT FROM CRIME_FACT WHERE YEAR = 1971");
while ((rs1 != null) && rs1.next()) {
colCount = rs1.getInt("LCOUNT") ; }
rs1.close() ;
stmt1.close();
ResultSet rs = stmt.executeQuery("SELECT NO_OF_CRIME FROM CRIME_FACT WHERE YEAR = 1971 ");
int currRow = 0 ;
while ((rs != null) && rs.next()) {
++currRow ;
if (currRow%colCount <0) {
i = 0 ;}
else if (currRow%colCount == 1) {
i++ ; }
j = currRow - (i * colCount) ; data[j] = new Integer(rs.getInt("NO_OF_CRIME"));
}
rs.close();
stmt.close();
conn.close();
}
catch (ClassNotFoundException e) {
System.err.println("Connection: driver unavailable");
}
catch (SQLException e) {
System.err.println("sql error");
}

return new DefaultCategoryDataSource(data);

}

Any hint will be hightly appreciated .

Thanks

Anu

[Anubrata]

I think I got the answer. I have to initialize array with row /column no.

Number [][] data = new Integer[5][6]{};

Anu

[Julio Guzman]

Are you always going to have the same size resultset? If you are cool, but if your not i would recommend using a collections object. I used vectors (many will say its inefficient but it does the job). For example:

while(rs.next())
{
Object[] records = new Object[6];
records[0] = rs.getString("SITE_ID");
records[1] = rs.getDate("DATE");
records[2] = rs.getTime("TIME");
records[3] = (Integer)new Integer(rs.getInt("KW"));
records[4] = (Integer)new Integer(rs.getInt("KVAR"));
records[5] = (Integer)new Integer(rs.getInt("KVA"));
rsVector.add(records);
}
rs.close();

then you can extract them out of there and throw them into the appropriate array depending on the DataSource. In your case a CategoryDataSource which takes the new Integer[][] as its source.

hope this helps,
julio

[Anubrata Chowdhury]

Thanks a lot julio.

Anu