Getting a NullPointerException at PreparedStatement

Question

So I'm supposedly connected to JDBC, using mySQL. I have a connection function, which tells me that it works fine. But I run into a problem in the PreparedStatement code.

My actual code does not show any errors but when I run, my terminal shows me "Database connection success" then gives me "Exception in thread "main" java.lang.NullPointerException".

Which directs me to my line -- PreparedStatement posted = conn.prepareStatement("INSERT INTO people(firstName, lastName) VALUES ('"+var1+"', '"+var2+"')")

So there is a disconnect when running my post(); And that does not make any sense to me....What should I check to fix this?

    public static void main(String[] args) throws Exception{
        People project = new People();
        project.createConnection();
        project.post();
    }
    
    void createConnection(){
        try {
            String driver = "com.mysql.cj.jdbc.Driver";
            Class.forName(driver);
            java.sql.Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/peopledb", "root", "root");
            if(conn != null){System.out.println("Database connection success");}
            
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(People.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(People.class.getName()).log(Level.SEVERE, null, ex);
        }
        
    }
    
    void post() {
        try {
            final String var1 = "John";
            final String var2 = "Doe";
            PreparedStatement posted = conn.prepareStatement("INSERT INTO people(firstName, lastName) VALUES ('"+var1+"', '"+var2+"')");
            posted.executeUpdate();
            if(conn != null){
                System.out.println("Insert Completed");
            }
        } catch (SQLException ex) {
            Logger.getLogger(People.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

}

N.B. Most of the point of the `PreparedStatement` is precisely to avoid the kind of string pasting you're doing. Use `?` and parameters instead. — chrylis -cautiouslyoptimistic-, Jul 17 '21 at 03:25
You're using a local variable `conn` in `createConnection()` instead of the field `conn`. As an aside, your code is extremely brittle because you catch and essentially ignore exceptions and continue on as if nothing wrong happened. You're also leaking resources (for example, you aren't closing the prepared statement, e.g. using a try-with-resources block). — Mark Rotteveel, Jul 17 '21 at 06:59

score -1 · Accepted Answer · answered Jul 17 '21 at 02:40

You may try the following modifications to address this specific problem.

    public static void main(String[] args) throws Exception{
        People project = new People();
        project.createConnection();
        project.post();
    }
    private java.sql.Connection conn; //create attribute accessible by both methods
    void createConnection(){
        try {
            String driver = "com.mysql.cj.jdbc.Driver";
            Class.forName(driver);
            //assign value to attribute instead of local variable
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/peopledb", "root", "root");
            if(conn != null){System.out.println("Database connection success");}
            
         } catch (ClassNotFoundException ex) {
            Logger.getLogger(People.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(People.class.getName()).log(Level.SEVERE, null, ex);
        }
        
    }
    
    void post() {
        try {
            final String var1 = "John";
            final String var2 = "Doe";
            //Below is prone to SQL Injection. 
            //PreparedStatement posted = conn.prepareStatement("INSERT INTO people(firstName, lastName) VALUES ('"+var1+"', '"+var2+"')");
            //Recommended approach
            PreparedStatement posted = conn.prepareStatement("INSERT INTO people(firstName, lastName) VALUES (?, ?)");
            posted.setString(1,var1); 
            posted.setString(2,var2);
            //we expect 1 row to inserted from this statement and executeUpdate
            //will return the number of affected rows
            if(posted.executeUpdate() ==1){
                System.out.println("Insert Completed");
            }
        } catch (SQLException ex) {
            Logger.getLogger(People.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

}

omg @ggordon apparently that worked....It's extra code to throw in. But since it worked, that's fine. I guess the way the statement was quoted caused the problem... Thank you so much! — vipham1, Jul 17 '21 at 03:16
Please don't post only code as answer, but also provide an explanation what your code does and how it solves the problem of the question. Answers with an explanation are usually more helpful and of better quality, and are more likely to attract upvotes. — Mark Rotteveel, Jul 17 '21 at 07:00
@MarkRotteveel I included comments in the code shared highlighting and explaining the specific changes to address this problem. There are a lot of recommendations that can be made for this snippet to make it better, however I tried to be specific to the question being asked. Should I also include recommendations pertaining to other conventions that would improve the code quality here? — ggordon, Jul 17 '21 at 12:52

Getting a NullPointerException at PreparedStatement

1 Answers1