flutter开发实战-本地SQLite数据存储

flutter开发实战-本地SQLite数据库存储

正在编写一个需要持久化且查询大量本地设备数据的 app，可考虑采用数据库。相比于其他本地持久化方案来说，数据库能够提供更为迅速的插入、更新、查询功能。这里需要用到sqflite package 来使用 SQLite 数据库

预览图

一、引入sqflite

在工程的pubspec.yaml中引入插件

  # sqflitesqflite: ^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 tablevar 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});@overrideState<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 tablevar 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--;}@overrideWidget 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.@overrideString toString() {return 'Dog{id: $id, name: $name, age: $age}';}
}

三、小结

flutter开发实战-本地SQLite数据库存储

学习记录，每天不停进步。