Inserting data into a Access Database

Question

I am having trouble with a exercise I am working on with Java using a Access Database, wondering if anyone could shed some light on this

In my main class I make a call like this

Product p = new Product("test", "Test Product", 49.50);
insertProduct(p);

This calls the insertProduct(p); method. What I am trying to do is insert some test data into the database, here is my code for that.

public static void insertProduct(Product p)
{
    try
    {
        System.out.println("Insert test: ");

        String insertProduct = "INSERT INTO Products (ProductCode, Description, Price) VALUES ('andr', 'Beginning Android', '38.99')";
        PreparedStatement ps = connection.prepareStatement(insertProduct);
        ps.setString(1, p.getCode());
        ps.setString(2, p.getDescription());
        ps.setDouble(3, p.getPrice());
        ps.executeUpdate();

        ResultSet rs = ps.executeQuery();

        rs.next();
        String productCode = rs.getString(1);
        String description = rs.getString(2);
        double price = rs.getDouble(3);

        p = new Product(productCode, description, price);


        printProduct(p);
        System.out.println();

    }
    catch(SQLException e)
    {
        e.printStackTrace();
    }
}

when I run this I get a

Exception in thread "main" java.lang.NullPointerException

and can't figure out what is going on, I know that when I run the debugger and step through the method, it crashes at the beginning of this statements

ps.setString(1, p.getCode());
ps.setString(2, p.getDescription());
ps.setDouble(3, p.getPrice());
ps.executeUpdate();

here is the results of the stack trace

at sun.jdbc.odbc.JdbcOdbcPreparedStatement.clearParameter(JdbcOdbcPreparedStatement.java:1023)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.setChar(JdbcOdbcPreparedStatement.java:3057)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.setString(JdbcOdbcPreparedStatement.java:766)
at DBTesterApp.insertProduct(DBTesterApp.java:165)
at DBTesterApp.main(DBTesterApp.java:21)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:120)

Answer 1

The line in the stack trace:

at sun.jdbc.odbc.JdbcOdbcPreparedStatement.setString(JdbcOdbcPreparedStatement.java:766)

indicates that it fell over during your call to PreparedStatement.setString(), which indicates that either p.getCode() or p.getDescription() returns null.

There is therefore a problem in your Product class. Are you setting those fields correctly in the constructor?

Once you've fixed that, you may be disappointed to find that all your rows you insert have exactly the same data.

Look at you SQL to find out why:

String insertProduct = "INSERT INTO Products (ProductCode, Description, Price) VALUES ('andr', 'Beginning Android', '38.99')";

This should be:

String insertProduct = "INSERT INTO Products (ProductCode, Description, Price) VALUES (?,?,?)";