flutter开发实战-本地SQLite数据库存储
正在编写一个需要持久化且查询大量本地设备数据的 app，可考虑采用数据库。相比于其他本地持久化方案来说，数据库能够提供更为迅速的插入、更新、查询功能。这里需要用到sqflite package 来使用 SQLite 数据库
预览图
一、引入sqflite
在工程的pubspec.yaml中引入插件
# sqflite
sqflite: ^2.2.8+4
二、使用sqflite
使用 sqflite 实现插入，读取，更新，删除数据。
- 打开数据库
Future<void> openDB(BuildContext context) async {
// Open the database and store the reference.
database = await openDatabase(
// Set the path to the database. Note: Using the `join` function from the
// `path` package is best practice to ensure the path is correctly
// constructed for each platform.
join(await getDatabasesPath(), 'doggie_database.db'),
// When the database is first created, create a table to store dogs.
onCreate: (db, version) {
// Run the CREATE TABLE statement on the database.
return db.execute(
'CREATE TABLE dogs(id INTEGER PRIMARY KEY, name TEXT, age INTEGER)',
);
},
version: 1,
);
}
- 插入一条记录
Future<void> insertDB(BuildContext context) async {
dogId++;
// Create a Dog and add it to the dogs table
var fido = Dog(
id: dogId,
name: 'Fido',
age: 35,
);
// Get a reference to the database.
final db = await database;
// Insert the Dog into the correct table. You might also specify the
// `conflictAlgorithm` to use in case the same dog is inserted twice.
//
// In this case, replace any previous data.
await db?.insert(
'dogs',
fido.toMap(),
conflictAlgorithm: ConflictAlgorithm.replace,
);
}
- 更新一条记录
Future<void> updateDog(Dog dog) async {
// Get a reference to the database.
final db = await database;
// Update the given Dog.
await db?.update(
'dogs',
dog.toMap(),
// Ensure that the Dog has a matching id.
where: 'id = ?',
// Pass the Dog's id as a whereArg to prevent SQL injection.
whereArgs: [dog.id],
);
}
- 删除一条记录
Future<void> deleteDog(int id) async {
// Get a reference to the database.
final db = await database;
// Remove the Dog from the database.
await db?.delete(
'dogs',
// Use a `where` clause to delete a specific dog.
where: 'id = ?',
// Pass the Dog's id as a whereArg to prevent SQL injection.
whereArgs: [id],
);
}
- 获取存储记录
// A method that retrieves all the dogs from the dogs table.
Future<List<Dog>> dogs() async {
// Get a reference to the database.
final db = await database;
// Query the table for all the dogs.
final List<Map<String, Object?>>? dogMaps = await db?.query('dogs');
if (dogMaps != null && dogMaps.isNotEmpty) {
// Convert the list of each dog's fields into a list of `Dog` objects.
List<Dog> dogs = [];
for(var dogMap in dogMaps) {
// Dog dog = Dog(id: dogMap['id']??0, name: name, age: age)
var id = dogMap['id'] as int;
var name = dogMap['name'] as String;
var age = dogMap['age'] as int;
Dog dog = Dog(id: id, name: name, age: age);
dogs.add(dog);
}
return dogs;
}
return [];
}
完整代码如下
import 'package:flutter/material.dart';
import 'dart:async';
import 'package:flutter/widgets.dart';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
class SqliteDemoPage extends StatefulWidget {
const SqliteDemoPage({super.key});
@override
State<SqliteDemoPage> createState() => _SqliteDemoPageState();
}
class _SqliteDemoPageState extends State<SqliteDemoPage> {
Database? database;
int dogId = 0;
Future<void> openDB(BuildContext context) async {
// Open the database and store the reference.
database = await openDatabase(
// Set the path to the database. Note: Using the `join` function from the
// `path` package is best practice to ensure the path is correctly
// constructed for each platform.
join(await getDatabasesPath(), 'doggie_database.db'),
// When the database is first created, create a table to store dogs.
onCreate: (db, version) {
// Run the CREATE TABLE statement on the database.
return db.execute(
'CREATE TABLE dogs(id INTEGER PRIMARY KEY, name TEXT, age INTEGER)',
);
},
version: 1,
);
}
// A method that retrieves all the dogs from the dogs table.
Future<List<Dog>> dogs() async {
// Get a reference to the database.
final db = await database;
// Query the table for all the dogs.
final List<Map<String, Object?>>? dogMaps = await db?.query('dogs');
if (dogMaps != null && dogMaps.isNotEmpty) {
// Convert the list of each dog's fields into a list of `Dog` objects.
List<Dog> dogs = [];
for(var dogMap in dogMaps) {
// Dog dog = Dog(id: dogMap['id']??0, name: name, age: age)
var id = dogMap['id'] as int;
var name = dogMap['name'] as String;
var age = dogMap['age'] as int;
Dog dog = Dog(id: id, name: name, age: age);
dogs.add(dog);
}
return dogs;
}
return [];
}
Future<void> updateDog(Dog dog) async {
// Get a reference to the database.
final db = await database;
// Update the given Dog.
await db?.update(
'dogs',
dog.toMap(),
// Ensure that the Dog has a matching id.
where: 'id = ?',
// Pass the Dog's id as a whereArg to prevent SQL injection.
whereArgs: [dog.id],
);
}
Future<void> deleteDog(int id) async {
// Get a reference to the database.
final db = await database;
// Remove the Dog from the database.
await db?.delete(
'dogs',
// Use a `where` clause to delete a specific dog.
where: 'id = ?',
// Pass the Dog's id as a whereArg to prevent SQL injection.
whereArgs: [id],
);
}
Future<void> insertDB(BuildContext context) async {
dogId++;
// Create a Dog and add it to the dogs table
var fido = Dog(
id: dogId,
name: 'Fido',
age: 35,
);
// Get a reference to the database.
final db = await database;
// Insert the Dog into the correct table. You might also specify the
// `conflictAlgorithm` to use in case the same dog is inserted twice.
//
// In this case, replace any previous data.
await db?.insert(
'dogs',
fido.toMap(),
conflictAlgorithm: ConflictAlgorithm.replace,
);
}
Future<void> getListFromDB(BuildContext context) async {
List<Dog> list = await dogs();
for(var dog in list) {
print("dog info:${dog.toString()}");
}
}
void updateDB(BuildContext context) {
var dog = Dog(
id: dogId,
name: 'AFarah',
age: 11,
);
updateDog(dog);
}
Future<void> deleteDB(BuildContext context) async {
await deleteDog(dogId);
dogId--;
}
@override
Widget build(BuildContext context) {
return Scaffold(
appBar: AppBar(
title: const Text('SqliteDemo'),
),
body: Center(
child: Column(
mainAxisAlignment: MainAxisAlignment.center,
crossAxisAlignment: CrossAxisAlignment.center,
children: [
const SizedBox(
height: 20,
),
TextButton(
onPressed: () {
openDB(context);
},
child: Container(
height: 36,
width: 200,
color: Colors.lightGreen,
alignment: Alignment.center,
child: const Text(
'openDatabase',
style: TextStyle(fontSize: 12, color: Colors.white),
),
),
),
const SizedBox(
height: 20,
),
TextButton(
onPressed: () {
insertDB(context);
},
child: Container(
height: 36,
width: 200,
color: Colors.lightGreen,
alignment: Alignment.center,
child: const Text(
'插入一条dog记录',
style: TextStyle(fontSize: 12, color: Colors.white),
),
),
),
const SizedBox(
height: 20,
),
TextButton(
onPressed: () {
getListFromDB(context);
},
child: Container(
height: 36,
width: 200,
color: Colors.lightGreen,
alignment: Alignment.center,
child: const Text(
'获取记录列表',
style: TextStyle(fontSize: 12, color: Colors.white),
),
),
),
const SizedBox(
height: 20,
),
TextButton(
onPressed: () {
updateDB(context);
},
child: Container(
height: 36,
width: 200,
color: Colors.lightGreen,
alignment: Alignment.center,
child: const Text(
'更新一条记录',
style: TextStyle(fontSize: 12, color: Colors.white),
),
),
),
const SizedBox(
height: 20,
),
TextButton(
onPressed: () {
deleteDB(context);
},
child: Container(
height: 36,
width: 200,
color: Colors.lightGreen,
alignment: Alignment.center,
child: const Text(
'删除一条记录',
style: TextStyle(fontSize: 12, color: Colors.white),
),
),
),
],
),
),
);
}
}
class Dog {
final int id;
final String name;
final int age;
const Dog({
required this.id,
required this.name,
required this.age,
});
// Convert a Dog into a Map. The keys must correspond to the names of the
// columns in the database.
Map<String, Object?> toMap() {
return {
'id': id,
'name': name,
'age': age,
};
}
// Implement toString to make it easier to see information about
// each dog when using the print statement.
@override
String toString() {
return 'Dog{id: $id, name: $name, age: $age}';
}
}
三、小结
flutter开发实战-本地SQLite数据库存储
学习记录，每天不停进步。