flutter数据库sqflite使用方法，完整demo(增删改查,事务，批处理)

1、在pubspec.yaml中导入sqflitesqflite:^1.1.52、flutter packages get如果是用vs code开发，直接ctrl -s 保存就行了3、sqflite的增删改查有两种方式，一种是原生的，一直是sql助手的，个人建议用sql助手的，相对更安全一点下面文件依次为：建表sql语句文件、启动时创建表的文件、工具类文件：增删改查，事务，批量执行、调用代码de

小宇

9134人浏览 · 2020-05-13 15:27:12

小宇 · 2020-05-13 15:27:12 发布

代码运行后是这样的：

flutter sqflite:

1、在pubspec.yaml中导入sqflite

sqflite: ^1.1.5

2、flutter packages get

如果是用vs code开发，直接ctrl -s 保存就行了

3、sqflite的增删改查有两种方式，一种是原生的，一直是sql助手的，个人建议用sql助手的，相对更安全一点

下面文件依次为：建表sql语句文件、启动时创建表的文件、工具类文件：增删改查，事务，批量执行、调用代码demo文件

4、我的思路是在一个文件中写了需要用到的所有表，软件启动的时候去检查是否有没创建的，如果有没创建的，则创建

表的文件为：

///数据表定义
class CreateTableSqls{
 //关系表语句
  static final String createTableSql_relation = '''
    CREATE TABLE IF NOT EXISTS relation (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, 
    uid BIGINT(20) NOT NULL, 
    fuid BIGINT(20) NOT NULL,
    type INTEGER(3) NOT NULL);
    ''';
  //用户表语句
  static final String createTableSql_user = '''
    CREATE TABLE IF NOT EXISTS user (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, 
    uid BIGINT(20) NOT NULL, 
    phonenumber TEXT(30) NOT NULL, 
    nickName TEXT(100));
    ''';

  Map<String,String> getAllTables(){
    Map<String,String> map = Map<String,String>();
    map['relation'] = createTableSql_relation;
    map['user'] = createTableSql_user;
  
    return map;
  }
}

软件启动时检查和创建表的文件为：软件启动时调用下面的init()方法即可完成所有表的创建

import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
import 'CreateTableSqls.dart';
import 'Constants.dart' as Constants;


class TablesInit{
  static Database db;
  
  Future init() async{
    String databasePath = await getDatabasesPath();
    String path = join(databasePath,Constants.DB_NAME);
    print('数据库存储路径path:'+path);
     //所有的sql语句
    CreateTableSqls  sqlTables = CreateTableSqls();
    //所有的sql语句
    Map<String,String> allTableSqls = sqlTables.getAllTables();
    try {
      db = await openDatabase(path);
    } catch (e) {
      print('CreateTables init Error $e');
    }
    //检查需要生成的表
    List<String> noCreateTables = await getNoCreateTables(allTableSqls);
    print('noCreateTables:'+noCreateTables.toString());
    if (noCreateTables.length>0) {
      //创建新表
      // 关闭上面打开的db，否则无法执行open
      db.close();
      db = await openDatabase(
        path,
        version: 1,
        onCreate: (Database db,int version) async{

            print('db created version is $version');
        },
        onOpen: (Database db)async{
            noCreateTables.forEach((sql) async{
               await db.execute(allTableSqls[sql]);
            });
            print('db补完表已打开');
        });
    }else{
      print("表都存在，db已打开");
    }
    List tableMaps = await db.rawQuery('SELECT name FROM sqlite_master WHERE type = "table"');
    print('所有表:'+tableMaps.toString());
    db.close();
    print("db已关闭");
  }
  // 检查数据库中是否有所有有表,返回需要创建的表 
  Future<List<String>> getNoCreateTables(Map<String,String> tableSqls) async {
    Iterable<String> tableNames = tableSqls.keys; 
    //已经存在的表
    List<String> existingTables = List<String>();
    //要创建的表
    List<String> createTables = List<String>();
    List tableMaps = await db.rawQuery('SELECT name FROM sqlite_master WHERE type = "table"');
    print('tableMaps:'+tableMaps.toString());
    tableMaps.forEach((item){
      existingTables.add(item['name']);
    });
    tableNames.forEach((tableName){
      if(!existingTables.contains(tableName)){
        createTables.add(tableName);
      }
    });
    return createTables;
  }

}

