Flutter SQFLite单条笔记存储实现求助：修正增删改查代码

针对单条笔记的存储需求，我们可以简化数据库逻辑，确保数据库中始终仅保留一条记录。以下是完整的修正代码及关键改动说明：

核心问题梳理 #

1. 原数据库方法基于多条笔记设计，需调整为单条记录逻辑
2. UI层调用数据库方法时缺少参数传递，且未处理异步操作
3. Note类的copy方法存在参数名与字段不匹配的错误

完整修正代码 #

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

final String myNoteTable = 'myNote';

class NoteFields {
  static final List<String> values = [id, myNote];
  static final String id = '_id'; // SQL命名规范
  static final String myNote = 'myNote';
}

class Note {
  final int? id;
  late String myText;

  Note({required this.id, required this.myText});
  
  // 修正copy方法的参数错误
  Note copy({int? id, String? myText}) => Note(
        id: id ?? this.id,
        myText: myText ?? this.myText,
      );

  Map<String, Object?> toJson() => {
        NoteFields.id: id,
        NoteFields.myNote: myText,
      };

  static Note fromJson(Map<String, Object?> json) => Note(
        id: json[NoteFields.id] as int?,
        myText: json[NoteFields.myNote] as String,
      );
}

class DB {
  static const String myDatabase = 'myDatabase.db';

  DB._init();
  static final DB instance = DB._init();
  static Database? database;

  Future<Database> get dataBase async {
    if (database != null) return database!;
    database = await initDB(myDatabase);
    return database!;
  }

  Future<Database> initDB(String filePath) async {
    final dbPath = await getDatabasesPath();
    final path = join(dbPath, filePath);
    return await openDatabase(path, version: 1, onCreate: createDB);
  }

  Future createDB(Database db, int version) async {
    var idType = 'INTEGER PRIMARY KEY AUTOINCREMENT';
    var textType = 'TEXT NOT NULL';
    await db.execute('''
      CREATE TABLE $myNoteTable(
        ${NoteFields.id} $idType,
        ${NoteFields.myNote} $textType
      )
    ''');
  }

  // 保存单条笔记：先清空表再插入，确保仅存一条记录
  Future<Note> saveSingleNote(Note note) async {
    final db = await instance.dataBase;
    await db.delete(myNoteTable);
    final id = await db.insert(myNoteTable, note.toJson());
    return note.copy(id: id);
  }

  // 获取单条笔记，无记录则返回null
  Future<Note?> getSingleNote() async {
    final db = await instance.dataBase;
    final result = await db.query(myNoteTable, limit: 1);
    return result.isNotEmpty ? Note.fromJson(result.first) : null;
  }

  // 更新单条笔记：有记录则更新，无记录则插入
  Future<int> updateSingleNote(Note note) async {
    final db = await instance.dataBase;
    final existingNote = await getSingleNote();
    if (existingNote != null) {
      return db.update(
        myNoteTable,
        note.toJson(),
        where: '${NoteFields.id} = ?',
        whereArgs: [existingNote.id],
      );
    } else {
      return await db.insert(myNoteTable, note.toJson());
    }
  }

  // 删除单条笔记：直接清空表
  Future<int> deleteSingleNote() async {
    final db = await instance.dataBase;
    return await db.delete(myNoteTable);
  }

  Future close() async {
    final db = await instance.dataBase;
    db.close();
  }
}

class MyApp extends StatefulWidget {
  final int? taskId;
  const MyApp({Key? key, this.taskId});
  @override
  State<MyApp> createState() => MyAppState();
}

class MyAppState extends State<MyApp> {
  double myfontsize = 9.0;
  final TextEditingController myNote = TextEditingController();
  final scrollController = ScrollController();

  @override
  void initState() {
    super.initState();
    _loadNote(); // 初始化时加载已有笔记
  }

  @override
  void dispose() {
    myNote.dispose();
    scrollController.dispose();
    super.dispose();
  }

