android-一个文件搞定SQLite数据库的创建与更新以及增删查改

一、SQLite简介

1、简介
Android嵌入式关系型数据库——SQLite。SQLite 是一款轻量级的关系型数据库，它运算速度快， 占用资源很少，通常只需要几百 K 的内存就足够了，因而特别适合在移动设备上使用。SQLite 支持标准的 SQL 语法，还遵循了数据库的 ACID 事务。SQLite的使用 一般的数据库要简单得多，数据库中所有的信息（比如表、视图等）都包含在一个文件内。这个文件可以自由复制到其它目录或其它机器上。

2、数据类型

数据类型	描述
INTEGER	值为整数
TEXT	字符串
REAL	浮动的数值,被存储为8字节的IEEE浮动标记序号
VARCHAR	字符串长度不超过4000
CHAR	字符串长度不超过254
BLOB	以输入的数据格式进行存储，不会改变格式
DATA	年月日
TIME	时分秒
NULL	值为空

二、使用

1.SQLiteOpenHelper

Android 为了让我们能够更加方便地管理数据库，专门提供了一个 帮 助类SQLiteOpenHelper，借助这个类就可以非常简单地对数据库进行创建和升级。当在程序当中调用这个类的方法getWritableDatabase()或者getReadableDatabase()方法的时候，如果当时没有数据，那么Android系统就会自动生成一个数据库。

 private static class DataBaseManagementHelper extends SQLiteOpenHelper {

    private static final String DB_NAME = "user_data";
    private static final String TABLE_NAME = "users";
     //创建用户book表
    private static final String DB_CREATE = "CREATE TABLE " + TABLE_NAME + " ("
            + ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + USER_NAME + " TEXT,"
            + USER_PWD + " TEXT" + ");";
        DataBaseManagementHelper(Context context) {
            super(context, DB_NAME, null, DB_VERSION);
        }

        //这里要实现实现SQLiteOpenHelper里面的3个函数：
        @Override
        public void onCreate(SQLiteDatabase db) {
            Log.i(TAG,"db.getVersion()="+db.getVersion());
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME + ";");
            db.execSQL(DB_CREATE);
            Log.i(TAG, "db.execSQL(DB_CREATE)");
            Log.e(TAG, DB_CREATE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.i(TAG, "DataBaseManagementHelper onUpgrade");
            onCreate(db);
        }
         @Override
        public void onOpen(SQLiteDatabase db) {
            super.onOpen(db);
            //这个不常用
        }
    }

2.下面是具体的一个代码管理数据库类

/**
 * @author daxue
 * @version 创建时间：2018-08-23 上午11:27:01
 */

public class RunRecordManager {
    private static final String TAG = "RunRecordManager";
    private static final String DB_NAME = "runrecord_data";//数据库名
    private static final String TABLE_NAME = "runrecord";//表名
    //数据库更新时候用的
    private static String CREATE_TEMP_BOOK = "alter table runrecord rename to _temp";
    private static String INSERT_DATA = "insert into runrecord select *,' ',' ',' ' from _temp";
    private static String DROP_BOOK = "drop table _temp";

    private static final int DB_VERSION = 2;//数据库版本号
    private Context mContext = null;

    private SQLiteDatabase mSQLiteDatabase = null;
    private DataBaseManagementHelper mDatabaseHelper = null;

    public RunRecordManager(Context context) {
        mContext = context;
        Log.i(TAG, "UserDataManager construction!");
    }

    //DataBaseManagementHelper继承自SQLiteOpenHelper
    private static class DataBaseManagementHelper extends SQLiteOpenHelper {

        DataBaseManagementHelper(Context context) {
            super(context, DB_NAME, null, DB_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            Log.i(TAG,"db.getVersion()="+db.getVersion());
            db.execSQL(getSql());
        }

        //onUpgrade这个方法是数据库更新的时候用到的，根据上面定义的newVersion，来判断是否更新
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.i(TAG, "DataBaseManagementHelper onUpgrade");
                switch (newVersion) {
                    case 2:
                        db.execSQL(CREATE_TEMP_BOOK);
                        db.execSQL(getSql());
                        db.execSQL(INSERT_DATA);
                        db.execSQL(DROP_BOOK);
                        break;
                }

        }
    }
    //创建数据库表的sql语句
    private static String getSql(){
        String sql = String.format("create table %s ( %s INTEGER PRIMARY KEY AUTOINCREMENT,"
                        + " %s TEXT, %s TEXT,%s TEXT, %s TEXT, %s TEXT);",
                TABLE_NAME,
                PcrConfig.Columns._ID,
                PcrConfig.Columns.USER_NAME,
                PcrConfig.Columns.EXPER_ID,
                PcrConfig.Columns.EXPER_NAME,
                PcrConfig.Columns.CREAT_TIME,
                PcrConfig.Columns.RUN_TIME);

        if(PcrConfig.debug) {
            Log.d(TAG, "sql:"+ sql);
        }
        return sql;
    }


