Android总结三SQLite数据库

package com.gnnuit.sqlite.dao;

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

//自定义类继承SQLiteOpenHelper
public class MyHelper extends SQLiteOpenHelper {

    /**
     * 由于父类没有无参构造函数, 定义一个构造函数调用父类有参的构造函数
     * 
     * @param context
     *            代表应用程序的环境, 用来确定数据库文件的位置
     * @param name
     *            数据文件的名字
     * @param factory
     *            用来创建Cursor(结果集)的工厂, 默认传null就可以
     * @param version
     *            数据库的版本, 后期用来更新数据库, 从1开始
     */
    public MyHelper(Context context) {
        super(context, "myDataBase.db", null, 2);
    }

    // 在数据库文件创建之后执行
    @Override
    public void onCreate(SQLiteDatabase db) {
        System.out.println("onCreate");
        // 执行SQL语句,创建表
        db.execSQL("CREATE TABLE account(_id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(20))");
    }

    // 在数据库版本提升之后执行
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        System.out.println("onUpgrade");
        db.execSQL("ALTER TABLE account ADD balance INTEGER");
    }

}

package com.gnnuit.sqlite.bean;

public class Account {
    private Integer id;
    private String name;
    private Integer balance;

    public Account() {

    }

    public Account(Integer id, String name, Integer balance) {
        super();
        this.id = id;
        this.name = name;
        this.balance = balance;
    }

    public Account(String name, Integer balance) {
        super();
        this.name = name;
        this.balance = balance;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getBalance() {
        return balance;
    }

    public void setBalance(Integer balance) {
        this.balance = balance;
    }

    @Override
    public String toString() {
        return "Account [id=" + id + ", name=" + name + ", balance=" + balance + "]";
    }

}

package com.gnnuit.sqlite.dao;

import java.util.ArrayList;
import java.util.List;

import com.gnnuit.sqlite.bean.Account;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class AccountDao {
    private MyHelper helper;

    public AccountDao(Context context) {
        helper = new MyHelper(context);
    }

    /**
     * 插入一条记录
     * 
     * @param account
     *            要插入的数据对象
     */
    public void insert(Account account) {
        // 获取SQLiteDatabase对象
        SQLiteDatabase db = helper.getWritableDatabase();
        // 执行一条SQL语句
        String sql = "insert into account(name,balance) values(?,?)";
        db.execSQL(sql, new Object[] { account.getName(), account.getBalance() });
        // 关闭
        db.close();
    }

    /**
     * 根据ID删除记录
     * 
     * @param id
     *            要删除记录的ID
     */
    public void delete(int id) {
        // 获取SQLiteDatabase对象
        SQLiteDatabase db = helper.getWritableDatabase();
        // 执行一条SQL语句
        String sql = "delete from account where _id=?";
        db.execSQL(sql, new Object[] { id });
        // 关闭
        db.close();
    }

    /**
     * 更新记录
     * 
     * @param account
     *            要更新的对象
     */
    public void update(Account account) {
        // 获取SQLiteDatabase对象
        SQLiteDatabase db = helper.getWritableDatabase();
        // 执行一条SQL语句
        String sql = "update account set name=?,balance=? where _id=?";
        db.execSQL(sql, new Object[] { account.getName(), account.getBalance(), account.getId() });
        // 关闭
        db.close();
    }

    /**
     * 根据ID查询一条记录
     * 
     * @param id
     *            要查询记录的ID
     * @return 数据对象
     */
    public Account query(int id) {
        Account account = null;
        // 获取SQLiteDatabase对象
        SQLiteDatabase db = helper.getReadableDatabase();
        // 执行一条SQL语句
        String sql = "select * from account where _id=?";
        Cursor cursor = db.rawQuery(sql, new String[] { id + "" });
        if (cursor.moveToNext()) {// 判断结果集是否包含下一条数据, 如果包含, 指针自动向后移动
            String name = cursor.getString(cursor.getColumnIndex("name"));// 从结果集中获取数据(先根据列名获取索引,再根据索引获取数据)
            int balance = cursor.getInt(cursor.getColumnIndex("balance"));
            account = new Account(id, name, balance);// 创建对象, 把数据设置到对象中
        }
        // 关闭
        cursor.close();
        db.close();
        // 返回对象
        return account;
    }

    /**
     * 查询所有记录
     * 
     * @return 所有记录组成的集合
     */
    public List<Account> queryAll() {
        List<Account> accountList = new ArrayList<Account>();
        SQLiteDatabase db = helper.getReadableDatabase();
        String sql = "select * from account";
        Cursor cursor = db.rawQuery(sql, null);// 查询表中所有数据
        while (cursor.moveToNext()) {
            Account account = null;
            int id = cursor.getInt(cursor.getColumnIndex("_id"));
            String name = cursor.getString(cursor.getColumnIndex("name"));
            int balance = cursor.getInt(cursor.getColumnIndex("balance"));
            account = new Account(id, name, balance);
            accountList.add(account);
        }
        return accountList;
    }

    /**
     * 分页查询
     * 
     * @param pageSize
     *            每页显示的数量
     * @param pageNum
     *            要查询第几页
     * @return
     */
    public List<Account> queryPage(int pageSize, int pageNum) {
        List<Account> accountList = new ArrayList<Account>();
        String index = (pageNum - 1) * pageSize + ""; // 翻页时的起始索引
        String count = pageSize + ""; // 查询多少条数据
        SQLiteDatabase db = helper.getReadableDatabase();
        String sql = "select * from account limit ?,?";
        Cursor cursor = db.rawQuery(sql, new String[] { index, count });
        while (cursor.moveToNext()) {
            Account account = null;
            int id = cursor.getInt(cursor.getColumnIndex("_id"));
            String name = cursor.getString(cursor.getColumnIndex("name"));
            int balance = cursor.getInt(cursor.getColumnIndex("balance"));
            account = new Account(id, name, balance);
            accountList.add(account);
        }
        cursor.close();
        db.close();
        return accountList;
    }

    /**
     * 查询所有记录的游标
     * 
     * @return 游标
     */
    public Cursor queryCursor() {
        SQLiteDatabase db = helper.getReadableDatabase();
        String sql = "select * from account order by balance desc";
        return db.rawQuery(sql, null);
    }

    /**
     * 查询所有记录的总数
     * 
     * @return 所有记录总数
     */
    public int queryCount() {
        int count = -1;
        SQLiteDatabase db = helper.getReadableDatabase();
        String sql = "select count(*) from account";
        Cursor cursor = db.rawQuery(sql, null);
        cursor.moveToNext();
        count = cursor.getInt(0);
        cursor.close();
        db.close();
        return count;
    }

    /**
     * 事务（转账案例）
     * 
     * @param fromId
     *            转账方的ID
     * @param toId
     *            接收方的ID
     * @param amount
     *            转账金额
     */
    public void remit(int fromId, int toId, int amount) {
        SQLiteDatabase db = helper.getWritableDatabase();
        try {
            db.beginTransaction();// 开启事务
            db.execSQL("UPDATE account SET balance=balance-? WHERE _id=?", new Object[] { amount, fromId });
            db.execSQL("UPDATE account SET balance=balance+? WHERE _id=?", new Object[] { amount, toId });
            db.setTransactionSuccessful();// 设置事务成功标记
        } finally {
            db.endTransaction();// 结束事务
            db.close();
        }
    }
}