Flutter 数据库插件 -- sqflite

flutter 开发中常用的数库插件sqflite
插件地址：sqflite | Flutter Package (pub.dev)

官方给出的例子

// Get a location using getDatabasesPath
var databasesPath = await getDatabasesPath();
String path = join(databasesPath, 'demo.db');

// Delete the database
await deleteDatabase(path);

// open the database
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)');
});

// Insert some records in a transaction
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');
});

// Update some record
int count = await database.rawUpdate(
    'UPDATE Test SET name = ?, value = ? WHERE name = ?',
    ['updated name', '9876', 'some name']);
print('updated: $count');

// Get the records
List<Map> list = await database.rawQuery('SELECT * FROM Test');
List<Map> expectedList = [
  {'name': 'updated name', 'id': 1, 'value': 9876, 'num': 456.789},
  {'name': 'another name', 'id': 2, 'value': 12345678, 'num': 3.1416}
];
print(list);
print(expectedList);
assert(const DeepCollectionEquality().equals(list, expectedList));

// Count the records
count = Sqflite
    .firstIntValue(await database.rawQuery('SELECT COUNT(*) FROM Test'));
assert(count == 2);

// Delete a record
count = await database
    .rawDelete('DELETE FROM Test WHERE name = ?', ['another name']);
assert(count == 1);

// Close the database
await database.close();

使用方法

1. 创建数据库

String databasesPath = await getDatabasesPath();
String path = join(databasesPath, '数据库名字.db');

2. 打开数据库

Database database = await openDatabase(
  path,
  version: VERSION,
  onCreate: (Database db, int version) async {
    //路径下没有数据库文件时创建数据库的回调
  },
  onUpgrade: (Database db, int oldVersion, int newVersion) async {
    // 数据库升级回调
  },
  onOpen: (Database db) async {
    // 打开数据库的回调
  },
);

3. 数据操作

• 创建表

// 创建表person
database.execute('CREATE TABLE person (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)');

• 增

await database.insert(
    'person',
    {'name': 'Civet', 'age': '18'},
    conflictAlgorithm: ConflictAlgorithm.replace,
  );

• 删

await database.delete(
    'person',
    where: 'name = ?',
    whereArgs:  ['Civet'],
  );

• 改

await database.update(
    'person',
    {'name': 'Civet', 'age': '19'},
    where: 'name = ?',
    whereArgs: ['Civet'],
  );

• 查

 final List<Map<String, dynamic>> maps = await database.query('person');

4. 关闭数据库

await database.close();

5. 删除数据库

await deleteDatabase(path);