Android SQLite Database - Tutorial
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 |
Table of Contents
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 .
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.
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.
The package android.database
contains all general classes for working with databases. android.database.sqlite
contains the SQLite specific classes.
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.
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);
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
.
ListViews
are views (widgets) which makes it easy to display a list of elements. ListActivities
are specialized Activities
which 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.
It is possible to access an SQLite database on the emulator or a rooted device via the command line. For this use adb shell
to connect to the device and the command "sqlite3" to connect to an database.
The following assumes that you have already basic knowledge in Android development. Please check the Android development tutorial to learn the basics.
We will create a Todo application which allow the user to maintain tasks for himself. These items will be stored in the SQLite
database.
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.
![](http://www.vogella.de/articles/AndroidSQLite/images/xdatabase1.png.pagespeed.ic.PUiTdfhesN.png)
![](http://www.vogella.de/articles/AndroidSQLite/images/xdatabase2.png.pagespeed.ic.Ej5SW6Pb3g.png)
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.
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(); }
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>
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;
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>
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.
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