Android数据持久化-Sqlite篇

约定不等于承诺〃 2022-04-10 01:56 216阅读 0赞

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

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

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1d2VpX25ldA_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1d2VpX25ldA_size_16_color_FFFFFF_t_70 1][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1d2VpX25ldA_size_16_color_FFFFFF_t_70 2][]

直接开始描述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原生查询对比参照图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1d2VpX25ldA_size_16_color_FFFFFF_t_70 3][]

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

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1d2VpX25ldA_size_16_color_FFFFFF_t_70]: /images/20220404/3a7feb5d60e144c9b6be51d6b1830379.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1d2VpX25ldA_size_16_color_FFFFFF_t_70 1]: /images/20220404/43ed9cc89d1c4efda76920043bd3b77e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1d2VpX25ldA_size_16_color_FFFFFF_t_70 2]: /images/20220404/5053661174a04c4783321daee85fe2c2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1d2VpX25ldA_size_16_color_FFFFFF_t_70 3]: /images/20220404/0fb4991973d94a0e8aee01598ce24373.png