Flutter sqflite 数据库在 OpenHarmony 上的企业级实践与深度优化

欢迎加入开源鸿蒙跨平台社区：https://openharmonycrossplatform.csdn.net

一、引言

在移动应用开发中，本地数据库是处理结构化数据的核心基础设施。sqflite 作为 Flutter 生态中最成熟、最广泛使用的 SQLite 数据库插件，为跨平台应用提供了强大的数据持久化能力。本文将系统性地介绍如何在 Flutter-OH 项目中构建企业级数据库解决方案，深入探讨鸿蒙化适配的关键技术点，并分享实战经验与性能优化策略。

二、sqflite 核心能力解析

sqflite 是一个功能完整的 SQLite 插件，提供了以下企业级特性：

2.1 核心功能特性

• 完整的 SQL 支持：支持 SQLite 99% 的语法特性
• 事务处理：支持原子性、一致性、隔离性、持久性（ACID）
• 异步操作：所有数据库操作均为异步，不阻塞 UI 线程
• 数据库版本管理：支持 onCreate、onUpgrade、onDowngrade 完整生命周期
• 批量操作：支持高效的批量插入和更新
• 索引优化：支持复合索引、唯一索引、部分索引等
• 外键约束：支持关系型数据库的外键约束
• 内存数据库：支持临时内存数据库用于高性能场景

2.2 技术架构

┌─────────────────────────────────────────┐
│         Flutter Application             │
├─────────────────────────────────────────┤
│        sqflite Dart API Layer           │
├─────────────────────────────────────────┤
│      Platform Specific Implementation   │
│  (Android/iOS/HarmonyOS/Windows/macOS)  │
├─────────────────────────────────────────┤
│           SQLite Engine                 │
└─────────────────────────────────────────┘

三、项目架构设计与集成

3.1 完整项目依赖配置

name: flutter_harmony_db
description: A Flutter database project for OpenHarmony
publish_to: 'none'
version: 1.0.0+1

environment:
  sdk: '>=3.0.0 <4.0.0'

dependencies:
  flutter:
    sdk: flutter
  sqflite: ^2.3.2
  path_provider: ^2.1.1
  path: ^1.8.3
  provider: ^6.1.1
  equatable: ^2.0.5
  uuid: ^4.3.3

dev_dependencies:
  flutter_test:
    sdk: flutter
  flutter_lints: ^3.0.0
  mockito: ^5.4.4
  build_runner: ^2.4.7

flutter:
  uses-material-design: true

3.2 项目目录结构

lib/
├── core/
│   ├── database/
│   │   ├── database_service.dart
│   │   ├── database_constants.dart
│   │   └── migration/
│   │       ├── migration_1_to_2.dart
│   │       ├── migration_2_to_3.dart
│   │       └── migration_manager.dart
│   ├── models/
│   │   ├── user.dart
│   │   ├── product.dart
│   │   └── order.dart
│   ├── repositories/
│   │   ├── base_repository.dart
│   │   ├── user_repository.dart
│   │   ├── product_repository.dart
│   │   └── order_repository.dart
│   └── utils/
│       ├── date_utils.dart
│       └── encryption_utils.dart
├── features/
│   └── user_management/
│       ├── pages/
│       ├── widgets/
│       └── providers/
└── main.dart

3.3 数据库常量定义

class DatabaseConstants {
  static const String dbName = 'app_database.db';
  static const int dbVersion = 3;

  static const String tableUsers = 'users';
  static const String tableProducts = 'products';
  static const String tableOrders = 'orders';
  static const String tableOrderItems = 'order_items';

  static const String columnId = 'id';
  static const String columnUuid = 'uuid';
  static const String columnName = 'name';
  static const String columnEmail = 'email';
  static const String columnPhone = 'phone';
  static const String columnPassword = 'password';
  static const String columnAvatar = 'avatar';
  static const String columnStatus = 'status';
  static const String columnCreatedAt = 'created_at';
  static const String columnUpdatedAt = 'updated_at';
  static const String columnDeletedAt = 'deleted_at';

  static const String columnUserId = 'user_id';
  static const String columnOrderId = 'order_id';
  static const String columnProductId = 'product_id';
  static const String columnQuantity = 'quantity';
  static const String columnPrice = 'price';
  static const String columnTotal = 'total';
}

3.4 数据模型设计

import 'package:equatable/equatable.dart';
import 'package:uuid/uuid.dart';

class User extends Equatable {
  final int? id;
  final String uuid;
  final String name;
  final String email;
  final String? phone;
  final String? password;
  final String? avatar;
  final String status;
  final DateTime createdAt;
  final DateTime? updatedAt;
  final DateTime? deletedAt;

  const User({
    this.id,
    required this.uuid,
    required this.name,
    required this.email,
    this.phone,
    this.password,
    this.avatar,
    required this.status,
    required this.createdAt,
    this.updatedAt,
    this.deletedAt,
  });

  User copyWith({
    int? id,
    String? uuid,
    String? name,
    String? email,
    String? phone,
    String? password,
    String? avatar,
    String? status,
    DateTime? createdAt,
    DateTime? updatedAt,
    DateTime? deletedAt,
  }) {
    return User(
      id: id ?? this.id,
      uuid: uuid ?? this.uuid,
      name: name ?? this.name,
      email: email ?? this.email,
      phone: phone ?? this.phone,
      password: password ?? this.password,
      avatar: avatar ?? this.avatar,
      status: status ?? this.status,
      createdAt: createdAt ?? this.createdAt,
      updatedAt: updatedAt ?? this.updatedAt,
      deletedAt: deletedAt ?? this.deletedAt,
    );
  }

  Map<String, dynamic> toMap() {
    return {
      DatabaseConstants.columnUuid: uuid,
      DatabaseConstants.columnName: name,
      DatabaseConstants.columnEmail: email,
      DatabaseConstants.columnPhone: phone,
      DatabaseConstants.columnPassword: password,
      DatabaseConstants.columnAvatar: avatar,
      DatabaseConstants.columnStatus: status,
      DatabaseConstants.columnCreatedAt: createdAt.toIso8601String(),
      DatabaseConstants.columnUpdatedAt: updatedAt?.toIso8601String(),
      DatabaseConstants.columnDeletedAt: deletedAt?.toIso8601String(),
    };
  }

