Detailed explanation of the usage tutorial of database in Flutter

During the Flutter development process, our department sometimes needs to store some data locally. Although using the sp file form can also solve the problem, sometimes when the data volume is large, it is obviously not suitable for our file form. At this time, we need to use the database for storage. We know that there is a lightweight system provided by the system in the native field.sqliteDatabase, in the powerful ecological environment of Flutter, there is also such a database plug-insqflite: ^2.0.2Can be at the same timeAndroud、iOSperform database operations in.

1. Create a database:Here I take storing my search history as an example。

First import:

import 'package:sqflite/';

Here I have created a database help class to prepare for future database updates, upgrades, etc.:

Code implementation: It mainly encapsulates the acquisition of the Database class.

/// Database Help Classclass DbHelper {

  final String path = ""; // Database name generally remains unchanged //The table name in the database Here is the table where I stored the wrong historical search record  static final searchTab = "SearchHistory";
   //Private structure  DbHelper._();
  static DbHelper? _instance;
  static DbHelper get instance =&gt; _getInstance();
  factory DbHelper() {
    return instance;
  }
  static DbHelper _getInstance() {
    if (_instance == null) {
      _instance = DbHelper._();
    }
    return _instance ?? DbHelper._();
  }

/// The default storage path of the database  /// The SQLite database is a file identified by a path in the file system.  If it is relative,  /// This path is relative to the obtained path getDatabasesPath(),  /// Android default database directory,  /// iOS/MacOS documents directory.
  Future&lt;Database&gt;? _db;

  Future&lt;Database&gt;? getDb() {
    _db ??= _initDb();
    return _db;
  }

  // Guaranteed to be called only once.  Future&lt;Database&gt; _initDb() async {
  // Here is where we really create the database vserion represents the version of the database, if the version changes  //, db will call the onUpgrade method to update the operation    final db =
        await openDatabase(, version: 1, onCreate: (db, version) {
      // Database creation is completed      // Create a table with one auto-increment id and one text("create table $searchTab (id integer primary key autoincrement, name text not null)");      
    }, onUpgrade: (db, oldV, newV) {
      // Upgrade database call    /// db database   /// oldV old version number   // newV new version number   // This method will not be called after the upgrade is completed    });

return db;
  }

// Close the database  close() async {
    await _db?.then((value) =&gt; ());
  }
}

existjavaDuring the background development process, the database will definitely be designed in layers. This benefit can greatly improve the robustness of the code and reduce the later maintenance costs during use. Although we use the database in the mobile front-end, I still recommend that the database be processed in layers. Although it can be implemented without layering, this can also reduce our code maintenance costs and good programming habits. Without further ado, we need to create a data processingdaolayer.

heresqfliteEncapsulated some commonly usedsqlSyntax, such as adding, deleting, modifying and checking, we don’t need to write SQL grammar by ourselves. Here I briefly encapsulate the method of adding, deleting, modifying and checking.

Specific code:

/// Data operation classclass DbSearchHistoryDao {
  /// Add  static insert(String text) {
    // Deduplication    queryAll().then((value) {
      bool isAdd = true;
      for (var data in value) {
        if ( == text) {
          isAdd = false;
          break;
        }
      }
      if (isAdd) {
        ()?.then((value) =&gt; (
              ,
              DbSearchHotBean(name: text).toJson(),
            ));
      }
    });
  }

  /// Delete all  static deleteAll() {
    ()?.then((value) =&gt; (
          ,
        ));
  }

  /// Update data Update data of specific rows in the table through id  static update(DbSearchHotBean dbSearchHotBean) {
    ()?.then((value) =&gt; (
      ,
      (),//Specific updated data      where: "id = ?"// Find the data that needs to be updated through id      ,whereArgs: []
    ));
  }

  /// Check the specific entity class by name  static Future&lt;DbSearchHotBean?&gt; getBean(String name) async {
  var db = await ();
  var maps = await db?.query(,
    columns: ['id','name'],// Get which fields of the entity class are all by default    where: 'name = ?',//By passing the name field in the entity class    whereArgs: [name]);//The value of the specific name is limited to the data  if(maps!=null &amp;&amp;  &gt; 0) {
       return ();
  }
  return null;
  }

  /// Check all  static Future&lt;List&lt;DbSearchHotBean&gt;&gt; queryAll() async {
    List&lt;DbSearchHotBean&gt; list = [];
    await 
        .getDb()
        ?.then((db) =&gt; ().then((value) {
              for (var data in value) {
                ((data));
              }
            }));
    return list;
  }
}

Entity class: Although there is only one field, creating an entity class is convenient for future expansion.

class DbSearchHotBean {
  int? id;
  String? name; // Search terms
  DbSearchHotBean({,required });

  (Map&lt;String, dynamic&gt; json) {
    id = json['id'];
    name = json['name'];
  }

  Map&lt;String, String?&gt; toJson() {
    var map = &lt;String, String?&gt;{};
    map['id'] = id?.toString() ;
    map['name'] = name ?? "";

    return map;
  }
}

The specific usage is very simple：

increase:("search term");

Delete all:();

Change: For example, change water to fire, find the entity of the water to modify the name by increasing the id

("water").then((value){
  if(value!=null){
    (DbSearchHotBean(id: ,name: "fire"));
  }
});

Check all:await ();

Here we have introduced the basic usage of the database. Of course, some operations such as deleting specified data, batch modification, batch deletion and other operations can be used for batch processing operations. I will not introduce it too much here. If you need it, you can view the author's document.Link

batch = ();
('Test', {'name': 'item'});
('Test', {'name': 'new_item'}, where: 'name = ?', whereArgs: ['item']);
('Test', where: 'name = ?', whereArgs: ['item']);
results = await ();

Summarize

Database operation is generally not difficult, but when we have a large amount of data, the design of the data table structure has a certain technical content, and we also have the mastery of some SQL statements. This plug-in helps us encapsulate commonly used functions. If there are special needs, we still need to master certain SQL syntax. Here I will briefly introduce some commonly used methods, which are probably enough on the mobile front-end~

This is the end of this article about the detailed explanation of the usage tutorial of Flutter database. For more related Flutter database content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!