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"));
    }

秒客网

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

相关文章