  factory User.fromMap(Map<String, dynamic> map) {
    return User(
      id: map[DatabaseConstants.columnId] as int?,
      uuid: map[DatabaseConstants.columnUuid] as String,
      name: map[DatabaseConstants.columnName] as String,
      email: map[DatabaseConstants.columnEmail] as String,
      phone: map[DatabaseConstants.columnPhone] as String?,
      password: map[DatabaseConstants.columnPassword] as String?,
      avatar: map[DatabaseConstants.columnAvatar] as String?,
      status: map[DatabaseConstants.columnStatus] as String,
      createdAt: DateTime.parse(map[DatabaseConstants.columnCreatedAt] as String),
      updatedAt: map[DatabaseConstants.columnUpdatedAt] != null
          ? DateTime.parse(map[DatabaseConstants.columnUpdatedAt] as String)
          : null,
      deletedAt: map[DatabaseConstants.columnDeletedAt] != null
          ? DateTime.parse(map[DatabaseConstants.columnDeletedAt] as String)
          : null,
    );
  }

  @override
  List<Object?> get props => [
    id, uuid, name, email, phone, password, avatar, status,
    createdAt, updatedAt, deletedAt,
  ];
}

四、数据库服务核心实现

4.1 单例模式数据库服务

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

class DatabaseService {
  static final DatabaseService _instance = DatabaseService._internal();
  static Database? _database;

  factory DatabaseService() => _instance;

  DatabaseService._internal();

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

  Future<Database> _initDatabase() async {
    final Directory directory = await _getDatabaseDirectory();
    final String path = join(directory.path, DatabaseConstants.dbName);

    return await openDatabase(
      path,
      version: DatabaseConstants.dbVersion,
      onCreate: _onCreate,
      onUpgrade: _onUpgrade,
      onDowngrade: _onDowngrade,
      onOpen: _onOpen,
      singleInstance: true,
    );
  }

  Future<Directory> _getDatabaseDirectory() async {
    if (Platform.isAndroid) {
      return await getApplicationDocumentsDirectory();
    } else if (Platform.isIOS) {
      return await getApplicationDocumentsDirectory();
    } else if (Platform.isWindows) {
      return await getApplicationSupportDirectory();
    } else if (Platform.isMacOS) {
      return await getApplicationSupportDirectory();
    } else if (Platform.isLinux) {
      return await getApplicationSupportDirectory();
    } else {
      return await getApplicationSupportDirectory();
    }
  }

  Future<void> _onCreate(Database db, int version) async {
    await _createTables(db);
    await _createIndexes(db);
    await _insertInitialData(db);
  }

  Future<void> _createTables(Database db) async {
    await db.execute('''
      CREATE TABLE ${DatabaseConstants.tableUsers} (
        ${DatabaseConstants.columnId} INTEGER PRIMARY KEY AUTOINCREMENT,
        ${DatabaseConstants.columnUuid} TEXT NOT NULL UNIQUE,
        ${DatabaseConstants.columnName} TEXT NOT NULL,
        ${DatabaseConstants.columnEmail} TEXT NOT NULL UNIQUE,
        ${DatabaseConstants.columnPhone} TEXT,
        ${DatabaseConstants.columnPassword} TEXT,
        ${DatabaseConstants.columnAvatar} TEXT,
        ${DatabaseConstants.columnStatus} TEXT NOT NULL DEFAULT 'active',
        ${DatabaseConstants.columnCreatedAt} TEXT NOT NULL,
        ${DatabaseConstants.columnUpdatedAt} TEXT,
        ${DatabaseConstants.columnDeletedAt} TEXT
      )
    ''');

    await db.execute('''
      CREATE TABLE ${DatabaseConstants.tableProducts} (
        ${DatabaseConstants.columnId} INTEGER PRIMARY KEY AUTOINCREMENT,
        ${DatabaseConstants.columnUuid} TEXT NOT NULL UNIQUE,
        ${DatabaseConstants.columnName} TEXT NOT NULL,
        ${DatabaseConstants.columnPrice} REAL NOT NULL,
        ${DatabaseConstants.columnStatus} TEXT NOT NULL DEFAULT 'available',
        ${DatabaseConstants.columnCreatedAt} TEXT NOT NULL,
        ${DatabaseConstants.columnUpdatedAt} TEXT,
        ${DatabaseConstants.columnDeletedAt} TEXT
      )
    ''');

    await db.execute('''
      CREATE TABLE ${DatabaseConstants.tableOrders} (
        ${DatabaseConstants.columnId} INTEGER PRIMARY KEY AUTOINCREMENT,
        ${DatabaseConstants.columnUuid} TEXT NOT NULL UNIQUE,
        ${DatabaseConstants.columnUserId} INTEGER NOT NULL,
        ${DatabaseConstants.columnTotal} REAL NOT NULL,
        ${DatabaseConstants.columnStatus} TEXT NOT NULL DEFAULT 'pending',
        ${DatabaseConstants.columnCreatedAt} TEXT NOT NULL,
        ${DatabaseConstants.columnUpdatedAt} TEXT,
        FOREIGN KEY (${DatabaseConstants.columnUserId}) 
          REFERENCES ${DatabaseConstants.tableUsers}(${DatabaseConstants.columnId})
          ON DELETE CASCADE
      )
    ''');

    await db.execute('''
      CREATE TABLE ${DatabaseConstants.tableOrderItems} (
        ${DatabaseConstants.columnId} INTEGER PRIMARY KEY AUTOINCREMENT,
        ${DatabaseConstants.columnOrderId} INTEGER NOT NULL,
        ${DatabaseConstants.columnProductId} INTEGER NOT NULL,
        ${DatabaseConstants.columnQuantity} INTEGER NOT NULL,
        ${DatabaseConstants.columnPrice} REAL NOT NULL,
        ${DatabaseConstants.columnCreatedAt} TEXT NOT NULL,
        FOREIGN KEY (${DatabaseConstants.columnOrderId}) 
          REFERENCES ${DatabaseConstants.tableOrders}(${DatabaseConstants.columnId})
          ON DELETE CASCADE,
        FOREIGN KEY (${DatabaseConstants.columnProductId}) 
          REFERENCES ${DatabaseConstants.tableProducts}(${DatabaseConstants.columnId})
          ON DELETE CASCADE
      )
    ''');
  }

  Future<void> _createIndexes(Database db) async {
    await db.execute('''
      CREATE INDEX idx_users_email 
      ON ${DatabaseConstants.tableUsers}(${DatabaseConstants.columnEmail})
    ''');

    await db.execute('''
      CREATE INDEX idx_users_status 
      ON ${DatabaseConstants.tableUsers}(${DatabaseConstants.columnStatus})
    ''');

    await db.execute('''
      CREATE INDEX idx_orders_user_id 
      ON ${DatabaseConstants.tableOrders}(${DatabaseConstants.columnUserId})
    ''');

    await db.execute('''
      CREATE INDEX idx_orders_status 
      ON ${DatabaseConstants.tableOrders}(${DatabaseConstants.columnStatus})
    ''');

    await db.execute('''
      CREATE INDEX idx_order_items_order_id 
      ON ${DatabaseConstants.tableOrderItems}(${DatabaseConstants.columnOrderId})
    ''');
  }

