Flutter中使用sqlite

最新推荐文章于 2024-08-16 09:24:56 发布

@Jerry-

最新推荐文章于 2024-08-16 09:24:56 发布

阅读量3.9k

点赞数 3

本文链接：https://blog.csdn.net/f234344435/article/details/127842657

版权

flutter

在本教程中，我们将介绍如何在flutter中使用sqlite。 SQLite是用于移动设备和某些计算机SQL引擎。我们可以使用它来保存应用程序的数据。持久性存储对于在加载新数据时缓存网络呼叫和脱机应用程序很有用。

引入依赖：查询sqflite最新版本地址：点击以下地址查询

sqflite | Flutter Package

在flutter项目中引入相关依赖：

 sqflite: ^2.0.0+4 #sqlite数据库
 path_provider: ^2.0.11 #用于获取主机平台文件系统上常用的位置，如temp和app数据目录
 fluttertoast: 8.0.9 #自定义弹窗

在需要用sqlite的组件里面指定包路径就可以了

import 'package:path_provider/path_provider.dart';

import 'package:sqflite/sqflite.dart';

自定义弹窗组件：

import 'package:flutter/material.dart';
import 'package:fluttertoast/fluttertoast.dart';

void okToast(String text) {
   Fluttertoast.showToast(
    msg: text,
    toastLength: Toast.LENGTH_SHORT,
    gravity: ToastGravity.CENTER,
    timeInSecForIosWeb: 1,
    backgroundColor: Colors.blue,
    textColor: Colors.white,
    fontSize: 16.0);
}

void errorToast(String text) {
   Fluttertoast.showToast(
    msg:text,
    toastLength: Toast.LENGTH_SHORT,
    gravity: ToastGravity.CENTER,
    timeInSecForIosWeb: 1,
    backgroundColor: Colors.red,
    textColor: Colors.white,
    fontSize: 16.0);
}

为了减少小伙伴们拷贝代码时出错，里面的组件就不分包了，避免拷贝代码不全出现的错误，该项目将会全部代码放在一个主组件main下面，代码能正常运行后可以自行分包管理

// ignore_for_file: use_build_context_synchronously

import 'dart:convert';
import 'package:flutter/material.dart';
import 'package:flutter/services.dart';
import 'package:path_provider/path_provider.dart';
import 'dart:io';
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
import '../FlutterToast.dart';

/**
 * sqlite用户数据管理类，该界面不提供给用户
 */
class SqlitePage extends StatefulWidget {
  const SqlitePage({Key? key}) : super(key: key);

  @override
  State<StatefulWidget> createState() {
    return _SqlitePageState();
  }
}

class _SqlitePageState extends State<SqlitePage> {
  List<User>? _userList;

  loadAllData() async {
    _userList = await DBManager().findAll();
    setState(() {});
  }

  updateData(User user) async {
    var count = await DBManager().update(user);
    if (count > 0) {
      okToast("修改成功");
    } else {
      errorToast("修改失败");
    }
  }

  deleteData(int id) async {
    var count = await DBManager().delete(id);
    if (count > 0) {
      okToast("删除成功");
    } else {
      errorToast("删除失败");
    }
  }

  deleteAllData() async {
    var count = await DBManager().deleteAll();
    if (count > 0) {
      okToast("删除全部成功");
    } else {
      errorToast("删除全部失败");
    }
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: const Text("账号管理"),
        actions: [
          IconButton(
            icon: const Icon(Icons.add),
            onPressed: () {
              Navigator.push(context, MaterialPageRoute(builder: (context) {
                return const AddStudentPage();
              }));
            },
          )
        ],
      ),
      body: SingleChildScrollView(
        child: Center(
          child: Column(
            children: [
              ElevatedButton(
                onPressed: () {
                  loadAllData();
                },
                child: const Text("查询数据库所有数据"),
              ),
              ElevatedButton(
                onPressed: () {
                  deleteAllData();
                },
                child: const Text("删除数据库所有数据"),
              ),
              Padding(
                padding: const EdgeInsets.all(10),
                child: _buildTable(),
              ),
            ],
          ),
        ),
      ),
    );
  }

  _buildTable() {
    return Table(
      border: TableBorder.all(),
      defaultVerticalAlignment: TableCellVerticalAlignment.middle,
      children: [
        const TableRow(children: [
          TableCell(child: Text("id",style: TextStyle(color: Colors.orange),)),
          TableCell(child: Text("用户名",style: TextStyle(color: Colors.red),)),
          TableCell(child: Text("密码",style: TextStyle(color: Colors.blue),)),
          TableCell(child: Text("权限",style: TextStyle(color: Colors.white),)),
          TableCell(child: Text("修改",style: TextStyle(color: Colors.lightBlue),)),
          TableCell(child: Text("删除",style: TextStyle(color: Colors.red),)),
        ]),
        ...?_userList
            ?.map((e) => TableRow(children: [
                  TableCell(child: Text("${e.id}")),
                  TableCell(child: Text("${e.name}")),
                  TableCell(child: Text("${e.pwd}")),
                  TableCell(child: Text("${e.power}")),
                  TableCell(
                    child: TextButton(
                      onPressed: () {
                        updateData(e);
                      },
                      child: const Text("修改"),
                    ),
                  ),
                  TableCell(
                    child: TextButton(
                      onPressed: () {
                        deleteData(e.id!);
                      },
                      child: const Text("删除"),
                    ),
                  ),
                ]))
            .toList(),
      ],
    );
  }
}

