引入插件
|
1 2 3 4 5 |
dependencies: flutter: sdk: flutter cupertino_icons: ^1.0.0 sqflite: ^1.3.2+1 |
封装公共方法
这里的两个公共类是封装的比较好的，只要有sqlflite数据库操作就可以拿过来直接用的
- 数据库初始化，获取数据库对象以及关闭数据库。在sqflite中的数据库操作，首先得获取到数据库对象，通过数据库对象才可以对相应的表进行增删查改。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
import 'package:path/path.dart'; import 'package:sqflite/sqflite.dart'; class SqlManager { static const _VERSION = 1; static const _NAME = "my.db"; static Database _database; ///初始化 static init() async { var databasesPath = await getDatabasesPath(); String path = join(databasesPath, _NAME); _database = await openDatabase(path, version: _VERSION, onCreate: (Database db, int version) async {}); } ///判断表是否存在 static isTableExits(String tableName) async { await getCurrentDatabase(); var res = await _database.rawQuery( "select * from Sqlite_master where type = 'table' and name = '$tableName'"); return res != null && res.length > 0; } ///获取当前数据库对象 static Future<Database> getCurrentDatabase() async { if (_database == null) { await init(); } return _database; } ///关闭 static close() { _database?.close(); _database = null; } } |
- 在对某一张表进行操作时，先得判断数据库对象是否存在，不存在就得创建。再判断表是否在该数据库中存在，不存在就创建。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
import 'package:meta/meta.dart'; import 'package:sqflite/sqflite.dart'; import 'SqlManager.dart'; abstract class BaseDbProvider { bool isTableExits = false; createTableString(); tableName(); ///创建表sql语句 tableBaseString(String sql) { return sql; } Future<Database> getDataBase() async { return await open(); } ///super 函数对父类进行初始化 @mustCallSuper prepare(name, String createSql) async { isTableExits = await SqlManager.isTableExits(name); if (!isTableExits) { Database db = await SqlManager.getCurrentDatabase(); return await db.execute(createSql); } } @mustCallSuper open() async { if (!isTableExits) { await prepare(tableName(), createTableString()); } return await SqlManager.getCurrentDatabase(); } } |
具体操作
- 新建bean类，这里除了基本的get和set方法之外还有User对象转map，map转User的两个方法。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
class User { User(); int _id; String _name; String _desc; int get id => _id; String get name => _name; String get desc => _desc; set desc(String value) { _desc = value; } set name(String value) { _name = value; } set id(int value) { _id = value; } Map<String, dynamic> toMap() { var map = Map<String, dynamic>(); map['id'] = _id; map['name'] = _name; map['desc'] = _desc; return map; } User.fromMapObject(Map<String, dynamic> map) { this._id = map['id']; this._name = map['name']; this._desc = map['desc']; } } |
- User表的增删查改方法
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 |
import 'package:sqflite/sqlite_api.dart'; import 'BaseDbProvider.dart'; import 'User.dart'; class UserDbProvider extends BaseDbProvider { ///表名 final String name = 'UserInfo'; final String columnId = "id"; final String columnName = "name"; final String columnDesc = "desc"; UserDbProvider(); //获取表名称 @override tableName() { return name; } //创建表操作 @override createTableString() { return ''' create table $name ( $columnId integer primary key,$columnName text not null, $columnDesc text not null) '''; } ///查询数据 Future selectUser(int id) async { Database db = await getDataBase(); return await db.rawQuery("select * from $name where $columnId = $id"); } //查询数据库所有 Future<List<Map<String, dynamic>>> selectMapList() async { var db = await getDataBase(); var result = await db.query(name); return result; } //获取数据库里所有user Future<List<User>> getAllUser() async { var userMapList = await selectMapList(); var count = userMapList.length; List<User> userList = List<User>(); for (int i = 0; i < count; i++) { userList.add(User.fromMapObject(userMapList[i])); } return userList; } //根据id查询user Future<User> getUser(int id) async { var noteMapList = await selectUser(id); // Get 'Map List' from database var user = User.fromMapObject(noteMapList[id]); return user; } //增加数据 Future<int> insertUser(User user) async { var db = await getDataBase(); var result = await db.insert(name, user.toMap()); return result; } //更新数据 Future<int> update(User user) async { var database = await getDataBase(); var result = await database.rawUpdate( "update $name set $columnName = ?,$columnDesc = ? where $columnId= ?", [user.name, user.desc, user.id]); return result; } //删除数据 Future<int> deleteUser(int id) async { var db = await getDataBase(); var result = await db.rawDelete('DELETE FROM $name WHERE $columnId = $id'); return result; } } |
外部使用
- 创建Provider对象。
|
1 |
UserDbProvider provider = UserDbProvider(); |
2.通过对象调用User的增删查改方法
|
1 2 3 4 5 6 |
if (user.id == null) { user.id = new DateTime.now().millisecondsSinceEpoch; //id 为当前时间戳 result = await provider.insertUser(user); } else { result = await provider.update(user); } |