Skip to content

Storing data in SQLite Database

Jump to bottom
rutura edited this page Apr 17, 2017 · 1 revision
  • The database structure(table and columns) is defined in the onCreate() function of your subclass of SQLOpenHelper.See myDbHelper.java
  • You get an instance to the db using SQLiteOpenHelper like this:
           SQLiteDatabase db = SQLiteOpenHelper.getWritableDatabase()

This function creates the database if not present and if it is there, it opens it for writing.

  • More practicaly, you use a subclass of SQLiteOpenHelper with custom overrides to take care of CRUD operations
   public class MyDbHelper extends SQLiteOpenHelper {
   ...
   }
  • Get information about a database table using a qery that returns a Cursor you will use for further manipulations.A Cursor is an interface designed to read, write, and traverse the results of a query.
                  String[] columns = new String[]{"_id", MyDbHelper.COL_NAME, MyDbHelper.COL_DATE};
                  //columns contains the names of all the columns  contained in your table.See MyDbHelper.java
                  Cursor mCursor = mDb.query(MyDbHelper.TABLE_NAME, columns, null, null, null, null, null, null);
                  //The cursor we get here is capable of manipulating all row entries in the columns specified in the columns String array.
  • Next you figure out a way to wire your database to the ui.In here we used a SimpleCursorAdapter to make the
         ListItems in our ListView reflect what is stored in the database:
               String[] headers = new String[]{MyDbHelper.COL_NAME, MyDbHelper.COL_DATE};
                mAdapter = new SimpleCursorAdapter(this, android.R.layout.two_line_list_item,
                mCursor, headers, new int[]{android.R.id.text1, android.R.id.text2});
                mList.setAdapter(mAdapter);
  • Add data to the database:
               //Add a new value to the database
                ContentValues cv = new ContentValues(2);
                 cv.put(MyDbHelper.COL_NAME, mText.getText().toString());
                 //Create a formatter for SQL date format
                SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                cv.put(MyDbHelper.COL_DATE, dateFormat.format(new Date())); //Insert 'now' as the date
                mDb.insert(MyDbHelper.TABLE_NAME, null, cv);
                //Refresh the cursor and the list
                mCursor.requery();
                mAdapter.notifyDataSetChanged();//This lets the list view update with new data
  • Delete data from the database at some position:
                //Delete the item from the database
                mCursor.moveToPosition(position);
                //Get the id value of this row
                String rowId = mCursor.getString(0); //Column 0 of the cursor is the id
                mDb.delete(MyDbHelper.TABLE_NAME, "_id = ?", new String[]{rowId});
                //Refresh the list
                mCursor.requery();
                mAdapter.notifyDataSetChanged();
  • Close database connections when you are done.In this case when the activity pauses:
                //Close all connections
                 mDb.close();
                mCursor.close();
Clone this wiki locally