【Android】通用系列 —— 数据持久化（通用的SQLite封装）

最新推荐文章于 2023-09-19 12:38:26 发布

lyloou

最新推荐文章于 2023-09-19 12:38:26 发布

阅读量5.8k

点赞数 1

分类专栏： Android 文章标签： android 数据库 sqlite 数据存储

本文链接：https://blog.csdn.net/ly1414725328/article/details/51901972

版权

Android 专栏收录该内容

35 篇文章 0 订阅

订阅专栏

关键字

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

案例

lyloou

关注

1
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
【Android】通用系列 —— 数据持久化（通用的SQLite封装）

关键字SQL SQLite 通用系列数据存储摘要：主要内容：本文介绍了封装后的SQLite，以便快速开发；只保留必要的定制，重复的代码封装到一个通用类中；说明LouSQLite.java文件为通用的代码，所有项目中不需要修改即可使用；MyCallBack.java文件是自定义的文件，关于项目的数据库配置都在这里进行，例如：数据库名称、数据库版本号、table语句等；
复制链接

扫一扫

专栏目录