Android SQLite 数据库操作

Android使用SQLite数据库，它是一个开源的、支持多操作系统的SQL数据库。在Android设备中数据库位于/data/data/databases文件夹。

首先来看一下android提供的关于数据库的类

·         SQLiteOpenHelper,这是一个辅助类，用来打开（创建）数据可，以及数据库升级，你需要创建它的子类，并且实现两个方法,

·         SQLiteDatabase, 用来管理SQLite database，它提供了create, delete, execute SQL commands等方法

参考网上的例子，自己做了个小程序，学习了一下android数据库的操作。

在这个例子中，定义了一个DBAdapter类，用来处理所有数据库相关操作,而在这个类中还定义了一个内嵌类DatabaseHelper extends SQLiteOpenHelper用来处理数据库的打开与升级。不过在操作数据库中，最重要的还是实例化一个SQLiteDatabase类对象，通过它数据库大部分的操作都能够实现。

Android中对db的使用有一种比较简单的模式，即派生一个 ContentProviderDatabaseHelper类来进行SQLiteDatabase对象实例的获取。基本上， ContentProviderDatabaseHelper类扮演了一个singleton的角色，提供单一的实例化入口点，并屏蔽了数据库创建、打开、升级等细节

例子比较简单，直接看代码吧

首先是数据库操作类：

package com.android.db;

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

/*
 * DB format: _id, isbn, title, publisher
 */

/*
 * Database operator class, to create, open, use, and close DB. *
 */
public class DBAdapter {
    public static final String KEY_ROWID = "_id";
    public static final String KEY_ISBN = "isbn";
    public static final String KEY_TITLE = "title";
    public static final String KEY_PUBLISHER = "publisher";
    private static final String TAG = "Book";
    private static final String DATABASE_NAME = "books";
    private static final String DATABASE_TABLE = "titles";
    private static final int DATABASE_VERSION = 1;
    
    
    /*
     * create table SQL
     */
    private static final String DATABASE_CREATE =
    "create table titles (_id integer primary key autoincrement, "
    + "isbn text not null, title text not null, "
    + "publisher text not null);";
    
    private final Context context;
    
    //DB assistant instance

    private DatabaseHelper DBHelper;
    
    //DB instance

    private SQLiteDatabase db;
    
    /*
     * DBAdapter constructor
     */
    public DBAdapter(Context ctx)
    {
     this.context = ctx;
     DBHelper = new DatabaseHelper(context);
    }
    
    /*
     * DB help class, it is a DB assistant class
     * You will need to override onCreate() and onUpgrade() method.
     */
    private static class DatabaseHelper extends SQLiteOpenHelper
    {
     DatabaseHelper(Context context)
     {
     super(context, DATABASE_NAME, null, DATABASE_VERSION);
     }
    
     @Override
     public void onCreate(SQLiteDatabase db)
     {
     db.execSQL(DATABASE_CREATE);
     }
     @Override
     public void onUpgrade(SQLiteDatabase db, int oldVersion,
     int newVersion)
     {
     Log.w(TAG, "Upgrading database from version " + oldVersion
     + " to "
     + newVersion + ", which will destroy all old data");
     db.execSQL("DROP TABLE IF EXISTS titles");
     onCreate(db);
     }
    }//end of DatabaseHelper

    
    /*****************************************************
     * Below are all DBAdaptor method: create, open...
     ****************************************************/
    
    /*
     * Open DB
     */
    public DBAdapter open() throws SQLException
    {
        //get a DB through DB assistant

     db = DBHelper.getWritableDatabase();
     return this;
    }

    /*
     * close DB
     */
    public void close()
    {
        //close DB through DB assistant

     DBHelper.close();
    }

    /*
     * Insert one title
     */
    public long insertTitle(String isbn, String title, String publisher)
    {
     ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_ISBN, isbn);
        initialValues.put(KEY_TITLE, title);
     initialValues.put(KEY_PUBLISHER, publisher);
     return db.insert(DATABASE_TABLE, null, initialValues);
    }

    /*
     * Delete one title
     */
    public boolean deleteTitle(long rowId)
    {
     return db.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
    }

    /*
     * Query all titles
     */
    public Cursor getAllTitles()
    {
     return db.query(DATABASE_TABLE, new String[] {
     KEY_ROWID,
     KEY_ISBN,
     KEY_TITLE,
     KEY_PUBLISHER},
     null,
     null,
     null,
     null,
     null);
    }

    /*
     * Query a specified title
     */
    public Cursor getTitle(long rowId) throws SQLException
    {
     Cursor mCursor =
        db.query(true, DATABASE_TABLE, new String[] {
     KEY_ROWID,
     KEY_ISBN,
     KEY_TITLE,
     KEY_PUBLISHER
     },
     KEY_ROWID + "=" + rowId,
     null,
     null,
     null,
     null,
     null);
     if (mCursor != null)
     {
     mCursor.moveToFirst();
     }
     return mCursor;
    }

    /*
     * update a title
     */
    public boolean updateTitle(long rowId, String isbn,
    String title, String publisher)
    {
     ContentValues args = new ContentValues();
     args.put(KEY_ISBN, isbn);
     args.put(KEY_TITLE, title);
     args.put(KEY_PUBLISHER, publisher);
     return db.update(DATABASE_TABLE, args,
     KEY_ROWID + "=" + rowId, null) > 0;
    }
}


接下来是主程序：

package com.android.db;

import java.util.ArrayList;
import java.util.List;
import android.app.ListActivity;
import android.database.Cursor;
import android.os.Bundle;
import android.util.Log;
import android.widget.ArrayAdapter;

public class BookActivity extends ListActivity {
    private static final String TAG = "Book";
    private DBAdapter dbAdapter = null;
    List<String> mList = new ArrayList<String>();
        
    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        //setContentView(R.layout.main);

        
        /*
         * create and open DB
         */
        dbAdapter = new DBAdapter(this);
        dbAdapter.open();
        
        /*
         * insert books to DB
         */
        //insertBook();

        
        /*get all book list*/
        loadAllList();
        
        /*
         * display all book as a list
         */
        setListAdapter(new ArrayAdapter<String>(this,
                android.R.layout.simple_list_item_1, mList));//mStrings)); //use List or String[]

        setListAdapter(new ArrayAdapter<String>(this,
                R.layout.file_row, mList));
        
        getListView().setTextFilterEnabled(true);
      
    }
    
    /*
     * insert titls to DB
     */
    public void insertBook()
    {
        long id;
        id = dbAdapter.insertTitle(
        "0470285818",
        "C# 2008 Programmer's Reference",
        "Wrox");
        id = dbAdapter.insertTitle(
        "047017661X",
        "Professional Windows Vista",
        "Wrox");
        id = dbAdapter.insertTitle("0470285818",
        "C Programming",
        "Wrox");
        Log.i(TAG, "id = "+ id);
    }
    
    @Override
    protected void onDestroy() {
        // TODO Auto-generated method stub

        super.onDestroy();
        dbAdapter.close();
    }

    /*
     * load all book list from DB
     */
    public void loadAllList()
    {
        mList.add("ALL BOOK LIST");
        Cursor c = dbAdapter.getAllTitles();
        if (c.moveToFirst())
        {
         do{
             int nameColumn = c.getColumnIndex(DBAdapter.KEY_TITLE);
         mList.add(c.getString(nameColumn));
         } while (c.moveToNext());
        }
    }
    
}