0

So this is my first time programming a database connection in Java to a DB2 database and I already received help so far, but now I'm stuck at a NullPointerException which I receive when trying to execute a SQL query by pressing a button. The NullPointerException seems to occur from my PreparedStatement I used for my SQL query. I'm using the MVC model and DAO pattern.

My Controller class (only showing the important parts):

public class Controller implements Observer, ActionListener{

Connection dbConnection = null;
public static PreparedStatement preparedStatement = null;

@Override
public void actionPerformed(ActionEvent e) {
    switch (e.getActionCommand()) {
    case View.SEARCH:
        try {

            String name = View.searchname.getText();
            String plz = View.searchplz.getText();
            String result = "SELECT BBSTBBNR, BBSTNABEG, BBSTPLZ FROM BP.TBBBST WHERE BBSTNABEG = name AND BBSTPLZ = plz";

            dbConnection = ConnectionManager.getConnection();
            PreparedStatement preparedStatement = dbConnection.prepareStatement(result);


            model.search();
        } catch (SQLException e1) {
            System.out.println("An SQL-Error occured: ");
            e1.printStackTrace();
        } catch (IOException e1) {
            System.out.println("An error occured: ");
            e1.printStackTrace();
        }
        break;

    default:
        System.out.println("Search error: " + e.getActionCommand());
        break;
    }

}

My BtrDao class:

public interface BtrDao {

Collection<BTRBean> getBTR() throws SQLException, IOException;
}

My DaoImpl class:

public class BtrDaoImpl extends AbstractDao implements BtrDao {

public Collection<BTRBean> getBTR() throws SQLException,
        IOException {
    final Collection<BTRBean> result = new ArrayList<BTRBean>();

    ResultSet resultset = null;
    try {
        //This is where the Nullpointerexception happens!
        resultset = Controller.preparedStatement.executeQuery();

        // while loop for finding all results
        while (resultset.next()) {
            BTRBean btr = new BTRBean();
            int btrid = resultset.getInt(1);
            String btrplz = resultset.getString(2);
            String btrname = resultset.getString(3);
            result.add(btr);
            System.out.println(btrid + " " + btrplz + " " + btrname);
        }

    } catch (SQLException e) {
        e.printStackTrace();
        System.out.println("There was an SQL processing error: (getBTR)");
        e.printStackTrace();
    } catch (NullPointerException npe) {
        System.out.println("NullPointerException");
        npe.printStackTrace();
    } finally {

        closeConnection(resultset);

    }

    return result;
}

}

My AbstractDao class:

public class AbstractDao {

public static Connection getConnection() throws SQLException {
    return ConnectionManager.getInstance().getConnection();
}

public ResultSet getResultset(final String statement) throws SQLException {
    final Statement stmnt = getConnection().createStatement();
    return stmnt.executeQuery(statement);
}

public void closeConnection(final ResultSet resultset) throws SQLException {
    if(resultset != null)
    resultset.close();
    getConnection().close();
}
}

My ConnectionManager Class:

public class ConnectionManager {

    public Connection getConnection() throws SQLException {

        try {
        Properties props = new Properties();
        FileInputStream in = new FileInputStream(".//required.\\dbprop.\\DBConn.properties");
        props.load(in);
        in.close();
        String drivers = props.getProperty("jdbc.drivers");
        if (drivers !=null) 
            System.setProperty("jdbc.drivers", drivers);

        String url = props.getProperty("jdbc.url");
        if (url !=null)
            System.setProperty("jdbc.url", url);

        String username = props.getProperty("jdbc.username");
        if (username !=null)
            System.setProperty("jdbc.username", username);
        String password = props.getProperty("jdbc.password");
        if (password !=null)
            System.setProperty("jdbc.password", password);

        return DriverManager.getConnection(url, username, password);
        } catch (IOException e) {
            throw new SQLException(e.getMessage(), e.getCause());
        }

    }


    //Close connection
    public void closeConnection(Connection con) throws SQLException {
        if (con != null)
            con.close();
    }

    // Making sure that there's only one instance of the class
    private static ConnectionManager singleton_Instance;

    // default constructor
    private ConnectionManager() {
    }

    // returns the single instance of the class
    public static ConnectionManager getInstance() {
        if (singleton_Instance == null) {
            singleton_Instance = new ConnectionManager();
        }
        return singleton_Instance;
    }

}

For my ConnectionManager the database drivers, url, username and password are read from a properties file. (Yes this is how I'm supposed to do it)

I've been sitting at this problem for a whole day now and whenever I try fixing it another error pops up. Can anyone tell me which part is wrong and how I can improve/fix it?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
APL
  • 101
  • 1
  • 3
  • 14
  • 2
    On *which line* does the error occur? Be specific. Add the stacktrace. – Manu Nov 17 '15 at 10:04
  • 1
    You never assign the PreparedStatement after: `public static PreparedStatement preparedStatement = null;` so it will always be `null`. – Manu Nov 17 '15 at 10:06
  • first of all where is model created? then how is name and plz included in your sql query? you search each time after name and plz. – nano_nano Nov 17 '15 at 10:06
  • I found my mistake. Yes I never did assign my prepared statement, but accidentally created a new one and pointed to the empty one. However now I get another error, just like when trying to fix this before. "SQLCODE=-206, SQLSTATE=42703" referencing to the variable "name" which should include the text entered into a JTextField by the user. – APL Nov 17 '15 at 13:17
  • I have rolled back your edit, please don't fundamentally alter a question once it has been asked. Please ask a new question. – Mark Rotteveel Nov 17 '15 at 13:27

0 Answers0