Flutter中如何创建包含嵌套类对象的SQLite数据表？

首先要明确：SQLite不支持直接存储嵌套对象，且IconData属于Flutter对象无法直接序列化存储，需要先将IconData转换为可存储的基础类型（如整数、字符串）。

第一步：先修复CategoryModel的序列化问题 #

修改CategoryModel，将IconData拆分为可存储的字段：

class CategoryModel {
  String title;
  IconData iconpath;

  CategoryModel({required this.title, required this.iconpath});

  // 转为数据库可存储的Map
  Map<String, dynamic> toMap() {
    return {
      'title': title,
      'icon_code_point': iconpath.codePoint,
      'icon_font_family': iconpath.fontFamily,
      'icon_font_package': iconpath.fontPackage,
    };
  }

  // 从数据库Map还原对象
  factory CategoryModel.fromMap(Map<String, dynamic> map) {
    return CategoryModel(
      title: map['title'],
      iconpath: IconData(
        map['icon_code_point'],
        fontFamily: map['icon_font_family'],
        fontPackage: map['icon_font_package'],
      ),
    );
  }
}

方案一：扁平化存储（适合简单场景） #

将Category的字段直接合并到Transaction表中，实现简单，无需额外关联查询。

修改SqliteHelper

class SqliteHelper{
  static Future<Database> _getdb() async {
    return openDatabase(join(await getDatabasesPath(), 'transactiondb.db'),
        onCreate: (db, version) async => await db.execute(
            """CREATE TABLE transactiontb(
              id TEXT NOT NULL PRIMARY KEY,
              title TEXT NOT NULL,
              amount INTEGER NOT NULL,
              isexpense INTEGER NOT NULL,
              category_title TEXT NOT NULL,
              category_icon_code_point INTEGER NOT NULL,
              category_icon_font_family TEXT,
              category_icon_font_package TEXT
            );"""),
        // 版本号升级，触发数据库更新
        version: 2);
  }

  static Future<int> addTransaction(TransactionModel transaction) async {
    final db=await _getdb();
    // 展开Category字段到Transaction的Map中
    final transactionMap = {
      'id': transaction.id,
      'title': transaction.title,
      'amount': transaction.amount,
      'isexpense': transaction.isexpense,
      'category_title': transaction.category.title,
      'category_icon_code_point': transaction.category.iconpath.codePoint,
      'category_icon_font_family': transaction.category.iconpath.fontFamily,
      'category_icon_font_package': transaction.category.iconpath.fontPackage,
    };
    return await db.insert('transactiontb', transactionMap,
        conflictAlgorithm: ConflictAlgorithm.replace);
  }

  // 示例：查询并还原TransactionModel
  static Future<List<TransactionModel>> getTransactions() async {
    final db = await _getdb();
    final List<Map<String, dynamic>> maps = await db.query('transactiontb');
    return List.generate(maps.length, (i) {
      return TransactionModel(
        id: maps[i]['id'],
        title: maps[i]['title'],
        amount: maps[i]['amount'],
        isexpense: maps[i]['isexpense'],
        category: CategoryModel(
          title: maps[i]['category_title'],
          iconpath: IconData(
            maps[i]['category_icon_code_point'],
            fontFamily: maps[i]['category_icon_font_family'],
            fontPackage: maps[i]['category_icon_font_package'],
          ),
        ),
      );
    });
  }
}

修改TransactionModel的序列化方法

class TransactionModel {
  String id;
  String title;
  int isexpense;
  int amount;
  CategoryModel category;

  TransactionModel({
    required this.category,
    required this.id,
    required this.title,
    this.isexpense = 1,
    required this.amount,
  });

  Map<String, dynamic> toJson() {
    return {
      'id': id,
      'title': title,
      'isexpense': isexpense,
      'amount': amount,
      'category_title': category.title,
      'category_icon_code_point': category.iconpath.codePoint,
      'category_icon_font_family': category.iconpath.fontFamily,
      'category_icon_font_package': category.iconpath.fontPackage,
    };
  }

  factory TransactionModel.fromjson(Map<String, dynamic> map) {
    return TransactionModel(
      id: map['id'],
      title: map['title'],
      amount: map['amount'],
      isexpense: map['isexpense'],
      category: CategoryModel(
        title: map['category_title'],
        iconpath: IconData(
          map['category_icon_code_point'],
          fontFamily: map['category_icon_font_family'],
          fontPackage: map['category_icon_font_package'],
        ),
      ),
    );
  }
}

方案二：关联表存储（适合分类可复用的场景） #

新建独立的分类表，通过外键与交易表关联，符合数据库规范化设计，便于分类的统一管理。

修改SqliteHelper

