prepared statements and result sets - Java null pointer exception

Question

Overview: Trying to connect to a MS Access DB to return a result set into a jtable.

Issue: java null pointer exception

Code:

package sundata;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Database {

public String strMIGID;
public String strEBID;
public String strSUN;
public String[][] objIDList;

//CONNECTION INFO
private Connection con;
private String strDBCon="jdbc:ucanaccess://C:/Users/Luke/Documents/MainDB.mdb";
public String strEXMessage;

public int CreateConnection(){
    try{
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        con = DriverManager.getConnection(strDBCon);

        return 1;
    }catch(Exception ex){
        ex.printStackTrace();
        strEXMessage=ex.getMessage();
        return 2;
    }

}

public int CloseConnection(){
    try{
        con.close();
        return 1;
    }catch(Exception ex){
        ex.printStackTrace();
        strEXMessage=ex.getMessage();
        return 2;
    }

}

public int GetMIGIDRecord(String strMIGIDRef){
    try{
        System.out.println("Connecting to database using MIGID");

        String strSQLString = "Select * from tblSuppliersData where Supplier1 = ?";
        PreparedStatement preStatement = con.prepareStatement(strSQLString, ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

        //SETTING FIRST CONDITION OF PREPARED STATEMENT IE ?
        preStatement.setString(1, strMIGIDRef);

        //EXECUTE QUERY
        preStatement.executeQuery();

        //RETURNS QUERY RESULTS INTO RESULT SET
        ResultSet rs = preStatement.getResultSet();

        //CHECK TO MAKE SURE SOME RECORDS ARE RETURNED
        rs.last();
        //IF CHECKS TO MAKE SURE RECORDS
        if(rs.getRow()==0){
            rs.close();      
            preStatement.close();
            return 3;
        }
        //RECORDS NUMBER OF RECORDS
        int iNoRecords = rs.getRow();

        //CREATING 2D ARRAY WITH NO RECORDS (ROWS) AND TWO COLUMNS
        String strTempdata[][] = new String [iNoRecords][3];

        //MOVES BACK TO BEFORE FIRST RECORD
        rs.beforeFirst();

        //TRANSPOSES RS IN TO ARRAY
        int i = 0;
        while (rs.next()){
            strTempdata[i][0]= rs.getString("MIGID");
            strTempdata[i][1]= rs.getString("EBID");
            strTempdata[i][2]= rs.getString("SUN");
            i++;
        }
        objIDList = strTempdata;
        //CLOSES CONNECTIONS
        rs.close();
        preStatement.close();

        System.out.println("Connection complete");

        return 1;
    }catch(Exception ex){
        ex.printStackTrace();
        strEXMessage=ex.getMessage();
        return 2;
    }
}

I have a form that grabs the strMIGIDREF which I can paste code from if needs be, but I think it's not the issue here.

This is the error I'm getting, I've pasted the first bit and can paste more if required.

Connecting to database using MIGID
java.lang.NullPointerException
at sundata.Database.GetMIGIDRecord(Database.java:63)
at sundata.MainForm.MIGIDSearch(MainForm.java:141)
at sundata.MainForm.jmenuMIGIDSearchActionPerformed(MainForm.java:337)
at sundata.MainForm.access$000(MainForm.java:6)
at sundata.MainForm$1.actionPerformed(MainForm.java:58)
at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2018)

Line 63 is

//CHECK TO MAKE SURE SOME RECORDS ARE RETURNED
rs.last();

I have tried a number of solutions I've found on Google and no joy as of yet. I have tried a test bit of code that checks connections and such and that seemed to work fine it's just when I'm trying use preparedstatement it seems to go a miss. ResultSet rs = preStatement.getResultSet(); just keeps saying it's value is null and so is getMaxRows.

I'm using Netbeans IDE 7.4 and JAVA SE SDK 7

Answer 1

You are getting a NullPointerException because you are mis-using the executeQuery method:

//EXECUTE QUERY
preStatement.executeQuery();

//RETURNS QUERY RESULTS INTO RESULT SET
ResultSet rs = preStatement.getResultSet();

The first line of code executes the SQL query and returns a ResultSet object, which you simply discard (because you don't assign it to anything). The second line of code attempts to retrieve the ResultSet for the PreparedStatement, but it has already been retrieved (and discarded) by the previous line, so getResultSet returns null.

Instead, you should simply do

ResultSet rs = preStatement.executeQuery();

Additional notes:

1. Omit the Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); statement because (1) It is irrelevant: UCanAccess does not use ODBC, and (2) Class.forName statements are almost never required any more (and UCanAccess doesn't need one at all).

2. Don't use rs.last() to test if rows were returned. Instead, simply call rs.next() immediately after the ResultSet is returned. If rs.next() returns true then the ResultSet contains rows (and you are now pointing at the first one). If rs.next() returns false then no rows were returned. rs.last() followed by rs.beforeFirst() will not work correctly with the default ResultSet type, which is ResultSet.TYPE_FORWARD_ONLY.