flutter对sqflite插件的简单使用增删改查

首先用到的插件在pubspec.yaml 里面的版本号

  sqflite: ^2.3.0
  path: ^1.8.3

然后我们封装一个数据库的增删改查的类
需要使用的话直接把下面代码移植,可直接使用,仅需要修改创建表的内容和自己插入的数据

/// sqflite封装
class DatabaseHelper {
  static Database? _database;
  static const String dbName = 'user_database.db';
  static const String tableName = 'user_table';

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

    // 如果数据库尚未初始化，则初始化数据库
    _database = await initDatabase();
    return _database!;
  }

  Future<Database> initDatabase() async {
    String path = join(await getDatabasesPath(), dbName);
    return await openDatabase(path, version: 1, onCreate: _createTable);
  }

  Future<void> _createTable(Database db, int version) async {
    await db.execute('''
      CREATE TABLE $tableName (
        id INTEGER PRIMARY KEY, 
        username TEXT,
        email TEXT,
        gender TEXT,
        birthday TEXT,
        password TEXT
      )
    ''');
   //INTEGER PRIMARY KEY 的语法表示创建一个整数类型的主键，并且会自动递增
  }

  /// 插入用户 return 插入的id
  Future<int> insertUser(Map<String, dynamic> user) async {
    Database db = await database;
    return await db.insert(tableName, user);
  }

  /// 获取所有用户
  Future<List<Map<String, dynamic>>> getAllUsers() async {
    Database db = await database;
    return await db.query(tableName);
  }

  /// 根据用户名查询用户
  Future<List<Map<String, dynamic>>> getUserByUsername(String username) async {
    Database db = await database;
    return await db
        .query(tableName, where: 'username = ?', whereArgs: [username]);
        //where的参数应该和whereArgs的参数相对应,放着sql被注入
  }

  /// 更新用户信息
  Future<int> updateUser(Map<String, dynamic> user, int userId) async {
    Database db = await database;
    return await db
        .update(tableName, user, where: 'id = ?', whereArgs: [userId]);
  }

  /// 删除用户
  Future<int> deleteUser(int userId) async {
    Database db = await database;
    return await db.delete(tableName, where: 'id = ?', whereArgs: [userId]);
  }
}

下面是flutter的页面对sqflite的简单测试使用,可成功跑通

class RegistrationPage extends StatelessWidget {
  final DatabaseHelper dbHelper = DatabaseHelper();

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: const Text('sql轻量级使用'),
      ),
      body: Center(
        child: Column(
          children: [
            const SizedBox(height: 50),
            ElevatedButton(
              onPressed: () async {
                await _insertUser();
              },
              child: const Text('增加用户(增)'),
            ),
            const SizedBox(height: 20),
            ElevatedButton(
              onPressed: () async {
                // await _getAllUsers();
                await _getUsersInfo('李');
              },
              child: const Text('打印所有用户(查)'),
            ),
            const SizedBox(height: 20),
            ElevatedButton(
              onPressed: () async {
                dbHelper.deleteUser(1);
              },
              child: const Text('删除指定id用户(删)'),
            ),
            const SizedBox(height: 20),
            ElevatedButton(
              onPressed: () async {
                Map<String, dynamic> userChange = {
                  'username': '李',
                  'email': '2323111232@qq.com',
                  'gender': 'Male',
                  'birthday': '1998-01-01',
                  'password': '123412345',
                };
                await dbHelper.updateUser(userChange, 2020);
              },
              child: const Text('修改指定id用户数据(改)'),
            ),
          ],
        ),
      ),
    );
  }
  ///插入数据进表
  Future<void> _insertUser() async {
    Map<String, dynamic> user = {
      'username': 'JohnDoe',
      'email': 'john.doe@example.com',
      'gender': 'Male',
      'birthday': '1990-01-01',
      'password': 'secure_password',
    };

    int userId = await dbHelper.insertUser(user);
    print('Inserted user with ID: $userId');
  }

  ///打印所有表中信息
  Future<void> _getAllUsers() async {
    List<Map<String, dynamic>> users = await dbHelper.getAllUsers();
    print('All Users:');
    for (var user in users) {
      print(
          'ID: ${user['id']}, Username: ${user['username']}, Email: ${user['email']}, Birthday: ${user['birthday']}, Gender: ${user['gender']}');
    }
  }

  ///查找指定username的信息
  Future<void> _getUsersInfo(String userName) async {
    List<Map<String, dynamic>> userInfo =
        await dbHelper.getUserByUsername(userName);
    for (var user in userInfo) {
      print(
          'ID: ${user['id']}, Username: ${user['username']}, Email: ${user['email']}, Birthday: ${user['birthday']}, Gender: ${user['gender']}');
    }
  }
}