Android中数据库的使用

之前我博客中提到了简单字符串数据可以通过共享参数的方式保存在本地，这也是Android中主要的一种数据持久化的方法。不过这种保存方法有个问题，一个是数据不能过多，另一个就是多条关联数据不好保存。这时我们就会很羡慕后台的数据库了。不过没有关系，Android也为我们准备了一个轻量级的数据库，并为我们提供了相关的操作接口。

首先我们创建一个操作类，这里也大致举出了相关增删改查的方法。如果需要自己写可以参考这个编码。

public class UserDBHelper extends SQLiteOpenHelper {

    private static final String TAG = "UserDBHelper";
    private static final String DB_NAME = "user.db";
    private static final int DB_VERSION = 2;
    private static UserDBHelper mHelper = null;
    private SQLiteDatabase mDB = null;
    private static final String TABLE_NAME = "user_info";

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

    private UserDBHelper(Context context, int version) {
        super(context, DB_NAME, null, version);
    }

    /**
     * 单例获取Helper对象
     *
     * @param context
     * @param version
     * @return
     */
    public static UserDBHelper getInstance(Context context, int version) {
        if (version > 0 && mHelper == null) {
            mHelper = new UserDBHelper(context, version);
        } else if (mHelper == null) {
            mHelper = new UserDBHelper(context);
        }
        return mHelper;
    }

    /**
     * 通过读链接获取SQLiteDatabase
     *
     * @return
     */
    public SQLiteDatabase openReadLink() {
        if (mDB == null || mDB.isOpen() != true) {
            mDB = mHelper.getReadableDatabase();
        }
        return mDB;
    }

    /**
     * 通过写链接获取SQLiteDatabase
     *
     * @return
     */
    public SQLiteDatabase openWriteLink() {
        if (mDB == null || mDB.isOpen() != true) {
            mDB = mHelper.getWritableDatabase();
        }
        return mDB;
    }

    /**
     * 关闭数据库
     */
    public void closeLink() {
        if (mDB != null && mDB.isOpen() == true) {
            mDB.close();
            mDB = null;
        }
    }

    /**
     * 获取数据库名称
     *
     * @return
     */
    public String getDbName() {
        if (mHelper != null) {
            return mHelper.getDatabaseName();
        } else {
            return DB_NAME;
        }
    }

    /**
     * 只在第一次打开数据库时执行，再此可进行表结构创建的操作
     *
     * @param db
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        String drop_sql = "DROP TABLE IF EXISTS " + TABLE_NAME + ";";
        db.execSQL(drop_sql);
        String create_sql = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " ("
                + "_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
                + "name VARCHAR NOT NULL," + "age INTEGER NOT NULL,"
                + "height LONG NOT NULL," + "weight FLOAT NOT NULL,"
                + "married INTEGER NOT NULL," + "update_time VARCHAR NOT NULL,"
                + "phone VARCHAR," + "password VARCHAR" + ");";
        db.execSQL(create_sql);
    }

    /**
     * 在数据库版本升高时执行，可以根据新旧版本号进行表结构变更处理
     *
     * @param db
     * @param oldVersion
     * @param newVersion
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        //其实这里是根据oldVersion的不同进行字段的添加
        if (oldVersion == 1) {
            // TODO: 2018/11/20 这里一会儿进行测试，先创建的表没有phone和password字段，然后添加
            //Android 的 ALTER 命令不支持一次添加多列，只能分多次添加
            String alter_sql = "ALTER TABLE " + TABLE_NAME + " ADD COLUMN " + "phone VARCHAR;";
            db.execSQL(alter_sql);
            alter_sql = "ALTER TABLE " + TABLE_NAME + " ADD COLUMN " + "password VARCHAR;";
            db.execSQL(alter_sql);
        }
    }

    /**
     * 根据条件删除数据
     *
     * @param condition
     * @return
     */
    public int delete(String condition) {
        int count = mDB.delete(TABLE_NAME, condition, null);
        //null的位置，如果condition中用了？作为数值占位，比如"id>?",null要给new String[]{"2"}
        return count;
    }

    /**
     * 删除全部数据
     *
     * @return
     */
    public int deleteAll() {
        int count = mDB.delete(TABLE_NAME, "1=1", null);
        return count;
    }

    /**
     * 增加单条数据，其实就是包装了一下调用增加多条数据
     *
     * @param info
     * @return
     */
    public long insert(UserInfo info) {
        ArrayList<UserInfo> infoArray = new ArrayList<>();
        infoArray.add(info);
        return insert(infoArray);
    }

