Another Android SQLite Example

To expose data from Cursor to a ListView widget:

package com.exercise.AndroidSQLite; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.database.sqlite.SQLiteDatabase.CursorFactory; public class SQLiteAdapter { public static final String MYDATABASE_NAME = "MY_DATABASE"; public static final String MYDATABASE_TABLE = "MY_TABLE"; public static final int MYDATABASE_VERSION = 1; public static final String KEY_ID = "_id"; public static final String KEY_CONTENT = "Content"; //create table MY_DATABASE (ID integer primary key, Content text not null); private static final String SCRIPT_CREATE_DATABASE = "create table " + MYDATABASE_TABLE + " (" + KEY_ID + " integer primary key autoincrement, " + KEY_CONTENT + " text not null);"; private SQLiteHelper sqLiteHelper; private SQLiteDatabase sqLiteDatabase; private Context context; public SQLiteAdapter(Context c){ context = c; } public SQLiteAdapter openToRead() throws android.database.SQLException { sqLiteHelper = new SQLiteHelper(context, MYDATABASE_NAME, null, MYDATABASE_VERSION); sqLiteDatabase = sqLiteHelper.getReadableDatabase(); return this; } public SQLiteAdapter openToWrite() throws android.database.SQLException { sqLiteHelper = new SQLiteHelper(context, MYDATABASE_NAME, null, MYDATABASE_VERSION); sqLiteDatabase = sqLiteHelper.getWritableDatabase(); return this; } public void close(){ sqLiteHelper.close(); } public long insert(String content){ ContentValues contentValues = new ContentValues(); contentValues.put(KEY_CONTENT, content); return sqLiteDatabase.insert(MYDATABASE_TABLE, null, contentValues); } public int deleteAll(){ return sqLiteDatabase.delete(MYDATABASE_TABLE, null, null); } public Cursor queueAll(){ String[] columns = new String[]{KEY_ID, KEY_CONTENT}; Cursor cursor = sqLiteDatabase.query(MYDATABASE_TABLE, columns, null, null, null, null, null); return cursor; } public class SQLiteHelper extends SQLiteOpenHelper { public SQLiteHelper(Context context, String name, CursorFactory factory, int version) { super(context, name, factory, version); } @Override public void onCreate(SQLiteDatabase db) { // TODO Auto-generated method stub db.execSQL(SCRIPT_CREATE_DATABASE); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // TODO Auto-generated method stub } } } 

Create /res/layout/row.xml for our ListView

<?xml version="1.0" encoding="utf-8"?> <TextView xmlns:android="" android:id="@+id/text" android:layout_width="fill_parent" android:layout_height="fill_parent" android:padding="10dip"/> 

Modify main.xml to change the result(contentlist) from TextView to ListView.

<?xml version="1.0" encoding="utf-8"?> <LinearLayout xmlns:android="" android:orientation="vertical" android:layout_width="fill_parent" android:layout_height="fill_parent" > <TextView android:layout_width="fill_parent" android:layout_height="wrap_content" android:text="@string/hello" /> <ListView android:id="@+id/contentlist" android:layout_width="fill_parent" android:layout_height="fill_parent"/> </LinearLayout> 

Finally, modify

package com.exercise.AndroidSQLite; import; import android.database.Cursor; import android.os.Bundle; import android.widget.ListView; import android.widget.SimpleCursorAdapter; public class AndroidSQLite extends Activity { private SQLiteAdapter mySQLiteAdapter; /** Called when the activity is first created. */ @Override public void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.main); ListView listContent = (ListView)findViewById(; /* * Create/Open a SQLite database * and fill with dummy content * and close it */ mySQLiteAdapter = new SQLiteAdapter(this); mySQLiteAdapter.openToWrite(); mySQLiteAdapter.deleteAll(); mySQLiteAdapter.insert("A for Apply"); mySQLiteAdapter.insert("B for Boy"); mySQLiteAdapter.insert("C for Cat"); mySQLiteAdapter.insert("D for Dog"); mySQLiteAdapter.insert("E for Egg"); mySQLiteAdapter.insert("F for Fish"); mySQLiteAdapter.insert("G for Girl"); mySQLiteAdapter.insert("H for Hand"); mySQLiteAdapter.insert("I for Ice-scream"); mySQLiteAdapter.insert("J for Jet"); mySQLiteAdapter.insert("K for Kite"); mySQLiteAdapter.insert("L for Lamp"); mySQLiteAdapter.insert("M for Man"); mySQLiteAdapter.insert("N for Nose"); mySQLiteAdapter.insert("O for Orange"); mySQLiteAdapter.insert("P for Pen"); mySQLiteAdapter.insert("Q for Queen"); mySQLiteAdapter.insert("R for Rain"); mySQLiteAdapter.insert("S for Sugar"); mySQLiteAdapter.insert("T for Tree"); mySQLiteAdapter.insert("U for Umbrella"); mySQLiteAdapter.insert("V for Van"); mySQLiteAdapter.insert("W for Water"); mySQLiteAdapter.insert("X for X'mas"); mySQLiteAdapter.insert("Y for Yellow"); mySQLiteAdapter.insert("Z for Zoo"); mySQLiteAdapter.close(); /* * Open the same SQLite database * and read all it's content. */ mySQLiteAdapter = new SQLiteAdapter(this); mySQLiteAdapter.openToRead(); Cursor cursor = mySQLiteAdapter.queueAll(); startManagingCursor(cursor); String[] from = new String[]{SQLiteAdapter.KEY_CONTENT}; int[] to = new int[]{}; SimpleCursorAdapter cursorAdapter = new SimpleCursorAdapter(this, R.layout.row, cursor, from, to); listContent.setAdapter(cursorAdapter); mySQLiteAdapter.close(); } } 

About henry416
I am a computer technology explorer and an university student based on Toronto. If you have any question, please feel free to discuss and comment here

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s