  Future<void> _insertInitialData(Database db) async {
    final now = DateTime.now().toIso8601String();
    const uuid = Uuid();

    await db.insert(
      DatabaseConstants.tableUsers,
      {
        DatabaseConstants.columnUuid: uuid.v4(),
        DatabaseConstants.columnName: 'Admin',
        DatabaseConstants.columnEmail: 'admin@example.com',
        DatabaseConstants.columnStatus: 'active',
        DatabaseConstants.columnCreatedAt: now,
      },
    );
  }

  Future<void> _onUpgrade(Database db, int oldVersion, int newVersion) async {
    final MigrationManager migrationManager = MigrationManager();
    await migrationManager.executeMigrations(db, oldVersion, newVersion);
  }

  Future<void> _onDowngrade(Database db, int oldVersion, int newVersion) async {
    print('Database downgrade from $oldVersion to $newVersion');
  }

  Future<void> _onOpen(Database db) async {
    await db.execute('PRAGMA foreign_keys = ON');
    await db.execute('PRAGMA journal_mode = WAL');
    await db.execute('PRAGMA synchronous = NORMAL');
    await db.execute('PRAGMA cache_size = -2000');
  }

  Future<void> close() async {
    if (_database != null) {
      await _database!.close();
      _database = null;
    }
  }

  Future<void> deleteDatabase() async {
    final Directory directory = await _getDatabaseDirectory();
    final String path = join(directory.path, DatabaseConstants.dbName);
    await databaseFactory.deleteDatabase(path);
    _database = null;
  }
}

4.2 数据库迁移管理

import 'package:sqflite/sqflite.dart';

class MigrationManager {
  final Map<int, Migration> migrations = {
    2: Migration1To2(),
    3: Migration2To3(),
  };

  Future<void> executeMigrations(Database db, int oldVersion, int newVersion) async {
    for (int version = oldVersion + 1; version <= newVersion; version++) {
      final migration = migrations[version];
      if (migration != null) {
        print('Executing migration from ${version - 1} to $version');
        await migration.up(db);
      }
    }
  }
}

abstract class Migration {
  Future<void> up(Database db);
  Future<void> down(Database db);
}

class Migration1To2 extends Migration {
  @override
  Future<void> up(Database db) async {
    await db.execute('''
      ALTER TABLE ${DatabaseConstants.tableUsers} 
      ADD COLUMN ${DatabaseConstants.columnPhone} TEXT
    ''');
  }

  @override
  Future<void> down(Database db) async {
    await db.execute('''
      ALTER TABLE ${DatabaseConstants.tableUsers} 
      DROP COLUMN ${DatabaseConstants.columnPhone}
    ''');
  }
}

class Migration2To3 extends Migration {
  @override
  Future<void> up(Database db) async {
    await db.execute('''
      ALTER TABLE ${DatabaseConstants.tableUsers} 
      ADD COLUMN ${DatabaseConstants.columnAvatar} TEXT
    ''');
  }

  @override
  Future<void> down(Database db) async {
    await db.execute('''
      ALTER TABLE ${DatabaseConstants.tableUsers} 
      DROP COLUMN ${DatabaseConstants.columnAvatar}
    ''');
  }
}

五、仓储层设计与实现

5.1 基础仓储类

abstract class BaseRepository<T> {
  final DatabaseService databaseService;

  BaseRepository(this.databaseService);

  String get tableName;
  T fromMap(Map<String, dynamic> map);
  Map<String, dynamic> toMap(T entity);

  Future<T?> getById(int id) async {
    final db = await databaseService.database;
    final List<Map<String, dynamic>> maps = await db.query(
      tableName,
      where: '${DatabaseConstants.columnId} = ?',
      whereArgs: [id],
    );
    if (maps.isEmpty) return null;
    return fromMap(maps.first);
  }

  Future<List<T>> getAll() async {
    final db = await databaseService.database;
    final List<Map<String, dynamic>> maps = await db.query(tableName);
    return maps.map((map) => fromMap(map)).toList();
  }

  Future<T> insert(T entity) async {
    final db = await databaseService.database;
    final id = await db.insert(tableName, toMap(entity));
    final result = await getById(id);
    return result!;
  }

  Future<int> update(T entity) async {
    final db = await databaseService.database;
    return await db.update(
      tableName,
      toMap(entity),
      where: '${DatabaseConstants.columnId} = ?',
      whereArgs: [(entity as dynamic).id],
    );
  }

  Future<int> delete(int id) async {
    final db = await databaseService.database;
    return await db.delete(
      tableName,
      where: '${DatabaseConstants.columnId} = ?',
      whereArgs: [id],
    );
  }

  Future<int> softDelete(int id) async {
    final db = await databaseService.database;
    return await db.update(
      tableName,
      {
        DatabaseConstants.columnDeletedAt: DateTime.now().toIso8601String(),
      },
      where: '${DatabaseConstants.columnId} = ?',
      whereArgs: [id],
    );
  }

  Future<List<T>> query({
    String? where,
    List<dynamic>? whereArgs,
    String? orderBy,
    int? limit,
    int? offset,
  }) async {
    final db = await databaseService.database;
    final List<Map<String, dynamic>> maps = await db.query(
      tableName,
      where: where,
      whereArgs: whereArgs,
      orderBy: orderBy,
      limit: limit,
      offset: offset,
    );
    return maps.map((map) => fromMap(map)).toList();
  }

  Future<int> count({String? where, List<dynamic>? whereArgs}) async {
    final db = await databaseService.database;
    final result = await db.rawQuery(
      'SELECT COUNT(*) FROM $tableName${where != null ? ' WHERE $where' : ''}',
      whereArgs,
    );
    return Sqflite.firstIntValue(result) ?? 0;
  }
}

5.2 用户仓储实现

class UserRepository extends BaseRepository<User> {
  UserRepository(super.databaseService);

  @override
  String get tableName => DatabaseConstants.tableUsers;

  @override
  User fromMap(Map<String, dynamic> map) => User.fromMap(map);

  @override
  Map<String, dynamic> toMap(User entity) => entity.toMap();