    /**
     * 根据条件进行查询
     *
     * @param condition
     * @return
     */
    public ArrayList<UserInfo> query(String condition) {
        String sql = String.format("select rowid,_id,name,age,height,weight,married,update_time,phone,password from %s where %s;", TABLE_NAME, condition);
        ArrayList<UserInfo> infoArray = new ArrayList<>();
        Cursor cursor = mDB.rawQuery(sql, null);
        if (cursor.moveToNext()) {
            for (; ; cursor.moveToNext()) {
                UserInfo info = new UserInfo();
                info.setRowid(cursor.getLong(0));
                info.setXuhao(cursor.getInt(1));
                info.setName(cursor.getString(2));
                info.setAge(cursor.getInt(3));
                info.setHeight(cursor.getLong(4));
                info.setWeight(cursor.getFloat(5));
                //SQLite没有布尔型，用0表示false，用1表示true
                info.setMarried(cursor.getInt(6) == 0 ? false : true);
                info.setUpdate_time(cursor.getString(7));
                info.setPhone(cursor.getString(8));
                info.setPassword(cursor.getString(9));
                infoArray.add(info);
                if (cursor.isLast() == true) {
                    break;
                }
            }
        }
        cursor.close();
        return infoArray;
    }

    /**
     * 更新多条数据
     *
     * @param info
     * @param condition
     * @return
     */
    public int update(UserInfo info, String condition) {
        ContentValues cv = new ContentValues();
        cv.put("name", info.getName());
        cv.put("age", info.getAge());
        cv.put("height", info.getHeight());
        cv.put("weight", info.getWeight());
        cv.put("married", info.isMarried());
        cv.put("update_time", info.getUpdate_time());
        cv.put("phone",info.getPhone());
        cv.put("password",info.getPassword());
        int count = mDB.update(TABLE_NAME, cv, condition, null);
        return count;
    }

    /**
     * 更新指定的数据条目
     *
     * @param info
     * @return
     */
    public int update(UserInfo info) {
        return update(info, "rowid=" + info.getRowid());
    }

    /**
     * 增加多条数据
     *
     * @param infoArray
     * @return
     */
    public long insert(ArrayList<UserInfo> infoArray) {
        long result = -1;
        //循环全部数据
        for (int i = 0; i < infoArray.size(); i++) {
            UserInfo info = infoArray.get(i);
            ArrayList<UserInfo> tempArray = new ArrayList<>();
            //如果存在同名记录，就更新记录。注意条件语句的等号后面要用单引号括起来
            if (info.getName() != null && info.getName().length() > 0) {
                String condition = String.format("name='%s'", info.getName());
                tempArray = query(condition);
                if (tempArray.size() > 0) {
                    update(info, condition);
                    result = tempArray.get(0).getRowid();
                    continue;
                }
            }
            //如果不存在唯一性重复的记录，就插入新记录
            ContentValues cv = new ContentValues();
            cv.put("name", info.getName());
            cv.put("age", info.getAge());
            cv.put("height", info.getHeight());
            cv.put("weight", info.getWeight());
            cv.put("married", info.isMarried());
            cv.put("update_time", info.getUpdate_time());
            cv.put("phone",info.getPhone());
            cv.put("password",info.getPassword());
            result = mDB.insert(TABLE_NAME, "", cv);
            //添加成功后返回行号，失败则返回-1
            if (result == -1) {
                return result;
            }
        }
        return result;
    }
}

下面是在对应的逻辑中进行的使用

UserDBHelper userDBHelper = UserDBHelper.getInstance(this,0);
userDBHelper.openWriteLink();
ArrayList<UserInfo> infoList = new ArrayList<>();
UserInfo userInfo = new UserInfo();
userInfo.setXuhao(10);
userInfo.setName("小红");
userInfo.setUpdate_time("2018-11-24");
userInfo.setMarried(false);
userInfo.setAge(99);
userInfo.setWeight(100);
userInfo.setHeight(180);
userInfo.setPhone("15835133570");
userInfo.setPassword("sdfsd");
infoList.add(userInfo);
UserInfo userInfo1 = new UserInfo();
userInfo1.setXuhao(11);
userInfo1.setName("小雅");
userInfo1.setUpdate_time("2018-11-26");
userInfo1.setMarried(true);
userInfo1.setAge(34);
userInfo1.setWeight(34);
userInfo1.setHeight(34);
userInfo1.setPhone("15536935920");
userInfo1.setPassword("sdfsd");
infoList.add(userInfo1);
long result = userDBHelper.insert(infoList);
userDBHelper.openReadLink();
userDBHelper.query("1==1");

具体其他使用不在这里赘述，只提供了简单的初级使用方法，