Android SQLite Database

1. Android Intents

1.1. SQLite in Android

SQLite is an Open Source Database which is embedded into Android. SQLight supports standard relational database features like SQLsyntax, transactions and prepared statements. In addition it requires only little memory at runtime (approx. 250 KByte).

Using SQLite 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 in Android can be slow due to the necessary I/O. Therefore is it recommended to perform this task in anAsyncTask . Please see Android Background Processing Tutorial for details.

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 on of these fields before saving them in the database. SQLight 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.

If your application creates an database this database is saved in the directory "DATA/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 a Content Provider.

1.2. SQLightOpenHelper

To create and upgrade a database in your Android application you usually subclass "SQLightOpenHelper". 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 "SQLightDatabase" object.

SQLightOpenHelper provides the methods getReadableDatabase() and getWriteableDatabase() to get access to an "SQLightDatabase" object which allows database access either in read or write mode.

For the primary key of the database you should always use the identifier "_id" as some of Android functions rely on this standard.

1.3. SQLightDatabase and Cursor

"SQLightDatabase" provides the methods insert(), update() and delete() and execSQL() method which 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 SQLightQueryBuilder. SQLightBuilder is similar to the interface of an content provider therefore it is typically used in ContentProviders. A query returns always a "Cursor".

The method query has the parameters String dbName, int[] columnNames, String whereClause, String[] valuesForWhereClause, String[] groupBy, String[] having, String[] orderBy. 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.

A Cursor represents the result of a query. To get the number of elements use the method getCount(). To move between individual data rows, you can use the methods moveToFirst() and moveToNext(). Via the method isAfterLast() you can check if there is still some data.

A Cursor can be directly used via the "SimpleCursorAdapter" in ListViews. Please see the ListView Tutorial for details.

2. Todo application

2.1. Overview

We will create a Todo application which allow the user to maintain items which should be done. 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 .

This tutorial assumes that you have already basic knowledge in Android development . Please check the Android development tutorial to learn the basics. You may also want to look at Android Intents and Android ListView .

The resulting application will look similar to the following.

2.2. Project

Create the project "de.vogella.android.todos" with the activity "TodosOverview". Create another activity "TodoDetails". Create the package "de.vogella.android.todos.database". This package will store the classes we will create for the database handling.

2.3. Database handling

Create the following class "TodoDatabaseHelper".

				
package de.vogella.android.todos.database;

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

public class TodoDatabaseHelper extends SQLiteOpenHelper {
	private static final String DATABASE_NAME = "applicationdata";

	private static final int DATABASE_VERSION = 1;

	// 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 TodoDatabaseHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	// Method is called during creation of the database
	@Override
	public void onCreate(SQLiteDatabase database) {
		database.execSQL(DATABASE_CREATE);
	}

	// 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) {
		Log.w(TodoDatabaseHelper.class.getName(),
				"Upgrading database from version " + oldVersion + " to "
						+ newVersion + ", which will destroy all old data");
		database.execSQL("DROP TABLE IF EXISTS todo");
		onCreate(database);
	}
}

			

Based on this helper class we can write the class "TodoDbAdapter" which will provide the functionality to query, create and update todos. The method open() will open the database via the helper class. For updating and creating values we use the "android.content.ContentValues" class. This class allows to store key/values. You use the column names as the key in ContentValues and pass the object to the insert or update method of your database.

				
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 DATABASE_TABLE = "todo";
	private Context context;
	private SQLiteDatabase database;
	private TodoDatabaseHelper dbHelper;

	public TodoDbAdapter(Context context) {
		this.context = context;
	}

	public TodoDbAdapter open() throws SQLException {
		dbHelper = new TodoDatabaseHelper(context);
		database = 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 initialValues = createContentValues(category, summary,
				description);

		return database.insert(DATABASE_TABLE, null, initialValues);
	}

	/**
	 * Update the todo
	 */
	public boolean updateTodo(long rowId, String category, String summary,
			String description) {
		ContentValues updateValues = createContentValues(category, summary,
				description);

		return database.update(DATABASE_TABLE, updateValues, KEY_ROWID + "="
				+ rowId, null) > 0;
	}

	/**
	 * Deletes todo
	 */
	public boolean deleteTodo(long rowId) {
		return database.delete(DATABASE_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 database.query(DATABASE_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 = database.query(true, DATABASE_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;
	}
}

			

2.4. Resources

Create create a few resources which we will later use.

Also define a menu "listmenu.xml" which will later use create a new todo.

				
<?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>

			

For the priorities we create an string array. Create "priority.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>

			

We create another resource for the other strings in 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>

			

2.5. Activities

We defined three layouts, one for the list, one for the rows of the list and one for the maintenance of an individual todo.

Please note that the row layout refers to an icon. Please replace this with a icon of your choice. To find icons have a lock at: Free icons for your application .

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:orientation="vertical" android:layout_width="fill_parent"
	android:layout_height="fill_parent" android:background="@color/listcolor">
	<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 "reminder" into your "res/layout" folder which will be used in the next layout. Alternatively remove the icon from the row layout. Afterwards create the layout "todo_row.xml" 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_height="wrap_content" android:layout_width="fill_parent">
	<ImageView android:id="@+id/icon" android:src="@drawable/reminder"
		android:layout_marginLeft="4px" android:layout_marginRight="8px"
		android:layout_height="40px" android:layout_marginTop="8px"
		android:layout_width="30px">
	</ImageView>
	<TextView android:text="@+id/TextView01" android:layout_height="wrap_content" android:id="@+id/label"
		android:textSize="40px" android:layout_marginTop="6px" android:layout_width="fill_parent" android:textColor="@color/black"></TextView>
</LinearLayout>

			

Create the layout "todo_edit". This layout will be used later to display and edit an individual todo.

				
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
	android:orientation="vertical" android:layout_width="fill_parent"
	android:layout_height="fill_parent" android:background="@color/listcolor">


	<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_height="wrap_content" android:layout_width="fill_parent">
		<EditText android:layout_height="wrap_content" android:id="@+id/todo_edit_summary"
			android:layout_weight="1" android:layout_width="wrap_content"
			android:hint="Summary"></EditText>

	</LinearLayout>


	<EditText android:layout_width="fill_parent"
		android:layout_height="fill_parent" android:layout_weight="1"
		android:id="@+id/todo_edit_description" android:hint="Description" android:gravity="top"></EditText>
	<Button android:layout_width="wrap_content"
		android:layout_height="wrap_content" android:id="@+id/todo_edit_button"
		android:text="@string/todo_edit_confirm"></Button>


</LinearLayout>

			

Finally change the coding of your activities to the following. First "TodoOverview.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 TodosOverview 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, TodoDetails.class);
		startActivityForResult(i, ACTIVITY_CREATE);
	}

	// ListView and view (row) on which was clicked, position and
	@Override
	protected void onListItemClick(ListView l, View v, int position, long id) {
		super.onListItemClick(l, v, position, id);
		Intent i = new Intent(this, TodoDetails.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 use to get some data from the caller
	@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 "TodoDetails.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 TodoDetails 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:name=".TodosOverview" android:label="@string/app_name">
			<intent-filter>
				<action android:name="android.intent.action.MAIN" />
				<category android:name="android.intent.category.LAUNCHER" />
			</intent-filter>
		</activity>

		<activity android:name=".TodoDetails"
			android:windowSoftInputMode="stateVisible|adjustResize">
			>
    
    </activity>
	</application>
	<uses-sdk android:minSdkVersion="9" />

</manifest> 
			

2.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.

3. Thank you

转载自： http://www.vogella.de/articles/AndroidSQLite/article.html