  Future<User?> getByEmail(String email) async {
    final db = await databaseService.database;
    final List<Map<String, dynamic>> maps = await db.query(
      tableName,
      where: '${DatabaseConstants.columnEmail} = ?',
      whereArgs: [email],
    );
    if (maps.isEmpty) return null;
    return fromMap(maps.first);
  }

  Future<User?> getByUuid(String uuid) async {
    final db = await databaseService.database;
    final List<Map<String, dynamic>> maps = await db.query(
      tableName,
      where: '${DatabaseConstants.columnUuid} = ?',
      whereArgs: [uuid],
    );
    if (maps.isEmpty) return null;
    return fromMap(maps.first);
  }

  Future<List<User>> getActiveUsers() async {
    return query(
      where: '${DatabaseConstants.columnStatus} = ? AND ${DatabaseConstants.columnDeletedAt} IS NULL',
      whereArgs: ['active'],
      orderBy: '${DatabaseConstants.columnCreatedAt} DESC',
    );
  }

  Future<List<User>> searchUsers(String keyword) async {
    final db = await databaseService.database;
    final List<Map<String, dynamic>> maps = await db.query(
      tableName,
      where: '''
        (${DatabaseConstants.columnName} LIKE ? OR ${DatabaseConstants.columnEmail} LIKE ?)
        AND ${DatabaseConstants.columnDeletedAt} IS NULL
      ''',
      whereArgs: ['%$keyword%', '%$keyword%'],
      orderBy: '${DatabaseConstants.columnName} ASC',
    );
    return maps.map((map) => fromMap(map)).toList();
  }

  Future<User> createUser({
    required String name,
    required String email,
    String? phone,
    String? password,
    String? avatar,
  }) async {
    final user = User(
      uuid: const Uuid().v4(),
      name: name,
      email: email,
      phone: phone,
      password: password,
      avatar: avatar,
      status: 'active',
      createdAt: DateTime.now(),
    );
    return await insert(user);
  }

  Future<User> updateUser(User user) async {
    final updatedUser = user.copyWith(
      updatedAt: DateTime.now(),
    );
    await update(updatedUser);
    return updatedUser;
  }
}

六、事务处理与批量操作

6.1 事务管理

class TransactionService {
  final DatabaseService databaseService;

  TransactionService(this.databaseService);

  Future<T> executeTransaction<T>(
    Future<T> Function(Transaction txn) action,
  ) async {
    final db = await databaseService.database;
    return await db.transaction(action);
  }

  Future<void> createOrderWithItems({
    required int userId,
    required List<Map<String, dynamic>> items,
  }) async {
    await executeTransaction((txn) async {
      final now = DateTime.now().toIso8601String();
      const uuid = Uuid();

      double total = 0;
      for (var item in items) {
        total += item['price'] * item['quantity'];
      }

      final orderId = await txn.insert(
        DatabaseConstants.tableOrders,
        {
          DatabaseConstants.columnUuid: uuid.v4(),
          DatabaseConstants.columnUserId: userId,
          DatabaseConstants.columnTotal: total,
          DatabaseConstants.columnStatus: 'pending',
          DatabaseConstants.columnCreatedAt: now,
        },
      );

      for (var item in items) {
        await txn.insert(
          DatabaseConstants.tableOrderItems,
          {
            DatabaseConstants.columnOrderId: orderId,
            DatabaseConstants.columnProductId: item['productId'],
            DatabaseConstants.columnQuantity: item['quantity'],
            DatabaseConstants.columnPrice: item['price'],
            DatabaseConstants.columnCreatedAt: now,
          },
        );
      }
    });
  }

  Future<void> batchInsertUsers(List<User> users) async {
    await executeTransaction((txn) async {
      for (var user in users) {
        await txn.insert(
          DatabaseConstants.tableUsers,
          user.toMap(),
          conflictAlgorithm: ConflictAlgorithm.replace,
        );
      }
    });
  }

  Future<void> batchUpdateUsers(List<User> users) async {
    await executeTransaction((txn) async {
      for (var user in users) {
        await txn.update(
          DatabaseConstants.tableUsers,
          user.toMap(),
          where: '${DatabaseConstants.columnId} = ?',
          whereArgs: [user.id],
        );
      }
    });
  }
}

6.2 批量操作优化

class BatchOperationService {
  final DatabaseService databaseService;

  BatchOperationService(this.databaseService);

  Future<void> batchInsert<T>(
    String table,
    List<T> items,
    Map<String, dynamic> Function(T) toMap, {
    int batchSize = 100,
  }) async {
    final db = await databaseService.database;
    final totalItems = items.length;

    for (int i = 0; i < totalItems; i += batchSize) {
      final end = (i + batchSize < totalItems) ? i + batchSize : totalItems;
      final batch = items.sublist(i, end);

      await db.transaction((txn) async {
        for (var item in batch) {
          await txn.insert(
            table,
            toMap(item),
            conflictAlgorithm: ConflictAlgorithm.replace,
          );
        }
      });

      print('Inserted batch ${i ~/ batchSize + 1} of ${(totalItems / batchSize).ceil()}');
    }
  }

  Future<List<Map<String, dynamic>>> batchQuery(
    String table,
    List<int> ids, {
    int batchSize = 100,
  }) async {
    final db = await databaseService.database;
    final List<Map<String, dynamic>> results = [];

    for (int i = 0; i < ids.length; i += batchSize) {
      final end = (i + batchSize < ids.length) ? i + batchSize : ids.length;
      final batchIds = ids.sublist(i, end);

      final placeholders = List.filled(batchIds.length, '?').join(',');
      final maps = await db.query(
        table,
        where: '${DatabaseConstants.columnId} IN ($placeholders)',
        whereArgs: batchIds,
      );

      results.addAll(maps);
    }

    return results;
  }
}

七、鸿蒙化适配深度解析

7.1 OpenHarmony 权限配置