5:工具类文件：其实sqflite已经分装的很好了，我加了一层，一是为了加一点注释和调用样例，方便别人使用，二是以后方法有改动，直接改工具类就可以了

import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
import 'Constants.dart' as Constants;

///sqflite数据库操作工具类
class DBUtil {
  Database db;

  ///sql助手插入 @tableName:表名  @paramters：参数map
  Future<int> insertByHelper(String tableName,Map<String,Object> paramters) async {
    return await db.insert(tableName, paramters);
  }
  ///sql原生插入 
  Future<int> insert(String sql,List paramters) async {
    //调用样例： dbUtil.insert('INSERT INTO Test(name, value) VALUES(?, ?)',['another name', 12345678]);
    return await db.rawInsert(sql,paramters);
  }

  ///sql助手查找列表  @tableName:表名  @selects 查询的字段数组 @wheres 条件，如：'uid=? and fuid=?' @whereArgs 参数数组
  Future<List<Map>> queryListByHelper(String tableName,List<String> selects,String whereStr,List whereArgs) async {
    //调用样例：await dbUtil.queryListByHelper('relation', ['id','uid','fuid'], 'uid=? and fuid=?', [6,1]);
    List<Map> maps = await db.query(
        tableName,
        columns: selects,
        where: whereStr,
        whereArgs: whereArgs);
    return maps;
  }

  ///sql原生查找列表
  Future<List<Map>> queryList(String sql) async {
    return await db.rawQuery(sql);
  }

  ///sql助手修改
  Future<int> updateByHelper(String tableName,Map<String,Object> setArgs,String whereStr,List whereArgs) async {
  //样例：
  //Map<String,Object> par = Map<String,Object>();
  //par['fuid'] = 1;
  //dbUtil.updateByHelper('relation', par, 'type=? and uid=?', [0,5]);
   return await db.update(
        tableName, 
        setArgs,
        where: whereStr, 
        whereArgs: whereArgs);
  }

  ///sql原生修改
  Future<int> update(String sql,List paramters) async {
     //样例：dbUtil.update('UPDATE relation SET fuid = ?, type = ? WHERE uid = ?', [1,2,3]);
     return await db.rawUpdate(sql,paramters);
  }

  ///sql助手删除   刪除全部whereStr和whereArgs传null
  Future<int> deleteByHelper(String tableName,String whereStr,List whereArgs) async {
    return await db.delete(
        tableName, 
        where: whereStr, 
        whereArgs: whereArgs);
  }

  ///sql原生删除
  Future<int> delete(String sql,List parameters) async {
    //样例： 样例：await dbUtil.delete('DELETE FROM relation WHERE uid = ? and fuid = ?', [123,234]);
    return await db.rawDelete(sql, parameters);
  }

  ///获取Batch对象，用于执行sql批处理
  Future<Batch> getBatch() async{
    //调用样例
    //  Batch batch = await DBUtil().getBatch();
    //  batch.insert('Test', {'name': 'item'});
    //  batch.update('Test', {'name': 'new_item'}, where: 'name = ?', whereArgs: ['item']);
    //  batch.delete('Test', where: 'name = ?', whereArgs: ['item']);
    //  List<Object> results = await batch.commit();  //返回的是id数组
    //                         //batch.commit(noResult: true);//noResult: true不关心返回结果，性能高
    //                         //batch.commit(continueOnError: true)//continueOnError: true  忽略错误，错误可继续执行
    return db.batch();
  }

