SQLite android NullPointerException when inserting on sqlite

Question

I know that similar questions exists, but all I found relates to not having an instance of SQLiteDatabase.

One of the table definitions in my onCreate method which creates a database looks like that:

db.execSQL("CREATE TABLE offers(" +
            "    game_state_id   INTEGER NOT NULL REFERENCES game_states (id)," +
            "    city_id         INTEGER NOT NULL REFERENCES cities (id)," +
            "    item_id         INTEGER NOT NULL REFERENCES items (id)," +
            "    sell_price      INTEGER NOT NULL," +
            "    buy_price       INTEGER NOT NULL," +
            "    amount          INTEGER NOT NULL DEFAULT 0," +
            "    UNIQUE (game_state_id, city_id, item_id)" +
            ")");

And it's working, the database looks fine.

public long createGameState() {
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put("name", "default");
    values.put("current_city_id", getInitialCityId());
    // it could return -1 if insert didn't work
    long res = db.insert("game_states", null, values);

    List<City> cities = loadCities();
    List<Item> items = loadItems();
    Log.d("Kupiec", "Itemki: "+String.valueOf(items.size())+" | Miasta: "+String.valueOf(cities.size()));

    for (City city : cities) {
        for (Item item : items) {
            ContentValues values2 = new ContentValues();
            values2.put("item_id", item.id);
            values2.put("city_id", city.id);
            values2.put("game_state_id", res);
            values2.put("buy_price", Utils.getRandom(100, 125));
            values2.put("sell_price", Utils.getRandom(75, 100));
            values2.put("amount", Utils.getRandom(15, 30));
            Log.d("Kupiec", "offer_values: "+values2.toString());
            db.insert("offers", null, values2);
        }
    }

    db.close();
    return res;
}

What's interesting, the first insert, into the game_states table is working fine, however it breaks on the first insert in the loop.

06-12 13:28:03.872      630-630/com.mippit.kupiec E/AndroidRuntime﹕ FATAL EXCEPTION: main
java.lang.IllegalStateException: Could not execute method of the activity
    at android.view.View$1.onClick(View.java:3044)
    at android.view.View.performClick(View.java:3511)
    at android.view.View$PerformClick.run(View.java:14105)
    at android.os.Handler.handleCallback(Handler.java:605)
    at android.os.Handler.dispatchMessage(Handler.java:92)
    at android.os.Looper.loop(Looper.java:137)
    at android.app.ActivityThread.main(ActivityThread.java:4424)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:511)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:784)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:551)
    at dalvik.system.NativeStart.main(Native Method)
Caused by: java.lang.reflect.InvocationTargetException
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:511)
    at android.view.View$1.onClick(View.java:3039)
    at android.view.View.performClick(View.java:3511)
    at android.view.View$PerformClick.run(View.java:14105)
    at android.os.Handler.handleCallback(Handler.java:605)
    at android.os.Handler.dispatchMessage(Handler.java:92)
    at android.os.Looper.loop(Looper.java:137)
    at android.app.ActivityThread.main(ActivityThread.java:4424)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:511)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:784)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:551)
    at dalvik.system.NativeStart.main(Native Method)
Caused by: java.lang.NullPointerException
    at android.database.sqlite.SQLiteStatement.releaseAndUnlock(SQLiteStatement.java:290)
    at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:115)
    at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1718)
    at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1591)
    at com.mippit.kupiec.Repository.createGameState(Repository.java:182)
    at com.mippit.kupiec.Repository.createNewGame(Repository.java:153)
    at com.mippit.kupiec.MainActivity.clicked_new_game(MainActivity.java:42)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:511)
    at android.view.View$1.onClick(View.java:3039)
    at android.view.View.performClick(View.java:3511)
    at android.view.View$PerformClick.run(View.java:14105)
    at android.os.Handler.handleCallback(Handler.java:605)
    at android.os.Handler.dispatchMessage(Handler.java:92)
    at android.os.Looper.loop(Looper.java:137)
    at android.app.ActivityThread.main(ActivityThread.java:4424)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:511)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:784)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:551)
    at dalvik.system.NativeStart.main(Native Method)

What's going on? Line 182 with this error is the line with db.insert("offers", null, values2);

I got the good answer, however the author removed it, probably because he got 2 downvotes (not from me!). If someone from the stuff can return it back, please do it, I would be glad to mark it as accepted.

The correct answer was, at last, to try {...} catch {} this NullPointerException and load the database once again. I don't understand why it's happening, because I thought that db will be open until I explicitly say db.close(), however, it's working.

Answer 1

I am seen that the problem is you already inserted values in database with different table and without closing it you are trying to insert again. so conflict is there. replace your for loop statement

db.insert("offers", null, values2);

with

db.insertWithOnConflict("table name", null, "values", SQLiteDatabase.CONFLICT_IGNORE); 

Answer 2

It is because your game_state_id is required to be unique. You keep assigning it the same value, res, which you got from above. So, it will work the first time, but not any other time...