Flutter資料庫Sqflite之增刪改查

ngu2008發表於2019-03-29
Flutter資料庫

簡介

  • sqflite是Flutter的SQLite外掛，支援iOS和Android，目前官方版本是sqflite1.1.3
  • sqflite外掛地址：pub.dartlang.org/packages/sq…
  • sqflite支援事務和批處理
  • sqflite支援開啟期間自動版本管理
  • sqflite支援插入/查詢/更新/刪除查詢的助手
  • sqflite支援在iOS和Android上的後臺執行緒中執行資料庫操作
  • 更多Flutter相關內容可以訪問我的Github

關鍵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<Map> 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<String, dynamic> toMap() {
    var map = new Map<String, dynamic>();
    map['name'] = name;
    map['age'] = age;
    map['id'] = id;
    return map;
  }

  static User fromMap(Map<String, dynamic> map) {
    User user = new User();
    user.name = map['name'];
    user.age = map['age'];
    user.id = map['id'];
    return user;
  }

  static List<User> fromMapList(dynamic mapList) {
    List<User> 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<Database> 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<int> saveItem(User user) async {
    var dbClient = await db;
    int res = await dbClient.insert("$tableName", user.toMap());
    print(res.toString());
    return res;
  }

  //查詢
  Future<List> getTotalList() async {
    var dbClient = await db;
    var result = await dbClient.rawQuery("SELECT * FROM $tableName ");
    return result.toList();
  }

  //查詢總數
  Future<int> getCount() async {
    var dbClient = await db;
    return Sqflite.firstIntValue(await dbClient.rawQuery(
        "SELECT COUNT(*) FROM $tableName"
    ));
  }

//按照id查詢
  Future<User> 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<int> clear() async {
    var dbClient = await db;
    return await dbClient.delete(tableName);
  }


  //根據id刪除
  Future<int> deleteItem(int id) async {
    var dbClient = await db;
    return await dbClient.delete(tableName,
        where: "$columnId = ?", whereArgs: [id]);
  }

  //修改
  Future<int> 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<StatefulWidget> createState() {
    return new _DataAppPageState();
  }
}

class _DataAppPageState extends State<DataAppPage> {
  List<User> _datas = new List();
  var db = DatabaseHelper();
  Future<Null> _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<Null> _add() async {
    User user = new User();
    user.name = "我是增加的";
    user.age = 33;
    await db.saveItem(user);
    _query();
  }

//刪除,預設刪除第一條資料
  Future<Null> _delete() async {
    List datas = await db.getTotalList();
    if (datas.length > 0) {
      //修改第一條資料
      User user = User.fromMap(datas[0]);
      db.deleteItem(user.id);
      _query();
    }

  }

//修改，預設修改第一條資料
  Future<Null> _update() async {
    List datas = await db.getTotalList();
    if (datas.length > 0) {
      //修改第一條資料
      User u = User.fromMap(datas[0]);
      u.name = "我被修改了";
      db.updateItem(u);
      _query();
    }
  }

//查詢
  Future<Null> _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: <Widget>[
          new PopupMenuButton(
              onSelected: (String value) {
                switch (value) {
                  case "增加":
                    _add();
                    break;
                  case "刪除":
                    _delete();
                    break;
                  case "修改":
                    _update();
                    break;
                  case "查詢":
                    _query();
                    break;
                }
              },
              itemBuilder: (BuildContext context) => <PopupMenuItem<String>>[
                    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: <Widget>[
        Padding(
            padding: EdgeInsets.all(5),
            child: Text("姓名：" + _datas[index].name)),
        Padding(
            padding: EdgeInsets.all(5),
            child: Text("年齡：" + _datas[index].age.toString())),
      ],
    );
  }
}
複製程式碼

相關文章