{
  "module": {
    "name": "entry",
    "type": "entry",
    "description": "$string:module_desc",
    "mainElement": "EntryAbility",
    "deviceTypes": [
      "phone",
      "tablet"
    ],
    "deliveryWithInstall": true,
    "installationFree": false,
    "pages": "$profile:main_pages",
    "abilities": [
      {
        "name": "EntryAbility",
        "srcEntry": "./ets/entryability/EntryAbility.ets",
        "description": "$string:EntryAbility_desc",
        "icon": "$media:icon",
        "label": "$string:EntryAbility_label",
        "startWindowIcon": "$media:icon",
        "startWindowBackground": "$color:start_window_background",
        "exported": true,
        "skills": [
          {
            "entities": [
              "entity.system.home"
            ],
            "actions": [
              "action.system.home"
            ]
          }
        ]
      }
    ],
    "requestPermissions": [
      {
        "name": "ohos.permission.INTERNET",
        "reason": "$string:internet_permission_reason",
        "usedScene": {
          "abilities": ["EntryAbility"],
          "when": "inuse"
        }
      },
      {
        "name": "ohos.permission.READ_MEDIA",
        "reason": "$string:read_media_permission_reason",
        "usedScene": {
          "abilities": ["EntryAbility"],
          "when": "inuse"
        }
      },
      {
        "name": "ohos.permission.WRITE_MEDIA",
        "reason": "$string:write_media_permission_reason",
        "usedScene": {
          "abilities": ["EntryAbility"],
          "when": "inuse"
        }
      },
      {
        "name": "ohos.permission.READ_USER_STORAGE",
        "reason": "$string:read_storage_permission_reason",
        "usedScene": {
          "abilities": ["EntryAbility"],
          "when": "inuse"
        }
      },
      {
        "name": "ohos.permission.WRITE_USER_STORAGE",
        "reason": "$string:write_storage_permission_reason",
        "usedScene": {
          "abilities": ["EntryAbility"],
          "when": "inuse"
        }
      }
    ]
  }
}

7.2 数据库路径优化适配

class HarmonyDatabasePathProvider {
  static Future<Directory> getDatabaseDirectory() async {
    if (Platform.isHarmony) {
      try {
        final appSupportDir = await getApplicationSupportDirectory();
        final dbDir = Directory('${appSupportDir.path}/databases');
        if (!await dbDir.exists()) {
          await dbDir.create(recursive: true);
        }
        return dbDir;
      } catch (e) {
        print('Failed to get Harmony database directory: $e');
        return await getApplicationDocumentsDirectory();
      }
    } else if (Platform.isAndroid) {
      return await getApplicationDocumentsDirectory();
    } else if (Platform.isIOS) {
      return await getApplicationDocumentsDirectory();
    } else {
      return await getApplicationSupportDirectory();
    }
  }

  static Future<String> getDatabasePath(String dbName) async {
    final directory = await getDatabaseDirectory();
    return join(directory.path, dbName);
  }

  static Future<void> ensureDatabaseDirectory() async {
    final directory = await getDatabaseDirectory();
    if (!await directory.exists()) {
      await directory.create(recursive: true);
    }
  }

  static Future<List<FileSystemEntity>> listDatabaseFiles() async {
    final directory = await getDatabaseDirectory();
    if (!await directory.exists()) {
      return [];
    }
    return directory.listSync().where((file) {
      return file.path.endsWith('.db') || 
             file.path.endsWith('.db-wal') || 
             file.path.endsWith('.db-shm');
    }).toList();
  }
}

7.3 平台特定数据库配置

class PlatformDatabaseConfig {
  static Future<Map<String, dynamic>> getConfig() async {
    final config = <String, dynamic>{};

    if (Platform.isHarmony) {
      config.addAll({
        'journalMode': 'WAL',
        'synchronous': 'NORMAL',
        'cacheSize': -4000,
        'tempStore': 'MEMORY',
        'foreignKeys': true,
        'lockingMode': 'EXCLUSIVE',
        'autoVacuum': 'NONE',
      });
    } else if (Platform.isAndroid) {
      config.addAll({
        'journalMode': 'WAL',
        'synchronous': 'NORMAL',
        'cacheSize': -2000,
        'foreignKeys': true,
      });
    } else if (Platform.isIOS) {
      config.addAll({
        'journalMode': 'WAL',
        'synchronous': 'FULL',
        'cacheSize': -2000,
        'foreignKeys': true,
      });
    } else {
      config.addAll({
        'journalMode': 'DELETE',
        'synchronous': 'FULL',
        'foreignKeys': true,
      });
    }

    return config;
  }

  static Future<void> applyConfig(Database db) async {
    final config = await getConfig();

    if (config['journalMode'] != null) {
      await db.execute('PRAGMA journal_mode = ${config['journalMode']}');
    }
    if (config['synchronous'] != null) {
      await db.execute('PRAGMA synchronous = ${config['synchronous']}');
    }
    if (config['cacheSize'] != null) {
      await db.execute('PRAGMA cache_size = ${config['cacheSize']}');
    }
    if (config['foreignKeys'] == true) {
      await db.execute('PRAGMA foreign_keys = ON');
    }
    if (config['tempStore'] != null) {
      await db.execute('PRAGMA temp_store = ${config['tempStore']}');
    }
    if (config['lockingMode'] != null) {
      await db.execute('PRAGMA locking_mode = ${config['lockingMode']}');
    }
    if (config['autoVacuum'] != null) {
      await db.execute('PRAGMA auto_vacuum = ${config['autoVacuum']}');
    }
  }
}

7.4 数据库备份与恢复

class DatabaseBackupService {
  final DatabaseService databaseService;

  DatabaseBackupService(this.databaseService);

  Future<String> createBackup() async {
    final dbPath = await HarmonyDatabasePathProvider.getDatabasePath(
      DatabaseConstants.dbName,
    );
    final backupDir = await _getBackupDirectory();
    final timestamp = DateTime.now().millisecondsSinceEpoch;
    final backupPath = '${backupDir.path}/backup_$timestamp.db';

    final dbFile = File(dbPath);
    if (!await dbFile.exists()) {
      throw Exception('Database file not found');
    }

    await dbFile.copy(backupPath);

    final walFile = File('$dbPath-wal');
    if (await walFile.exists()) {
      await walFile.copy('$backupPath-wal');
    }

    final shmFile = File('$dbPath-shm');
    if (await shmFile.exists()) {
      await shmFile.copy('$backupPath-shm');
    }

    return backupPath;
  }

  Future<void> restoreBackup(String backupPath) async {
    final dbPath = await HarmonyDatabasePathProvider.getDatabasePath(
      DatabaseConstants.dbName,
    );

    await databaseService.close();

    final backupFile = File(backupPath);
    if (!await backupFile.exists()) {
      throw Exception('Backup file not found');
    }

    await backupFile.copy(dbPath);

    final backupWalFile = File('$backupPath-wal');
    if (await backupWalFile.exists()) {
      await backupWalFile.copy('$dbPath-wal');
    }

    final backupShmFile = File('$backupPath-shm');
    if (await backupShmFile.exists()) {
      await backupShmFile.copy('$dbPath-shm');
    }
  }

