Android数据库操作

SQLite数据库的一般操作包括：创建数据库、创建表、向表中添加数据、从表中删除数据、修改表中的数据、关闭数据库、删除指定表、删除数据库和查询表中的某条数据。

1、重写SQLiteOpenHelper 类

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

public class AppSQLHelper extends SQLiteOpenHelper {
    /**
     * 当前数据库版本(最新版本)
     */
    public final static int version = 1;
    /**
     * 数据库名
     */
    public final static String SQL_NAME = "AppDataBase";


    public AppSQLHelper(Context context, String name, CursorFactory factory,
            int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        creatTables(db);
    }

    private void creatTables(SQLiteDatabase db) {
        if (db == null)
            return;
        db.execSQL("CREATE TABLE IF NOT EXISTS appRunTimeToday(_id integer primary key autoincrement, appid integer, name varchar(20), stepNumber integer)");


        upgradeTo2(db);
    }

    /* 1.0.1数据库新增的数据表 */
    private void upgradeTo2(SQLiteDatabase db) {

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        switch (oldVersion) {
        case 1:
            upgradeTo2(db);
            break;

        default:
            break;
        }
    }

    public void closeCursor(Cursor cursor){
        if(cursor == null)
            return;
        cursor.close();
    }

    public void closeDataBase(SQLiteDatabase db){
        if(db == null)
            return;
        db.close();
    }

    public void closeDbAndCursor(SQLiteDatabase db, Cursor cursor){
        closeCursor(cursor);
        closeDataBase(db);
    }

}

2、定义数据库字段

public class SQLColumns {
    public static String TABLE_NAME ="appRunTimeToday";
    public static String APPID = "appid";
    public static String NAME ="name";
    public static String STEPNUMBER = "stepNumber";
}

3、定义JavaBean

import java.io.Serializable;

public class AppInfo implements Serializable{
    /**
     * 
     */
    private static final long serialVersionUID = 1L;
    private int appid;
    private String name;
    private int stepNumber;

    public AppInfo(){

    }


    public AppInfo(int appid, String name, int stepNumber) {
        super();
        this.appid = appid;
        this.name = name;
        this.stepNumber = stepNumber;
    }
    public int getAppid() {
        return appid;
    }
    public void setAppid(int appid) {
        this.appid = appid;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getStepNumber() {
        return stepNumber;
    }
    public void setStepNumber(int stepNumber) {
        this.stepNumber = stepNumber;
    }
    @Override
    public String toString() {
        return "AppInfo [name=" + name + ", stepNumber=" + stepNumber + "]";
    }


}

3、数据库常见操作。为了方便所以对其 数据库的操作进行了封装

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 com.wangly.database.AppSQLHelper;
import com.wangly.database.bean.AppInfo;
import com.wangly.database.utils.SQLColumns;

public class DataBaseManger {
    public static DataBaseManger dBadapter = null;
    private AppSQLHelper openHelper;

    private DataBaseManger(Context context) {
        this.openHelper = new AppSQLHelper(context, AppSQLHelper.SQL_NAME, null, AppSQLHelper.version);
    }

    public static synchronized DataBaseManger getNotedBadapter(Context context){
        if(dBadapter == null)
            dBadapter = new DataBaseManger(context);
        return dBadapter;
    }


    /**
     * 向表中插入数据
     * @param info
     */
    public boolean insert(AppInfo info){
        long i = 0;
        SQLiteDatabase db = openHelper.getWritableDatabase();
        //开始事务
        try{
            i = db.insert(SQLColumns.TABLE_NAME, null, converToValues(info));
            //事务成功
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            openHelper.closeDataBase(db);
            //结束事务
        }
        return i>0;
    }


    private ContentValues converToValues(AppInfo info){
        ContentValues vaules = new ContentValues();
        vaules.put(SQLColumns.APPID, info.getAppid());
        vaules.put(SQLColumns.NAME, info.getName());
        vaules.put(SQLColumns.STEPNUMBER, info.getStepNumber());

        return vaules;
    }

