How to Use Flutter Database
- 2021-12-12 09:53:28
- OfStack
Description
Flutter Native does not support database operations. It uses SQLlit plug-ins to enable applications to use databases. In fact, Flutter communicates with native systems through plug-ins to operate databases.
Platform support
The SQLite plug-in of FLutter supports IOS, Android, and MacOS platforms Use sqflite_common_ffi if you want to support Linux/Windows/DartVM The web platform is not supported Database operations are performed in the background of Android or iosUse case
notepad_sqflite Simple Notepad application that can run on iOS/Android/Windows/linux/Mac
Simple use
Add dependencies
In order to use the SQLite database, you first need to import two package, sqflite and path
sqflite provides a wealth of classes and methods so that you can easily use the SQLite database. path provides a number of methods so that you can correctly define the storage location of the database on disk.
dependencies:
sqflite: ^1.3.0
path: Version number
Use
Import sqflite. dart
import 'dart:async';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
Open the database
An SQLite database is a file in a file system. If it is a relative path, it is the path obtained by getDatabasesPath (), which is associated with the default database directory on Android and the documents directory on iOS.
var db = await openDatabase('my_db.db');
Many times we don't need to close the database manually when we use it, because the database will be closed when the program is closed. If you want to release resources automatically, you can use the following methods:
await db.close();
Execute the original SQL query
Use getDatabasesPath () to get the database location
Use the getDatabasesPath method in sqflite package in conjunction with the join method in path package to define the path to the database. Using the join method in the path package is a best practice to ensure path correctness across platforms.
var databasesPath = await getDatabasesPath();
String path = join(databasesPath, 'demo.db');
Open the database
Database database = await openDatabase(path, version: 1,
onCreate: (Database db, int version) async {
// Create a table when you create a database
await db.execute(
'CREATE TABLE Test (id INTEGER PRIMARY KEY, name TEXT, value INTEGER, num REAL)');
});
Increase
Insert several pieces of data into a table in a transaction
await database.transaction((txn) async {
int id1 = await txn.rawInsert(
'INSERT INTO Test(name, value, num) VALUES("some name", 1234, 456.789)');
print('inserted1: $id1');
int id2 = await txn.rawInsert(
'INSERT INTO Test(name, value, num) VALUES(?, ?, ?)',
['another name', 12345678, 3.1416]);
print('inserted2: $id2');
});
Delete
Delete 1 piece of data from a table
count = await database
.rawDelete('DELETE FROM Test WHERE name = ?', ['another name']);
Change
Modify the data in the table
int count = await database.rawUpdate('UPDATE Test SET name = ?, value = ? WHERE name = ?',
['updated name', '9876', 'some name']);
print('updated: $count');
Check
Query the data in the table
// Get the records
List<Map> list = await database.rawQuery('SELECT * FROM Test');
List<Map> expectedList = [
{'name': 'updated name', 'id': 1, 'value': 9876, 'num': 456.789},
{'name': 'another name', 'id': 2, 'value': 12345678, 'num': 3.1416}
];
print(list);
print(expectedList);
The total number of pieces of data stored in the lookup table
import 'dart:async';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
0
Close the database
import 'dart:async';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
1
Delete a database
import 'dart:async';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
2
Using the SQL Assistant
Create fields and associated classes in a table
// Field
final String tableTodo = 'todo';
final String columnId = '_id';
final String columnTitle = 'title';
final String columnDone = 'done';
// Corresponding class
class Todo {
int id;
String title;
bool done;
// Converts the current class to Map For external use
Map<String, Object?> toMap() {
var map = <String, Object?>{
columnTitle: title,
columnDone: done == true ? 1 : 0
};
if (id != null) {
map[columnId] = id;
}
return map;
}
// Parametric structure
Todo();
// Put map Converts data of type to the constructor of the current class object.
Todo.fromMap(Map<String, Object?> map) {
id = map[columnId];
title = map[columnTitle];
done = map[columnDone] == 1;
}
}
Use the above class to create and delete the database and add, delete and change the data.
import 'dart:async';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
4
= Query all data in the table
import 'dart:async';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
5
Get the first data in the result
import 'dart:async';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
6
Map in the list of query results above is read-only data. Modifying this data will throw an exception
import 'dart:async';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
7
Create a copy of map and modify the fields in it
// According to the above map Create 1 A map Copy
Map<String, Object?> map = Map<String, Object?>.from(mapRead);
// Modify the field values stored in this copy in memory
map['my_column'] = 1;
Put the queried List < map > Convert data of type to List < Todo > Type, so that we can use it happily.
import 'dart:async';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
9
Batch processing
You can use batch processing to avoid frequent interactions between dart and native.
batch = db.batch();
batch.insert('Test', {'name': 'item'});
batch.update('Test', {'name': 'new_item'}, where: 'name = ?', whereArgs: ['item']);
batch.delete('Test', where: 'name = ?', whereArgs: ['item']);
results = await batch.commit();
Getting the results of each operation is costly (the number of Id inserted and the number of changes updated and deleted). If you don't care about the result of the operation, you can do the following to turn off the response to the result
await batch.commit(noResult: true);
Using batch processing in transactions
Batch operations are performed in transactions, and the batch is committed only after the transaction is committed.
await database.transaction((txn) async {
var batch = txn.batch();
// ...
// commit but the actual commit will happen when the transaction is committed
// however the data is available in this transaction
await batch.commit();
// ...
});
Batch Exception Ignore
By default, a batch will stop once an error occurs (unexecuted statements will not be executed), and you can ignore the error so that subsequent operations can continue.
var db = await openDatabase('my_db.db');
3
About table and column names
In general, we should avoid using the SQLite keyword to name table and column names. Such as:
"add","all","alter","and","as","autoincrement","between","case","check","collate",
"commit","constraint","create","default","deferrable","delete","distinct","drop",
"else","escape","except","exists","foreign","from","group","having","if","in","index",
"insert","intersect","into","is","isnull","join","limit","not","notnull","null","on",
"or","order","primary","references","select","set","table","then","to","transaction",
"union","unique","update","using","values","when","where"
Supported storage types
Avoid using unsupported types because the value has not been validated. See: The DateTime type is not supported and can be stored as int or String bool type not supported, can be stored as int type 0: false, 1: true
SQLite类型 | dart类型 | 值范围 |
---|---|---|
integer | int | 从-2 ^ 63到2 ^ 63-1 |
real | num | |
text | String | |
blob | Uint8List |
Reference
sqflile Official Address
flutter official documentation for data storage using SQLite