Flutter数据库Sqflite之增删改查-白红宇

简介

sqflite是Flutter的SQLite插件，支持iOS和Android，目前官方版本是

sqflite插件地址：

sqflite支持事务和批处理

sqflite支持打开期间自动版本管理

sqflite支持插入/查询/更新/删除查询的助手

sqflite支持在iOS和Android上的后台线程中执行数据库操作

更多Flutter相关内容可以访问我的

关键API

获取数据库的路径

var databasesPath = await getDatabasesPath();String path = join(databasesPath, 'demo.db');复制代码

打开数据库

Database database = await openDatabase(path, version: 1,    onCreate: (Database db, int version) async {  // When creating the db, create the table  await db.execute(      'CREATE TABLE Test (id INTEGER PRIMARY KEY, name TEXT, value INTEGER, num REAL)');});复制代码

使用事务插入一条记录

await database.transaction((txn) async {  int id1 = await txn.rawInsert(      'INSERT INTO Test(name, value, num) VALUES("some name", 1234, 456.789)');  print('inserted1: $id1');  int id2 = await txn.rawInsert(      'INSERT INTO Test(name, value, num) VALUES(?, ?, ?)',      ['another name', 12345678, 3.1416]);  print('inserted2: $id2');});复制代码

更新一条记录

int count = await database.rawUpdate(    'UPDATE Test SET name = ?, VALUE = ? WHERE name = ?',    ['updated name', '9876', 'some name']);print('updated: $count');复制代码

查询记录

List list = await database.rawQuery('SELECT * FROM Test');复制代码

查询总记录数

count = Sqflite.firstIntValue(await database.rawQuery('SELECT COUNT(*) FROM Test'));复制代码

删除一条记录

count = await database.rawDelete('DELETE FROM Test WHERE name = ?', ['another name']);复制代码

关闭数据库

await database.close();复制代码

使用

首先创建model

class User {  String name;  int age;  int id;  Map
    
      toMap() {    var map = new Map
     
      ();    map['name'] = name;    map['age'] = age;    map['id'] = id;    return map;  }  static User fromMap(Map
      
        map) {    User user = new User();    user.name = map['name'];    user.age = map['age'];    user.id = map['id'];    return user;  }  static List
       
         fromMapList(dynamic mapList) {    List
        
          list = new List(mapList.length);    for (int i = 0; i < mapList.length; i++) {      list[i] = fromMap(mapList[i]);    }    return list;  }}复制代码

创建db_helper，数据库帮助类

class DatabaseHelper {  static final DatabaseHelper _instance = DatabaseHelper.internal();  factory DatabaseHelper() => _instance;  final String tableName = "table_user";  final String columnId = "id";  final String columnName = "name";  final String columnAge = "age";  static Database _db;  Future
    
      get db async {    if (_db != null) {      return _db;    }    _db = await initDb();    return _db;  }  DatabaseHelper.internal();  initDb() async {    var databasesPath = await getDatabasesPath();    String path = join(databasesPath, 'sqflite.db');    var ourDb = await openDatabase(path, version: 1, onCreate: _onCreate);    return ourDb;  }  //创建数据库表  void _onCreate(Database db, int version) async {    await db.execute(        "create table $tableName($columnId integer primary key,$columnName text not null ,$columnAge integer not null )");    print("Table is created");  }//插入  Future
     
       saveItem(User user) async {    var dbClient = await db;    int res = await dbClient.insert("$tableName", user.toMap());    print(res.toString());    return res;  }  //查询  Future
      
        getTotalList() async {    var dbClient = await db;    var result = await dbClient.rawQuery("SELECT * FROM $tableName ");    return result.toList();  }  //查询总数  Future
       
         getCount() async {    var dbClient = await db;    return Sqflite.firstIntValue(await dbClient.rawQuery(        "SELECT COUNT(*) FROM $tableName"    ));  }//按照id查询  Future
        
          getItem(int id) async {    var dbClient = await db;    var result = await dbClient.rawQuery("SELECT * FROM $tableName WHERE id = $id");    if (result.length == 0) return null;    return User.fromMap(result.first);  }  //清空数据  Future
         
           clear() async { var dbClient = await db; return await dbClient.delete(tableName); } //根据id删除 Future
          
            deleteItem(int id) async { var dbClient = await db; return await dbClient.delete(tableName, where: "$columnId = ?", whereArgs: [id]); } //修改 Future
           
             updateItem(User user) async { var dbClient = await db; return await dbClient.update("$tableName", user.toMap(), where: "$columnId = ?", whereArgs: [user.id]); } //关闭 Future close() async { var dbClient = await db; return dbClient.close(); }}复制代码

在进行页面增删该查操作

class DataAppPage extends StatefulWidget {  @override  State
    
      createState() {    return new _DataAppPageState();  }}class _DataAppPageState extends State
     
       {  List
      
        _datas = new List();  var db = DatabaseHelper();  Future
       
         _refresh() async {    _query();  }  @override  void initState() {    super.initState();    _getDataFromDb();  }  _getDataFromDb() async {    List datas = await db.getTotalList();    if (datas.length > 0) {      //数据库有数据      datas.forEach((user) {        User item = User.fromMap(user);        _datas.add(item);      });    } else {      //数据库没有数据      User user = new User();      user.name = "张三";      user.age = 10;      user.id = 1;      User user2 = new User();      user2.name = "李四";      user2.age = 12;      user2.id = 2;      await db.saveItem(user);      await db.saveItem(user2);      _datas.add(user);      _datas.add(user2);    }    setState(() {});  }//添加  Future
        
          _add() async {    User user = new User();    user.name = "我是增加的";    user.age = 33;    await db.saveItem(user);    _query();  }//删除,默认删除第一条数据  Future
         
           _delete() async { List datas = await db.getTotalList(); if (datas.length > 0) { //修改第一条数据 User user = User.fromMap(datas[0]); db.deleteItem(user.id); _query(); } }//修改，默认修改第一条数据 Future
          
            _update() async { List datas = await db.getTotalList(); if (datas.length > 0) { //修改第一条数据 User u = User.fromMap(datas[0]); u.name = "我被修改了"; db.updateItem(u); _query(); } }//查询 Future
           
             _query() async { _datas.clear(); List datas = await db.getTotalList(); if (datas.length > 0) { //数据库有数据 datas.forEach((user) { User dataListBean = User.fromMap(user); _datas.add(dataListBean); }); } setState(() {}); } @override Widget build(BuildContext context) { return new Scaffold( appBar: AppBar( title: Text("sqflite学习"), centerTitle: true, actions: 
            
             [ new PopupMenuButton( onSelected: (String value) { switch (value) { case "增加": _add(); break; case "删除": _delete(); break; case "修改": _update(); break; case "查询": _query(); break; } }, itemBuilder: (BuildContext context) => 
             
              
               >[ new PopupMenuItem(value: "增加", child: new Text("增加")), new PopupMenuItem(value: "删除", child: new Text("删除")), new PopupMenuItem(value: "修改", child: new Text("修改")), new PopupMenuItem(value: "查询", child: new Text("查询")), ]) ], ), body: RefreshIndicator( displacement: 15, onRefresh: _refresh, child: ListView.separated( itemBuilder: _renderRow, physics: new AlwaysScrollableScrollPhysics(), separatorBuilder: (BuildContext context, int index) { return Container( height: 0.5, color: Colors.black38, ); }, itemCount: _datas.length), ), ); } Widget _renderRow(BuildContext context, int index) { return Column( crossAxisAlignment: CrossAxisAlignment.start, children: 
               
                [ Padding( padding: EdgeInsets.all(5), child: Text("姓名：" + _datas[index].name)), Padding( padding: EdgeInsets.all(5), child: Text("年龄：" + _datas[index].age.toString())), ], ); }}复制代码