首页 > 代码库 > Android SQLite案例

Android SQLite案例

重点掌握execSQL()和rawQuery()方法,rawQuery()方法用于执行select语句。

SQLiteOpenHelper,实现了onCreate和onUpgrade方法。

第一次创建之后接着会调用onCreate方法(我们在这里创建了数据表),之后onCreate就不再被调用。

DatabaseHelper类:

package com.example.hellodatabases;import android.content.Context;import android.database.DatabaseErrorHandler;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteDatabase.CursorFactory;import android.database.sqlite.SQLiteOpenHelper;import android.util.Log;//参考:http://blog.csdn.net/liuhe688/article/details/6715983public class DatabaseHelper extends SQLiteOpenHelper// 继承SQLiteOpenHelper类{    // 数据库版本号    private static final int DATABASE_VERSION = 1;    // 数据库名    private static final String DATABASE_NAME = "TestDB.db";    // 数据表名,一个数据库中可以有多个表(虽然本例中只建立了一个表)    public static final String TABLE_NAME = "PersonTable";    // 构造函数,调用父类SQLiteOpenHelper的构造函数    public DatabaseHelper(Context context, String name, CursorFactory factory,            int version, DatabaseErrorHandler errorHandler)    {        super(context, name, factory, version, errorHandler);    }    public DatabaseHelper(Context context, String name, CursorFactory factory,            int version)    {        super(context, name, factory, version);        // SQLiteOpenHelper的构造函数参数:        // context:上下文环境        // name:数据库名字        // factory:游标工厂(可选)        // version:数据库模型版本号    }    public DatabaseHelper(Context context)    {        super(context, DATABASE_NAME, null, DATABASE_VERSION);        // 数据库实际被创建是在getWritableDatabase()或getReadableDatabase()方法调用时        Log.d(AppConstants.LOG_TAG, "DatabaseHelper Constructor");        // CursorFactory设置为null,使用系统默认的工厂类    }    // 继承SQLiteOpenHelper类,必须要覆写的三个方法:onCreate(),onUpgrade(),onOpen()    @Override    public void onCreate(SQLiteDatabase db)    {        // 调用时间:数据库第一次创建时onCreate()方法会被调用        // onCreate方法有一个 SQLiteDatabase对象作为参数,根据需要对这个对象填充表和初始化数据        // 这个方法中主要完成创建数据库后对数据库的操作        Log.d(AppConstants.LOG_TAG, "DatabaseHelper onCreate");        // 构建创建表的SQL语句(可以从SQLite Expert工具的DDL粘贴过来加进StringBuffer中)        StringBuffer sBuffer = new StringBuffer();        sBuffer.append("CREATE TABLE [" + TABLE_NAME + "] (");        sBuffer.append("[_id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ");        sBuffer.append("[name] TEXT,");        sBuffer.append("[age] INTEGER,");        sBuffer.append("[info] TEXT)");        // 执行创建表的SQL语句        db.execSQL(sBuffer.toString());        // 即便程序修改重新运行,只要数据库已经创建过,就不会再进入这个onCreate方法    }    @Override    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)    {        // 调用时间:如果DATABASE_VERSION值被改为别的数,系统发现现有数据库版本不同,即会调用onUpgrade        // onUpgrade方法的三个参数,一个 SQLiteDatabase对象,一个旧的版本号和一个新的版本号        // 这样就可以把一个数据库从旧的模型转变到新的模型        // 这个方法中主要完成更改数据库版本的操作        Log.d(AppConstants.LOG_TAG, "DatabaseHelper onUpgrade");        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);        onCreate(db);        // 上述做法简单来说就是,通过检查常量值来决定如何,升级时删除旧表,然后调用onCreate来创建新表        // 一般在实际项目中是不能这么做的,正确的做法是在更新数据表结构时,还要考虑用户存放于数据库中的数据不丢失    }    @Override    public void onOpen(SQLiteDatabase db)    {        super.onOpen(db);        // 每次打开数据库之后首先被执行        Log.d(AppConstants.LOG_TAG, "DatabaseHelper onOpen");    }}

Person类:

package com.example.hellodatabases;public class Person{    public int _id;    public String name;    public int age;    public String info;    public Person()    {    }    public Person(String name, int age, String info)    {        this.name = name;        this.age = age;        this.info = info;    }}

