引入插件
dependencies:
flutter:
sdk: flutter
cupertino_icons: ^1.0.0
sqflite: ^1.3.2+1
封装公共方法
这里的两个公共类是封装的比较好的，只要有sqlflite数据库操作就可以拿过来直接用的
- 数据库初始化，获取数据库对象以及关闭数据库。在sqflite中的数据库操作，首先得获取到数据库对象，通过数据库对象才可以对相应的表进行增删查改。
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;
}
}
- 在对某一张表进行操作时，先得判断数据库对象是否存在，不存在就得创建。再判断表是否在该数据库中存在，不存在就创建。
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的两个方法。
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表的增删查改方法
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对象。
UserDbProvider provider = UserDbProvider();
2.通过对象调用User的增删查改方法
if (user.id == null) {
user.id = new DateTime.now().millisecondsSinceEpoch; //id 为当前时间戳
result = await provider.insertUser(user);
} else {
result = await provider.update(user);
}