Hi,

SQL Demo

This example shows how to interact with Android SDK internal database SQLite to perform a simple action of inserting and querying data from a database table and database display content.

Our simple data format is: [row id] [time recorded] [Hello Android Event]



To make this possible requires;

1. An Activity SQLDemo

In this example, the sqldemo activity has four important methods related to our database;
AddEvent - ability to add a string type event into the database note the use of the getWritableDatabase() method to the database handler instance,
GetEvents - ability to query database and here note the getReadableDatabase() method useage together with the startManagingCursor(cursor) and therefore return a cursor (walker),
ShowEvents - display of the data submitted by GetEvents cursor that was returned and loop through all the data,
onDestroy - always release database instance back to system;

SQLDemo.java

Code:


package org.example.sqldemo;

import static android.provider.BaseColumns._ID;
import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.widget.TextView;

public class SQLDemo extends Activity {
  EventDataSQLHelper eventsData;
  TextView output;

  @Override
  public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);

    output = (TextView) findViewById(R.id.output);

    eventsData = new EventDataSQLHelper(this);
    addEvent("Hello Android Event");
    Cursor cursor = getEvents();
    showEvents(cursor);
  }
 
  @Override
  public void onDestroy() {
    eventsData.close();
  }

  private void addEvent(String title) {
    SQLiteDatabase db = eventsData.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(EventDataSQLHelper.TIME, System.currentTimeMillis());
    values.put(EventDataSQLHelper.TITLE, title);
    db.insert(EventDataSQLHelper.TABLE, null, values);

  }

  private Cursor getEvents() {
    SQLiteDatabase db = eventsData.getReadableDatabase();
    Cursor cursor = db.query(EventDataSQLHelper.TABLE, null, null, null, null,
        null, null);
   
    startManagingCursor(cursor);
    return cursor;
  }

  private void showEvents(Cursor cursor) {
    StringBuilder ret = new StringBuilder("Saved Events:\n\n");
    while (cursor.moveToNext()) {
      long id = cursor.getLong(0);
      long time = cursor.getLong(1);
      String title = cursor.getString(2);
      ret.append(id + ": " + time + ": " + title + "\n");
    }
    output.setText(ret);
  }
}

2. Database helper

Handles all the database connections, creation of the necessary table and has additions like database upgrade information thrown in to code in case of future database upgrades.


EventDataSQLHelper.java

Code:


package org.example.sqldemo;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;
import android.util.Log;

/** Helper to the database, manages versions and creation */
public class EventDataSQLHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "events.db";
    private static final int DATABASE_VERSION = 1;

    // Table name
    public static final String TABLE = "events";

    // Columns
    public static final String TIME = "time";
    public static final String TITLE = "title";

    public EventDataSQLHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String sql = "create table " + TABLE + "( " + BaseColumns._ID
                + " integer primary key autoincrement, " + TIME + " integer, "
                + TITLE + " text not null);";
        Log.d("EventsData", "onCreate: " + sql);
        db.execSQL(sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion >= newVersion)
            return;

        String sql = null;
        if (oldVersion == 1)
            sql = "alter table " + TABLE + " add note text;";
        if (oldVersion == 2)
            sql = "";

        Log.d("EventsData", "onUpgrade    : " + sql);
        if (sql != null)
            db.execSQL(sql);
    }

}

Output


Thanks,

Alvaro