Android数据持久化-Sqlite篇

威哥在本期妙管家项目中需要实现收货数据本地化存储，防止app意外崩溃，再次打开app时还能继续上次的收货数据，接着收货，比喻门店蔬菜100多个品类，正好收到99个Sku，各自原先导致app崩溃，或者不小心退出了app，总不能再重头开始收货。

故威哥设计将收货实时存储在本地，之前使用的SharedPreferences不能存储大量数据，因为威哥的收货信息比较多，一个品类收货就有百来条数据信息需要存储，故威哥使用了原生的sqlite来实现app的数据本地持久化，原先威哥是想使用GreenDao的，发现威哥的Android IDE已经升级到3.2，不能使用GreenDao了。

下面先看看威哥的妙管家app截图：

 

直接开始描述Sqlite使用吧。

1、首先先下一个类继承SQLiteOpenHelper：

public class ReceiveOptDao extends SQLiteOpenHelper {

}

2、创建数据库：

private static final String DB_NAME = "mshpda.db";
private static final int VERSION = 1;

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

public ReceiveOptDao(Context context) {
    super(context, DB_NAME, null, VERSION);
}

3、创建表结构：

/**
 * 创建数据库表结构（存在则不创建）
 * Author：William（徐威）
 * Create Time：2018-12-17
 *
 * @param db
 */
@Override
public void onCreate(SQLiteDatabase db) {
    boolean result = false;
    Cursor cursor = null;
    StringBuffer str = new StringBuffer();
    str.append("Select * From sqlite_master where type='table' and name = 'shiftReceiveList';");
    cursor = db.rawQuery("Select * From sqlite_master where type='table' and name = ?;",
            new String[]{"shiftReceiveList"});
    result = null != cursor && cursor.moveToFirst();
    if (!result) {
        //不存在表结构，需创建
        str = new StringBuffer();
        str.append("create table ShiftReceiveList (");
        str.append("SysNo integer primary key,rowCreateDate text,rowModifyDate text,rowCreateDateTimestamp integer,");
        str.append("rowModifyDateTimestamp integer,JsonContent text )");
        db.execSQL(str.toString());

        // db.execSQL("create table if not exists person (personid integer primary key autoincrement ,name varchar(30) ,age integer(3) )");
    }
}

4、新增&删除数据：

/**
 * 新增数据(先删除，再新增)
 * Author：William（徐威）
 * Create Time：2018-12-17
 *
 * @param itemList 收货操作List
 * @return
 */
public long addWithDelete(List<ShiftReceiveProductDto> itemList) {
    long rowCount = 0;
    SQLiteDatabase db = this.getWritableDatabase();
    try {
        ShiftReceiveProductInfo model = new ShiftReceiveProductInfo();
        model.setShiftSysNo(itemList.get(0).ShiftSysNo);
        model.setItemList(itemList);

        if (db.isOpen()) {
            Date dt = Calendar.getInstance().getTime();
            String strTime = TimerHelper.getStrDataTime(null, dt);
            long timestamp = TimerHelper.getDateTimestamp(dt, null);
            String strJson = JsonHelper.converJavaBeanToJson(model);

            //先删除数据
            rowCount = db.delete("ShiftReceiveList", "SysNo=?", new String[]{String.valueOf(model.getShiftSysNo())});

            //再写入数据
            ContentValues values = new ContentValues();
            values.put("SysNo", model.getShiftSysNo());
            values.put("rowCreateDate", strTime);
            values.put("rowModifyDate", strTime);
            values.put("rowCreateDateTimestamp", timestamp);
            values.put("rowModifyDateTimestamp", timestamp);
            values.put("JsonContent", strJson);
            rowCount += db.insert("ShiftReceiveList", null, values);
        }

    } catch (Exception ex) {
        ex.printStackTrace();
        Log.e("Add", ex.getMessage());
        throw ex;
    } finally {
        db.close();
    }

    return rowCount;
}

5、获取信息（此处是威哥的业务数据获取，直接使用了Json存储和读取）：

/**
 * 获取泛型List信息
 * Author：William（徐威）
 * Create Time：2018-12-17
 *
 * @param request
 * @return
 */
