Android SQLite Example

0

Android system uses SQLite database to persist information. This example introduces you to Android provide SQLite Helper classes like SQLiteOpenHelper and SQLiteDatabase. SQLiteOpenHelper is the helper class that provides a connection to the database.

In this example, we will create a spinner widget and then set its adapter based on the values from a database table.

We will build our example on our previous spinner article.

Main Screen

Our main screen contains just the spinner widget. It contains a list of languages.

welcome.xml:

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    android:paddingBottom="@dimen/activity_vertical_margin"
    android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:paddingTop="@dimen/activity_vertical_margin"
    tools:context="com.javarticles.android.DatePickerExample" >

    <TextView
        android:id="@+id/welcome"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginLeft="40dp"
        android:layout_marginTop="18dp"
        android:text="@string/welcome"
        android:textColor="@color/welcome_text_color"
        android:textSize="20sp" />

    <Spinner
        android:id="@+id/spinner_languages"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" />   

</LinearLayout>

We will insert the below languages into a database table as we create the database first time.

strings.xml:

<?xml version="1.0" encoding="utf-8"?>
<resources>

    <string name="app_name">JavArticles</string>
    <string name="welcome">SQLite Example</string>
    <string name="lang_c">C</string>
    <string name="lang_c_plus">C++</string>
    <string name="lang_java">Java</string>
    <string name="lang_python">Python</string>
    <string name="lang_scala">Scala</string>
    <string name="lang_perl">Perl</string>
    <string name="lang_ruby">Ruby</string>

</resources>

SQLite Database APIs

Our SQLIte helper class extends android provided SQLiteOpenHelper.
To access a database using the SQLiteOpenHelper, call getWritableDatabase() or getReadableDatabase() to open and obtain a writable or read-only instance of the underlying database, respectively.
We create the schema when our application starts in the onCreate() method. We also read the languages array and insert rows.

We don’t have to implement onUpgrade() as of now and is needed only when there is a change in schema. We started with version 1.0, we might add new fields or change existing ones in
a later version of our application, in which case we will increment the schema change and alter schema in onUpgrade().

Method getLanguagesFromDb() retrieves the languages and returns as a list.

SQLiteHelper:

package com.javarticles.android;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.content.res.Resources;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.javarticles.android.database.DatabaseConstants;

public class SQLiteHelper extends SQLiteOpenHelper implements DatabaseConstants {
	private Resources resources;
	private SQLiteDatabase database;

	public static final int DB_VERSION = 2;

	public SQLiteHelper(Context context) {
		super(context, DB_NAME, null, DB_VERSION);
		resources = context.getResources();
		openDatabase();
	}

	public void openDatabase() {
		try {
			this.database = getWritableDatabase();
		} catch (final SQLException se) {
			se.printStackTrace();
		}
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE " + TABLE_LANG + "(" + COL_LANG_ID
				+ " INTEGER PRIMARY KEY NOT NULL, " + " " + COL_LANG_NAME
				+ " VARCHAR(50) NOT NULL);");
		insertLanguage(db, resources.getString(R.string.lang_java));
		insertLanguage(db, resources.getString(R.string.lang_perl));
		insertLanguage(db, resources.getString(R.string.lang_python));
		insertLanguage(db, resources.getString(R.string.lang_ruby));
		insertLanguage(db, resources.getString(R.string.lang_scala));
		insertLanguage(db, resources.getString(R.string.lang_c));
		insertLanguage(db, resources.getString(R.string.lang_c_plus));
	}

	private static void insertLanguage(SQLiteDatabase db, String language) {
		db.execSQL("INSERT INTO " + TABLE_LANG + " (" + COL_LANG_NAME
				+ ") VALUES ('" + language + "');");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	}

	public List getLanguagesFromDb() {
		final String sqlQuery = "SELECT " + COL_LANG_ID + ", " + COL_LANG_NAME
				+ " FROM " + TABLE_LANG + " ORDER BY "
				+ COL_LANG_NAME;

		final Cursor cursor = this.database.rawQuery(sqlQuery, null);

		final List langList = new ArrayList();

		while (cursor.moveToNext()) {
			langList.add(cursor.getString(cursor.getColumnIndex(COL_LANG_NAME)));
		}
		cursor.close();
		return langList;
	}
}

DatabaseConstants:

package com.javarticles.android.database;

public interface DatabaseConstants {
	public static String DB_NAME = "sqlite_example.db";
	public static final String TABLE_LANG = "languages";
	public static final String COL_LANG_ID = "lang_id";
	public static final String COL_LANG_NAME = "lang_name";
}

In our main activity, we create SQLite helper object and retrieve the languages from the database. Next we create ArrayAdapter based on the retrieved languages list and set the adapter to the spinner widget.

MainActivity:

package com.javarticles.android;

import android.app.Activity;
import android.os.Bundle;
import android.widget.ArrayAdapter;
import android.widget.Spinner;

public class MainActivity extends Activity {
	private Spinner spinnerLanguages;
	private SQLiteHelper sqLiteHelper;

	public void onCreate(final Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		sqLiteHelper= new SQLiteHelper(this);
		setContentView(R.layout.welcome);
		spinnerLanguages = (Spinner) findViewById(R.id.spinner_languages);

		spinnerLanguages.setAdapter(new ArrayAdapter(this, android.R.layout.simple_spinner_item, sqLiteHelper.getLanguagesFromDb()));
		((ArrayAdapter<?>)spinnerLanguages.getAdapter()).setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);		
	}	
}

Run the application

Spinner populated using SQLite database

Spinner populated using SQLite database

Verify Database

We know now that the data was created but in case you want to verify your database, table structure, or new few queries. You can do it using sqlite3.

  1. Run your app.
  2. Open command line.
  3. Cd to android SDK home and then \platform-tools. Mine is at C:\android\sdk\platform-tools.
  4. Run command ‘adb shell’
  5. Cd to data/data/com.javarticles.android/databases
  6. Run command sqlite3 sqlite_example.db
  7. Run command .schema
  8. Now you will be able to see your ‘CREATE TABLE’ commands here

Command Output:

C:\android\sdk\platform-tools>adb shell
# cd data/data/com.javarticles.android/databases
cd data/data/com.javarticles.android/databases
# ls
ls
sqlite_example.db
sqlite_example.db-journal
# sqlite3 sqlite_example.db
sqlite3 sqlite_example.db
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .schema
.schema
CREATE TABLE android_metadata (locale TEXT);
CREATE TABLE languages(lang_id INTEGER PRIMARY KEY NOT NULL,  lang_name VARCHAR(
50) NOT NULL);
sqlite>

Download the source code

This was an example about SQLite database and using it to populate the spinner widget. You can download the source code here: sqliteHelperExample.zip

Share.

Comments are closed.