CODEWITHSUNDEEP
javadatabasesqlitejdbc
user3932611
user3932611

Reputation:

JDBC prepared statement select from table where

I am trying to write a JDBC SQLite preparedstatement that will return the row where the customerID matches the one passed in. My method is returning a null pointer exception.

I do not have great understanding of JDBC, SQLite or preparedstatements, but from what I can see I have everything I need and cant work out why it is not working. My code is below:

public static void CustomersSelectWhere(JPanel customers, int CustID)
      {              
         CustomersTable();
          String [] entries = new String[7]; 
        Connection c = null;
        PreparedStatement pstmt = null;
        try {
          Class.forName("org.sqlite.JDBC");
          c = DriverManager.getConnection("jdbc:sqlite:WalkerTechCars.db");
          c.setAutoCommit(false);

          String query = "SELECT * FROM CUSTOMERS WHERE CUSTID=? " ;

          ResultSet rs = pstmt.executeQuery( query );

          pstmt = c.prepareStatement(query);
          pstmt.setInt(1, CustID);
          pstmt.executeUpdate();
          c.commit();



          while ( rs.next() ) {
             int custId = rs.getInt("custID");
             String phone = rs.getString("phone");
             String  surname = rs.getString("surname");
             String  firstname = rs.getString("firstname");
             String home  = rs.getString("home");
             String  address = rs.getString("address");
             String  postcode = rs.getString("postcode");

             customers.add(customersTableSingle(Integer.toString(custId), firstname, surname, phone, home, address, postcode, false, customers ));


          }
          rs.close();
          pstmt.close();
          c.close();
        } catch ( Exception e ) {
          System.err.println( e.getClass().getName() + ": " + e.getMessage() );
          System.exit(0);
        }

      }

}

Upvotes: 1

Views: 3206

Answers (4)

rinilnath
rinilnath

Reputation: 136

Your code is missing the assignment for pstmt prepared statement object, it is initialized to null and left as it is.

please add the line of

PreparedStatment pstmt = c.preparedStatement(query); and then get it in result set object rs=pstmt.executeQuery(); then iterating the result set will do.

Upvotes: 0

Elliott Frisch
Elliott Frisch

Reputation: 201507

Your syntax is a little off, first preapreStatement then bind the parameter and then call executeQuery() like

String query = "SELECT * FROM CUSTOMERS WHERE CUSTID=?";
// ResultSet rs = pstmt.executeQuery( query );
pstmt = c.prepareStatement(query);
pstmt.setInt(1, CustID);
rs = pstmt.executeQuery();
// c.commit();

Also, you should close() everything in a finally. So, something like

public static void customersSelectWhere(JPanel customers, int CustID) {
    CustomersTable();
    String[] entries = new String[7];
    Connection c = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        // You should not need Class.forName anymore.
        // Class.forName("org.sqlite.JDBC");
        c = DriverManager.getConnection("jdbc:sqlite:WalkerTechCars.db");
        c.setAutoCommit(false);

        String query = "SELECT * FROM CUSTOMERS WHERE CUSTID=?";
        // ResultSet rs = pstmt.executeQuery( query );
        pstmt = c.prepareStatement(query);
        pstmt.setInt(1, CustID);
        rs = pstmt.executeQuery();
        // c.commit();
        while (rs.next()) {
            // Make it a String to begin with.
            // String custId = rs.getString("custID");
            String custId = Integer.toString(CustID);
            String phone = rs.getString("phone");
            String surname = rs.getString("surname");
            String firstname = rs.getString("firstname");
            String home = rs.getString("home");
            String address = rs.getString("address");
            String postcode = rs.getString("postcode");
            customers.add(customersTableSingle(custId, firstname, surname,
                    phone, home, address, postcode, false, customers));
        }
    } catch (Exception e) {
        System.err.println(e.getClass().getName() + ": " + e.getMessage());
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (c != null) {
            try {
                c.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

Upvotes: 0

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726939

You have mixed prepared statements of two different kinds - queries and updates. You are querying pstmt before assigning it, which causes NPE. You need to remove everything related to committing, too:

try {
    Class.forName("org.sqlite.JDBC");
    c = DriverManager.getConnection("jdbc:sqlite:WalkerTechCars.db");
    c.setAutoCommit(false);
    String query = "SELECT * FROM CUSTOMERS WHERE CUSTID=? " ;
    pstmt = c.prepareStatement(query);
    pstmt.setInt(1, CustID);
    ResultSet rs = pstmt.executeQuery( query );
    while ( rs.next() ) {
        String phone = rs.getString("phone");
        String surname = rs.getString("surname");
        String firstname = rs.getString("firstname");
        String home  = rs.getString("home");
        String address = rs.getString("address");
        String postcode = rs.getString("postcode");
        customers.add(customersTableSingle(Integer.toString(CustId), firstname, surname, phone, home, address, postcode, false, customers ));
    }
    rs.close();
    pstmt.close();
    c.close();
} catch ( Exception e ) {
    System.err.println( e.getClass().getName() + ": " + e.getMessage() );
    System.exit(0);
}

Note that you do not need to query CustId back - it's going to be the same ID that you have supplied. It is also not a good idea to pass * in production code, and then retrieve columns by their index: this is too error-prone. You should list out the names of all columns explicitly.

Upvotes: 1

Amar
Amar

Reputation: 971

You have defined 

PreparedStatement pstmt = null;

The prepared statement should have been created from the connection.Since your prepared statement is null and you are trying to call ResultSet rs = pstmt.executeQuery( query ); which is the reason of NPE.

Upvotes: 1

Related Questions