如何修复SqfliteDatabaseException (DatabaseException(database_closed))异常？

使用sqflite包开发时遇到以下问题：

• 点击组件执行DbHelper.instance.deleteDB();
• 完成后点击另一组件执行查询：List<IconList> lists = await DbHelper.instance.selectAllIcons(); print(lists)
• 抛出异常：SqfliteDatabaseException (DatabaseException(database_closed))
• 重启Flutter后再次执行查询，功能恢复正常

相关代码如下：

主页面操作代码 #

//main.dart 删除数据库的按钮点击事件
onPressed: () async {
  DbHelper.instance.deleteDB();
},

//main.dart 查询数据的按钮点击事件
onPressed: () async {
  List<IconList> lists = await DbHelper.instance.selectAllIcons();
  print(lists);
},

数据库管理类代码 #

//db_manager.dart
import 'package:intl/intl.dart';
import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';
import 'package:sqflite/sqflite.dart';

const String columnId = '_id';
const String columnTitle = 'title';
const String columnSubTitle = 'subTitle';
const String columnIconPass = 'iconPass';
const String columnStatus = 'status';
const String columnMemo = 'memo';
const String columnCreatedAt = 'createdAt';

const List<String> columns = [
  columnId,
  columnTitle,
  columnSubTitle,
  columnIconPass,
  columnStatus,
  columnMemo,
  columnCreatedAt,
];

class DbHelper {
  DbHelper._createInstance();
  static DbHelper instance = DbHelper._createInstance();
  static Database? _database;

  Future<Database> get database async {
    return _database ??= await _initDB();
  }

  Future<Database> _initDB() async {
    final dbDirectory = await getApplicationSupportDirectory();
    final dbFilePath = dbDirectory.path;
    String path = join(dbFilePath, 'cards.db');
    return await openDatabase(
      path,
      version: 1,
      onCreate: _onCreate,
    );
  }

  void deleteDB() async{
    final dbDirectory = await getApplicationSupportDirectory();
    final dbFilePath = dbDirectory.path;
    String path = join(dbFilePath, 'cards.db');
    await deleteDatabase(path);
  }

  Future _onCreate(Database database, int version) async {
    await database.execute('''
      CREATE TABLE icons(
        _id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT,
        subTitle TEXT,
        iconPass TEXT,
        status INTEGER,
        memo TEXT,
        createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
      )
   ''');
  }

  Future<List<IconList>> selectAllIcons() async {
    final db = await instance.database;
    final iconListData = await db.query('icons');

    return iconListData
        .map((json) => IconList.fromJson(json))
        .toList();
  }

  Future insert(IconList iconList) async {
    final db = await database;
    return await db.insert(
      'icons',//tablename
      iconList.toJson()
    );
  }
  
  Future insertRows(List<IconList> rows) async{
    final db = await database;
    Batch batch = db.batch();
    rows.forEach((row) => batch.insert('icons', row.toJson()));
    await batch.commit(noResult: true);
  }
}

class IconList {
  final int? id;
  final String title;
  final String? subTitle;
  final String iconPass;
  final int status;
  final String memo;
  DateTime createdAt;

  IconList({
    this.id,
    required this.title,
    this.subTitle,
    required this.iconPass,
    required this.status,
    required this.memo,
    required this.createdAt
  });

  IconList copy({
    int? id,
    String? title,
    String? subTitle,
    String? iconPass,
    int? status,
    String? memo,
    DateTime? createdAt,
  }) =>
      IconList(
        id: id ?? this.id,
        title: title ?? this.title,
        subTitle: subTitle ?? this.subTitle,
        iconPass: iconPass ?? this.iconPass,
        status: status ?? this.status,
        memo: memo ?? this.memo,
        createdAt: createdAt ?? this.createdAt,
      );

  static IconList fromJson(Map<String, Object?> json) => IconList(
        id: json[columnId] as int,
        title: json[columnTitle] as String,
        subTitle: json[columnSubTitle] as String?,
        iconPass: json[columnIconPass] as String,
        status: json[columnStatus] as int,
        memo: json[columnMemo] as String,
        createdAt: DateTime.parse(json[columnCreatedAt] as String),
      );

  Map<String, Object?> toJson() => {
        columnTitle: title,
        columnSubTitle: subTitle,
        columnIconPass: iconPass,
        columnStatus: status,
        columnMemo: memo,
        columnCreatedAt: DateFormat('yyyy-MM-dd HH:mm:ss').format(createdAt),
      };
}

异常处理代码 #

//exception
import 'package:sqflite/src/services_impl.dart';
import 'package:sqflite/src/sqflite_import.dart';

/// Wrap any exception to a [DatabaseException]
Future<T> wrapDatabaseException<T>(Future<T> Function() action) async {
  try {
    final result = await action();
    return result;
  } on PlatformException catch (e) {
    if (e.code == sqliteErrorCode) {
      throw SqfliteDatabaseException(e.message!, e.details);
    } else {
      rethrow;
    }
  }
}

删除数据库文件后，DbHelper类中的_database静态变量仍然持有之前打开的数据库连接引用，但实际数据库文件已被删除。此时执行查询操作时，这个无效的连接会触发database_closed异常。

而重启Flutter后，应用进程重启，_database变量被重置为null，调用selectAllIcons时会重新执行_initDB方法，自动创建新的数据库文件并建立有效连接，因此查询正常。

修改DbHelper的deleteDB方法，在删除数据库文件前关闭现有连接，并将_database置为null，确保后续操作会重新初始化数据库：

// 修改后的deleteDB方法
Future<void> deleteDB() async {
  final dbDirectory = await getApplicationSupportDirectory();
  final dbFilePath = dbDirectory.path;
  String path = join(dbFilePath, 'cards.db');

  // 关闭并清空现有数据库连接
  if (_database != null) {
    await _database!.close();
    _database = null;
  }

  await deleteDatabase(path);
}

同时，在调用deleteDB时必须使用await，确保删除操作完成后再执行后续查询：

// 修改后的按钮点击事件
onPressed: () async {
  await DbHelper.instance.deleteDB();
},

这样修改后，删除数据库时会彻底清理无效连接，后续查询会自动创建新的数据库文件和连接，避免出现database_closed异常。

内容的提问来源于stack exchange，提问作者SugiuraY