  Future<Directory> _getBackupDirectory() async {
    final appDir = await getApplicationSupportDirectory();
    final backupDir = Directory('${appDir.path}/backups');
    if (!await backupDir.exists()) {
      await backupDir.create(recursive: true);
    }
    return backupDir;
  }

  Future<List<FileSystemEntity>> listBackups() async {
    final backupDir = await _getBackupDirectory();
    if (!await backupDir.exists()) {
      return [];
    }
    return backupDir.listSync().where((file) {
      return file.path.endsWith('.db');
    }).toList();
  }

  Future<void> deleteOldBackups({int keepCount = 5}) async {
    final backups = await listBackups();
    if (backups.length <= keepCount) return;

    backups.sort((a, b) {
      return b.statSync().modified.compareTo(a.statSync().modified);
    });

    for (int i = keepCount; i < backups.length; i++) {
      await backups[i].delete();
      final walFile = File('${backups[i].path}-wal');
      if (await walFile.exists()) {
        await walFile.delete();
      }
      final shmFile = File('${backups[i].path}-shm');
      if (await shmFile.exists()) {
        await shmFile.delete();
      }
    }
  }
}

八、性能优化策略

8.1 数据库性能调优

class DatabasePerformanceOptimizer {
  final DatabaseService databaseService;

  DatabasePerformanceOptimizer(this.databaseService);

  Future<void> optimizeDatabase() async {
    final db = await databaseService.database;

    await db.execute('VACUUM');
    await db.execute('ANALYZE');
    await db.execute('REINDEX');
  }

  Future<Map<String, dynamic>> getDatabaseStats() async {
    final db = await databaseService.database;
    final stats = <String, dynamic>{};

    final pageCountResult = await db.rawQuery('PRAGMA page_count');
    stats['pageCount'] = Sqflite.firstIntValue(pageCountResult);

    final pageSizeResult = await db.rawQuery('PRAGMA page_size');
    stats['pageSize'] = Sqflite.firstIntValue(pageSizeResult);

    if (stats['pageCount'] != null && stats['pageSize'] != null) {
      stats['databaseSize'] = stats['pageCount'] * stats['pageSize'];
    }

    final freelistCountResult = await db.rawQuery('PRAGMA freelist_count');
    stats['freelistCount'] = Sqflite.firstIntValue(freelistCountResult);

    final cacheSizeResult = await db.rawQuery('PRAGMA cache_size');
    stats['cacheSize'] = Sqflite.firstIntValue(cacheSizeResult);

    return stats;
  }

  Future<void> rebuildIndexes() async {
    final db = await databaseService.database;
    await db.execute('REINDEX');
  }

  Future<void> shrinkDatabase() async {
    final db = await databaseService.database;
    await db.execute('VACUUM');
  }
}

8.2 查询性能优化

class QueryOptimizer {
  static String buildSelectQuery({
    required String table,
    List<String>? columns,
    String? where,
    List<String>? orderBy,
    int? limit,
    int? offset,
  }) {
    final buffer = StringBuffer();

    buffer.write('SELECT ');
    if (columns == null || columns.isEmpty) {
      buffer.write('*');
    } else {
      buffer.write(columns.join(', '));
    }

    buffer.write(' FROM $table');

    if (where != null) {
      buffer.write(' WHERE $where');
    }

    if (orderBy != null && orderBy.isNotEmpty) {
      buffer.write(' ORDER BY ${orderBy.join(', ')}');
    }

    if (limit != null) {
      buffer.write(' LIMIT $limit');
    }

    if (offset != null) {
      buffer.write(' OFFSET $offset');
    }

    return buffer.toString();
  }

  static String buildCountQuery({
    required String table,
    String? where,
  }) {
    final buffer = StringBuffer('SELECT COUNT(*) FROM $table');
    if (where != null) {
      buffer.write(' WHERE $where');
    }
    return buffer.toString();
  }

  static String buildPaginationQuery({
    required String table,
    required int page,
    required int pageSize,
    List<String>? columns,
    String? where,
    List<String>? orderBy,
  }) {
    final offset = (page - 1) * pageSize;
    return buildSelectQuery(
      table: table,
      columns: columns,
      where: where,
      orderBy: orderBy,
      limit: pageSize,
      offset: offset,
    );
  }
}

8.3 连接池与缓存策略

class DatabaseCacheManager {
  final DatabaseService databaseService;
  final Map<String, dynamic> _cache = {};
  final Map<String, DateTime> _cacheTimestamps = {};
  static const Duration _defaultCacheDuration = Duration(minutes: 5);

  DatabaseCacheManager(this.databaseService);

  Future<T> getCached<T>(
    String key,
    Future<T> Function() fetch, {
    Duration? duration,
  }) async {
    final cacheDuration = duration ?? _defaultCacheDuration;
    final now = DateTime.now();

    if (_cache.containsKey(key)) {
      final timestamp = _cacheTimestamps[key];
      if (timestamp != null && now.difference(timestamp) < cacheDuration) {
        return _cache[key] as T;
      }
    }

    final value = await fetch();
    _cache[key] = value;
    _cacheTimestamps[key] = now;
    return value;
  }

  void invalidate(String key) {
    _cache.remove(key);
    _cacheTimestamps.remove(key);
  }

  void invalidateAll() {
    _cache.clear();
    _cacheTimestamps.clear();
  }

  void invalidatePattern(String pattern) {
    final keysToRemove = _cache.keys.where((key) => key.contains(pattern)).toList();
    for (var key in keysToRemove) {
      invalidate(key);
    }
  }
}

九、完整实战案例

9.1 用户管理模块

class UserManager {
  final UserRepository userRepository;
  final DatabaseCacheManager cacheManager;

  UserManager({
    required this.userRepository,
    required this.cacheManager,
  });

  Future<User> registerUser({
    required String name,
    required String email,
    String? phone,
    String? password,
  }) async {
    final existingUser = await userRepository.getByEmail(email);
    if (existingUser != null) {
      throw Exception('User with this email already exists');
    }

    return await userRepository.createUser(
      name: name,
      email: email,
      phone: phone,
      password: password,
    );
  }

  Future<User?> loginUser(String email, String password) async {
    final user = await userRepository.getByEmail(email);
    if (user == null) {
      return null;
    }

    if (user.status != 'active') {
      throw Exception('User account is not active');
    }

    if (user.password != password) {
      throw Exception('Invalid password');
    }

    return user;
  }