class SqliteHelper{
  static Future<Database> _getdb() async {
    return openDatabase(join(await getDatabasesPath(), 'transactiondb.db'),
        onCreate: (db, version) async {
          // 创建分类表
          await db.execute(
            """CREATE TABLE categorytb(
              id TEXT NOT NULL PRIMARY KEY,
              title TEXT NOT NULL UNIQUE,
              icon_code_point INTEGER NOT NULL,
              icon_font_family TEXT,
              icon_font_package TEXT
            );""",
          );
          // 创建交易表，添加外键关联分类
          await db.execute(
            """CREATE TABLE transactiontb(
              id TEXT NOT NULL PRIMARY KEY,
              title TEXT NOT NULL,
              amount INTEGER NOT NULL,
              isexpense INTEGER NOT NULL,
              category_id TEXT NOT NULL,
              FOREIGN KEY (category_id) REFERENCES categorytb(id)
            );""",
          );
        },
        // 升级旧版本数据库
        onUpgrade: (db, oldVersion, newVersion) async {
          if (oldVersion < 2) {
            await db.execute(
              """CREATE TABLE categorytb(
                id TEXT NOT NULL PRIMARY KEY,
                title TEXT NOT NULL UNIQUE,
                icon_code_point INTEGER NOT NULL,
                icon_font_family TEXT,
                icon_font_package TEXT
              );""",
            );
            await db.execute("ALTER TABLE transactiontb ADD COLUMN category_id TEXT NOT NULL;");
          }
        },
        version: 2);
  }

  // 新增/获取分类ID（避免重复创建相同分类）
  static Future<String> addOrGetCategoryId(CategoryModel category) async {
    final db = await _getdb();
    final existing = await db.query(
      'categorytb',
      where: 'title = ?',
      whereArgs: [category.title],
    );
    if (existing.isNotEmpty) return existing.first['id'];
    
    // 生成唯一ID（需要引入uuid包：uuid: ^4.3.3）
    final categoryId = const Uuid().v4();
    await db.insert(
      'categorytb',
      {
        'id': categoryId,
        'title': category.title,
        'icon_code_point': category.iconpath.codePoint,
        'icon_font_family': category.iconpath.fontFamily,
        'icon_font_package': category.iconpath.fontPackage,
      },
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
    return categoryId;
  }

  static Future<int> addTransaction(TransactionModel transaction) async {
    final db=await _getdb();
    final categoryId = await addOrGetCategoryId(transaction.category);
    return await db.insert('transactiontb', {
      'id': transaction.id,
      'title': transaction.title,
      'amount': transaction.amount,
      'isexpense': transaction.isexpense,
      'category_id': categoryId,
    }, conflictAlgorithm: ConflictAlgorithm.replace);
  }

  // 关联查询交易与分类
  static Future<List<TransactionModel>> getTransactions() async {
    final db = await _getdb();
    final maps = await db.rawQuery('''
      SELECT t.*, c.title as category_title, c.icon_code_point, c.icon_font_family, c.icon_font_package
      FROM transactiontb t
      JOIN categorytb c ON t.category_id = c.id
    ''');
    return List.generate(maps.length, (i) {
      return TransactionModel(
        id: maps[i]['id'],
        title: maps[i]['title'],
        amount: maps[i]['amount'],
        isexpense: maps[i]['isexpense'],
        category: CategoryModel(
          title: maps[i]['category_title'],
          iconpath: IconData(
            maps[i]['icon_code_point'],
            fontFamily: maps[i]['icon_font_family'],
            fontPackage: maps[i]['icon_font_package'],
          ),
        ),
      );
    });
  }
}

修改CategoryModel（新增ID字段）

class CategoryModel {
  String? id; // 用于数据库关联的ID
  String title;
  IconData iconpath;

  CategoryModel({this.id, required this.title, required this.iconpath});

  Map<String, dynamic> toMap() {
    return {
      'id': id,
      'title': title,
      'icon_code_point': iconpath.codePoint,
      'icon_font_family': iconpath.fontFamily,
      'icon_font_package': iconpath.fontPackage,
    };
  }

  factory CategoryModel.fromMap(Map<String, dynamic> map) {
    return CategoryModel(
      id: map['id'],
      title: map['title'],
      iconpath: IconData(
        map['icon_code_point'],
        fontFamily: map['icon_font_family'],
        fontPackage: map['icon_font_package'],
      ),
    );
  }
}

方案选择建议 #

• 扁平化存储：适合分类无需复用、业务逻辑简单的场景，代码量少，查询效率高。
• 关联表存储：适合分类需要被多个交易复用、后续可能扩展分类管理功能的场景，数据库设计更规范。

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