  ///事务控制
  Future<dynamic> transaction(Future<dynamic> Function(Transaction txn) action)async{
    //调用样例
    //  try {
    //     await dbUtil.transaction((txn) async {
    //        Map<String,Object> par = Map<String,Object>();
    //        par['uid'] = Random().nextInt(10); par['fuid'] = Random().nextInt(10);
    //        par['type'] = Random().nextInt(2); par['id'] = 1;
    //        var a = await txn.insert('relation', par);
    //        var b = await txn.insert('relation', par);
    //   });
    //   } catch (e) {
    //     print('sql异常:$e');
    //   }
     return await db.transaction(action);
  }
  

  //打开DB
  open() async {
    String databasePath = await getDatabasesPath();
    String path = join(databasePath,Constants.DB_NAME);
    print('数据库存储路径path:'+path);
    try {
       db = await openDatabase(path);
       print('DB open');
    } catch (e) {
      print('DBUtil open() Error $e');
    }
  }

  // 记得及时关闭数据库，防止内存泄漏
  close() async {
    await db.close();
    print('DB close');
  }
}

6：调用样例

import 'dart:math';

import 'package:flutter/material.dart';
import 'package:sqflite/sqflite.dart';
import 'TablesInit.dart';
import 'DBUtil.dart';
void main() => runApp(MyApp());

class MyApp extends StatelessWidget{
  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      title: 'Demo',
      theme: ThemeData(
        primarySwatch:Colors.blue,
      ),
      home: MyHomePage(title:'数据操作'),
    );
  }
}

class MyHomePage extends StatefulWidget{
  MyHomePage({Key key,this.title}) :super(key:key);
  @override
  _MyHomePageState createState() => _MyHomePageState();
  final String title;

}