/**
 * 初始化
 */
class DBManager {
  /// 数据库名
  final String _dbName = "skzn";
  /// 数据库版本
  final int _version = 1;
  static final DBManager _instance = DBManager._();
  factory DBManager() {
    return _instance;
  }

  DBManager._();
  static Database? _db;
  Future<Database> get db async {
    return _db ??= await _initDB();
  }

  /// 初始化数据库
  Future<Database> _initDB() async {
    Directory directory = await getApplicationDocumentsDirectory();
    String path = join(directory.path, _dbName);

    return await openDatabase(
      path,
      version: _version,
      onCreate: _onCreate,
      onUpgrade: _onUpgrade,
    );
  }

  /// 创建表
  Future _onCreate(Database db, int version) async {
    const String sql = """
    CREATE TABLE User(
      id INTEGER primary key AUTOINCREMENT,
      name TEXT,
      pwd TEXT,
      power INTEGER
    )
    """;
    return await db.execute(sql);
  }

  /// 更新表
  Future _onUpgrade(Database db, int oldVersion, int newVersion) async {}

  /// 使用SQL保存数据
  Future saveDataBySQL(User user) async {
    const String sql = """
    INSERT INTO User(name,pwd,power) values(?,?,?)
    """;
    Database database = await db;
    return await database.rawInsert(sql, [user.name, user.pwd, user.power]);
  }

  /// 查询全部数据
  Future<List<User>?> findAll() async {
    Database? database = await db;
    List<Map<String, Object?>> result = await database.query("User");
    if (result.isNotEmpty) {
      return result.map((e) => User.fromJson(e)).toList();
    } else {
      return [];
    }
  }

  /**
   * 条件查询
   *  ==》该方法主要用于用户登录，通过传入用户名和密码来查询数据库
   * 用法：直接在登录按钮里面直接调用：DBManager().find(_username, _password, context);
   */
  Future<List<User>?> find(
      String name, String password, BuildContext context) async {
    Database database = await db;
    List<Map<String, Object?>> result = await database.query("User",
        where: "name =? and pwd=? ", whereArgs: [name, password]);

    if (result.isNotEmpty) {
      okToast("登录成功");
      // GoRouter.of(context).go('/main'); //登录成功跳转的页面
    } else {
      errorToast("用户名或者密码错误");
    }
    return null;
  }

  /**
   * 条件查询  
   * ==》该方法用于默然一个管理员账号，通过登录的时候判断登录的是否为管理员
   * 用法：直接在登录按钮里面直接调用：DBManager().find(_username, _password, context);
   */
  Future<List<User>?> LoginQuery(
      String name, String password, BuildContext context) async {
    String username = 'admin';
    String pwd = 'sk123';
    Database database = await db;
    List<Map<String, Object?>> result = await database.query("User",
        where: "name =? and pwd=? ", whereArgs: [name, password]);

    if (result.isNotEmpty) {
      if (name == username && password == pwd) {
        // GoRouter.of(context).go('/main'); //查询结果为管理员跳转的页面
        print("管理员界面");
      } else {
        // GoRouter.of(context).go('/main'); //查询结果为用户跳转的页面
        print("测试用户界面");
      }
    }
    return null;
  }

/**
 * 添加默认账户表单 
 * ==>sqlite存储的本地文件，当用户使用app的时候是本地文件是没有数据的，所以我们要在新用户登录的时候创建一个默认本地账户表
 *  用法: 在登录按钮里面直接调用: DBManager().insertUser();
 */
  Future<int?> insertUser() async {
    Database? database = await db;
    List<Map<String, Object?>> result = await database.query("User");
    if (result.isEmpty) {
      var admin = User(
        name: 'admin',
        pwd: 'admin123',
        power: 1,
      );
      var test = User(
        name: 'test',
        pwd: 'test123',
        power: 1,
      );
      await DBManager().saveDataBySQL(admin);
      await DBManager().saveDataBySQL(test);
    } else {
      return null;
    }
    return null;
  }