public List<ShiftReceiveProductDto> getList(BaseSearchInfo request) {
    List<ShiftReceiveProductDto> list = null;
    SQLiteDatabase db = this.getReadableDatabase();
    try {
        StringBuffer str = new StringBuffer();
        str.append("Select * From ShiftReceiveList Where 1=1");
        if (!TextUtils.isEmpty(request.getSysNo())) {
            str.append(String.format(" And SysNo=%s ", request.getSysNo()));
        }
        if (!TextUtils.isEmpty(request.getRowCreateDateFrom())) {
            str.append(String.format(" And datetime(rowCreateDate) >= datetime('%s') ", request.getRowCreateDateFrom()));
        }
        if (!TextUtils.isEmpty(request.getRowCreateDateTo())) {
            str.append(String.format(" And datetime(rowCreateDate) <= datetime('%s') ", request.getRowCreateDateTo()));
        }
        if (!TextUtils.isEmpty(request.getRowModifyDateFrom())) {
            str.append(String.format(" And datetime(rowModifyDate) >= datetime('%s') ", request.getRowModifyDateFrom()));
        }
        if (!TextUtils.isEmpty(request.getRowModifyDateTo())) {
            str.append(String.format(" And datetime(rowModifyDate) <= datetime('%s') ", request.getRowModifyDateTo()));
        }
        if (!TextUtils.isEmpty(request.getRowCreateDateTimestampFrom())) {
            str.append(String.format(" And rowCreateDateTimestamp >= %s ", request.getRowCreateDateTimestampFrom()));
        }
        if (!TextUtils.isEmpty(request.getRowCreateDateTimestampTo())) {
            str.append(String.format(" And rowCreateDateTimestamp) <= %s ", request.getRowCreateDateTimestampTo()));
        }
        if (!TextUtils.isEmpty(request.getRowModifyDateTimestampFrom())) {
            str.append(String.format(" And rowModifyDateTimestamp >= %s ", request.getRowModifyDateTimestampFrom()));
        }
        if (!TextUtils.isEmpty(request.getRowModifyDateTimestampTo())) {
            str.append(String.format(" And rowModifyDateTimestamp) <= %s ", request.getRowModifyDateTimestampTo()));
        }

        Cursor cursor = db.rawQuery(str.toString(), null);
        if (cursor != null) {
            list = new ArrayList<ShiftReceiveProductDto>();
            while (cursor.moveToNext()) {
                String strJson = String.valueOf(cursor.getString(cursor.getColumnIndex("JsonContent")));
                if (!TextUtils.isEmpty(strJson)) {
                    Type type = new TypeToken<List<ShiftReceiveProductDto>>() {
                    }.getType();
                    List<ShiftReceiveProductDto> tempList = JsonHelper.convetJsonToList(strJson, type);
                    if (tempList != null && tempList.size() > 0) {
                        list.addAll(tempList);
                    }
                }
            }
        }
    } catch (Exception ex) {
        ex.printStackTrace();
        Log.e("getList", ex.getMessage());
        list = null;
    } finally {
        db.close();
    }

    if (list != null && list.size() == 0) {
        list = null;
    }

    return list;
}

6、数据更新（威哥目前使用不到，给大家写个简单点的）：

ContentValues values = new ContentValues();
values.put("JsonContent", "威哥威武");
db.update("ShiftReceiveList", values, "SysNo= ?", new String[] { "24000320" });

7、sqlite查询方法query和sql原生查询对比参照图：

最后威哥直接把威哥的类代码贴出来吧：

package com.msh.mshpda.dao;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.TextUtils;
import android.util.Log;

import com.google.gson.reflect.TypeToken;
import com.msh.mshpda.dto.shift.ShiftReceiveProductDto;
import com.msh.mshpda.modelInfo.BaseSearchInfo;
import com.msh.mshpda.modelInfo.ShiftReceiveProductInfo;
import com.msh.mshpda.utils.JsonHelper;
import com.msh.mshpda.utils.TimerHelper;

import java.lang.reflect.Type;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

/**
 * 收货数据处理层
 * Author：William（徐威）
 * Create Time：2018-12-17
 */
public class ReceiveOptDao extends SQLiteOpenHelper {
    private static final String DB_NAME = "mshpda.db";
    private static final int VERSION = 1;

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

    public ReceiveOptDao(Context context) {
        super(context, DB_NAME, null, VERSION);
    }


