Resultset problem
Hi all,
I encountered a problem executing the following code:
/********************************************************************/
ResultSet rset = sql_stmt.executeQuery("select NAME from test");
rset.last();
int size=rset.getRow();
rset.first();
int fileInd=0;
while (rset.next())
{
fileName[fileInd] = rset.getString(1);
fileInd++;
}
/********************************************************************/
the code after
"rset.last();
int size=rset.getRow();
rset.first();" will not execute. but if I comment these three lines(I used it to get the number of rows selected), then the rest of the program will execute correctly.
Does anyone knows what's the problem? Did I do anything wrong?
Thanks in advance!
Karen
[846 byte] By [
karenJJa] at [2007-11-27 10:17:19]
# 1
I suggest not trying to get the number of items first (via rset.last();
) to determine what size your fixed array shoud be. Instead, put the items in an array that can grow in size, then convert it to a fixed array (and of course, using the try/catch/finally block to properly close connections). Example:
public String[] myFunction(){
Connection conn=null;
PreparedStatement pstmt1= null;
ResultSet resultSet=null;
ArrayList list1;//array that can grow in size
try{
list1=new ArrayList();
conn= dataSource.getConnection();
pstmt1= conn.prepareStatement();
resultSet= pstmt1.executeUpdate();
while(resultSet.next()){
arrayList.add(resultSet.getString("lastName");
}
return ( String[] ) arrayList.toArray( new String[arrayList.size()] );
} catch (SqlException e){
e.printStackTrace();
} finally {
if(rsultSet!=null)
resultSet.close();
if(pstmt1!=null)
pstmt1.close();
if(conn!=null)
conn.close();
}
}
}
# 4
You need ResultSet#beforeFirst() instead of ResultSet#first().
But using a Collection is indeed the best way. See George's solution (which doesn't compile however, but you got the point ;) ). Also the conversion to String[] puts question marks to me. I think that this is not needed after all.
# 5
"Also the conversion to String[] puts question marks to me"
You're right, you can return whatever collection type you consider most appropriate for your function.
# 6
> } finally {
> if(rsultSet!=null)
> resultSet.close();
> if(pstmt1!=null)
> pstmt1.close();
> if(conn!=null)
> conn.close();
> }
This was a good attempt, George, but I wouldn't recommend doing it this way. Each of those calls to close needs to be in its own try/catch block inside the finally block. That way they'll all be executed regardless.
%
# 7
Your right. I left out putting a 'throws SQLException' in the function declaration such as "public void myFunction throws SQLException() { }"
to handle the exceptions they may throw. Then again, it would be better to wrap them in a runTime exception and throw that so the caller isn't forced to handle the exception immediately. Likewise, e.printStackTrace() shown should be replaced with a write to the log file followed possibly by rethrowing the exception, or perhaps some type of recovery routine (depending on what you want). I didn't want to go into a full discussion of exception handling when I presented the example.
Also, a lot of programmers would not use the raw example as shown, but would wrap it in some other reusable utility function so you dont have to open/close connections and handle exceptions each time you want to read/write to the database. Best to reseach solutions to these issues than spelling them out in full here.
Here is a reference about handling exceptions:
http://www.javapractices.com/index.cjp