  /**
   * 修改表中的某一项的值为自定义的默认值
   */
  Future<int> update(User user) async {
    Database database = await db;
    user.pwd = '123456'; //修改表中password字段的默认值为 123456
    int count = await database
        .update("User", user.toJson(), where: "id=?", whereArgs: [user.id]);
    return count;
  }

  /**
   * 删除操作
   * 根据id删除
   */
  Future<int> delete(int id) async {
    Database database = await db;
    int count = await database.delete("User", where: "id=?", whereArgs: [id]);
    return count;
  }

  /**
   * 删除全部数据
   */
  Future<int> deleteAll() async {
    Database database = await db;
    int count = await database.delete("User");
    return count;
  }
}

/**
 * 数据实体
 */
User studentFromJson(String str) => User.fromJson(json.decode(str));
String studentToJson(User data) => json.encode(data.toJson());

class User {
  User({
    this.id,
    this.name,
    this.pwd,
    this.power,
  });

  User.fromJson(dynamic json) {
    id = json['id'];
    name = json['name'];
    pwd = json['pwd'];
    power = json['power'];
  }

  int? id;
  String? name;
  String? pwd;
  int? power;

  Map<String, dynamic> toJson() {
    final map = <String, dynamic>{};
    map['id'] = id;
    map['name'] = name;
    map['pwd'] = pwd;
    map['power'] = power;
    return map;
  }
}

class AddStudentPage extends StatefulWidget {
  const AddStudentPage({Key? key}) : super(key: key);

  @override
  State<StatefulWidget> createState() {
    return _AddStudentPageState();
  }
}

class _AddStudentPageState extends State<AddStudentPage> {
  int _sexValue = 0;
  late TextEditingController _nameController;
  late TextEditingController _ageController;

  @override
  void initState() {
    _nameController = TextEditingController();
    _ageController = TextEditingController();
    super.initState();
  }

  @override
  void dispose() {
    _nameController.dispose();
    _ageController.dispose();
    super.dispose();
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: const Text("添加用户账号"),
      ),
      body: Padding(
        padding: const EdgeInsets.all(8.0),
        child: Column(
          children: [
            TextField(
              controller: _nameController,
              decoration: const InputDecoration(labelText: "用户名："),
            ),
            TextField(
              controller: _ageController,
              decoration: const InputDecoration(labelText: "密码："),
              //限制输入框输入只能为数字
              // inputFormatters: [
              //   FilteringTextInputFormatter.digitsOnly,
              // ],
            ),
            Row(
              children: [
                Expanded(
                  child: RadioListTile<int>(
                    title: const Text("管理员"),
                    value: 0,
                    groupValue: _sexValue,
                    onChanged: (value) {
                      setState(() {
                        _sexValue = value!;
                      });
                    },
                  ),
                ),
                Expanded(
                  child: RadioListTile<int>(
                    title: const Text("测试用户"),
                    value: 1,
                    groupValue: _sexValue,
                    onChanged: (value) {
                      setState(() {
                        _sexValue = value!;
                      });
                    },
                  ),
                ),
              ],
            ),
            Builder(builder: (BuildContext context) {
              return Column(
                children: [
                  ElevatedButton(
                    onPressed: () async {
                      var user = User(
                        name: _nameController.text.toString(),
                        pwd: _ageController.text.toString(),
                        power: _sexValue,
                      );
                      int result = await DBManager().saveDataBySQL(user);
                      if (result > 0) {
                        okToast("保存数据成功，result:$result");
                      } else {
                        errorToast("保存数据失败，result:$result");
                      }
                    },
                    child: const Text("添加"),
                  ),
                ],
              );
            }),
          ],
        ),
      ),
    );
  }
}

运行效果展示：