Flutter-sqflite使用样例

一：添加sqflite和path_provider依赖库

dependencies:
  flutter:
    sdk: flutter
  sqflite: ^2.3.0 #数据库
  path_provider: ^2.1.0 #文件路径获取

二：创建数据库单类-创建多张表单样例

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

class DatabaseHelper {
  static final _dbName = 'myDatabase.db';
  static final _dbVersion = 1;

  // 表名
  static final userTable = 'users';
  static final productTable = 'products';

  DatabaseHelper._privateConstructor();
  static final DatabaseHelper instance = DatabaseHelper._privateConstructor();

  static Database? _database;
  Future<Database> get database async => _database ??= await _initDatabase();

  _initDatabase() async {
    var documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentsDirectory.path, _dbName);
    return await openDatabase(path, version: _dbVersion, onCreate: _onCreate, onUpgrade: _onUpgrade);
  }

  Future _onCreate(Database db, int version) async {
    // 创建users表
    await db.execute('''
          CREATE TABLE $userTable (
            id INTEGER PRIMARY KEY,
            username TEXT NOT NULL,
            password TEXT NOT NULL
          )
          ''');

    // 创建products表
    await db.execute('''
          CREATE TABLE $productTable (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            price REAL NOT NULL
          )
          ''');
  }

   // 更新数据库结构时使用的方法
  Future _onUpgrade(Database db, int oldVersion, int newVersion) async {
   if (oldVersion < newVersion) {
     // 执行数据库升级相关的操作
     if (oldVersion == 1) {
       // 从版本1升级到版本2: 添加新表orders
       await db.execute('''
         CREATE TABLE orders (
           id INTEGER PRIMARY KEY,
           user_id INTEGER,
           total REAL NOT NULL,
           created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        )
       ''');
     }
     // 如果有更多的版本更新，可以继续添加else if语句来处理
     // 例如，如果你想要从版本2升级到版本3，你可以这样做：
     /*
     else if (oldVersion == 2) {
       // 从版本2升级到版本3: 添加新列到现有表
       await db.execute('ALTER TABLE users ADD COLUMN email TEXT');
    }
     */
  }
 }

  // 增删改查示例（以users表为例）
  Future<int> insertUser(Map<String, dynamic> row) async {
    Database db = await instance.database;
    return await db.insert(userTable, row);
  }

  Future<List<Map<String, dynamic>>> queryAllUsers() async {
    Database db = await instance.database;
    return await db.query(userTable);
  }

  Future<int> updateUser(Map<String, dynamic> row) async {
    Database db = await instance.database;
    int id = row['id'];
    return await db.update(userTable, row, where: 'id = ?', whereArgs: [id]);
  }

  Future<int> deleteUser(int id) async {
    Database db = await instance.database;
    return await db.delete(userTable, where: 'id = ?', whereArgs: [id]);
  }
  
  // 可以添加相似的方法来处理products表的增删改查
}

三：使用方法

void addUser() async {
  Map<String, dynamic> row = {
    'username': 'Alice',
    'password': 'password123'
  };
  final id = await DatabaseHelper.instance.insertUser(row);
  print('Inserted user with id: $id');
}

void getAllUsers() async {
  final allRows = await DatabaseHelper.instance.queryAllUsers();
  print('All users:');
  allRows.forEach((row) => print(row));
}

void updateUser(int id) async {
  Map<String, dynamic> row = {
    'id': id,
    'username': 'Bob',
    'password': 'newpassword'
  };
  final rowsAffected = await DatabaseHelper.instance.updateUser(row);
  print('Updated $rowsAffected row(s)');
}

void deleteUser(int id) async {
  final rowsDeleted = await DatabaseHelper.instance.deleteUser(id);
  print('Deleted $rowsDeleted row(s)');
}

注意：在执行任何升级操作之前，为了安全起见，最好进行数据备份