  Future<List<User>> getUsers({int page = 1, int pageSize = 20}) async {
    final cacheKey = 'users_page_${page}_$pageSize';
    return await cacheManager.getCached(
      cacheKey,
      () async {
        final db = await userRepository.databaseService.database;
        final offset = (page - 1) * pageSize;
        final maps = await db.query(
          DatabaseConstants.tableUsers,
          where: '${DatabaseConstants.columnDeletedAt} IS NULL',
          orderBy: '${DatabaseConstants.columnCreatedAt} DESC',
          limit: pageSize,
          offset: offset,
        );
        return maps.map((map) => User.fromMap(map)).toList();
      },
    );
  }

  Future<void> updateUserProfile(User user) async {
    await userRepository.updateUser(user);
    cacheManager.invalidatePattern('users_');
  }

  Future<void> deleteUser(int userId) async {
    await userRepository.softDelete(userId);
    cacheManager.invalidatePattern('users_');
  }
}

9.2 主应用集成

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

void main() async {
  WidgetsFlutterBinding.ensureInitialized();

  final databaseService = DatabaseService();
  await databaseService.database;

  final userRepository = UserRepository(databaseService);
  final cacheManager = DatabaseCacheManager(databaseService);
  final userManager = UserManager(
    userRepository: userRepository,
    cacheManager: cacheManager,
  );

  runApp(
    MultiProvider(
      providers: [
        Provider.value(value: databaseService),
        Provider.value(value: userRepository),
        Provider.value(value: cacheManager),
        Provider.value(value: userManager),
      ],
      child: const MyApp(),
    ),
  );
}

class MyApp extends StatelessWidget {
  const MyApp({super.key});

  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      title: 'Flutter Harmony DB Demo',
      theme: ThemeData(
        primarySwatch: Colors.blue,
        useMaterial3: true,
      ),
      home: const HomePage(),
    );
  }
}

class HomePage extends StatefulWidget {
  const HomePage({super.key});

  @override
  State<HomePage> createState() => _HomePageState();
}

class _HomePageState extends State<HomePage> {
  List<User> _users = [];
  bool _isLoading = true;

  @override
  void initState() {
    super.initState();
    _loadUsers();
  }

  Future<void> _loadUsers() async {
    final userManager = Provider.of<UserManager>(context, listen: false);
    try {
      final users = await userManager.getUsers();
      setState(() {
        _users = users;
        _isLoading = false;
      });
    } catch (e) {
      setState(() {
        _isLoading = false;
      });
      ScaffoldMessenger.of(context).showSnackBar(
        SnackBar(content: Text('Error loading users: $e')),
      );
    }
  }

  Future<void> _showAddUserDialog() async {
    final nameController = TextEditingController();
    final emailController = TextEditingController();
    final phoneController = TextEditingController();

    if (!mounted) return;

    await showDialog(
      context: context,
      builder: (context) => AlertDialog(
        title: const Text('Add User'),
        content: Column(
          mainAxisSize: MainAxisSize.min,
          children: [
            TextField(
              controller: nameController,
              decoration: const InputDecoration(labelText: 'Name'),
            ),
            TextField(
              controller: emailController,
              decoration: const InputDecoration(labelText: 'Email'),
            ),
            TextField(
              controller: phoneController,
              decoration: const InputDecoration(labelText: 'Phone'),
            ),
          ],
        ),
        actions: [
          TextButton(
            onPressed: () => Navigator.pop(context),
            child: const Text('Cancel'),
          ),
          TextButton(
            onPressed: () async {
              final userManager = Provider.of<UserManager>(context, listen: false);
              try {
                await userManager.registerUser(
                  name: nameController.text,
                  email: emailController.text,
                  phone: phoneController.text,
                );
                if (!mounted) return;
                Navigator.pop(context);
                await _loadUsers();
              } catch (e) {
                if (!mounted) return;
                ScaffoldMessenger.of(context).showSnackBar(
                  SnackBar(content: Text('Error: $e')),
                );
              }
            },
            child: const Text('Add'),
          ),
        ],
      ),
    );
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: const Text('User Management'),
        actions: [
          IconButton(
            icon: const Icon(Icons.add),
            onPressed: _showAddUserDialog,
          ),
        ],
      ),
      body: _isLoading
          ? const Center(child: CircularProgressIndicator())
          : ListView.builder(
              itemCount: _users.length,
              itemBuilder: (context, index) {
                final user = _users[index];
                return ListTile(
                  title: Text(user.name),
                  subtitle: Text(user.email),
                  trailing: Text(user.status),
                  onTap: () {
                    Navigator.push(
                      context,
                      MaterialPageRoute(
                        builder: (context) => UserDetailPage(user: user),
                      ),
                    );
                  },
                );
              },
            ),
    );
  }
}

class UserDetailPage extends StatelessWidget {
  final User user;

  const UserDetailPage({super.key, required this.user});

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: Text(user.name),
      ),
      body: Padding(
        padding: const EdgeInsets.all(16.0),
        child: Column(
          crossAxisAlignment: CrossAxisAlignment.start,
          children: [
            Text('UUID: ${user.uuid}'),
            const SizedBox(height: 8),
            Text('Email: ${user.email}'),
            const SizedBox(height: 8),
            Text('Phone: ${user.phone ?? 'N/A'}'),
            const SizedBox(height: 8),
            Text('Status: ${user.status}'),
            const SizedBox(height: 8),
            Text('Created At: ${user.createdAt}'),
            const SizedBox(height: 8),
            Text('Updated At: ${user.updatedAt ?? 'N/A'}'),
          ],
        ),
      ),
    );
  }
}

十、测试策略

10.1 单元测试

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

class MockDatabaseService extends Mock implements DatabaseService {}

