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

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}';
  }
}