Android SQLite的使用

MainActivity.java

package cn.guodao.listviewdemo;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import android.app.Activity;
import android.os.Bundle;
import android.view.View;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import cn.guodao.dao.DBManager;
import cn.guodao.domain.Person;

public class MainActivity extends Activity {

    private DBManager dbManager;
    private ListView listView;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        listView = (ListView) findViewById(R.id.listView);
        // 初始化DBManager
        dbManager = new DBManager(this);

    }

    @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.setName("Jane");
        person.setAge(3);
        dbManager.updateAge(person);
    }

    public void delete(View view) {
        // 删除所有三十岁以上的人（此操作在update之后进行，Jane会被删除（因为她的年龄被改为30））
        // 同样是查询才能查看更改结果
        Person person = new Person();
        person.setAge(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.getName());
            map.put("info", person.getAge() + " years old, " + person.getInfo());
            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);
    }*/

}

DatabaseHelper extends SQLiteOpenHelper

package cn.guodao.dao;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper// 继承SQLiteOpenHelper类
{
    // 测试过滤字符串设置
    // public static final String LOG_TAG = "Hello DB";
    // 数据库版本号
    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) {
        // SQLiteOpenHelper的构造函数参数：
        // context：上下文环境
        // name：数据库名字
        // factory：游标工厂（可选）
        // version：数据库模型版本号
        super(context, name, factory, version);
    }

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        // 数据库实际被创建是在getWritableDatabase()或getReadableDatabase()方法调用时
        // Log.d(LOG_TAG, "DatabaseHelper Constructor");
        // CursorFactory设置为null,使用系统默认的工厂类
    }

    // 继承SQLiteOpenHelper类,必须要覆写的三个方法：onCreate(),onUpgrade(),onOpen()
    @Override
    public void onCreate(SQLiteDatabase db) {
        // 调用时间：数据库第一次创建时onCreate()方法会被调用
        // onCreate方法有一个 SQLiteDatabase对象作为参数，根据需要对这个对象填充表和初始化数据
        // 这个方法中主要完成创建数据库后对数据库的操作
        // Log.d(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] varchar(20),");
        sBuffer.append("[age] integer,");
        sBuffer.append("[info] text)");
        // 执行创建表的SQL语句
        db.execSQL(sBuffer.toString());
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // 调用时间：如果DATABASE_VERSION值被改为别的数,系统发现现有数据库版本不同,即会调用onUpgrade
        //Log.d(LOG_TAG, "DatabaseHelper onUpgrade");
        if (oldVersion == 1 && newVersion == 2) {
            //从版本1到版本2时，增加了一个字段 desc            
            String sql = "alter table [" + TABLE_NAME + "] add [desc] nvarchar(300)";
            db.execSQL(sql);
        }
    }

    public void updateColumn(SQLiteDatabase db, String oldColumn, String newColumn, String typeColumn) {
        try {
            db.execSQL("ALTER TABLE " + TABLE_NAME + " CHANGE " + oldColumn + " " + newColumn + " " + typeColumn);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);
        // 每次打开数据库之后首先被执行
        //Log.d(LOG_TAG, "DatabaseHelper onOpen");
    }

}

DBManager

package cn.guodao.dao;

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 cn.guodao.domain.Person;

public class DBManager {
    private DatabaseHelper helper;
    private SQLiteDatabase db;

    public DBManager(Context context) {
        // Log.d(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(LOG_TAG, "DBManager --> add");
        // 采用事务处理，确保数据完整性
        db.beginTransaction(); // 开始事务
        try {
            for (Person person : persons) {
                db.execSQL("INSERT INTO " + DatabaseHelper.TABLE_NAME + " VALUES(null, ?, ?, ?)", new Object[] { person.getName(), person.getAge(), person.getInfo() });
                // 带两个参数的execSQL()方法，采用占位符参数？，把参数值放在后面，顺序对应
                // 一个参数的execSQL()方法中，用户输入特殊字符时需要转义
                // 使用占位符有效区分了这种情况
            }
            db.setTransactionSuccessful(); // 设置事务成功完成
        } finally {
            db.endTransaction(); // 结束事务
        }
    }

    /**
     * update person's age
     * 
     * @param person
     */
    public void updateAge(Person person) {
        // Log.d(LOG_TAG, "DBManager --> updateAge");
        ContentValues cv = new ContentValues();
        cv.put("age", person.getAge());
        db.update(DatabaseHelper.TABLE_NAME, cv, "name = ?", new String[] { person.getName() });
    }

    /**
     * delete old person
     * 
     * @param person
     */
    public void deleteOldPerson(Person person) {
        // Log.d(LOG_TAG, "DBManager --> deleteOldPerson");
        db.delete(DatabaseHelper.TABLE_NAME, "age >= ?", new String[] { String.valueOf(person.getAge()) });
    }

    /**
     * query all persons, return list
     * 
     * @return List<Person>
     */
    public List<Person> query() {
        // Log.d(LOG_TAG, "DBManager --> query");
        ArrayList<Person> persons = new ArrayList<Person>();
        Cursor c = queryTheCursor();
        while (c.moveToNext()) {
            Person person = new Person();
            person.setId(c.getInt(c.getColumnIndex("id")));
            person.setName(c.getString(c.getColumnIndex("name")));
            person.setAge(c.getInt(c.getColumnIndex("age")));
            person.setInfo(c.getString(c.getColumnIndex("info")));
            persons.add(person);
        }
        c.close();
        return persons;
    }

    /**
     * query all persons, return cursor
     * 
     * @return Cursor
     */
    public Cursor queryTheCursor() {
        // Log.d(LOG_TAG, "DBManager --> queryTheCursor");
        Cursor c = db.rawQuery("SELECT * FROM " + DatabaseHelper.TABLE_NAME, null);
        return c;
    }

    /**
     * close database
     */
    public void closeDB() {
        // Log.d(LOG_TAG, "DBManager --> closeDB");
        // 释放数据库资源
        db.close();
    }

}