Flutter数据库Sqflite

• sqflite是Flutter的SQLite插件，支持iOS和Android，目前官方版本是sqflite1.1.6+1
• sqflite插件地址：https://pub.dartlang.org/packages/sqflite#-readme-tab-
• sqflite支持事务和批处理
• sqflite支持打开期间自动版本管理
• sqflite支持插入/查询/更新/删除查询的助手
• sqflite支持在iOS和Android上的后台线程中执行数据库操作

1.首选需要在pubspec.yaml 导入库

  #https://pub.dev/packages/sqflite 数据库管理 相当于sqllite
  sqflite: ^1.1.6+1

2.创建一个sql 管理器

import 'package:sqflite/sqflite.dart';

import 'package:path/path.dart';
/**
 * Created with IntelliJ IDEA.
 * Package: db
 * Author: sirai
 * Create Time: 2019-06-27 15:16
 * QQ: 785716471
 * Email: 785716471@qq.com
 * Description:数据库管理
 */

class SqlManager{

  static const _VERSION=1;

  static const _NAME="qss.db";

  static Database _database;


  ///初始化
  static init() async {
    var databasesPath=await getDatabasesPath();

    String path = join(databasesPath, _NAME);

    _database=await openDatabase(path,version: _VERSION,onCreate: (Database db,int version) async{});
  }

  ///判断表是否存在
  static isTableExits(String tableName) async {
    await getCurrentDatabase();
    var res=await _database.rawQuery("select * from Sqlite_master where type = 'table' and name = '$tableName'");
    return res!=null && res.length >0;
  }

  ///获取当前数据库对象
  static Future<Database> getCurrentDatabase() async {
    if(_database == null){
      await init();
    }
    return _database;
  }

  ///关闭
  static close() {
    _database?.close();
    _database = null;
  }
}

3. 创建一个父类 主要应用于 获取表名 判断表是否存在等

import 'package:bigtoe/db/sql_manager.dart';
import 'package:sqflite/sqflite.dart';
import 'package:meta/meta.dart';

/**
 * Created with IntelliJ IDEA.
 * Package: db
 * Author: sirai
 * Create Time: 2019-06-27 15:29
 * QQ: 785716471
 * Email: 785716471@qq.com
 * Description:数据库表
 */

abstract class BaseDbProvider {
  bool isTableExits = false;

  createTableString();

  tableName();

  ///创建表sql语句
  tableBaseString(String sql) {
    return sql;
  }

  Future<Database> getDataBase() async {
    return await open();
  }

  ///super 函数对父类进行初始化
  @mustCallSuper
  prepare(name, String createSql) async {
    isTableExits = await SqlManager.isTableExits(name);
    if (!isTableExits) {
      Database db = await SqlManager.getCurrentDatabase();
      return await db.execute(createSql);
    }
  }

  @mustCallSuper
  open() async {
    if (!isTableExits) {
      await prepare(tableName(), createTableString());
    }
    return await SqlManager.getCurrentDatabase();
  }

}

4.新建一个表 里边包含 增删改查 等方法

import 'package:bigtoe/model/user_model.dart';
import 'package:sqflite/sqlite_api.dart';

import '../sql_provider.dart';

/**
 * Created with IntelliJ IDEA.
 * Package: db.provider
 * Author: sirai
 * Create Time: 2019-06-28 17:27
 * QQ: 785716471
 * Email: 785716471@qq.com
 * Description:
 */

class PersonDbProvider extends BaseDbProvider{
  ///表名
  final String name = 'PresonInfo';

  final String columnId="id";
  final String columnMobile="mobile";
  final String columnHeadImage="headImage";


  PersonDbProvider();

  @override
  tableName() {
    return name;
  }

  @override
  createTableString() {
   return '''
        create table $name (
        $columnId integer primary key,$columnHeadImage text not null,
        $columnMobile text not null)
      ''';
  }

  ///查询数据库
  Future _getPersonProvider(Database db, int id) async {
    List<Map<String, dynamic>> maps =
    await db.rawQuery("select * from $name where $columnId = $id");
    return maps;
  }

  ///插入到数据库
  Future insert(UserModel model) async {
    Database db = await getDataBase();
    var userProvider = await _getPersonProvider(db, model.id);
    if (userProvider != null) {
      ///删除数据
      await db.delete(name, where: "$columnId = ?", whereArgs: [model.id]);
    }
    return await db.rawInsert("insert into $name ($columnId,$columnMobile,$columnHeadImage) values (?,?,?)",[model.id,model.mobile,model.headImage]);
  }

  ///更新数据库
  Future<void> update(UserModel model) async {
    Database database = await getDataBase();
    await database.rawUpdate(
        "update $name set $columnMobile = ?,$columnHeadImage = ? where $columnId= ?",[model.mobile,model.headImage,model.id]);

  }


  ///获取事件数据
  Future<UserModel> getPersonInfo(int id) async {
    Database db = await getDataBase();
    List<Map<String, dynamic>> maps  = await _getPersonProvider(db, id);
    if (maps.length > 0) {
      return UserModel.fromJson(maps[0]);
    }
    return null;
  }
}

4. 使用方法

  static insert() async{
    PersonDbProvider provider = new PersonDbProvider();
    UserModel userModel= UserModel();
    userModel.id=1143824942687547394;
    userModel.mobile="15801071158";
    userModel.headImage="http://www.img";
    provider.insert(userModel);
  }


  static update() async{
    PersonDbProvider provider = new PersonDbProvider();
    UserModel userModel= await provider.getPersonInfo(1143824942687547394);
    userModel.mobile="15801071157";
    userModel.headImage="http://www.img1";
    provider.update(userModel);
  }