Null pointer exception when trying to create table in sqlite database with Java

Question

I have a simple Java program that connects to an empty sqlite database. I am trying to create a table, but get a null pointer exception when I try to execute the query.

• I made sure all the required jar's are there.
• I used the debugger to find the problematic line of code. It is line 51 of my code below. Another relevant line of code is line 113 of the Statement class, which I can post for reference, if necessary.
• I made sure that my statement was properly initialised

import java.sql.*;

public class SqliteConnection
{
    public static Connection connect()
    {
        Connection conn = null;
        try
        {
            // db parameters
            String url = "jdbc:sqlite:/database.db";
            // create a connection to the database
            conn = DriverManager.getConnection(url);

            System.out.println("Connection to SQLite has been established.");

        } 
        catch (SQLException e) 
        {
            System.out.println(e.getMessage());
        } 
        finally 
        {
            try
            {
                if (conn != null) 
                {
                    conn.close();
                }
            } 
            catch (SQLException ex) 
            {
                System.out.println(ex.getMessage());
            }
        }

        return conn;
    }

    public static void main(String[] args)
    {
        // TODO Auto-generated method stub
        Connection c = connect();

        try
        {
            //create the table
            Statement stmt = null;
            stmt = c.createStatement();
            String sql = "CREATE TABLE Car (CarID INT NOT NULL, Manufacturer TEXT, Type TEXT, FuelEfficiency REAL, PRIMARY KEY(CarID));";
            stmt.execute(sql);
            stmt.close();
            c.close();
        }
        catch (Exception e)
        {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
    }
}

Stack trace:

java.lang.NullPointerException
    at org.sqlite.Stmt.execute(Stmt.java:113)
    at SqliteConnection.main(SqliteConnection.java:52)

Answer 1

Your connect method creates a connection object that then... immediately closes itself. This is not how you use try/finally. (finally means: Do this the moment execution attempts to exit the associated try block. It does NOT mean: If an exception happens anywhere in the entire codebase from here on out, do this block).

TIP: Exceptions contain 5 useful things: Type, message, stack trace, and causal chain. Printing all that is difficult to do; so don't try. If you don't know how to handle an exception, either throw it onwards (just put throws Exception on your main method here) or if you can't/don't want that either, throw new RuntimeException(e); is a far superior way to go compared to e.printStackTrace().

Example better implementation:

import java.sql.*;

public class SqliteConnection {
    public static Connection connect() throws SQLException
    {
        Connection conn = DriverManager.getConnection(url);
        System.out.println("Connection to SQLite has been established.");
        return conn;
    }

    public static void main(String[] args) throws SQLException
    {
        try (Connection c = connect(); Statement stmt = c.createStatement();) {
            String sql = "CREATE TABLE Car (CarID INT NOT NULL, Manufacturer TEXT, Type TEXT, FuelEfficiency REAL, PRIMARY KEY(CarID));";
            stmt.execute(sql);
        }
    }
}

Note how the above sample:

1. Uses the 'ARM' (Automatic Resource Management) construct to create the connection and statement objects, ensuring they are always closed when the block exits no matter how it exits.

2. The code is much shorter and easier to read.

3. SQLExceptions that do occur will print all relevant information now, instead of only bits and pieces. Also, if an exception occurs, the code path that caused it will abort instead of continuing onwards in an invalid state.