void main() {
  group('UserRepository Tests', () {
    late Database testDb;
    late UserRepository userRepository;
    late DatabaseService databaseService;

    setUp(() async {
      testDb = await openDatabase(
        join(await getDatabasesPath(), 'test_database.db'),
        version: 1,
        onCreate: (db, version) async {
          await db.execute('''
            CREATE TABLE ${DatabaseConstants.tableUsers} (
              ${DatabaseConstants.columnId} INTEGER PRIMARY KEY AUTOINCREMENT,
              ${DatabaseConstants.columnUuid} TEXT NOT NULL UNIQUE,
              ${DatabaseConstants.columnName} TEXT NOT NULL,
              ${DatabaseConstants.columnEmail} TEXT NOT NULL UNIQUE,
              ${DatabaseConstants.columnStatus} TEXT NOT NULL DEFAULT 'active',
              ${DatabaseConstants.columnCreatedAt} TEXT NOT NULL
            )
          ''');
        },
      );

      databaseService = MockDatabaseService();
      when(databaseService.database).thenAnswer((_) async => testDb);
      
      userRepository = UserRepository(databaseService);
    });

    tearDown(() async {
      await testDb.close();
      await databaseFactory.deleteDatabase(
        join(await getDatabasesPath(), 'test_database.db'),
      );
    });

    test('createUser should insert user into database', () async {
      final user = await userRepository.createUser(
        name: 'Test User',
        email: 'test@example.com',
      );

      expect(user.id, isNotNull);
      expect(user.name, 'Test User');
      expect(user.email, 'test@example.com');
    });

    test('getByEmail should return correct user', () async {
      await userRepository.createUser(
        name: 'Test User',
        email: 'test@example.com',
      );

      final user = await userRepository.getByEmail('test@example.com');

      expect(user, isNotNull);
      expect(user?.name, 'Test User');
    });

    test('getAll should return all users', () async {
      await userRepository.createUser(
        name: 'User 1',
        email: 'user1@example.com',
      );
      await userRepository.createUser(
        name: 'User 2',
        email: 'user2@example.com',
      );

      final users = await userRepository.getAll();

      expect(users.length, 2);
    });
  });
}

10.2 集成测试

import 'package:flutter/material.dart';
import 'package:flutter_test/flutter_test.dart';
import 'package:integration_test/integration_test.dart';

void main() {
  IntegrationTestWidgetsFlutterBinding.ensureInitialized();

  group('App Integration Tests', () {
    testWidgets('User management flow works correctly', (tester) async {
      await tester.pumpWidget(const MyApp());

      expect(find.text('User Management'), findsOneWidget);
      expect(find.byType(CircularProgressIndicator), findsOneWidget);

      await tester.pumpAndSettle();

      expect(find.byType(ListTile), findsWidgets);

      await tester.tap(find.byIcon(Icons.add));
      await tester.pumpAndSettle();

      expect(find.text('Add User'), findsOneWidget);

      await tester.enterText(find.byLabelText('Name'), 'Integration Test User');
      await tester.enterText(find.byLabelText('Email'), 'integration@test.com');

      await tester.tap(find.text('Add'));
      await tester.pumpAndSettle();

      expect(find.text('Integration Test User'), findsOneWidget);
    });
  });
}

十一、常见问题与解决方案

11.1 数据库锁定问题

问题描述：

DatabaseException(database is locked (code 5 SQLITE_BUSY))

解决方案：

Future<Database> _initDatabase() async {
  final directory = await _getDatabaseDirectory();
  final path = join(directory.path, DatabaseConstants.dbName);

  return await openDatabase(
    path,
    version: DatabaseConstants.dbVersion,
    onCreate: _onCreate,
    onUpgrade: _onUpgrade,
    onOpen: (db) async {
      await db.execute('PRAGMA foreign_keys = ON');
      await db.execute('PRAGMA journal_mode = WAL');
      await db.execute('PRAGMA busy_timeout = 5000');
      await db.execute('PRAGMA synchronous = NORMAL');
    },
    singleInstance: true,
  );
}

11.2 数据迁移失败

问题描述：升级应用时数据库迁移失败导致数据丢失。

解决方案：

1. 总是在迁移前备份数据库
2. 使用事务确保迁移的原子性
3. 提供降级机制
4. 详细的迁移日志

Future<void> _onUpgrade(Database db, int oldVersion, int newVersion) async {
  print('Starting database upgrade from $oldVersion to $newVersion');

  try {
    await db.execute('BEGIN EXCLUSIVE');

    final backupService = DatabaseBackupService(databaseService);
    await backupService.createBackup();

    final migrationManager = MigrationManager();
    await migrationManager.executeMigrations(db, oldVersion, newVersion);

    await db.execute('COMMIT');
    print('Database upgrade completed successfully');
  } catch (e) {
    await db.execute('ROLLBACK');
    print('Database upgrade failed: $e');
    rethrow;
  }
}

11.3 内存泄漏问题

问题描述：长时间使用应用后内存持续增长。

解决方案：

class DatabaseLifecycleManager {
  static final DatabaseLifecycleManager _instance = 
      DatabaseLifecycleManager._internal();

  factory DatabaseLifecycleManager() => _instance;

  DatabaseLifecycleManager._internal();

  void onAppPause() {
    print('App paused - considering database cleanup');
  }

  void onAppResume() {
    print('App resumed - ensuring database is ready');
  }

  Future<void> onAppTerminate() async {
    print('App terminating - closing database');
    final databaseService = DatabaseService();
    await databaseService.close();
  }
}

十二、最佳实践总结

12.1 设计原则

1. 单例模式：确保只有一个数据库连接
2. 分层架构：清晰的服务层、仓储层、模型层
3. 事务优先：涉及多表操作时必须使用事务
4. 软删除：避免物理删除，使用软删除便于数据恢复
5. 版本管理：完善的数据库版本控制和迁移机制

12.2 性能优化

1. 索引优化：为常用查询字段创建索引
2. 批量操作：使用批量操作减少数据库访问次数
3. 查询优化：避免 SELECT *，只查询需要的字段
4. 缓存策略：合理使用缓存减少数据库查询
5. 定期维护：定期执行 VACUUM 和 ANALYZE

12.3 安全建议

1. 敏感数据加密：密码等敏感数据必须加密存储
2. 权限控制：最小权限原则配置数据库访问
3. 定期备份：建立完善的数据库备份机制
4. 输入验证：防止 SQL 注入攻击（使用参数化查询）

12.4 鸿蒙平台特定建议

1. 路径适配：使用平台特定的数据库存储路径
2. 权限配置：正确配置 OpenHarmony 的存储权限
3. 资源管理：合理管理数据库连接，避免资源泄漏
4. 性能调优：根据鸿蒙平台特性优化数据库配置

十三、总结

sqflite 作为 Flutter 生态中最成熟的数据库解决方案，为 OpenHarmony 平台的应用开发提供了强大的数据持久化能力。通过本文介绍的架构设计、最佳实践和优化策略，开发者可以构建出高性能、高可靠性、易维护的企业级数据库应用。

在实际开发中，建议根据具体业务需求选择合适的架构模式，注重代码的可测试性和可维护性，同时充分考虑鸿蒙平台的特性进行针对性优化。

---

欢迎加入开源鸿蒙跨平台社区：https://openharmonycrossplatform.csdn.net

相关资源：

• sqflite 官方文档：https://pub.dev/packages/sqflite
• OpenHarmony 开发指南：https://developer.harmonyos.com/
• Flutter-OH 项目地址：https://gitee.com/openharmony/flutter_flutter