  // 加载笔记到输入框
  Future<void> _loadNote() async {
    final note = await DB.instance.getSingleNote();
    if (note != null) {
      setState(() {
        myNote.text = note.myText;
      });
    }
  }

  // 保存笔记
  Future<void> _saveNote() async {
    if (myNote.text.isNotEmpty) {
      await DB.instance.saveSingleNote(Note(id: null, myText: myNote.text));
      ScaffoldMessenger.of(context).showSnackBar(
        const SnackBar(content: Text('笔记已保存')),
      );
    }
  }

  // 更新笔记
  Future<void> _updateNote() async {
    if (myNote.text.isNotEmpty) {
      await DB.instance.updateSingleNote(Note(id: null, myText: myNote.text));
      ScaffoldMessenger.of(context).showSnackBar(
        const SnackBar(content: Text('笔记已更新')),
      );
    }
  }

  // 删除笔记
  Future<void> _deleteNote() async {
    await DB.instance.deleteSingleNote();
    setState(() {
      myNote.text = '';
    });
    ScaffoldMessenger.of(context).showSnackBar(
      const SnackBar(content: Text('笔记已删除')),
    );
  }

  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      home: Scaffold(
        resizeToAvoidBottomInset: false,
        appBar: AppBar(title: const Text('单条笔记')),
        body: Container(
          padding: const EdgeInsets.all(5),
          child: SingleChildScrollView(
            child: Column(
              mainAxisAlignment: MainAxisAlignment.spaceEvenly,
              mainAxisSize: MainAxisSize.min,
              children: [
                const SizedBox(height: 5),
                SizedBox(
                  width: double.infinity,
                  height: 80,
                  child: TextField(
                    maxLines: 10,
                    scrollController: scrollController,
                    decoration: const InputDecoration(border: OutlineInputBorder()),
                    controller: myNote,
                  ),
                ),
                const SizedBox(height: 5),
                SizedBox(
                  height: 30,
                  child: Row(
                    children: [
                      Expanded(
                        child: TextButton(
                          onPressed: _saveNote,
                          style: TextButton.styleFrom(
                            backgroundColor: Colors.blue,
                            side: const BorderSide(width: 1.0),
                          ),
                          child: Text(
                            "Save",
                            style: TextStyle(color: Colors.white, fontSize: myfontsize),
                          ),
                        ),
                      ),
                      Expanded(
                        child: TextButton(
                          onPressed: _updateNote,
                          style: TextButton.styleFrom(
                            backgroundColor: Colors.blue,
                            side: const BorderSide(width: 1.0),
                          ),
                          child: Text(
                            "Update",
                            style: TextStyle(color: Colors.white, fontSize: myfontsize),
                          ),
                        ),
                      ),
                      Expanded(
                        child: TextButton(
                          onPressed: _deleteNote,
                          style: TextButton.styleFrom(
                            backgroundColor: Colors.blue,
                            side: const BorderSide(width: 1.0),
                          ),
                          child: Text(
                            "Delete",
                            style: TextStyle(color: Colors.white, fontSize: myfontsize),
                          ),
                        ),
                      ),
                    ],
                  ),
                ),
                const SizedBox(height: 5),
              ],
            ),
          ),
        ),
      ),
    );
  }
}

关键改动说明 #

1. 数据库层调整

   • 新增saveSingleNote：先清空表再插入记录，保证数据库中始终只有一条笔记
   • 新增getSingleNote：查询并返回单条笔记，无记录时返回null
   • 调整updateSingleNote：自动判断是否存在记录，存在则更新，不存在则插入
   • 调整deleteSingleNote：直接清空表，删除唯一记录

2. UI层修正

   • 所有数据库操作改为异步处理（使用async/await），适配Future类型操作
   • 初始化时调用_loadNote加载已有笔记到输入框
   • 按钮点击逻辑添加状态更新和操作提示
   • 优化TextField宽度为double.infinity，适配不同屏幕尺寸

3. 数据模型修正

   • 修复Note类copy方法的参数错误，确保参数名与字段名匹配

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