管理类DBManager:

package com.example.hellodatabases;import java.util.ArrayList;import java.util.List;import android.content.ContentValues;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.util.Log;//参考:http://blog.csdn.net/liuhe688/article/details/6715983public class DBManager{    private DatabaseHelper helper;    private SQLiteDatabase db;    public DBManager(Context context)    {        Log.d(AppConstants.LOG_TAG, "DBManager --> Constructor");        helper = new DatabaseHelper(context);        // 因为getWritableDatabase内部调用了mContext.openOrCreateDatabase(mName, 0,        // mFactory);        // 所以要确保context已初始化,我们可以把实例化DBManager的步骤放在Activity的onCreate里        db = helper.getWritableDatabase();    }    /**     * add persons     *      * @param persons     */    public void add(List<Person> persons)    {        Log.d(AppConstants.LOG_TAG, "DBManager --> add");        // 采用事务处理,确保数据完整性        db.beginTransaction(); // 开始事务        try        {            for (Person person : persons)            {                db.execSQL("INSERT INTO " + DatabaseHelper.TABLE_NAME                        + " VALUES(null, ?, ?, ?)", new Object[] { person.name,                        person.age, person.info });                // 带两个参数的execSQL()方法,采用占位符参数?,把参数值放在后面,顺序对应                // 一个参数的execSQL()方法中,用户输入特殊字符时需要转义                // 使用占位符有效区分了这种情况            }            db.setTransactionSuccessful(); // 设置事务成功完成        }        finally        {            db.endTransaction(); // 结束事务        }    }    /**     * update person‘s age     *      * @param person     */    public void updateAge(Person person)    {        Log.d(AppConstants.LOG_TAG, "DBManager --> updateAge");        ContentValues cv = new ContentValues();        cv.put("age", person.age);        db.update(DatabaseHelper.TABLE_NAME, cv, "name = ?",                new String[] { person.name });    }    /**     * delete old person     *      * @param person     */    public void deleteOldPerson(Person person)    {        Log.d(AppConstants.LOG_TAG, "DBManager --> deleteOldPerson");        db.delete(DatabaseHelper.TABLE_NAME, "age >= ?",                new String[] { String.valueOf(person.age) });    }    /**     * query all persons, return list     *      * @return List<Person>     */    public List<Person> query()    {        Log.d(AppConstants.LOG_TAG, "DBManager --> query");        ArrayList<Person> persons = new ArrayList<Person>();        Cursor c = queryTheCursor();        while (c.moveToNext())        {            Person person = new Person();            person._id = c.getInt(c.getColumnIndex("_id"));            person.name = c.getString(c.getColumnIndex("name"));            person.age = c.getInt(c.getColumnIndex("age"));            person.info = c.getString(c.getColumnIndex("info"));            persons.add(person);        }        c.close();        return persons;    }    /**     * query all persons, return cursor     *      * @return Cursor     */    public Cursor queryTheCursor()    {        Log.d(AppConstants.LOG_TAG, "DBManager --> queryTheCursor");        Cursor c = db.rawQuery("SELECT * FROM " + DatabaseHelper.TABLE_NAME,                null);        return c;    }    /**     * close database     */    public void closeDB()    {        Log.d(AppConstants.LOG_TAG, "DBManager --> closeDB");        // 释放数据库资源        db.close();    }}

Activity:

