Flutter數(shù)據(jù)庫封裝

SQLite

如果您正在編寫一個(gè)需要持久化且查詢大量本地設(shè)備數(shù)據(jù)的 app，可考慮采用數(shù)據(jù)庫，而不是本地文件夾或關(guān)鍵值庫?？偟膩碚f，相比于其他本地持久化方案來說，數(shù)據(jù)庫能夠提供更為迅速的插入、更新、查詢功能。

Flutter應(yīng)用程序中可以通過 sqflite package 來使用 SQLite 數(shù)據(jù)庫。本文將通過使用 sqflite 來演示插入，讀取，更新，刪除各種數(shù)據(jù)。

如果你對于 SQLite 和 SQL 的各種語句還不熟悉，請查看 SQLite 官方的教程 SQLite 教程，在查看本文之前需要掌握基本的SQL語句。

添加sqflite依賴

dependencies:
  flutter:
    sdk: flutter
  sqflite:
  path:

封裝

MessageDB

MessageDB以單例實(shí)現(xiàn)，提供以下功能。

  • 數(shù)據(jù)庫初始化
  • 表初始化
  • 數(shù)據(jù)庫打開關(guān)閉
  • 是持有表對象【SessionListTable、SessionChatTable、】
class MessageDB {
  // 單例公開訪問點(diǎn)
  factory MessageDB() => _sharedInstance();

  static MessageDB get instance => _sharedInstance();

  // 靜態(tài)私有成員，沒有初始化
  static MessageDB _instance;

  // 私有構(gòu)造函數(shù)
  MessageDB._() {
    // 具體初始化代碼
  }

  // 靜態(tài)、同步、MessageDB
  static MessageDB _sharedInstance() {
    if (_instance == null) {
      _instance = MessageDB._();
    }
    return _instance;
  }

  /// init db
  Future<Database> database;
  final SessionListTable sessionListTable = SessionListTable();
  final SessionChatTable sessionChatTable = SessionChatTable();

  initDB() async {
    try {
      String uid = UserManager.instance.userInfo.uid;
      database = openDatabase(
        // Set the path to the database.
        // 需要升級可以直接修改數(shù)據(jù)庫名稱
        join(await getDatabasesPath(), 'message_database_v1_$uid.db'),
        // When the database is first created, create a table to store dogs.
        onCreate: (db, version) {
          // Run the CREATE TABLE statement on the database.
          try {
            Batch batch = db.batch();
            _createTable().forEach((element) {
              batch.execute(element);
            });
            batch.commit();
          } catch (err) {
            throw (err);
          }
        },
        onUpgrade: (Database db, int oldVersion, int newVersion) {
          try {
            Batch batch = db.batch();
            _createTable().forEach((element) {
              batch.execute(element);
            });
            batch.commit();
          } catch (err) {
            throw (err);
          }
        },
        // Set the version. This executes the onCreate function and provides a
        // path to perform database upgrades and downgrades.
        version: 1,
      );
      sessionListTable.database = database;
      sessionChatTable.database = database;
    } catch (err) {
      throw (err);
    }
  }

  /// close db
  closeDB() async {
    await database
      ..close();
  }

  /// create table
  List<String> _createTable() {
    return [sessionListTable.createTable(), sessionChatTable.createTable()];
  }
}

SessionTableListener

SessionTableListener作為抽象類，提供以下接口。

abstract class SessionTableListener {
  Future<Database> database;

  createTable();

  insertList(List values);

  queryListMaxVersionId({num id});

  queryList({num id});

  deleteItem(num id);
}

SessionListTable和SessionChatTable繼承SessionTableListener實(shí)現(xiàn)對表數(shù)據(jù)的增刪查改操作。

SessionListTable實(shí)現(xiàn)如下。

class SessionListTable implements SessionTableListener {

  @override
  Future<Database> database;

  final String sessionListTable = 'session_list';

  @override
  createTable() {
    return "CREATE TABLE IF NOT EXISTS $sessionListTable ("
        "peer_id INTEGER PRIMARY KEY,"
        " last_msg_id INTEGER,"
        " unread_count INTEGER,"
        " version_id INTEGER,"
        " update_time INTEGER,"
        " last_msg TEXT,"
        " contact_type INTEGER,"
        " contact_user TEXT,"
        " unread_gift_count INTEGER,"
        " del_status INTEGER,"
        " top_weight INTEGER,"
        " sort_key INTEGER,"
        " stick INTEGER)";
  }

  ///插入會話列表
  @override
  insertList(List values) async {
    // Get a reference to the database.
    final Database db = await database;

    // Insert the Dog into the correct table. You might also specify the
    // `conflictAlgorithm` to use in case the same maps is inserted twice.
    //
    // In this case, replace any previous data.
    try {
      Batch batch = db.batch();
      values.forEach((element) {
        Map<String, dynamic> values = Map<String, dynamic>();
        values['peer_id'] = element['peer_id'];
        values['last_msg_id'] = element['last_msg_id'];
        values['unread_count'] = element['unread_count'];
        values['version_id'] = element['version_id'];
        values['update_time'] = element['update_time'];
        values['last_msg'] = element['last_msg'];
        values['contact_type'] = element['contact_type'];
        values['contact_user'] = element['contact_user'];
        values['unread_gift_count'] = element['unread_gift_count'];
        values['del_status'] = element['del_status'];
        values['top_weight'] = element['top_weight'];
        values['sort_key'] = element['sort_key'];
        values['stick'] = element['stick'];

        batch.insert(
          sessionListTable,
          values,
          conflictAlgorithm: ConflictAlgorithm.replace,
        );
      });
      await batch.commit();
    } catch (err) {
      throw err;
    }
  }

