Flutter数据库工具插件dbutils的使用

简介

dbutils 是一个用于Flutter应用程序中操作SQLite数据库的Dart包。它与sqflite插件协同工作，简化了对SQLite数据库的操作流程。本文将详细介绍如何安装、配置和使用dbutils进行数据库操作，并提供完整的示例代码。

安装

在pubspec.yaml文件中添加以下依赖项：

dependencies:
  dbutils:^5.0.0

确保版本号遵循语义化版本控制规范，即主要版本号后跟随两个零（如^5.0.0），以便接收次要版本更新带来的新特性或补丁修复。

数据库表定义

以Employee类为例，该类继承自DBInterface并实现了三个必需属性：name（数据库名称）、version（数据库版本）和onCreate()方法（创建数据库时执行）。以下是Employee.dart文件的部分代码片段：

import 'package:dbutils/dbutils.dart';

class Employee extends DBInterface {
  static final String tableName = 'employee';
  
  // 单例模式
  static late Employee _this;
  factory Employee() {
    if (_this == null) {
      _this = Employee._();
      _this.name = 'employees.db';
      _this.version = 1;
    }
    return _this;
  }

  Employee._();

  @override
  Future<void> onCreate(Database db, int version) async {
    await db.execute('''
      CREATE TABLE $tableName (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        firstName TEXT,
        lastName TEXT,
        position TEXT
      )
    ''');
  }

  // 添加员工记录
  Future<int> addEmployee(Map<String, dynamic> employee) async {
    return await saveRec(employee, tableName);
  }

  // 删除员工记录
  Future<int> deleteEmployee(int id) async {
    return await deleteRec({'id': id}, tableName);
  }

  // 获取所有员工记录
  Future<List<Map<String, dynamic>>> getEmployees() async {
    return await rawQuery('SELECT * FROM $tableName');
  }
}

示例应用

主程序入口

main.dart是应用程序的入口点，负责启动Material风格的应用程序并导航到员工列表页面。

import 'package:flutter/material.dart';
import 'src/employee_list.dart';

void main() => runApp(const MyApp());

class MyApp extends StatelessWidget {
  const MyApp({Key? key}) : super(key: key);

  @override
  Widget build(BuildContext context) => MaterialApp(
        title: 'DBUtils Demo app',
        theme: ThemeData(
          primarySwatch: Colors.blue,
        ),
        home: const EmployeeList(key: Key('EmployeeList')),
      );
}

员工列表页面

EmployeeList.dart展示了从数据库中检索到的所有员工信息，并允许用户添加新员工或查看详情。

import 'package:flutter/material.dart';
import 'employee.dart';
import 'employee_detail.dart';

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

  @override
  State createState() => _EmployeeListState();
}

class _EmployeeListState extends State<EmployeeList> {
  final Employee _employee = Employee();

  @override
  void initState() {
    super.initState();
    _employee.init(); // 初始化数据库连接
  }

  @override
  void dispose() {
    _employee.dispose(); // 关闭数据库连接
    super.dispose();
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: const Text('Employees'),
      ),
      body: FutureBuilder<List<Map<String, dynamic>>>(
        future: _employee.getEmployees(),
        builder: (context, snapshot) {
          if (!snapshot.hasData) return const Center(child: CircularProgressIndicator());
          final employees = snapshot.data!;
          return ListView.builder(
            itemCount: employees.length,
            itemBuilder: (context, index) {
              final employee = employees[index];
              return ListTile(
                title: Text('${employee['firstName']} ${employee['lastName']}'),
                subtitle: Text(employee['position']),
                onTap: () {
                  Navigator.push(
                    context,
                    MaterialPageRoute(
                      builder: (_) => EmployeeDetail(employee: employee),
                    ),
                  );
                },
              );
            },
          );
        },
      ),
      floatingActionButton: FloatingActionButton(
        onPressed: () {
          Navigator.push(
            context,
            MaterialPageRoute(
              builder: (_) => EmployeeDetail(),
            ),
          );
        },
        child: const Icon(Icons.add),
      ),
    );
  }
}

员工详情页面

EmployeeDetail.dart用于展示单个员工的具体信息，同时支持编辑和保存更改。

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

class EmployeeDetail extends StatefulWidget {
  final Map<String, dynamic>? employee;

  const EmployeeDetail({Key? key, this.employee}) : super(key: key);

  @override
  State createState() => _EmployeeDetailState();
}

class _EmployeeDetailState extends State<EmployeeDetail> {
  final Employee _employee = Employee();
  final TextEditingController _firstNameController = TextEditingController();
  final TextEditingController _lastNameController = TextEditingController();
  final TextEditingController _positionController = TextEditingController();

  @override
  void initState() {
    super.initState();
    if (widget.employee != null) {
      _firstNameController.text = widget.employee!['firstName'];
      _lastNameController.text = widget.employee!['lastName'];
      _positionController.text = widget.employee!['position'];
    }
  }

  void _submit() async {
    final Map<String, dynamic> employee = {
      'firstName': _firstNameController.text,
      'lastName': _lastNameController.text,
      'position': _positionController.text,
    };

    if (widget.employee != null) {
      employee['id'] = widget.employee!['id'];
    }

    try {
      bool success = await _employee.save(employee, 'employee');
      if (success) {
        ScaffoldMessenger.of(context).showSnackBar(
          const SnackBar(content: Text('Saved successfully')),
        );
        Navigator.pop(context);
      } else {
        ScaffoldMessenger.of(context).showSnackBar(
          const SnackBar(content: Text('Failed to save')),
        );
      }
    } catch (e) {
      ScaffoldMessenger.of(context).showSnackBar(
        SnackBar(content: Text('Error: $e')),
      );
    }
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: const Text('Employee Detail'),
        actions: [
          IconButton(
            icon: const Icon(Icons.delete),
            onPressed: () async {
              if (widget.employee != null) {
                try {
                  await _employee.deleteEmployee(widget.employee!['id']);
                  ScaffoldMessenger.of(context).showSnackBar(
                    const SnackBar(content: Text('Deleted successfully')),
                  );
                  Navigator.pop(context);
                } catch (e) {
                  ScaffoldMessenger.of(context).showSnackBar(
                    SnackBar(content: Text('Error: $e')),
                  );
                }
              }
            },
          ),
        ],
      ),
      body: Padding(
        padding: const EdgeInsets.all(16.0),
        child: Column(
          children: [
            TextField(
              controller: _firstNameController,
              decoration: const InputDecoration(labelText: 'First Name'),
            ),
            TextField(
              controller: _lastNameController,
              decoration: const InputDecoration(labelText: 'Last Name'),
            ),
            TextField(
              controller: _positionController,
              decoration: const InputDecoration(labelText: 'Position'),
            ),
            ElevatedButton(
              onPressed: _submit,
              child: const Text('Save'),
            ),
          ],
        ),
      ),
    );
  }
}

通过上述步骤，您可以快速上手dbutils插件，在Flutter项目中实现对SQLite数据库的基本CRUD操作。希望这份指南能帮助您更好地理解和使用dbutils。如果有任何问题或需要进一步的帮助，请随时查阅官方文档或访问GitHub仓库获取更多信息。