    //打开数据库
    public void openDataBase() throws SQLException {
        mDatabaseHelper = new DataBaseManagementHelper(mContext);
        mSQLiteDatabase = mDatabaseHelper.getWritableDatabase();
    }
    //关闭数据库
    public void closeDataBase() throws SQLException {
        mDatabaseHelper.close();
    }
    //添加记录（插入数据）
    public long insertRecordData(RunRecordBean data) {
        ContentValues values = new ContentValues();
        values.put(PcrConfig.Columns.USER_NAME, data.getUserName());
        values.put(PcrConfig.Columns.EXPER_NAME, data.getExperName());
        values.put(PcrConfig.Columns.EXPER_ID, data.getId());
        values.put(PcrConfig.Columns.CREAT_TIME, data.getCreatTime());
        values.put(PcrConfig.Columns.RUN_TIME, data.getRunTime());
        return mSQLiteDatabase.insert(TABLE_NAME, PcrConfig.Columns._ID, values);
    }

    public Cursor fetchUserData(int id) throws SQLException {
        Cursor mCursor = mSQLiteDatabase.query(false, TABLE_NAME, null, PcrConfig.Columns._ID
                + "=" + id, null, null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }
    //
    public Cursor fetchAllUserDatas() {
        return mSQLiteDatabase.query(TABLE_NAME, null, null, null, null, null,
                null);
    }
    //根据id删除用户
    public boolean deleteUserData(int id) {
        return mSQLiteDatabase.delete(TABLE_NAME, PcrConfig.Columns._ID + "=" + id, null) > 0;
    }
    //根据用户名注销
    public boolean deleteUserDatabyname(String name) {
        return mSQLiteDatabase.delete(TABLE_NAME, PcrConfig.Columns.USER_NAME + "='" + name+"'", null) > 0;
    }
    //删除所有用户
    public boolean deleteAllUserDatas() {
        return mSQLiteDatabase.delete(TABLE_NAME, null, null) > 0;
    }
   //更新用户信息，如修改密码
    public boolean updateUserData(UserData userData) {
        //int id = userData.getUserId();
        String userName = userData.getUserName();
        String userPwd = userData.getUserPwd();
        ContentValues values = new ContentValues();
        values.put(USER_NAME, userName);
        values.put(USER_PWD, userPwd);
        return mSQLiteDatabase.update(TABLE_NAME, values,null, null) > 0;
        //return mSQLiteDatabase.update(TABLE_NAME, values, ID + "=" + id, null) > 0;
    }
    //获取全部记录
    public List<RunRecordBean> getAllUserDatas() {
        List<RunRecordBean> ret=new ArrayList<RunRecordBean>();
        try{
            Cursor cursor = mSQLiteDatabase.query(TABLE_NAME, null, null, null, null, null,
                    null);
            if(cursor != null){
                while(cursor.moveToNext()){
                    RunRecordBean value = new RunRecordBean();
                    String str = cursor.getString(cursor.getColumnIndex(PcrConfig.Columns.EXPER_ID));
                    value.setExperId(str);
                    str = cursor.getString(cursor.getColumnIndex(PcrConfig.Columns._ID));
                    value.setId(str);
                    str = cursor.getString(cursor.getColumnIndex(PcrConfig.Columns.USER_NAME));
                    value.setUserName(str);
                    str = cursor.getString(cursor.getColumnIndex(PcrConfig.Columns.EXPER_NAME));
                    value.setExperName(str);
                    str = cursor.getString(cursor.getColumnIndex(PcrConfig.Columns.CREAT_TIME));
                    value.setCreatTime(str);
                    str = cursor.getString(cursor.getColumnIndex(PcrConfig.Columns.RUN_TIME));
                    value.setRunTime(str);
                    ret.add(value);
                }
                cursor.close();
            }
        } catch (Exception e){
            Log.d(TAG, "getAllUserDatas Execption "+e.toString());
        }

        return ret;
    }

}

3. 使用数据库

  private RunRecordManager runrecManger=null;
  private void recRun() {
        if(runrecManger == null) {
            runrecManger = new RunRecordManager(this);
            runrecManger.openDataBase();
        }
        data.setUserName("daxue");
        data.setCreatTime("123");
        long flag = runrecManger.insertRecordData(data);

    }

4.清除数据库

public void clearDatabases() {
        deleteFilesByDirectory(new File("/data/data/"
                + context.getPackageName() + "/databases"));
    }