Android SQLite Database
Android SQLite Database - Tutorial
Lars Vogel
Version 1.9
Copyright © 2010, 2011 Lars Vogel
22.11.2011
Revision History
Revision 0.1
22.12.2010
Lars Vogel
Created
Revision 0.2 - 1.9
31.12.2010 - 22.11.2011
Lars Vogel
bug fixes and enhancements
Using the Android SQLite Database
This tutorial describes how to use the SQLite database in Android applications. The tutorial is based on Eclipse 3.7, Java 1.6 and Android 2.2.
Table of Contents
1. SQLite and Android
1.1. What is SQLite
1.2. SQLite in Android
1.3. Content Provider and sharing data
2. Android Architecture
2.1. Packages
2.2. SQLiteOpenHelper
2.3. SQLiteDatabase
2.4. Cursor
2.5. ListViews, ListActivities and SimpleCursorAdapter
3. Command line interface for SQLite
4. Prerequisites for this tutorial
5. Creating the Todo application
5.1. Overview
5.2. Project
5.3. Database handling
5.4. Resources
5.5. Activities and Layouts
5.6. Start your application
6. Thank you
7. Questions and Discussion
8. Links and Literature
8.1. Source Code
8.2. Android SQLite Resources
8.3. Android Resources
8.4. vogella Resources
1. SQLite and Android
1.1. What is SQLite
SQLite is an Open Source Database which is embedded into Android. SQLite supports standard relational database features like SQL syntax, transactions and prepared statements. In addition it requires only little memory at runtime (approx. 250 KByte).
SQLite supports the data types TEXT (similar to String in Java), INTEGER (similar to long in Java) and REAL (similar to double in Java). All other types must be converted into one of these fields before saving them in the database. SQLite itself does not validate if the types written to the columns are actually of the defined type, you can write an integer into a string column.
More information about SQLite is available on http://www.sqlite.org .
1.2. SQLite in Android
SQLite is available on every Android device. Using an SQLite database in Android does not require any database setup or administration. You specify the SQL for working with the database and the database is automatically managed for you.
Working with databases can be slow. Therefore is it recommended to perform these task in the background, for example via anAsyncTask.
If your application creates an database this database is saved in the directoryDATA/data/APP_NAME/databases/FILENAME. DATA is the path which Environment.getDataDirectory()returns, APP_NAME is your application name and FILENAME is the name you give the database during creation.Environment.getDataDirectory() usually return the SD card as location.
1.3. Content Provider and sharing data
A SQLite database is private to the application which creates it. If you want to share data with other applications you can use aContentProvider. If data is not shared it typically easier to work directly with the database. ContentProviders are not part of this tutorial.
2. Android Architecture
2.1. Packages
The package android.database contains all general classes for working with databases. android.database.sqlitecontains the SQLite specific classes.
2.2. SQLiteOpenHelper
To create and upgrade a database in your Android application you usually subclass SQLiteOpenHelper. In this class you need to override the methods onCreate() to create the database and onUpgrade() to upgrade the database in case of changes in the database schema. Both methods receive an SQLiteDatabase object which represents the database.
SQLiteOpenHelper provides the methods getReadableDatabase() and getWriteableDatabase() to get access to anSQLiteDatabase object which allows database access either in read or write mode.
For the primary key of the database tables you should always use the identifier _id as some of Android functions rely on this standard.
A best practice is to create per table a separate class which define static onCreate() and onUpdate() methods. These methods are then called in the corresponding methods of SQLiteOpenHelper . This way your implementation ofSQLiteOpenHelper will not get to large even if you have several tables.
2.3. SQLiteDatabase
SQLiteDatabase is the base class for working with an SQLite database in Android and provides methods to open, query, update and close the database. More specifically SQLiteDatabase provides the insert(), update() and delete() methods. The execSQL() method allows to execute directly SQL. The object ContentValues allow to define key/values for insert and update. The key is the column and the value is the value for this column.
Queries can be created via the method rawQuery() which accepts SQL or query() which provides an interface for specifying dynamic data or SQLiteQueryBuilder.
For example to run a rawQuery() you can do the following:
Cursor cursor = getReadableDatabase().rawQuery("select * from todo where _id = ?", new String[] { id });
The method query() has the following parameters.
[*] String dbName- The table name to compile the query against
[*] int[] columnNames - A list of which columns to return. Passing null will return all columns.
[*] String whereClause - Filter for the selection of data without the "WHERE" clause, null will select all
[*] selectionArgs You may include ?s in the whereClause, which will be replaced by the values from selectionArgs.
[*] String[] groupBy - A filter declaring how to group rows, null will cause the rows to not be grouped.
[*] String[] having - Filter for the goups, null means no filter
[*] String[] orderBy - row which will be used to order the data, null means no ordering
If all data should be selected you can pass null as the where clause. The where clause is specified without where, for example _id=19 and summary=? . If several values are required via ? you pass them in the valuesForWhereClause array to the query. In general if something is not required you can pass null, e.g. for the group by clause.
For example to run a query() you can do the following:
return database.query(DATABASE_TABLE,
new String[] { KEY_ROWID, KEY_CATEGORY, KEY_SUMMARY, KEY_DESCRIPTION },
null, null, null, null, null);
2.4. Cursor
A query returns always a Cursor. A Cursor represents the result of a query and basically points always to one row of the database. This way Android can buffer the results efficiently as it does not have to load all data into memory.
To get the number of elements use the method getCount(). To move between individual data rows, you can use the methodsmoveToFirst() and moveToNext(). Via the method isAfterLast() you can check if there is still some data.
To access data Cursor provides typed get methods, e.g. getLong(columnIndex), getString(columnIndex) whereby the columnIndex is the number of the column you are accessing.
A Cursor can be directly used via the SimpleCursorAdapter in ListViews.
2.5. ListViews, ListActivities and SimpleCursorAdapter
ListViews are views (widgets) which makes it easy to display a list of elements. ListActivities are specialized Activitieswhich make the usage of ListViews easier. This tutorial will use ListActivities but not look into the details of them. Please see http://www.vogella.de/articles/AndroidListView/article.html for an introduction into ListViews and ListActivities.
To work with databases and ListViews you can use the SimpleCursorAdapter. The SimpleCursorAdapter allows to set a layout for each row of the ListViews. You also define an array column names and an array of view Ids. The adapter will map the columns to the views based on the Cursor passed to it.
3. Command line interface for SQLite
It is possible to access an SQLite database on the emulator or a rooted device via the command line. For this use adb shellto connect to the device and the command "sqlite3" to connect to an database.
4. Prerequisites for this tutorial
The following assumes that you have already basic knowledge in Android development. Please check the Android development tutorial to learn the basics.
5. Creating the Todo application
5.1. Overview
We will create a Todo application which allow the user to maintain tasks for himself. These items will be stored in the SQLitedatabase.
The application will consists out of two Activities, one for seeing a list of all todo items and one for creating / maintaining a specific todo. Both Activities will be communicating via Intents.
The resulting application will look similar to the following.
5.2. Project
Create the project de.vogella.android.todos with the Activity called TodosOverviewActivity. . Create also anotherActivity called TodoDetailsActivity.
Create the package de.vogella.android.todos.database. This package will store the classes for the database handling.
5.3. Database handling
We will create a separate class for creating and updating the todo table.
package de.vogella.android.todos.database;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
public class TodoTable {
// Database creation SQL statement
private static final String DATABASE_CREATE = "create table todo "
+ "(_id integer primary key autoincrement, "
+ "category text not null, " + "summary text not null,"
+ " description text not null);";
public static void onCreate(SQLiteDatabase database) {
database.execSQL(DATABASE_CREATE);
}
public static void onUpgrade(SQLiteDatabase database, int oldVersion,
int newVersion) {
Log.w(TodoTable.class.getName(), "Upgrading database from version "
+ oldVersion + " to " + newVersion
+ ", which will destroy all old data");
database.execSQL("DROP TABLE IF EXISTS todo");
onCreate(database);
}
}
Create the following TodoDatabaseHelper class. This class extends SQLiteOpenHelper and call the static methods of the TodoTable helper class.
package de.vogella.android.todos.database;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class TodoDatabaseHelper extends SQLiteOpenHelper {
private static final String DATABASE_NAME = "applicationdata";
private static final int DATABASE_VERSION = 1;
public TodoDatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
// Method is called during creation of the database
@Override
public void onCreate(SQLiteDatabase database) {
TodoTable.onCreate(database);
}
// Method is called during an upgrade of the database,
// e.g. if you increase the database version
@Override
public void onUpgrade(SQLiteDatabase database, int oldVersion,
int newVersion) {
TodoTable.onUpgrade(database, oldVersion, newVersion);
}
}
Based on TodoDatabaseHelper class we can write the TodoDbAdapter class which will provide the functionality to query, create and update todos.
The method open() will open the database via the TodoDatabaseHelper lass. For updating and creating values we use the android.content.ContentValues class as described earlier.
package de.vogella.android.todos.database;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
public class TodoDbAdapter {
// Database fields
public static final String KEY_ROWID = "_id";
public static final String KEY_CATEGORY = "category";
public static final String KEY_SUMMARY = "summary";
public static final String KEY_DESCRIPTION = "description";
private static final String DB_TABLE = "todo";
private Context context;
private SQLiteDatabase db;
private TodoDatabaseHelper dbHelper;
public TodoDbAdapter(Context context) {
this.context = context;
}
public TodoDbAdapter open() throws SQLException {
dbHelper = new TodoDatabaseHelper(context);
db = dbHelper.getWritableDatabase();
return this;
}
public void close() {
dbHelper.close();
}
/**
* Create a new todo If the todo is successfully created return the new
* rowId for that note, otherwise return a -1 to indicate failure.
*/
public long createTodo(String category, String summary, String description) {
ContentValues values = createContentValues(category, summary,
description);
return db.insert(DB_TABLE, null, values);
}
/**
* Update the todo
*/
public boolean updateTodo(long rowId, String category, String summary,
String description) {
ContentValues values = createContentValues(category, summary,
description);
return db.update(DB_TABLE, values, KEY_ROWID + "=" + rowId, null) > 0;
}
/**
* Deletes todo
*/
public boolean deleteTodo(long rowId) {
return db.delete(DB_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
}
/**
* Return a Cursor over the list of all todo in the database
*
* @return Cursor over all notes
*/
public Cursor fetchAllTodos() {
return db.query(DB_TABLE, new String[] { KEY_ROWID, KEY_CATEGORY,
KEY_SUMMARY, KEY_DESCRIPTION }, null, null, null, null, null);
}
/**
* Return a Cursor positioned at the defined todo
*/
public Cursor fetchTodo(long rowId) throws SQLException {
Cursor mCursor = db.query(true, DB_TABLE, new String[] { KEY_ROWID,
KEY_CATEGORY, KEY_SUMMARY, KEY_DESCRIPTION }, KEY_ROWID + "="
+ rowId, null, null, null, null, null);
if (mCursor != null) {
mCursor.moveToFirst();
}
return mCursor;
}
private ContentValues createContentValues(String category, String summary,
String description) {
ContentValues values = new ContentValues();
values.put(KEY_CATEGORY, category);
values.put(KEY_SUMMARY, summary);
values.put(KEY_DESCRIPTION, description);
return values;
}
}
5.4. Resources
In the following we will create several resources which we will later use in our application.
First define a menu listmenu.xml in the folder res/menu. This XML file will be used to define the option menu in our application.
<?xml version="1.0" encoding="utf-8"?>
<menu
xmlns:android="http://schemas.android.com/apk/res/android">
<item android:id="@+id/insert" android:title="Insert"></item>
</menu>
The user will be able to select for priority for the tasks he maintains. For the priorities we create an string array. Createpriority.xml under res/values.
<?xml version="1.0" encoding="utf-8"?>
<resources>
<string-array name="priorities"><item>Urgent</item>
<item>Reminder</item>
</string-array>
</resources>
Define also additional strings for our application. Edit strings.xml under res/values.
<?xml version="1.0" encoding="utf-8"?>
<resources>
<string name="hello">Hello World, Todo!</string>
<string name="app_name">Todo</string>
<string name="no_todos">Currently there are no Todo items maintained</string>
<string name="menu_insert">Add Item</string>
<string name="menu_delete">Delete Todo</string>
<string name="todo_summary">Summary</string>
<string name="todo_description">Delete Todo</string>
<string name="todo_edit_summary">Summary</string>
<string name="todo_edit_description">Description</string>
<string name="todo_edit_confirm">Confirm</string>
<color name="listcolor">#FFE87C</color>
<color name="black">#000000</color>
</resources>
5.5. Activities and Layouts
We defined three layouts, one for the list, one for the rows of the list and one for the maintenance of an individual task.
Please note that the row layout refers to an icon. Please replace this with an icon of your choice.
Create the layout todo_list.xml. This layout will define how the list looks like.
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
android:background="@color/listcolor"
android:orientation="vertical" >
<ListView
android:id="@android:id/list"
android:layout_width="wrap_content"
android:layout_height="wrap_content" >
</ListView>
<TextView
android:id="@android:id/empty"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="@string/no_todos" />
</LinearLayout>
Paste an icon called reminder into your res/drawable folders ( drawable-hdpi, drawable-mdpi, drawable-ldpi ) This icon will be used in the row layout. If you don't want to use an icon you can alternatively you could remove the icon definition from the following layout definition.
Create the layout todo_row.xml in the folder res/layout which will be used for the layout of an individual row.
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="fill_parent"
android:layout_height="wrap_content" >
<ImageView
android:id="@+id/icon"
android:layout_width="30px"
android:layout_height="40px"
android:layout_marginLeft="4px"
android:layout_marginRight="8px"
android:layout_marginTop="8px"
android:src="@drawable/reminder" >
</ImageView>
<TextView
android:id="@+id/label"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:layout_marginTop="6px"
android:lines="1"
android:text="@+id/TextView01"
android:textColor="@color/black"
android:textSize="40px" >
</TextView>
</LinearLayout>
Create the layout todo_edit. This layout will be used to display and edit an individual task in the second Activity.
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
android:background="@color/listcolor"
android:orientation="vertical" >
<Spinner
android:id="@+id/category"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:entries="@array/priorities" >
</Spinner>
<LinearLayout
android:id="@+id/LinearLayout01"
android:layout_width="fill_parent"
android:layout_height="wrap_content" >
<EditText
android:id="@+id/todo_edit_summary"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_weight="1"
android:hint="Summary"
android:imeOptions="actionNext" >
</EditText>
</LinearLayout>
<EditText
android:id="@+id/todo_edit_description"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
android:layout_weight="1"
android:gravity="top"
android:hint="Description"
android:imeOptions="actionNext" >
</EditText>
<Button
android:id="@+id/todo_edit_button"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="@string/todo_edit_confirm" >
</Button>
</LinearLayout>
Finally change the coding of your activities to the following. First TodosOverviewActivity.java.
package de.vogella.android.todos;
import android.app.ListActivity;
import android.content.Intent;
import android.database.Cursor;
import android.os.Bundle;
import android.view.ContextMenu;
import android.view.ContextMenu.ContextMenuInfo;
import android.view.Menu;
import android.view.MenuInflater;
import android.view.MenuItem;
import android.view.View;
import android.widget.AdapterView.AdapterContextMenuInfo;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;
import de.vogella.android.todos.database.TodoDbAdapter;
public class TodosOverviewActivity extends ListActivity {
private TodoDbAdapter dbHelper;
private static final int ACTIVITY_CREATE = 0;
private static final int ACTIVITY_EDIT = 1;
private static final int DELETE_ID = Menu.FIRST + 1;
private Cursor cursor;
/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.todo_list);
this.getListView().setDividerHeight(2);
dbHelper = new TodoDbAdapter(this);
dbHelper.open();
fillData();
registerForContextMenu(getListView());
}
// Create the menu based on the XML defintion
@Override
public boolean onCreateOptionsMenu(Menu menu) {
MenuInflater inflater = getMenuInflater();
inflater.inflate(R.menu.listmenu, menu);
return true;
}
// Reaction to the menu selection
@Override
public boolean onMenuItemSelected(int featureId, MenuItem item) {
switch (item.getItemId()) {
case R.id.insert:
createTodo();
return true;
}
return super.onMenuItemSelected(featureId, item);
}
@Override
public boolean onOptionsItemSelected(MenuItem item) {
switch (item.getItemId()) {
case R.id.insert:
createTodo();
return true;
}
return super.onOptionsItemSelected(item);
}
@Override
public boolean onContextItemSelected(MenuItem item) {
switch (item.getItemId()) {
case DELETE_ID:
AdapterContextMenuInfo info = (AdapterContextMenuInfo) item
.getMenuInfo();
dbHelper.deleteTodo(info.id);
fillData();
return true;
}
return super.onContextItemSelected(item);
}
private void createTodo() {
Intent i = new Intent(this, TodoDetailActivity.class);
startActivityForResult(i, ACTIVITY_CREATE);
}
// Opens the second activity if an entry is clicked
@Override
protected void onListItemClick(ListView l, View v, int position, long id) {
super.onListItemClick(l, v, position, id);
Intent i = new Intent(this, TodoDetailActivity.class);
i.putExtra(TodoDbAdapter.KEY_ROWID, id);
// Activity returns an result if called with startActivityForResult
startActivityForResult(i, ACTIVITY_EDIT);
}
// Called with the result of the other activity
// requestCode was the origin request code send to the activity
// resultCode is the return code, 0 is everything is ok
// intend can be used to get data
@Override
protected void onActivityResult(int requestCode, int resultCode,
Intent intent) {
super.onActivityResult(requestCode, resultCode, intent);
fillData();
}
private void fillData() {
cursor = dbHelper.fetchAllTodos();
startManagingCursor(cursor);
String[] from = new String[] { TodoDbAdapter.KEY_SUMMARY };
int[] to = new int[] { R.id.label };
// Now create an array adapter and set it to display using our row
SimpleCursorAdapter notes = new SimpleCursorAdapter(this,
R.layout.todo_row, cursor, from, to);
setListAdapter(notes);
}
@Override
public void onCreateContextMenu(ContextMenu menu, View v,
ContextMenuInfo menuInfo) {
super.onCreateContextMenu(menu, v, menuInfo);
menu.add(0, DELETE_ID, 0, R.string.menu_delete);
}
@Override
protected void onDestroy() {
super.onDestroy();
if (dbHelper != null) {
dbHelper.close();
}
}
}
And then TodoDetailsActivity.java
package de.vogella.android.todos;
import android.app.Activity;
import android.database.Cursor;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Spinner;
import de.vogella.android.todos.database.TodoDbAdapter;
public class TodoDetailActivity extends Activity {
private EditText mTitleText;
private EditText mBodyText;
private Long mRowId;
private TodoDbAdapter mDbHelper;
private Spinner mCategory;
@Override
protected void onCreate(Bundle bundle) {
super.onCreate(bundle);
mDbHelper = new TodoDbAdapter(this);
mDbHelper.open();
setContentView(R.layout.todo_edit);
mCategory = (Spinner) findViewById(R.id.category);
mTitleText = (EditText) findViewById(R.id.todo_edit_summary);
mBodyText = (EditText) findViewById(R.id.todo_edit_description);
Button confirmButton = (Button) findViewById(R.id.todo_edit_button);
mRowId = null;
Bundle extras = getIntent().getExtras();
mRowId = (bundle == null) ? null : (Long) bundle
.getSerializable(TodoDbAdapter.KEY_ROWID);
if (extras != null) {
mRowId = extras.getLong(TodoDbAdapter.KEY_ROWID);
}
populateFields();
confirmButton.setOnClickListener(new View.OnClickListener() {
public void onClick(View view) {
setResult(RESULT_OK);
finish();
}
});
}
private void populateFields() {
if (mRowId != null) {
Cursor todo = mDbHelper.fetchTodo(mRowId);
startManagingCursor(todo);
String category = todo.getString(todo
.getColumnIndexOrThrow(TodoDbAdapter.KEY_CATEGORY));
for (int i = 0; i < mCategory.getCount(); i++) {
String s = (String) mCategory.getItemAtPosition(i);
Log.e(null, s + " " + category);
if (s.equalsIgnoreCase(category)) {
mCategory.setSelection(i);
}
}
mTitleText.setText(todo.getString(todo
.getColumnIndexOrThrow(TodoDbAdapter.KEY_SUMMARY)));
mBodyText.setText(todo.getString(todo
.getColumnIndexOrThrow(TodoDbAdapter.KEY_DESCRIPTION)));
}
}
protected void onSaveInstanceState(Bundle outState) {
super.onSaveInstanceState(outState);
saveState();
outState.putSerializable(TodoDbAdapter.KEY_ROWID, mRowId);
}
@Override
protected void onPause() {
super.onPause();
saveState();
}
@Override
protected void onResume() {
super.onResume();
populateFields();
}
private void saveState() {
String category = (String) mCategory.getSelectedItem();
String summary = mTitleText.getText().toString();
String description = mBodyText.getText().toString();
if (mRowId == null) {
long id = mDbHelper.createTodo(category, summary, description);
if (id > 0) {
mRowId = id;
}
} else {
mDbHelper.updateTodo(mRowId, category, summary, description);
}
}
}
The resulting AndroidManifest.xml looks like the following.
<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
package="de.vogella.android.todos"
android:versionCode="1"
android:versionName="1.0" >
<application
android:icon="@drawable/todo"
android:label="@string/app_name" >
<activity
android:label="@string/app_name"
android:name=".TodosOverviewActivity" >
<intent-filter >
<action android:name="android.intent.action.MAIN" />
<category android:name="android.intent.category.LAUNCHER" />
</intent-filter>
</activity>
<activity
android:name=".TodoDetailActivity"
android:windowSoftInputMode="stateVisible|adjustResize" >
</activity>
<provider
android:authorities="de.vogella.android.todos.provider"
android:name="MyTodoContentProvider" >
</provider>
</application>
<uses-sdk android:minSdkVersion="9" />
</manifest>
5.6. Start your application
Start your application. You should be able to maintain new Todos via the menu. An existing todo can be deleted on the list via a long press.
6. Thank you
Please help me to support this article:
7. Questions and Discussion
Before posting questions, please see the vogella FAQ. If you have questions or find an error in this article please use thewww.vogella.de Google Group. I have created a short list how to create good questions which might also help you.
8. Links and Literature
8.1. Source Code
Source Code of Examples
8.2. Android SQLite Resources
SQlite website
SQL Tutorial
8.3. Android Resources
Android Tutorial
ListView Tutorial
Intents
Android Background Processing Tutorial
Android Location API and Google Maps
Android and Networking
8.4. vogella Resources
Eclipse RCP Training (German) Eclipse RCP Training with Lars Vogel
Android Tutorial Introduction to Android Programming
GWT Tutorial Program in Java and compile to JavaScript and HTML
Eclipse RCP Tutorial Create native applications in Java
JUnit Tutorial Test your application
Git Tutorial Put everything you have under distributed version control system
页:
[1]