    /**
     * 删除表中全部数据
     * @return
     */
    public boolean deleteAll(){
        SQLiteDatabase db = openHelper.getWritableDatabase();
        return db.delete(SQLColumns.TABLE_NAME, null, null) > 0;
    }


    /**
     * 删除表中数据
     * @return
     */
    public boolean delete(String name){
        SQLiteDatabase db = openHelper.getWritableDatabase();
        return db.delete(SQLColumns.TABLE_NAME, SQLColumns.NAME+"'" + name+"'", null) > 0;
    }


    /**
     * 查询全部数据
     * @return
     */
    public synchronized ArrayList<AppInfo> queryAll(){
        SQLiteDatabase  db = openHelper.getReadableDatabase();
        ArrayList<AppInfo> list = new ArrayList<AppInfo>();
        Cursor cursor = null;
        try{
            cursor = db.query(SQLColumns.TABLE_NAME, null, null, null, null, null, null);
            if(cursor == null)
                return list;
            cursor.moveToFirst();
            do{
                AppInfo noteSQLInfo = cursorToInfo(cursor);
                list.add(noteSQLInfo);
            }while(cursor.moveToNext());
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            openHelper.closeDbAndCursor(db, cursor);
        }
        return list;
    }

    /**
     * 通过游标(Cursor)来获取数据
     * @param cursor
     */
    private AppInfo cursorToInfo(Cursor cursor){
        AppInfo info = new AppInfo();
        info.setAppid(cursor.getInt(cursor.getColumnIndex(SQLColumns.APPID)));
        info.setName(cursor.getString(cursor.getColumnIndex(SQLColumns.NAME)));
        info.setStepNumber(cursor.getInt(cursor.getColumnIndex(SQLColumns.STEPNUMBER)));
        return info;
    }
    /**
     * 修改表中数据
     */
    public boolean update(AppInfo info){
        SQLiteDatabase db = openHelper.getWritableDatabase();
        try{
            int result = db.update(SQLColumns.TABLE_NAME, converToValues(info), SQLColumns.APPID+"'" + info.getAppid()+"'", null);
            if(result > 0){
                return true;
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            openHelper.closeDataBase(db);
        }
        return false;
    }


    /**
     * 采取分页的方法进行读取数据内容
     * 
     * @param begain 从什么位置开始
     * @param end    到什么位置结束
     * @return persons  集合
     */
    public List<AppInfo> getScroolDate(int begain, int end) {
        List<AppInfo> persons = new ArrayList<AppInfo>();
        SQLiteDatabase db = openHelper.getReadableDatabase();
        Cursor cursor = null;
        try {
            cursor = db
                    .rawQuery(
                            "select * from"+ SQLColumns.TABLE_NAME+" order by personid asc limit ?,?",
                            new String[] { String.valueOf(begain),
                                    String.valueOf(end) });
            while (cursor.moveToNext()) {
                int appid = cursor.getInt(cursor.getColumnIndex(SQLColumns.APPID));
                String name = cursor.getString(cursor.getColumnIndex(SQLColumns.NAME));
                int step = cursor.getInt(cursor.getColumnIndex(SQLColumns.STEPNUMBER));
                persons.add(new AppInfo(appid, name, step));
            }
        } catch (Exception e) {

        } finally {
            openHelper.closeDbAndCursor(db, cursor);
        }
        return persons;
    }


    /**
     * 获得数据库的总条目数
     * 
     * @return result 总数
     */
    public long getCount() {
        SQLiteDatabase db = openHelper.getReadableDatabase();
        Cursor cursor = null;
        long result = 0;
        try {
            cursor = db.rawQuery("select count(*)from "+SQLColumns.TABLE_NAME, new String[] {});
            cursor.moveToFirst();
            result = cursor.getLong(0);
        } catch (Exception e) {
            // TODO: handle exception
        } finally {
            openHelper.closeDbAndCursor(db, cursor);
        }
        return result;
    }
}