HelloDBActivitypackage com.example.hellodatabases;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import android.app.Activity;import android.database.Cursor;import android.database.CursorWrapper;import android.os.Bundle;import android.view.Menu;import android.view.View;import android.widget.ListView;import android.widget.SimpleAdapter;import android.widget.SimpleCursorAdapter;//参考:http://blog.csdn.net/liuhe688/article/details/6715983public class HelloDBActivity extends Activity{    private DBManager dbManager;    private ListView listView;    @Override    protected void onCreate(Bundle savedInstanceState)    {        super.onCreate(savedInstanceState);        setContentView(R.layout.activity_hello_db);        listView = (ListView) findViewById(R.id.listView);        // 初始化DBManager        dbManager = new DBManager(this);    }    @Override    public boolean onCreateOptionsMenu(Menu menu)    {        // Inflate the menu; this adds items to the action bar if it is present.        getMenuInflater().inflate(R.menu.hello_db, menu);        return true;    }    @Override    protected void onDestroy()    {        super.onDestroy();        dbManager.closeDB();// 释放数据库资源    }    public void add(View view)    {        ArrayList<Person> persons = new ArrayList<Person>();        Person person1 = new Person("Ella", 22, "lively girl");        Person person2 = new Person("Jenny", 22, "beautiful girl");        Person person3 = new Person("Jessica", 23, "sexy girl");        Person person4 = new Person("Kelly", 23, "hot baby");        Person person5 = new Person("Jane", 25, "a pretty woman");        persons.add(person1);        persons.add(person2);        persons.add(person3);        persons.add(person4);        persons.add(person5);        dbManager.add(persons);    }    public void update(View view)    {        // 把Jane的年龄改为30(注意更改的是数据库中的值,要查询才能刷新ListView中显示的结果)        Person person = new Person();        person.name = "Jane";        person.age = 30;        dbManager.updateAge(person);    }    public void delete(View view)    {        // 删除所有三十岁以上的人(此操作在update之后进行,Jane会被删除(因为她的年龄被改为30))        // 同样是查询才能查看更改结果        Person person = new Person();        person.age = 30;        dbManager.deleteOldPerson(person);    }    public void query(View view)    {        List<Person> persons = dbManager.query();        ArrayList<Map<String, String>> list = new ArrayList<Map<String, String>>();        for (Person person : persons)        {            HashMap<String, String> map = new HashMap<String, String>();            map.put("name", person.name);            map.put("info", person.age + " years old, " + person.info);            list.add(map);        }        SimpleAdapter adapter = new SimpleAdapter(this, list,                android.R.layout.simple_list_item_2, new String[] { "name",                        "info" }, new int[] { android.R.id.text1,                        android.R.id.text2 });        listView.setAdapter(adapter);    }    @SuppressWarnings("deprecation")    public void queryTheCursor(View view)    {        Cursor c = dbManager.queryTheCursor();        startManagingCursor(c); // 托付给activity根据自己的生命周期去管理Cursor的生命周期        CursorWrapper cursorWrapper = new CursorWrapper(c)        {            @Override            public String getString(int columnIndex)            {                // 将简介前加上年龄                if (getColumnName(columnIndex).equals("info"))                {                    int age = getInt(getColumnIndex("age"));                    return age + " years old, " + super.getString(columnIndex);                }                return super.getString(columnIndex);            }        };        // 确保查询结果中有"_id"列        SimpleCursorAdapter adapter = new SimpleCursorAdapter(this,                android.R.layout.simple_list_item_2, cursorWrapper,                new String[] { "name", "info" }, new int[] {                        android.R.id.text1, android.R.id.text2 });        ListView listView = (ListView) findViewById(R.id.listView);        listView.setAdapter(adapter);    }}

布局:

<?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:orientation="vertical" >    <Button        android:layout_width="fill_parent"        android:layout_height="wrap_content"        android:onClick="add"        android:text="add" />    <Button        android:layout_width="fill_parent"        android:layout_height="wrap_content"        android:onClick="update"        android:text="update" />    <Button        android:layout_width="fill_parent"        android:layout_height="wrap_content"        android:onClick="delete"        android:text="delete" />    <Button        android:layout_width="fill_parent"        android:layout_height="wrap_content"        android:onClick="query"        android:text="query" />    <Button        android:layout_width="fill_parent"        android:layout_height="wrap_content"        android:onClick="queryTheCursor"        android:text="queryTheCursor" />    <ListView        android:id="@+id/listView"        android:layout_width="fill_parent"        android:layout_height="wrap_content" /></LinearLayout>

 常量:

package com.example.hellodatabases;public class AppConstants{    public static final String LOG_TAG="Hello DB";}

官网Training: Saving Data in SQL Databases

  http://developer.android.com/training/basics/data-storage/databases.html

  Android中SQLite应用详解:

  http://blog.csdn.net/liuhe688/article/details/6715983

  关于Cursor类的介绍:

  http://www.cnblogs.com/TerryBlog/archive/2010/07/05/1771459.html

  Android 小项目之--SQLite 使用法门 (附源码):

  http://www.cnblogs.com/TerryBlog/archive/2010/06/12/1757166.html

Android SQLite案例