关键字
SQL
SQLite
通用系列
数据存储
摘要:
主要内容:
本文介绍了封装后的SQLite,以便快速开发;
只保留必要的定制,重复的代码封装到一个通用类中;
说明
LouSQLite.java
文件为通用的代码,所有项目中不需要修改即可使用;MyCallBack.java
文件是自定义的文件,关于项目的数据库配置都在这里进行,例如:数据库名称、数据库版本号、table语句等;LouSQLite.java
支持常用的CRUD操作(支持事务);
代码
【用法】
使用方法
// 初始化
LouSQLite.init(this.getApplicationContext(), new MyCallBack());
// 查找
List<Phrase> lists = LouSQLite.query(MyCallBack.TABLE_NAME_PHRASE, "select * from " + MyCallBack.TABLE_NAME_PHRASE, null);
// 插入一个数据到数据库
Phrase phrase = new Phrase("青青子衿,悠悠我心");
LouSQLite.insert(MyCallBack.TABLE_NAME_PHRASE, phrase);
// 插入一组数据
List<Phrase> lists = Arrays.asList(
new Phrase("窈窕淑女,君子好逑"),
new Phrase("海上生明月,天涯共此时"),
new Phrase("青青子衿,悠悠我心"),
new Phrase("人生若只如初见")
);
LouSQLite.insert(MyCallBack.TABLE_NAME_PHRASE, lists);
// 更新到数据库
phrase.setContent(phrase.getContent() + " 嘿嘿嘿");
LouSQLite.update(MyCallBack.TABLE_NAME_PHRASE, phrase, PhraseEntry.COLEUM_NAME_ID + "=?", new String[]{phrase.getId()});
// 从数据库中删除
LouSQLite.delete(MyCallBack.TABLE_NAME_PHRASE, MyCallBack.COLEUM_NAME_ID + "=?", new String[]{phrase.getId()});
【数据库定制】
//: MyCallBack.java
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import java.util.Arrays;
import java.util.List;
public class MyCallBack implements LouSQLite.ICallBack {
public static final String TABLE_NAME_PHRASE = "phrase";
public static final String TABLE_NAME_FAVORITE = "favorite";
private static final String DATABASE_NAME = "ledfan.db";
private static final int DATABASE_VERSION = 1;
private static final String TYPE_TEXT = " TEXT";
private static final String TYPE_INTEGER = " INTEGER";
private static final String SEP_COMMA = ",";
private static final String TABLE_SCHEMA_PHRASE =
"CREATE TABLE " + TABLE_NAME_PHRASE + " (" +
PhraseEntry._ID + TYPE_INTEGER + " PRIMARY KEY AUTOINCREMENT, " +
PhraseEntry.COLEUM_NAME_ID + TYPE_TEXT + SEP_COMMA +
PhraseEntry.COLEUM_NAME_CONTENT + TYPE_TEXT + SEP_COMMA +
PhraseEntry.COLEUM_NAME_FAVORITE + TYPE_INTEGER +
")";
private static final String TABLE_SCHEMA_FAVORITE =
"CREATE TABLE " + TABLE_NAME_FAVORITE + " (" +
PhraseEntry._ID + TYPE_INTEGER + " PRIMARY KEY AUTOINCREMENT, " +
PhraseEntry.COLEUM_NAME_ID + TYPE_TEXT + SEP_COMMA +
PhraseEntry.COLEUM_NAME_CONTENT + TYPE_TEXT + SEP_COMMA +
PhraseEntry.COLEUM_NAME_FAVORITE + TYPE_INTEGER +
")";
public MyCallBack() {
}
@Override
public List<String> createTablesSQL() {
return Arrays.asList(
TABLE_SCHEMA_PHRASE,
TABLE_SCHEMA_FAVORITE
);
}
@Override
public String getDatabaseName() {
return DATABASE_NAME;
}
@Override
public int getVersion() {
return DATABASE_VERSION;
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
switch (oldVersion) {
case 0:
db.execSQL(TABLE_SCHEMA_FAVORITE); // 升级操作;
case 1:
break;
default:
break;
}
}
@Override
public <T> void assignValuesByEntity(String tableName, T t, ContentValues values) {
switch (tableName) {
case TABLE_NAME_PHRASE:
if (t instanceof Phrase) {
Phrase phrase = (Phrase) t;
values.put(PhraseEntry.COLEUM_NAME_ID, phrase.getId());
values.put(PhraseEntry.COLEUM_NAME_CONTENT, phrase.getContent());
values.put(PhraseEntry.COLEUM_NAME_FAVORITE, phrase.getFavorite());
}
break;
case TABLE_NAME_FAVORITE:
if (t instanceof Phrase) {
Phrase phrase = (Phrase) t;
values.put(PhraseEntry.COLEUM_NAME_ID, phrase.getId());
values.put(PhraseEntry.COLEUM_NAME_CONTENT, phrase.getContent());
values.put(PhraseEntry.COLEUM_NAME_FAVORITE, phrase.getFavorite());
}
break;
}
}
@Override
public Object newEntityByCursor(String tableName, Cursor cursor) {
switch (tableName) {
case TABLE_NAME_PHRASE:
return new Phrase(
cursor.getString(cursor.getColumnIndex(PhraseEntry.COLEUM_NAME_ID)),
cursor.getString(cursor.getColumnIndex(PhraseEntry.COLEUM_NAME_CONTENT)),
cursor.getInt(cursor.getColumnIndex(PhraseEntry.COLEUM_NAME_FAVORITE))
);
case TABLE_NAME_FAVORITE:
return new Phrase(
cursor.getString(cursor.getColumnIndex(PhraseEntry.COLEUM_NAME_ID)),
cursor.getString(cursor.getColumnIndex(PhraseEntry.COLEUM_NAME_CONTENT)),
cursor.getInt(cursor.getColumnIndex(PhraseEntry.COLEUM_NAME_FAVORITE))
);
}
return null;
}
}
//: Pharse.java
import java.util.UUID;
public class Phrase {
private String mId; // 短语的唯一标识
private String mContent; // 短语的内容
private int mFavorite; // 是否是收藏状态:0表示未收藏,1表示已收藏;
public Phrase(String content) {
this(UUID.randomUUID().toString(), content, 0);
}
public Phrase(String content, int favorite) {
this(UUID.randomUUID().toString(), content, favorite);
}
public Phrase(String id, String content, int favorite) {
mId = id;
mContent = content;
mFavorite = favorite;
}
public String getId() {
return mId;
}
public void setId(String id) {
mId = id;
}
public String getContent() {
return mContent;
}
public void setContent(String content) {
mContent = content;
}
public int getFavorite() {
return mFavorite;
}
public void setFavorite(int favorite) {
mFavorite = favorite;
}
@Override
public String toString() {
return "\n Phrase id: " + mId
+ " content: " + mContent
+ " favorite: " + mFavorite;
}
}
//: PhraseEntry.java
public class PhraseEntry implements BaseColumns {
public static final String COLEUM_NAME_ID = "id";
public static final String COLEUM_NAME_CONTENT = "content";
public static final String COLEUM_NAME_FAVORITE = "favorite";
}
【通用源码】
//: LouSQLite.java
package com.lyloou.lou.db;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.support.annotation.NonNull;
import android.support.annotation.Nullable;
import android.util.Log;
import java.util.ArrayList;
import java.util.List;
/**
* 一个通用的SQLite,通过简单的配置快速搭建一个数据库存储的方案;
*/
public final class LouSQLite extends SQLiteOpenHelper {
private static final String TAG = "LouSQLite";
private static LouSQLite INSTANCE;
private final ICallBack callBack;
private LouSQLite(Context context, ICallBack callBack) {
super(context, callBack.getDatabaseName(), null, callBack.getVersion());
this.callBack = callBack;
}
public static void init(@NonNull Context context, @NonNull ICallBack callBack) {
INSTANCE = new LouSQLite(context, callBack);
}
public static <T> void insert(String tableName, T entity) {
SQLiteDatabase db = INSTANCE.getWritableDatabase();
db.beginTransaction();
try {
ContentValues values = new ContentValues();
INSTANCE.callBack.assignValuesByEntity(tableName, entity, values);
db.insert(tableName, null, values);
values.clear();
db.setTransactionSuccessful();
} finally {
db.endTransaction();
db.close();
}
}
public static <T> void insert(String tableName, List<T> entities) {
SQLiteDatabase db = INSTANCE.getWritableDatabase();
db.beginTransaction();
try {
ContentValues values = new ContentValues();
for (T entity : entities) {
INSTANCE.callBack.assignValuesByEntity(tableName, entity, values);
db.insert(tableName, null, values);
values.clear();
}
db.setTransactionSuccessful();
} finally {
db.endTransaction();
db.close();
}
}
public static <T> void update(String tableName, T entity, String whereClause, String[] whereArgs) {
SQLiteDatabase db = INSTANCE.getWritableDatabase();
db.beginTransaction();
try {
ContentValues values = new ContentValues();
INSTANCE.callBack.assignValuesByEntity(tableName, entity, values);
db.update(tableName, values, whereClause, whereArgs);
values.clear();
db.setTransactionSuccessful();
} finally {
db.endTransaction();
db.close();
}
}
public static <T> List<T> query(String tableName, @NonNull String queryStr, @Nullable String[] whereArgs) {
SQLiteDatabase db = INSTANCE.getReadableDatabase();
Cursor cursor = db.rawQuery(queryStr, whereArgs);
try {
List<T> lists = new ArrayList<>(cursor.getCount());
if (cursor.moveToFirst()) {
do {
T entity = INSTANCE.callBack.newEntityByCursor(tableName, cursor);
if (entity != null) {
lists.add(entity);
}
} while (cursor.moveToNext());
}
return lists;
} finally {
cursor.close();
db.close();
}
}
public static void deleteFrom(String tableName) {
SQLiteDatabase db = INSTANCE.getWritableDatabase();
db.beginTransaction();
try {
String sql = "DELETE FROM " + tableName;
db.execSQL(sql);
db.setTransactionSuccessful();
} finally {
db.endTransaction();
db.close();
}
}
// delete的适用场合是涉及到删除的对象数量较少时。
// 当删除多条数据时(例如:500条),通过循环的方式来一个一个的删除需要12s,而使用execSQL语句结合(delete from table id in("1", "2", "3"))的方式只需要50ms
public static void delete(String tableName, String whereClause, String[] whereArgs) {
SQLiteDatabase db = INSTANCE.getWritableDatabase();
db.beginTransaction();
try {
db.delete(tableName, whereClause, whereArgs);
db.setTransactionSuccessful();
} finally {
db.endTransaction();
db.close();
}
}
/*
* 当操作数据较多时,直接使用sql语句或许效率更高
*
* 执行sql语句(例如: String sql = "delete from tableName where mac in ('24:71:89:0A:DD:82', '24:71:89:0A:DD:83','24:71:89:0A:DD:84')")
* 注意:db.execSQL文档中有说明"the SQL statement to be executed. Multiple statements separated by semicolons are not supported.",
* 也就是说通过分号分割的多个statement操作是不支持的。
*
*/
public static void execSQL(String sql) {
SQLiteDatabase db = INSTANCE.getWritableDatabase();
db.beginTransaction();
try {
db.execSQL(sql);
db.setTransactionSuccessful();
} finally {
db.endTransaction();
db.close();
}
}
@Override
public void onCreate(SQLiteDatabase db) {
List<String> tablesSQL = callBack.createTablesSQL();
for (String create_table : tablesSQL) {
db.execSQL(create_table);
Log.d(TAG, "create table " + "[ \n" + create_table + "\n ]" + " successful! ");
}
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
callBack.onUpgrade(sqLiteDatabase, oldVersion, newVersion);
}
public interface ICallBack {
String getDatabaseName();
int getVersion();
void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion);
List<String> createTablesSQL();
<T> void assignValuesByEntity(String tableName, T entity, ContentValues values);
<T> T newEntityByCursor(String tableName, Cursor cursor);
}
}