Flutter sqflite 使用案例
import 'package:flutter/material.dart'; import 'package:sqflite/sqflite.dart'; import 'package:path/path.dart'; import 'package:path_provider/path_provider.dart'; import 'dart:io'; void main() { runApp(MyApp()); } class MyApp extends StatelessWidget { // This widget is the root of your application. @override Widget build(BuildContext context) { return MaterialApp( title: 'Flutter Demo', theme: ThemeData( primarySwatch: Colors.blue, ), home: MyHomePage(title: 'Flutter Demo Home Page'), ); } } class MyHomePage extends StatefulWidget { MyHomePage({Key key, this.title}) : super(key: key); final String title; @override _MyHomePageState createState() => _MyHomePageState(); } class Student { String id; String name; int score; //构造方法 Student({ this.id, this.name, this.score, }); //用于将JSON字典转换成类对象的工厂类方法 factory Student.fromJson(Map<String, dynamic> parsedJson) { return Student( id: parsedJson['id'], name: parsedJson['name'], score: parsedJson['score'], ); } Map<String, dynamic> toJson() { return { 'id': id, 'name': name, 'score': score, }; } } class _MyHomePageState extends State<MyHomePage> { var student1 = Student(id: '123', name: '张三', score: 110); query_database(id) { Future<Student> user = query_by_id(id); user.then((t) { print("单用户查询成功"); print('the sroce is : ' + t.score.toString()); student1.score = t.score; }); } //插入数据 Future<void> insertStudent(Student std) async { final Database db = await database; await db.insert( 'students', std.toJson(), //插入冲突策略，新的替换旧的 conflictAlgorithm: ConflictAlgorithm.replace, ); } Future<Student> query_by_id(String id) async { final Database db = await database; List<Map> maps = await db.query('students', columns: ['id', 'name', 'score'], where: 'id = ?', whereArgs: [id]); if (maps.length > 0) { print("根据id查到了数据"); return Student.fromJson(maps.first); } print("根据id没有查到数据"); return null; } //初始化数据库方法 initDB() async { Directory documentsDirectory = await getApplicationDocumentsDirectory(); String path = join(documentsDirectory.path, "students_database.db"); return await openDatabase(path, version: 1, onOpen: (db) {}, onCreate: (Database db, int version) async { await db.execute(''' CREATE TABLE students(id TEXT PRIMARY KEY, name TEXT, score INTEGER) '''); }); } Database _database; Future<Database> get database async { if (_database != null) { print(_database); return _database; } _database = await initDB(); print("创建新数据库"); return _database; } void _incrementCounter() async { student1.score += 1; await insertStudent(student1); setState(() {}); } Future<List<Student>> students() async { final Database db = await database; final List<Map<String, dynamic>> maps = await db.query('students'); return List.generate(maps.length, (i) => Student.fromJson(maps[i])); } @override Widget build(BuildContext context) { return Scaffold( appBar: AppBar( title: Text(widget.title), ), body: Center( child: Column( mainAxisAlignment: MainAxisAlignment.center, children: <Widget>[ Text( 'You have pushed the button this many times:', ), Text( student1.score.toString(), style: Theme.of(context).textTheme.headline4, ), ], ), ), floatingActionButton: FloatingActionButton( onPressed: _incrementCounter, tooltip: 'Increment', child: Icon(Icons.add), ), ); } @override void dispose() async { // TODO: implement dispose super.dispose(); final Database db = await database; db.close(); } }
另一个案例
class MainDatabaseManager { // 工厂模式 factory MainDatabaseManager() => _getInstance(); static MainDatabaseManager get instance => _getInstance(); static MainDatabaseManager? _instance; static Database? tkDatabase; static List<String> _nameList = []; MainDatabaseManager._internal() { // 初始化 } static MainDatabaseManager _getInstance() { if (_instance == null) { _instance = new MainDatabaseManager._internal(); } _instance!._createTable(); return _instance!; } ///初始化数据库 initDB() { _createTable(); } ///打开数据库,创建数据缓存表 _createTable() async { var databasePath = await getDatabasesPath(); String path = join(databasePath, "tkDataBase.db"); ///打开数据库 tkDatabase = await openDatabase(path, version: 1, onCreate: (Database db, int version) async { /// 创建表 await db.execute( "create table if not EXISTS TK_Main_Data (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,name TEXT UNIQUE,data BLOB)"); }); } ///操作事务Transaction commitTransaction(Function(Transaction txn) action) async { try { await tkDatabase?.transaction((txn) async { Batch batch = txn.batch(); await action(txn); await batch.commit(noResult: true, continueOnError: true); }); } catch (e) { print('--- transaction failed -----'); } } /* 保存到数据库,区分当前用 - parameter name: 保存的名称（用于查询） - parameter data: 数据 - parameter type: 类型 */ _saveDataWith( String name, bool containsMember, String data, Transaction txn) async { String dataName = name + (containsMember ? UserStore().getMemberId() : ""); int id = await txn.rawInsert( 'INSERT or replace INTO TK_Main_Data(name,data) VALUES(?,?)', [dataName, data]); print('inserted2:$id'); } ///更新数据 updateData( String name, bool containsMember, dynamic data, Transaction txn) async { dynamic result = await selectData(name, containsMember, txn); if (result == null || result == "") { _nameList.add(name); } _saveDataWith(name, containsMember, data, txn); } /* 查询数据 - parameter name: 要查询的数据的名称 - parameter type: 类型 - returns: 返回的数据 */ Future<dynamic> selectData( String name, bool containsMember, Transaction txn) async { String dataName = name + (containsMember ? UserStore().getMemberId() : ""); /// 查询 dynamic result = await txn .rawQuery("select data from TK_Main_Data where name = '$dataName'"); if (result != null && result is List && result.length > 0) { return result.first["data"] ?? ""; } return ""; } ///退出登录时,删除与当前用户有关的缓存 deleteAllDataForMember(Transaction txn) async { if (UserStore.instance.getMemberId() != "") { for (String name in _nameList) { String dataName = name + UserStore.instance.getMemberId(); //与当前用户有关的,无关的不需要删除 await txn .execute("delete from TK_Main_Data where name = (?)", [dataName]); } _nameList = []; } } onDisposed() { tkDatabase?.close(); } }ping 使用的是 ICMP 协议，不支持代理