Accessing Data with Android Cursors (Part 1)

Creating and connecting to a Database

Create a new project in Eclipse called TestingData. Import android.database.sqllite.SQLiteDatabase in this project.

package com.kais.testingdata;

import android.app.Activity;

import android.database.sqlite.SQLiteDatabase;

import android.os.Bundle;

import android.view.Menu;

public class MainActivity extends Activity {

@Override

protected void onCreate(Bundle savedInstanceState) {

super.onCreate(savedInstanceState);

setContentView(R.layout.activity_main);

//here we create a database called TestingData.db

SQLiteDatabase db;

db = openOrCreateDatabase("TestingData.db", SQLiteDatabase.CREATE_IF_NECESSARY, null);

}

Creating Tables

Tables are created by executing statements on the database. The queries should be executed with the execSQL statement.Use  the setVersion(), setLocale() and setLockingEnabled() methods to set these properties. These will be demonstrated in the creating tables example.

Just put these lines of codes below the creation of database.

final String CREATE_TABLE_COUNTRIES = "CREATE TABLE tbl_countries("+ "id INTEGER                                                                                     PRIMARY KEY AUTOINCREMENT,"+"country_name                                                                           TEXT);";

final String CREATE_TABLE_STATES = "CREATE_TABLE tbl_states("+" id INTEGER PRIMARY                                                                   KEY AUTOINCREMENT,"+" state_name TEXT "+"                                                                                 country_id INTEGER KEY NULL CONSTRAINT "+"                                                                               country_id INTEGER NOT NULL CONSRAINT "+"                                                                                  country_id REFRENCES tbl1_countries(id) "+" ON DELETE                                                                      CASCADE);";

db.execSQL(CREATE_TABLE_COUNTRIES);

db.execSQL(CREATE_TABLE_STATES);

final String CREATE_TRIGGER_STATES = 

        "CREATE TRIGGER fk_insert_state BEFORE "

        + "INSERT on tbl_states"

        + "FOR EACH ROW "

        + "BEGIN "

                                + "SELECT RAISE(ROLLBACK, 'insert on table

                               "+" tbl_states violates foreign key constraint 

                               "+" fk_insert_state') WHERE (SELECT id FROM "+" tbl_countries WHERE id =                                         NEW.country_id) IS NULL; "+" END;";

db.execSQL(CREATE_TRIGGER_STATES);

Additionally here we manually have to create triggers to handle the foreign key relationships between the table. In  a production application there would also need to be foreign key triggers to handle row updates and deletes. The foreign key triggers are executed with execSQL just like the table creation.

Inserting Records

Android comes with a series of classes that simplify database usage. Use a ContentValues instance to create a series of table field to data matchings that will be passed into an insert() method. Android has created similar methods for updating and deleting records. 

ContentValues values = new ContentValues();
        values.put("country_name", "US");
        long countryId = db.insert("tbl_countries", null, values);
        ContentValues stateValues = new ContentValues();
        stateValues.put("state_name", "Texas");
        stateValues.put("country_id", Long.toString(countryId));
        try {
            db.insertOrThrow("tbl_states", null, stateValues);
        } catch (Exception e) {
            //catch code
        }

Append this code to the previous example. First create a ContentValues object to store the data to insert and use the put method to load the data. Then use the insert() method to perform the insert query into SQLite. The insert() function expects three parameters, the table name, null, and the ContentValues pairs. Also a long is returned by the insert() function. This long will hold the primary key of the inserted row.

Updating Records

Updating records is handled with the update() method. The update() function supports WHERE syntax similar to other SQL engines. The update() method expects the table name, a ContentValues instance similar to insert with the fields to update. Also allowed are optional WHERE syntax, add a String containing the WHERE statement as parameter 3. Use the ? to designate an argument replacement in the WHERE clause with the replacements passed as an array in parameter 4 to update. 

 ContentValues updateCountry = new ContentValues();
       updateCountry.put("country_name", "United States");
  db.update("tbl_countries", updateCountry, "id=?", new String[] {Long.toString(countryId)});

First remove the table create statements from the code. We don't need to keep creating and dropping tables. Now create a new ContentValues instance, updateCountry, to hold the data to be updated. Then use the update() method to update the table. The where clause in parameter 3 uses replacement of the ? with the values stored in parameter 4. If multiple? existed in the where statement they would be replaced in order by the values of the array.

Deleting Data

Once data is no longer needed it can be removed from the database with the delete() method. The delete() method expects 3 parameters, the database name, a WHERE clause, and an argument array for the WHERE clause. To delete all records from a table pass null for the WHERE clause and WHERE clause argument array. 

 db.delete("tbl_states", "id=?", new String[] {Long.toString(countryId)});

Simply call the delete() method to remove records from the SQLite database. The delete method expects, the table name, and optionally a where clause and where clause argument replacement arrays as parameters. The where clause and argument replacement array work just as with update where ? is replaced by the values in the array.