SQLite Database
For more complex data structures, a database provides a quicker and more flexible access method than flat files or shared preferences.Android provides a built-in database called SQLite that provides full relational database capability utilizing SQL commands. Each application that uses SQLite has its own instance of the database, which is by default accessible only from the application itself.The database is stored in the /data/data/<package_name>/databases folder of an Android device.A Content Provider can be used to share the database information between applications.The different steps for utilizing SQLite are
1. Create a database.
2. Open the database.
3. Create a table.
4. Create an insert interface for datasets.
5. Create a query interface for datasets.
6. Close the database.
The next recipe provides a general method to accomplish these steps.
Creating a Separate Database Package
A good modular structure to classes is essential for more complicated Android projects. Here, the database class is put in its own package com.cookbook.data so it is easy to reuse.This package contains three classes: MyDB, MyDBhelper, and Constants.
The MyDB class It contains a SQLiteDatabase instance and a MyDBhelper class with the methods that follow:
- MyDB( )—Initializes a MyDBhelper instance (the constructor).
- open( )—Initializes a SQLiteDatabase instance using the MyDBhelper.This opens a writeable database connection. If SQLite throws any exception, it tries to get a readable database instead.
- close( )—Closes the database connection.
- insertdiary( )—Saves a diary entry to the database as name-value pairs in a ContentValues instance, and then passes the data to the SQLitedatabase instance to do an insert.
- getdiaries( )—Reads the diary entries from the database, saves them in a Cursor class, and returns it from the method.
Android Database Example Code
DataBaseSQLActivity.java Code
package
android.example.database;
import java.util.List;
import
java.util.Random;
import
android.app.ListActivity;
import
android.os.Bundle;
import
android.view.View;
import
android.widget.ArrayAdapter;
public class
DataBaseSQLActivity extends ListActivity {
private
CommentsDataSource datasource;
@Override
public void onCreate(Bundle
savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
datasource = new
CommentsDataSource(this);
datasource.open();
List<Comment> values = datasource.getAllComments();
// Use the SimpleCursorAdapter to show
the
// elements in a ListView
ArrayAdapter<Comment> adapter
= new
ArrayAdapter<Comment>(this,
android.R.layout.simple_list_item_1, values);
setListAdapter(adapter);
}
// Will be called via the onClick
attribute
// of the buttons in main.xml
public void onClick(View
view) {
@SuppressWarnings("unchecked")
ArrayAdapter<Comment>
adapter = (ArrayAdapter<Comment>) getListAdapter();
Comment comment = null;
switch (view.getId())
{
case R.id.add:
String[] comments = new String[] { "Cool", "Very
nice", "Hate it" };
int nextInt = new Random().nextInt(3);
// Save the
new comment to the database
comment = datasource.createComment(comments[nextInt]);
adapter.add(comment);
break;
case R.id.delete:
if
(getListAdapter().getCount() > 0) {
comment =
(Comment) getListAdapter().getItem(0);
datasource.deleteComment(comment);
adapter.remove(comment);
}
break;
}
adapter.notifyDataSetChanged();
}
@Override
protected void onResume() {
datasource.open();
super.onResume();
}
@Override
protected void onPause() {
datasource.close();
super.onPause();
}
}
MySQLiteHelper.java Code
package
android.example.database;
import
android.content.Context;
import
android.database.sqlite.SQLiteDatabase;
import
android.database.sqlite.SQLiteOpenHelper;
import
android.util.Log;
public class MySQLiteHelper extends
SQLiteOpenHelper {
public static final String TABLE_COMMENTS = "comments";
public static final String COLUMN_ID = "_id";
public static final String COLUMN_COMMENT = "comment";
private static final String DATABASE_NAME = "commments.db";
private static final int DATABASE_VERSION = 1;
// Database creation sql
statement
private static final String DATABASE_CREATE = "create
table "
+ TABLE_COMMENTS + "( " + COLUMN_ID
+ " integer
primary key autoincrement, " + COLUMN_COMMENT
+ " text
not null);";
public
MySQLiteHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void
onCreate(SQLiteDatabase database) {
database.execSQL(DATABASE_CREATE);
}
@Override
public void
onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.w(MySQLiteHelper.class.getName(),
"Upgrading
database from version " + oldVersion + " to "
+ newVersion
+ ",
which will destroy all old data");
db.execSQL("DROP
TABLE IF EXISTS " + TABLE_COMMENTS);
onCreate(db);
}
}
CommentsDataSource.java Code
package
android.example.database;
import
java.util.ArrayList;
import java.util.List;
import
android.content.ContentValues;
import
android.content.Context;
import
android.database.Cursor;
import
android.database.SQLException;
import
android.database.sqlite.SQLiteDatabase;
public class
CommentsDataSource {
// Database fields
private SQLiteDatabase database;
private MySQLiteHelper dbHelper;
private String[] allColumns = {
MySQLiteHelper.COLUMN_ID,
MySQLiteHelper.COLUMN_COMMENT };
public
CommentsDataSource(Context context) {
dbHelper = new
MySQLiteHelper(context);
}
public void open() throws SQLException {
database = dbHelper.getWritableDatabase();
}
public void close() {
dbHelper.close();
}
public Comment
createComment(String comment) {
ContentValues values = new
ContentValues();
values.put(MySQLiteHelper.COLUMN_COMMENT, comment);
//values.put(MySQLiteHelper.COLUMN_FILE,
comment);
long insertId = database.insert(MySQLiteHelper.TABLE_COMMENTS, null,
values);
Cursor cursor = database.query(MySQLiteHelper.TABLE_COMMENTS,
allColumns,
MySQLiteHelper.COLUMN_ID + " = " + insertId, null,
null, null, null);
cursor.moveToFirst();
Comment newComment =
cursorToComment(cursor);
cursor.close();
return newComment;
}
public void
deleteComment(Comment comment) {
long id =
comment.getId();
System.out.println("Comment
deleted with id: " + id);
database.delete(MySQLiteHelper.TABLE_COMMENTS,
MySQLiteHelper.COLUMN_ID
+ " =
" + id, null);
}
public
List<Comment> getAllComments() {
List<Comment> comments = new ArrayList<Comment>();
Cursor cursor = database.query(MySQLiteHelper.TABLE_COMMENTS,
allColumns, null, null, null, null, null);
cursor.moveToFirst();
while
(!cursor.isAfterLast()) {
Comment comment =
cursorToComment(cursor);
comments.add(comment);
cursor.moveToNext();
}
// Make sure to close the cursor
cursor.close();
return comments;
}
private Comment
cursorToComment(Cursor cursor) {
Comment comment = new Comment();
comment.setId(cursor.getLong(0));
comment.setComment(cursor.getString(1));
return comment;
}
}
Comment.java Code
package
android.example.database;
public class Comment {
private long id;
private String comment;
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String
getComment() {
return comment;
}
public void
setComment(String comment) {
this.comment = comment;
}
// Will be used by the ArrayAdapter in
the ListView
@Override
public String
toString() {
return comment;
}
}
main.xml Code
<?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="#094601"
android:orientation="vertical"
>
<TextView
android:id="@+id/textView1"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_gravity="center_horizontal"
android:layout_marginBottom="5dp"
android:layout_marginTop="5dp"
android:text="Database
SQLite Example" />
<LinearLayout
android:id="@+id/group"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_gravity="center_horizontal"
>
<Button
android:id="@+id/add"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Add New"
android:onClick="onClick"/>
<Button
android:id="@+id/delete"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Delete
First"
android:onClick="onClick"/>
</LinearLayout>
<ListView
android:id="@android:id/list"
android:background="#75df67"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:text="@string/hello"
/>
</LinearLayout>
Download Android Database Example Code -->
Android Control ควบคุมอุปกรณ์ต่างๆ ด้วย Android
สอนเขียน Android สอนเขียนโปรแกรม Android
http://androidcontrol.blogspot.com/2012/01/beginning-android-training-android.html
ติดต่อ amphancm@gmail.com
ติดต่อ amphancm@gmail.com