Search code examples
javaandroidsqlitecursor

App Crashes When Using Cursor To Read Data From SQLITE Database

I am trying to make simple application where I can save notes and see them with a listview using an SQLite database. The problem is that I cannot see the contents of the listview. The app crashes instantly. I figured out there is a problem with cursor database.query but I didn't find a way to solve it...

Mainactivity.java: 

package com.example.notemaker;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.support.design.widget.FloatingActionButton;
import android.support.design.widget.Snackbar;
import android.support.v7.app.AppCompatActivity;
import android.support.v7.widget.Toolbar;
import android.view.View;
import android.view.Menu;
import android.view.MenuItem;
import android.content.Intent;
import android.view.Window;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.widget.ArrayAdapter;
import android.widget.ListAdapter;
import android.widget.ListView;
import android.widget.TextView;
import android.widget.Toast;

import java.util.ArrayList;

public class MainActivity extends AppCompatActivity {  

private ListView listView;
     private ArrayList<String> listItem;
     private ArrayAdapter adapter;
     private DBOpenHelper helper;
     private SQLiteDatabase database;
     private TextView noNotesView;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        Toolbar toolbar = findViewById(R.id.toolbar);
        setSupportActionBar(toolbar);

        FloatingActionButton fab = findViewById(R.id.fab);
        fab.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                Intent intent = new Intent(MainActivity.this, Edit_notes.class);
                startActivity(intent);
            }
        });

        noNotesView = findViewById(R.id.empty_notes);
        listView = (ListView) findViewById(R.id.listView);
        listItem = new ArrayList<>();
        ViewData();
    }

   private void ViewData(){

       helper = new DBOpenHelper(this, "notes.db", null, 1); // db
       database = helper.getWritableDatabase();

       String table_name = "note_table";
       String[] columns = {"ID", "NOTE_TEXT"};
       String where = null;
       String where_args[] = null;
       String group_by = null;
       String having = null;
       String order_by = null;
       Cursor result = database.query(table_name, columns, where, where_args, group_by, having, order_by);
    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        getMenuInflater().inflate(R.menu.menu_main, menu);
        return true;
    }

    @Override
    public boolean onOptionsItemSelected(MenuItem item) {
        int id = item.getItemId();
        if (id == R.id.action_settings) {
            return true;
        }
        return super.onOptionsItemSelected(item);
    }

    @Override
    public void onResume()
    {
        super.onResume();
        ViewData();
    }
}

SQLite database:

package com.example.notemaker;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class DBOpenHelper extends SQLiteOpenHelper {
    // constructor for the class here we just map onto the constructor of the
// super class
    public DBOpenHelper(Context context, String name, CursorFactory
            factory, int version) {
        super(context, name, factory, version);
    }
    // overridden method that is called when the database is to be created
    public void onCreate(SQLiteDatabase db) {
// create the database
        db.execSQL(create_table);
    }
// overridden method that is called when the database is to be upgraded
// note in this example we simply reconstruct the database not caring for
// data loss ideally you should have a method for storing the data while
    // are reconstructing the database
    public void onUpgrade(SQLiteDatabase db, int version_old, int version_new)
    {
// drop the tables and recreate them
        db.execSQL(drop_table);
        db.execSQL(create_table);
    }
    // a bunch of constant strings that will be needed to create and drop
// databases
    private static final String create_table = "create table note_table(" +
            "ID integer primary key autoincrement, " +
            "NOTE_TEXT string" +
            ")";
    private static final String drop_table = "drop table note_table";
}

Editnotes.java: 

package com.example.notemaker;

import android.content.ContentValues;
import android.content.DialogInterface;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.support.v7.app.AlertDialog;
import android.support.v7.app.AppCompatActivity;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.EditText;
import android.widget.Toast;
import java.util.Date;

public class Edit_notes extends AppCompatActivity {

    private DBOpenHelper dbop;
    private SQLiteDatabase sdb;

  //  private TestDBOpenHelper tdb;
  //  private SQLiteDatabase sdb;

    @Override
    protected void onCreate(Bundle savedInstanceState){
        super.onCreate(savedInstanceState);
        setContentView(R.layout.note_edit);

        getSupportActionBar().setTitle("");
        getSupportActionBar().setDisplayHomeAsUpEnabled(true);
    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        getMenuInflater().inflate(R.menu.cancel_button_menu, menu);
        getMenuInflater().inflate(R.menu.save_button_menu, menu);
        return super.onCreateOptionsMenu(menu);
    }
    @Override
    public boolean onOptionsItemSelected(MenuItem item) {

        int id = item.getItemId();

        // Create confirmation dialog when click on CANCEL button
        if (id == R.id.cancel_note) {
            final AlertDialog.Builder alert = new AlertDialog.Builder(this);
            alert.setTitle("NoteMaker");
            alert.setMessage("Cancel this note ?");
            alert.setPositiveButton("Yes", new DialogInterface.OnClickListener() {
                @Override
                public void onClick(DialogInterface dialog, int which) {
                    Toast.makeText(alert.getContext(), "Note cancelled", Toast.LENGTH_SHORT).show();
                    finish();
                }

            });

            alert.setNegativeButton("No", new DialogInterface.OnClickListener() {
                @Override
                public void onClick(DialogInterface dialog, int which) {
                    dialog.dismiss();
                }
            });
            alert.create().show();
            return true;
        }

        // Save note title and note text to the database
        if (id == R.id.save_note)
        {

         //   tdb = new TestDBOpenHelper(this, "test.db", null, 1);
         //   sdb = tdb.getWritableDatabase();

            String note_title_string = findViewById(R.id.input_note_title).toString();
            String note_text_string = findViewById(R.id.input_note).toString();

            //if (!note_title_string.isEmpty()){
              //  long date = new Date().getTime(); // Get date

                AddData(note_title_string); // Add title to the database
                Toast.makeText(this, "Note saved", Toast.LENGTH_SHORT).show();
                finish();
           // }
            //else{
              //  Toast.makeText(this, "Title cannot be empty", Toast.LENGTH_SHORT).show();
           // }

            return true;
        }

        return super.onOptionsItemSelected(item);
    }

