Detailed explanation of database storage of Flutter persistent storage (sqflite)

Preface

Database storage is one of our commonly used storage methods. When we need to add, delete, change and check a large amount of data, we will think of using the database. Flutter provides an sqflite plug-in for us to perform CRUD operations for a large amount of data. In this article, we will learn the use of sqflite.

sqflite is a lightweight relational database similar to SQLite.

On the Flutter platform we use the sqflite library to support both Android and iOS.

sqflite use

Introducing plug-ins

Add the path_provider plug-in to the pubspec. yaml file, the latest version is 1.0. 0, as follows:

dependencies:
 flutter:
 sdk: flutter
 #sqflite Plug-ins 
 sqflite: 1.0.0

The plug-in can then be downloaded locally by executing flutter packages get on the command line.

Introduction of database operation method

1. Insert

There are two methods for inserting data operation:

Future<int> rawInsert(String sql, [List<dynamic> arguments]);

Future<int> insert(String table, Map<String, dynamic> values,
 {String nullColumnHack, ConflictAlgorithm conflictAlgorithm});

The first parameter of rawInsert method is an insert sql statement. Can you use? As a placeholder, populate the data with the second parameter.

The first parameter of the insert method is the table name of the operation, and the second parameter map is the name of the field you want to add and the corresponding field value.

2. Query action

The query operation also implements two methods:

Future<List<Map<String, dynamic>>> query(String table,
 {bool distinct,
 List<String> columns,
 String where,
 List<dynamic> whereArgs,
 String groupBy,
 String having,
 String orderBy,
 int limit,
 int offset});
 
Future<List<Map<String, dynamic>>> rawQuery(String sql,
 [List<dynamic> arguments]);

The first parameter of query method is the table name of operation, and the optional parameters after it indicate whether to deduplicate, query field, WHERE clause (you can use? As a placeholder), WHERE clause placeholder parameter value, GROUP BY clause, HAVING clause, ORDER BY clause, number of queries, offset bits of queries, etc.

The first parameter of rawQuery method is a query sql statement, which can be used? As a placeholder, populate the data with the second parameter.

3. Modify the action

The modification operation also implements two methods:

Future<int> rawUpdate(String sql, [List<dynamic> arguments]);

Future<int> update(String table, Map<String, dynamic> values,
 {String where,
 List<dynamic> whereArgs,
 ConflictAlgorithm conflictAlgorithm});

The first parameter of the rawUpdate method is an update sql statement. Can you use? As a placeholder, populate the data with the second parameter.

The first parameter of update method is the table name of operation, the second parameter is the modified field and corresponding value, and the optional parameters after it indicate WHERE clause (you can use? As a placeholder), WHERE clause placeholder parameter value, and operation algorithm in case of conflict (including rollback, termination, ignorance, etc.).

4. Delete action

The modification operation also implements two methods:

Future<int> rawDelete(String sql, [List<dynamic> arguments]);

Future<int> delete(String table, {String where, List<dynamic> whereArgs});

The first parameter of rawDelete method is one delete sql statement. Can you use? As a placeholder, populate the data with the second parameter.

The first parameter of the delete method is the table name of the operation, followed by optional parameters representing the WHERE clause (you can use? As a placeholder) and the WHERE clause placeholder parameter value in turn.

Take a chestnut

Let's take the library management system as an example.

First, we create a book class, including book ID, title, author, price, publishing house and other information.

final String tableBook = 'book';
final String columnId = '_id';
final String columnName = 'name';
final String columnAuthor = 'author';
final String columnPrice = 'price';
final String columnPublishingHouse = 'publishingHouse';

class Book {
 int id;
 String name;
 String author;
 double price;
 String publishingHouse;
 
 Map<String, dynamic> toMap() {
 var map = <String, dynamic>{
 columnName: name,
 columnAuthor: author,
 columnPrice: price,
 columnPublishingHouse: publishingHouse
 };
 if (id != null) {
 map[columnId] = id;
 }
 return map;
 }

 Book();

 Book.fromMap(Map<String, dynamic> map) {
 id = map[columnId];
 name = map[columnName];
 author = map[columnAuthor];
 price = map[columnPrice];
 publishingHouse = map[columnPublishingHouse];
 }
}

Secondly, we began to implement database-related operations:

1. Create database files and corresponding tables

//  Get the storage path of the database file 
 var databasesPath = await getDatabasesPath();
 String path = join(databasesPath, 'demo.db');

// Create a database table based on the database file path and database version number 
 db = await openDatabase(path, version: 1,
 onCreate: (Database db, int version) async {
 await db.execute('''
  CREATE TABLE $tableBook (
  $columnId INTEGER PRIMARY KEY, 
  $columnName TEXT, 
  $columnAuthor TEXT, 
  $columnPrice REAL, 
  $columnPublishingHouse TEXT)
  ''');
 });

2. Implementation of CRUD operation

 //  Insert 1 Book data 
 Future<Book> insert(Book book) async {
 book.id = await db.insert(tableBook, book.toMap());
 return book;
 }

 //  Find all book information 
 Future<List<Book>> queryAll() async {
 List<Map> maps = await db.query(tableBook, columns: [
 columnId,
 columnName,
 columnAuthor,
 columnPrice,
 columnPublishingHouse
 ]);

 if (maps == null || maps.length == 0) {
 return null;
 }

 List<Book> books = [];
 for (int i = 0; i < maps.length; i++) {
 books.add(Book.fromMap(maps[i]));
 }

 return books;
 }

 //  According to ID Find book information 
 Future<Book> getBook(int id) async {
 List<Map> maps = await db.query(tableBook,
 columns: [
  columnId,
  columnName,
  columnAuthor,
  columnPrice,
  columnPublishingHouse
 ],
 where: '$columnId = ?',
 whereArgs: [id]);
 if (maps.length > 0) {
 return Book.fromMap(maps.first);
 }
 return null;
 }

 //  According to ID Delete book information 
 Future<int> delete(int id) async {
 return await db.delete(tableBook, where: '$columnId = ?', whereArgs: [id]);
 }

 //  Update book information 
 Future<int> update(Book book) async {
 return await db.update(tableBook, book.toMap(),
 where: '$columnId = ?', whereArgs: [book.id]);
 }

Step 3 Shut down the database

To shut down the database object at the appropriate time after use, you can implement the following methods in the helper class.

Future close() async => db.close();

Affairs

sqflite also supports transactions, through which multiple atomic operations can be executed in one operation, ensuring that all operations are either completed or none are executed.
For example, if there are two pieces of book data that must be inserted into the stack before they are added successfully, use the following method

 Future<bool> insertTwoBook(Book book1, Book book2) async {
 return await db.transaction((Transaction txn) async {
 book1.id = await db.insert(tableBook, book1.toMap());

 book2.id = await db.insert(tableBook, book2.toMap());

 print('book1.id = ${book1.id}, book2.id = ${book2.id}');
 return book1.id != null && book2.id != null;
 });
 }

Write at the end

Above introduced sqflite we commonly used in several operations, with sqflite we can develop richer applications, in the development of practice we encounter any problems can give us feedback message, we discuss common progress. In response to some user feedback, we will introduce the code debugging of Flutter in the next article.