ResultSet: Exception: set type is TYPE_FORWARD_ONL

Question

I have very simple code:

pstat=con.prepareStatement("select typeid from users where username=? and password=?");             
pstat.setString(1, username);
pstat.setString(2, password);
rs=pstat.executeQuery();
int rowCount=0;
while(rs.next())
{       
    rowCount++;         
}
rs.beforeFirst();
if(rowCount>=1)
{
while(rs.next())
{
    typeID=rs.getInt(1);
}

But when execute this code I am getting...

java.sql.SQLException: Result set type is TYPE_FORWARD_ONLY
at sun.jdbc.odbc.JdbcOdbcResultSet.beforeFirst(Unknown Source)
at server.ClientImpl.login(ClientImpl.java:57)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at sun.rmi.server.UnicastServerRef.dispatch(Unknown Source)
at sun.rmi.transport.Transport$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at sun.rmi.transport.Transport.serviceCall(Unknown Source)
at sun.rmi.transport.tcp.TCPTransport.handleMessages(Unknown Source)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(Unknown Source)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)

What is causing this and how can I fix it?

Answer 1

java.sql.SQLException: Result set type is TYPE_FORWARD_ONLY

with JDBC 2.0 API, the user has the flexibility to move the cursor either forward or backward.

Your error can be removed by creating the statemnt as follows

Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);

Also, a better way to count the number of rows would be

rs=pstat.executeQuery();                //execute the query
rs.last();                              //move the cursor to the last row
int numberOfRows = rs.getRow();         //get the number of rows
rs.beforeFirst();                       //back to initial state

Answer 2

The type TYPE_FORWARD_ONLY means you can only move forward on the result set, not backward, so you get an exception when you try to go back with beforeFirst(). Instead you can either use the following prepareStatement(), which receives the resultset type as a parameter, or to do:

        pstat=con.prepareStatement("select typeid from users where username=? and password=?");             
        pstat.setString(1, username);
        pstat.setString(2, password);
        rs=pstat.executeQuery();
        int rowCount=0;

        while(rs.next())
        {
            rowCount++;
            typeID=rs.getInt(1);
        }