    public void AddData (String newEntry){
        dbop = new DBOpenHelper(this, "notes.db", null, 1);
        sdb = dbop.getWritableDatabase();

        ContentValues cv = new ContentValues();
        cv.put("note", newEntry);

        sdb.insert("note_table", null, cv);
        //long insertData = dbop.insertNote(newEntry);
    }
}

Crash log

Solution

  • The issue that caused the crash, was that you likely ran the App before the DBOpenHelper's onCreate method created the table.

    The DBOpenHelper's onCreate method only ever runs automatically once for the entire lifetime of the database, thus the changes made to create the table, were not being applied. Uninstalling the App results in the App's data and therefore the database being deleted, thus allowing the onCreate method to run.

    However I still can't view the contents of my listview –

    For a ListView to display data you need to instantiate a suitable adapter and to then set the adapter that the ListView is to use using the ListView's setAdapter method.

    Working Example

    The following is a cut down version (no FAB) for convenience that utilises a SimpleCursorAdapter and is based upon your code.

    Note that for Cursor adapters the Cursor must have a column named _id so the column name has been changed throughout.

    Most changes have been commented (except the FAB code which has been removed), so please read the comments.

    When applying the above, you would need to uninstall the App so that the changed column name can be applied.

    DBOpenHelper.java

    public class DBOpenHelper extends SQLiteOpenHelper {
    // constructor for the class here we just map onto the constructor of the
    // super class
    public DBOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory
            factory, int version) {
        super(context, name, factory, version);
    }
    // overridden method that is called when the database is to be created
    public void onCreate(SQLiteDatabase db) {
        // create the database
        db.execSQL(create_table);
    }
    // overridden method that is called when the database is to be upgraded
    // note in this example we simply reconstruct the database not caring for
    // data loss ideally you should have a method for storing the data while
    // are reconstructing the database
    public void onUpgrade(SQLiteDatabase db, int version_old, int version_new)
    {
        // drop the tables and recreate them
        db.execSQL(drop_table);
        db.execSQL(create_table);
    }
    // a bunch of constant strings that will be needed to create and drop
    // databases
    private static final String create_table = "create table note_table(" +
            BaseColumns._ID + " integer primary key autoincrement, " + //<<<<<<<<<< changed column name to _id for CursorAdapter
            "NOTE_TEXT string" +
            ")";
    private static final String drop_table = "drop table note_table";

    /**
     * ADDED to demonstrate typical inclusion of DB access within the helper
     * @param note
     * @return the id of the added note or -1 if not added.
     */
    public long addNote(String note) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put("NOTE_TEXT",note);
        return db.insert("note_table",null,cv);
    }
}

    MainActivity.java

    public class MainActivity extends AppCompatActivity {

    private ListView listView;
    private ArrayList<String> listItem;
    private ArrayAdapter adapter; //<<<<<<<<<< Not used
    private SimpleCursorAdapter sca; //<<<<<<<<<< ADDED
    private Cursor csr; //<<<<<<<<<< ADDED
    private DBOpenHelper helper;
    private SQLiteDatabase database;
    private TextView noNotesView;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        helper = new DBOpenHelper(this,"notes.db", null, 1); //<<<<<< moved to here
        database = helper.getWritableDatabase(); //<<<<<<<<<< moved to here so has scope throughout the class

        //noNotesView = findViewById(R.id.empty_notes); //<<<<<<<<<< commented out for simplicity
        listView = (ListView) findViewById(R.id.listView);
        listItem = new ArrayList<>();
        addSomeTestData(); //Adds a row every time the App is run (for testing)
        ViewData();
    }

    private void ViewData(){

        String table_name = "note_table";
        String[] columns = {"_id", "NOTE_TEXT"}; //<<<<<<<<<< ID column is now _id
        String where = null;
        String where_args[] = null;
        String group_by = null;
        String having = null;
        String order_by = null;
        csr = database.query(table_name, columns, where, where_args, group_by, having, order_by);
        //<<<<<<<<<< ADDED following code to method to instantiate the adapter and set the adapter
        //                 and to alternatively swap the cursor to refresh the listview
        if (sca == null) {
            sca = new SimpleCursorAdapter(
                    this,
                    android.R.layout.simple_list_item_1,
                    csr,
                    new String[]{"NOTE_TEXT"},
                    new int[]{android.R.id.text1},
                    0);
            listView.setAdapter(sca);
        } else {
            sca.swapCursor(csr);
        }
    }

    private void addSomeTestData() {
        helper.addNote("My Note"); //<<<<<<<<<< Uses the addNote method in the DB helper
    }

    /**
     * ADDED for correct cursor handling i.e. close
     * although not really required in mainactivity
     */
    @Override
    protected void onDestroy() {
        super.onDestroy();
        csr.close();
    }

    @Override
    public void onResume()
    {
        super.onResume();
        ViewData();
    }
}