基于SQLite的Flutter应用CRUD实现适配特定表结构

前置依赖 #

确保你的pubspec.yaml里已经添加了常用的SQLite依赖：

dependencies:
  sqflite: ^2.3.2
  path: ^1.8.3

数据库初始化（结合你的GenericTable） #

先实现数据库初始化逻辑，利用你定义的GenericTable自动生成建表语句：

import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';

// 复用你已定义的抽象表类
abstract class GenericTable {
  String get tableName;
  List<List<String>> get fields;
}

// 数据库工具类
class DBHelper {
  static Database? _database;
  static final DBHelper instance = DBHelper._internal();

  DBHelper._internal();

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

  Future<Database> _initDB() async {
    String dbPath = join(await getDatabasesPath(), 'app_safety.db');
    return await openDatabase(
      dbPath,
      version: 1,
      onCreate: (db, version) async {
        // 自动创建你定义的两张表
        await _createTable(db, SafeTable());
        await _createTable(db, EmergenciesTable());
      },
    );
  }

  Future<void> _createTable(Database db, GenericTable table) async {
    // 拼接字段SQL语句
    String fieldDefinitions = table.fields.map((field) {
      return '${field[0]} ${field[1]}';
    }).join(', ');
    await db.execute('CREATE TABLE ${table.tableName} ($fieldDefinitions)');
  }
}

Safe表操作：创建（插入）与更新 #

1. 插入安全状态记录 #

// 插入一条Safe表记录
Future<int> insertSafe({
  required String timestamp,
  required String userId,
  String situation = '',
  int isSafe = 0, // 0=不安全，1=安全
}) async {
  final db = await DBHelper.instance.database;
  final safeTable = SafeTable();

  return await db.insert(
    safeTable.tableName,
    {
      'timestamp': timestamp,
      'user_id': userId,
      'situation': situation,
      'is_safe': isSafe,
    },
    conflictAlgorithm: ConflictAlgorithm.replace,
  );
}

2. 更新安全状态记录 #

根据主键id更新指定字段：

// 更新Safe表记录（仅更新传入的非空字段）
Future<int> updateSafe({
  required int id,
  String? timestamp,
  String? userId,
  String? situation,
  int? isSafe,
}) async {
  final db = await DBHelper.instance.database;
  final safeTable = SafeTable();

  Map<String, dynamic> updateData = {};
  if (timestamp != null) updateData['timestamp'] = timestamp;
  if (userId != null) updateData['user_id'] = userId;
  if (situation != null) updateData['situation'] = situation;
  if (isSafe != null) updateData['is_safe'] = isSafe;

  return await db.update(
    safeTable.tableName,
    updateData,
    where: 'id = ?',
    whereArgs: [id],
  );
}

Emergencies表操作：读取数据 #

1. 获取所有紧急事件记录 #

// 获取Emergencies表所有数据
Future<List<Map<String, dynamic>>> getAllEmergencies() async {
  final db = await DBHelper.instance.database;
  final emergenciesTable = EmergenciesTable();
  
  return await db.query(emergenciesTable.tableName);
}

2. 根据ID获取单条紧急事件 #

// 根据主键ID获取指定紧急事件
Future<Map<String, dynamic>?> getEmergencyById(int id) async {
  final db = await DBHelper.instance.database;
  final emergenciesTable = EmergenciesTable();
  
  final result = await db.query(
    emergenciesTable.tableName,
    where: 'id = ?',
    whereArgs: [id],
  );

  return result.isNotEmpty ? result.first : null;
}

3. 筛选活跃紧急事件（可选） #

如果需要只获取标记为紧急状态（is_emergency=1）的记录：

// 获取所有标记为紧急的记录，按时间倒序排列
Future<List<Map<String, dynamic>>> getActiveEmergencies() async {
  final db = await DBHelper.instance.database;
  final emergenciesTable = EmergenciesTable();
  
  return await db.query(
    emergenciesTable.tableName,
    where: 'is_emergency = ?',
    whereArgs: [1],
    orderBy: 'timestamp DESC',
  );
}

可选：模型类转换（简化数据操作） #

为了更直观地处理数据，可以给表定义对应的模型类，比如：

class SafeModel {
  final int? id;
  final String timestamp;
  final String userId;
  final String situation;
  final int isSafe;

  SafeModel({
    this.id,
    required this.timestamp,
    required this.userId,
    this.situation = '',
    this.isSafe = 0,
  });

  // 从数据库Map转换为模型
  factory SafeModel.fromMap(Map<String, dynamic> map) {
    return SafeModel(
      id: map['id'],
      timestamp: map['timestamp'],
      userId: map['user_id'],
      situation: map['situation'],
      isSafe: map['is_safe'],
    );
  }

  // 模型转换为数据库Map
  Map<String, dynamic> toMap() {
    return {
      'id': id,
      'timestamp': timestamp,
      'user_id': userId,
      'situation': situation,
      'is_safe': isSafe,
    };
  }
}

后续插入/更新时可直接调用toMap()，读取数据时用fromMap()转换为模型对象，代码可读性更强。

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