    /**
     * 创建数据库表结构（存在则不创建）
     * Author：William（徐威）
     * Create Time：2018-12-17
     *
     * @param db
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        boolean result = false;
        Cursor cursor = null;
        StringBuffer str = new StringBuffer();
        str.append("Select * From sqlite_master where type='table' and name = 'shiftReceiveList';");
        cursor = db.rawQuery("Select * From sqlite_master where type='table' and name = ?;",
                new String[]{"shiftReceiveList"});
        result = null != cursor && cursor.moveToFirst();
        if (!result) {
            //不存在表结构，需创建
            str = new StringBuffer();
            str.append("create table ShiftReceiveList (");
            str.append("SysNo integer primary key,rowCreateDate text,rowModifyDate text,rowCreateDateTimestamp integer,");
            str.append("rowModifyDateTimestamp integer,JsonContent text )");
            db.execSQL(str.toString());

            // db.execSQL("create table if not exists person (personid integer primary key autoincrement ,name varchar(30) ,age integer(3) )");
        }
    }

    /**
     * 数据库升级
     * Author：William（徐威）
     * Create Time：2018-12-17
     *
     * @param db
     * @param oldVersion
     * @param newVersion
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    @Override
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);
    }

    /**
     * 新增数据(先删除，再新增)
     * Author：William（徐威）
     * Create Time：2018-12-17
     *
     * @param itemList 收货操作List
     * @return
     */
    public long addWithDelete(List<ShiftReceiveProductDto> itemList) {
        long rowCount = 0;
        SQLiteDatabase db = this.getWritableDatabase();
        try {
            ShiftReceiveProductInfo model = new ShiftReceiveProductInfo();
            model.setShiftSysNo(itemList.get(0).ShiftSysNo);
            model.setItemList(itemList);

            if (db.isOpen()) {
                Date dt = Calendar.getInstance().getTime();
                String strTime = TimerHelper.getStrDataTime(null, dt);
                long timestamp = TimerHelper.getDateTimestamp(dt, null);
                String strJson = JsonHelper.converJavaBeanToJson(model);

                //先删除数据
                rowCount = db.delete("ShiftReceiveList", "SysNo=?", new String[]{String.valueOf(model.getShiftSysNo())});

                //再写入数据
                ContentValues values = new ContentValues();
                values.put("SysNo", model.getShiftSysNo());
                values.put("rowCreateDate", strTime);
                values.put("rowModifyDate", strTime);
                values.put("rowCreateDateTimestamp", timestamp);
                values.put("rowModifyDateTimestamp", timestamp);
                values.put("JsonContent", strJson);
                rowCount += db.insert("ShiftReceiveList", null, values);
            }

        } catch (Exception ex) {
            ex.printStackTrace();
            Log.e("Add", ex.getMessage());
            throw ex;
        } finally {
            db.close();
        }

        return rowCount;
    }

    /**
     * 获取泛型List信息
     * Author：William（徐威）
     * Create Time：2018-12-17
     *
     * @param request
     * @return
     */
    public List<ShiftReceiveProductDto> getList(BaseSearchInfo request) {
        List<ShiftReceiveProductDto> list = null;
        SQLiteDatabase db = this.getReadableDatabase();
        try {
            StringBuffer str = new StringBuffer();
            str.append("Select * From ShiftReceiveList Where 1=1");
            if (!TextUtils.isEmpty(request.getSysNo())) {
                str.append(String.format(" And SysNo=%s ", request.getSysNo()));
            }
            if (!TextUtils.isEmpty(request.getRowCreateDateFrom())) {
                str.append(String.format(" And datetime(rowCreateDate) >= datetime('%s') ", request.getRowCreateDateFrom()));
            }
            if (!TextUtils.isEmpty(request.getRowCreateDateTo())) {
                str.append(String.format(" And datetime(rowCreateDate) <= datetime('%s') ", request.getRowCreateDateTo()));
            }
            if (!TextUtils.isEmpty(request.getRowModifyDateFrom())) {
                str.append(String.format(" And datetime(rowModifyDate) >= datetime('%s') ", request.getRowModifyDateFrom()));
            }
            if (!TextUtils.isEmpty(request.getRowModifyDateTo())) {
                str.append(String.format(" And datetime(rowModifyDate) <= datetime('%s') ", request.getRowModifyDateTo()));
            }
            if (!TextUtils.isEmpty(request.getRowCreateDateTimestampFrom())) {
                str.append(String.format(" And rowCreateDateTimestamp >= %s ", request.getRowCreateDateTimestampFrom()));
            }
            if (!TextUtils.isEmpty(request.getRowCreateDateTimestampTo())) {
                str.append(String.format(" And rowCreateDateTimestamp) <= %s ", request.getRowCreateDateTimestampTo()));
            }
            if (!TextUtils.isEmpty(request.getRowModifyDateTimestampFrom())) {
                str.append(String.format(" And rowModifyDateTimestamp >= %s ", request.getRowModifyDateTimestampFrom()));
            }
            if (!TextUtils.isEmpty(request.getRowModifyDateTimestampTo())) {
                str.append(String.format(" And rowModifyDateTimestamp) <= %s ", request.getRowModifyDateTimestampTo()));
            }

            Cursor cursor = db.rawQuery(str.toString(), null);
            if (cursor != null) {
                list = new ArrayList<ShiftReceiveProductDto>();
                while (cursor.moveToNext()) {
                    String strJson = String.valueOf(cursor.getString(cursor.getColumnIndex("JsonContent")));
                    if (!TextUtils.isEmpty(strJson)) {
                        Type type = new TypeToken<List<ShiftReceiveProductDto>>() {
                        }.getType();
                        List<ShiftReceiveProductDto> tempList = JsonHelper.convetJsonToList(strJson, type);
                        if (tempList != null && tempList.size() > 0) {
                            list.addAll(tempList);
                        }
                    }
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            Log.e("getList", ex.getMessage());
            list = null;
        } finally {
            db.close();
        }

        if (list != null && list.size() == 0) {
            list = null;
        }

        return list;
    }
}