  ///查詢會話列表最大的version id
  @override
  queryListMaxVersionId({num id}) async {
    final Database db = await database;
    List<Map> maps = await db.query(sessionListTable, orderBy: 'version_id DESC', limit: 1);
    if (maps.length>0) {
      return maps[0]['version_id'];
    }
    else {
      return 0;
    }
  }

  ///查詢所有的會話列表
  @override
  queryList({num id}) async {
    // Get a reference to the database.
    final Database db = await database;

    //聯(lián)系人類型 0 普通 1 打招呼 100 系統(tǒng)
    // Query the table for all The maps.
    final List<Map<String, dynamic>> maps = await db.query(sessionListTable,
        where: 'del_status = ? AND contact_type != ?',
        whereArgs: [0, 100],
        orderBy: 'sort_key DESC');

    return maps;
  }

  ///會話刪除
  @override
  deleteItem(num id) async {
    // Get a reference to the database.
    final Database db = await database;

    await db.delete(sessionListTable, where: 'peer_id = ?', whereArgs: [id]);
  }

  ///所有會話未讀數(shù)
  querySessionAllUnRead() async {
    // Get a reference to the database.
    final Database db = await database;

    //聯(lián)系人類型 0 普通 1 打招呼 100 系統(tǒng)
    final List<Map<String, dynamic>> maps = await db.query(sessionListTable,
        columns: ['unread_count'],
        where: 'unread_count > ? AND contact_type != ?',
        whereArgs: [0, 100]);
    int count = maps.fold(0, (previousValue, element) => previousValue+element['unread_count']);
    return count;
  }

  ///所有會話標(biāo)記已讀
  updateSessionMarkAllRead() async {
    // Get a reference to the database.
    final Database db = await database;

    await db.update(sessionListTable, {'unread_count':0});
  }

  ///會話標(biāo)記已讀
  updateSessionMarkRead(num id) async {
    // Get a reference to the database.
    final Database db = await database;

    await db.update(sessionListTable, {'unread_count':0}, where: 'peer_id = ?', whereArgs: [id]);
  }

}

問題

異步初始化數(shù)據(jù)庫

//初始化數(shù)據(jù)庫
MessageDB.instance.initDB();

登錄成功之后，初始化數(shù)據(jù)庫為異步操作。同時(shí)會拉取會話列表，這時(shí)可能數(shù)據(jù)庫還未初始化完成。解決辦法，延遲2s后再拉取會話列表。

Future.delayed(Duration(seconds: 2), () {
      MessageDB.instance.sessionChatTable.queryList(id: _peerUser.id)
});

數(shù)據(jù)庫升級

升級表或字段，修改version，會調(diào)用onUpgrade方法，可以在此處升級。

// Set the version. This executes the onCreate function and provides a
// path to perform database upgrades and downgrades.
version: 1,

如果業(yè)務(wù)比較簡單，本地?cái)?shù)據(jù)不是很重要，服務(wù)端會同步數(shù)據(jù)。不如簡單粗暴，直接更改數(shù)據(jù)庫名稱，重新創(chuàng)建數(shù)據(jù)庫。

  database = openDatabase(
    // Set the path to the database.
    // 需要升級可以直接修改數(shù)據(jù)庫名稱
    join(await getDatabasesPath(), 'message_database_v1_$uid.db'),

v1改為v2

添加新的字段

  insertList(List values) async {
    // Get a reference to the database.
    final Database db = await database;

    // Insert the Dog into the correct table. You might also specify the
    // `conflictAlgorithm` to use in case the same maps is inserted twice.
    //
    // In this case, replace any previous data.
    try {
      Batch batch = db.batch();
      values.forEach((element) {
        Map<String, dynamic> values = Map<String, dynamic>();
        values['peer_id'] = element['peer_id'];
        values['last_msg_id'] = element['last_msg_id'];
        values['unread_count'] = element['unread_count'];
        values['version_id'] = element['version_id'];
        values['update_time'] = element['update_time'];
        values['last_msg'] = element['last_msg'];
        values['contact_type'] = element['contact_type'];
        values['contact_user'] = element['contact_user'];
        values['unread_gift_count'] = element['unread_gift_count'];
        values['del_status'] = element['del_status'];
        values['top_weight'] = element['top_weight'];
        values['sort_key'] = element['sort_key'];
        values['stick'] = element['stick'];

        batch.insert(
          sessionListTable,
          values,
          conflictAlgorithm: ConflictAlgorithm.replace,
        );
      });
      await batch.commit();
    } catch (err) {
      throw err;
    }
  }

不要把List values直接傳入table，如果map包含table不存在的數(shù)據(jù)，就會插入失敗。為了保證傳入的map包含的key都存在table的字段，對values的map需要的字段做一次解析，再傳入sql。

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

友情鏈接更多精彩內(nèi)容