class _MyHomePageState extends State<MyHomePage>{
  var dataList = "";
  DBUtil dbUtil = null;
 @override
  void initState() {
    super.initState();
    initDB();
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
        appBar: AppBar(
          title: Text("数据操作"),
          actions: <Widget>[
            IconButton(
              icon: Icon(Icons.settings),
              onPressed: (){

              },
            )
          ],
        ),
        body: new Container(
          alignment: Alignment.center,
          child:Column(
            //mainAxisAlignment: MainAxisAlignment.center,
            children: <Widget>[
              Row(
                mainAxisAlignment: MainAxisAlignment.center,
                children: <Widget>[
                // SizedBox(width: 400),
                  RaisedButton.icon(
                      icon: Icon(Icons.search),
                      label: Text('查询'),
                      color: Colors.blue,
                      textColor: Colors.white,
                      // onPressed: null,
                      onPressed: () {
                        print("查询数据");
                        queryData();
                        
                      }),
                  SizedBox(width: 10),
                  RaisedButton(
                    padding: new EdgeInsets.fromLTRB(10.0, 10.0, 10.0, 10.0),
                    child: Text('插入'),
                    color: Colors.blue,
                    textColor: Colors.white,
                    onPressed: () {
                      print("插入");
                      insertData();
                    },
                  ),
                  SizedBox(width: 10),
                  RaisedButton(
                    child: Text('清除'),
                    color: Colors.blue,
                    textColor: Colors.white,
                    onPressed: () {
                      print("清除");
                      delete();
                    },
                  ),
                  SizedBox(width: 10),
                  RaisedButton(
                    child: Text('修改'),
                    color: Colors.blue,
                    textColor: Colors.white,
                    elevation: 20,
                    onPressed: () {
                      print("修改");
                      update();
                    },
                  ),
                  
                ],
              ),Row(
                mainAxisAlignment: MainAxisAlignment.center,
                children: <Widget>[
                // SizedBox(width: 400),
                  RaisedButton.icon(
                      icon: Icon(Icons.search),
                      label: Text('批量插入'),
                      color: Colors.blue,
                      textColor: Colors.white,
                      // onPressed: null,
                      onPressed: () {
                        print("批量插入");
                        batchDispose();
                        
                      }),
                  SizedBox(width: 10),
                  RaisedButton(
                    padding: new EdgeInsets.fromLTRB(10.0, 10.0, 10.0, 10.0),
                    child: Text('事务控制'),
                    color: Colors.blue,
                    textColor: Colors.white,
                    onPressed: () {
                      print("事务控制");
                      transaction();
                    },
                  ),
                  SizedBox(width: 10),
                  RaisedButton(
                    child: Text(''),
                    color: Colors.blue,
                    textColor: Colors.white,
                    onPressed: () {
                      print("");
                      delete();
                    },
                  ),
                ],
              ),
              new Container(
                child: Column(
                mainAxisAlignment: MainAxisAlignment.center,
                children: <Widget>[
                  Text(
                    "数据:",
                  ),
                  Text(
                    dataList,
                  )
          ],
        ),
              )
            ],
        ),
        ),
        
        );
  }
  void initDB() async {
    TablesInit tables = TablesInit();
    tables.init();
    dbUtil = new DBUtil();
  }
  void insertData() async {
    await dbUtil.open();
    Map<String,Object> par = Map<String,Object>();
    par['uid'] = Random().nextInt(10);
    par['fuid'] = Random().nextInt(10);
    par['type'] = Random().nextInt(2);
    int flag = await dbUtil.insertByHelper('relation', par);
    //int flag = await dbUtil.insert('INSERT INTO relation(uid, fuid, type) VALUES("111111", "2222222", 1)');
    print('flag:$flag');
    await dbUtil.close();
    queryData();
  }

  void delete() async{
    await dbUtil.open();
    dbUtil.delete('DELETE FROM relation', null);
    //dbUtil.deleteByHelper('relation','uid=? and fuid=?',[1,6]);
    await dbUtil.close();
    queryData();
  }

  void update() async{
    await dbUtil.open();
    //dbUtil.update('UPDATE relation SET fuid = ?, type = ? WHERE uid = ?', [Random().nextInt(10),Random().nextInt(10),5]);
    Map<String,Object> par = Map<String,Object>();
    par['fuid'] = Random().nextInt(10);
    dbUtil.updateByHelper('relation', par, 'type=? and uid=?', [0,5]);
    await dbUtil.close();
    queryData();
  }

  void queryData() async{
    await dbUtil.open();
    List<Map> data = await dbUtil.queryList("SELECT * FROM relation");
    //List<Map> data = await dbUtil.queryListByHelper('relation', ['id','uid','fuid','type'], 'uid=?', [5]);
    print('data：$data');
    String showdata = "";
    if(data == null){
      showdata = "";
    }else{
      showdata = data.toString();
    }
    setState(() {
      dataList = showdata;
    });
    await dbUtil.close();
  }

  //批量处理
  void batchDispose() async{
    await dbUtil.open();
    Batch batch = await dbUtil.getBatch();
    Map<String,Object> par = Map<String,Object>();
    for(int n=0; n<5;n++){
        par['uid'] = Random().nextInt(10);
        par['fuid'] = Random().nextInt(10);
        par['type'] = Random().nextInt(2);
        batch.insert('relation', par);
    }
     List<Object> results = await batch.commit(); 
     print('results:$results');//返回的是id
                            
    await dbUtil.close();
    queryData();
  }

  //事务控制
  void transaction() async{
      await dbUtil.open();
      try {
        await dbUtil.transaction((txn) async {
           Map<String,Object> par = Map<String,Object>();
           par['uid'] = Random().nextInt(10); par['fuid'] = Random().nextInt(10);
           par['type'] = Random().nextInt(2); par['id'] = 1;
           var a = await txn.insert('relation', par);
           var b = await txn.insert('relation', par);
      });
      } catch (e) {
        print('sql异常:$e');
      }
      await dbUtil.close();
      queryData();
  }
}

7：运行起来就是这样：

页面很简单，就是那几个方法的调用

上面也是完整的代码

git地址：https://github.com/jack-xiaoyu/flutter_sqflite

感谢浏览，如有不当之处还望指出~~~