preface

Database storage is one of our common storage methods. When we need to add, delete, modify, or query a large amount of data, we will think of using a database. Flutter provides a SQflite plug-in to perform CRUD operations on a large amount of data. In this article, we will learn the use of SQflite.

Sqflite use

The introduction of the plugin

Add the path_provider plugin to the pubspec.yaml file, the latest version is 1.0.0, as follows:

dependencies:
  flutter:
    sdk: flutter
  # sqflite plug-inSqflite: 1.0.0Copy the code

Then run the flutter Packages get command to download the plugin locally.

This section describes how to operate the database

1. Insert operations

There are two methods for inserting data:

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

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

The first argument to the rawInsert method is an insert SQL statement. As a placeholder, populate the data with the second argument.

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

2. Query operations

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]);
Copy the code

The first argument to the query method is the name of the operation table, followed by optional arguments indicating whether to de-weigh, query fields, and WHERE clauses (available? As placeholders), WHERE clause placeholder parameter values, GROUP BY clause, HAVING clause, ORDER BY clause, number of queries, offset bits of queries, etc.

The rawQuery method takes a query SQL statement as the first argument. As a placeholder, populate the data with the second argument.

3. Modify operations

The modify 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});
Copy the code

The rawUpdate method takes the first argument to an update SQL statement. As a placeholder, populate the data with the second argument.

The first parameter of the update method is the name of the table to be operated on, the second parameter is the field to be modified and the corresponding value, and the following optional parameters represent the WHERE clause. As placeholders), WHERE clause placeholder parameter values, and the operation algorithm in case of a conflict (including rollback, termination, ignoring, and so on).

4. Delete the vm

The modify operation also implements two methods:

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

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

The rawDelete method takes a delete SQL statement as the first argument. As a placeholder, populate the data with the second argument.

The delete method takes the first argument to the table name of the operation, followed by optional arguments to the WHERE clause. As placeholders), WHERE clause placeholder parameter values.

Take a chestnut

Let’s take the library management system for example.

First, we create a book class that includes information about the book ID, title, author, price, publisher, and so on.

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; }returnmap; } Book(); Book.fromMap(Map<String, dynamic> map) { id = map[columnId]; name = map[columnName]; author = map[columnAuthor]; price = map[columnPrice]; publishingHouse = map[columnPublishingHouse]; }}Copy the code

Second, we start implementing database-related operations:

1. Create database files and corresponding tables
Var databasesPath = await getDatabasesPath(); String path = join(databasesPath,'demo.db'); 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) '' ');
    });
Copy the code
2. CRUD operation
Future<Book> insert(Book Book) async {book.id = await db.insert(tableBook, book.tomap ());returnbook; 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]));
    }

    returnbooks; 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);
    }
    returnnull; } Future<int> delete(int ID) async {return await db.delete(tableBook, where: '$columnId = ? '.whereArgs: [id]); Future<int> Update (Book Book) async {return await db.update(tableBook, book.toMap(),
        where: '$columnId = ? '.whereArgs: [book.id]);
  }
Copy the code
3. Close the database

To close database objects when appropriate, implement the following methods in helper classes.

Future close() async => db.close();
Copy the code

Operation effect:

The transaction

Sqflite also supports transactions, which allow multiple atomic operations to be executed together, ensuring that all or none of the operations are executed. For example, if the data of two books must be inserted into the library to be 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}');
      returnbook1.id ! = null && book2.id ! = null; }); }Copy the code

Write in the last

The above introduces several operations we commonly use in SQflite. With SQflite, we can develop richer applications. In the development practice, we can send message feedback to us if we encounter any problems, so that we can communicate and discuss and make progress together. We will introduce the code debugging of Flutter in the next article in response to some user feedback.

Description:

This article is reprinted from the corresponding “Flutter Programming Guide” wechat official account. For more Flutter related articles, open our wechat and scan our QR code to follow our wechat official account.