威哥在本期妙管家项目中需要实现收货数据本地化存储,防止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;
}
}