The author | | vlad source vlad (public number: fulade_me)

sqflite

Data persistence is an essential technology in mobile development. We always have some user information, application resources, list data to store, and so on. Here we will focus on data storage based on SQLite database. SQLite is a lightweight database. It is designed to be embedded, and has been used in many embedded products, it is very low resource footprint, in embedded devices, may need only a few hundred K of memory is enough. For more details, please refer to Wikipedia and Baidu Baike. Flutter has helped us encapsulate the library that operates SQLite: SQflite

Integrated sqflite library

< span style = “box-sizing: inherit! Important; word-break: inherit! Important;”

sqflite: ^ 1.1.3
Copy the code

Here is 1.1.3 as an example to add after the save, VSCode by default will execute pub get to help us to download the required libraries, also we can execute pub get in the project root directory to manually pull the required libraries.

1. Create a local data file

static Future<SqfliteManager> _initDataBase() async {
SqfliteManager manager = SqfliteManager();
String dbPath = await getDatabasesPath() + "/$sqlName";
if (manager.db == null) {
    manager.db = await openDatabase(
    dbPath,
    version: 1,
    onCreate: (db, version) async {
        /// Create the required table if no current table exists
        if (await manager.isTableExit(db, tableName) == false) {
        awaitdb.execute(CREATE_DATA_TABLE); }}); }return manager;
}
Copy the code

First we use getDatabasesPath() to get the path to the locally saved database file. After this path we concatenate the name of our database file. On iOS, this path is in the Documents folder under the Shahe path, and on Android, the default database directory. We then declare a Database object to hold Database action objects

Database db;
Copy the code

Check if the object exists, and if it doesn’t we call openDatabase to create it and pass in the database address, version number, and onCreate callback that we got earlier. Inside the onCreate callback, determine if the table name we want to use exists, and execute the SQL statement to create the database table if it does not.

In addition to onCreate callback, onUpgrade, onDowngrade, onOpen, etc. The other argument, singleInstance, indicates whether the same instance object is returned when passed to the same database path. The default is true

2. Insert data

Since SQflite does a lot of work for us, it’s easy to use basic add, delete, change, and search

  /// Insert data
  Future<int> insertData(Map<String.dynamic> value) async {
    return await db.insert(tableName, value);
  }
Copy the code

All you need to do is pass in the name of the table and the data to insert. Let’s look at the other arguments to insert

Future<int> insert(String table, Map<String.dynamic> values,
      {String nullColumnHack, ConflictAlgorithm conflictAlgorithm});
Copy the code
  • NullColumnHack is useful when the incoming insert data is empty

If the insert data is empty: If nullColumnHack is not added, the SQL statement will end up like INSERT into tableName()values(), which is not allowed. Insert into tableName (nullColumnHack)values(NULL). This is fine.

  • ConflictAlgorithm is an enumeration of which policy to use when inserted data conflicts or errors occur. It has the following values
enum ConflictAlgorithm {
  rollback,
  abort,
  fail,
  ignore,
  replace,
}
Copy the code

3. Delete data

The code for deleting data is as follows

  /// Delete a piece of data
Future<int> deleteData(int id) async {
    return await db.delete(tableName, where: "id = ?", whereArgs: [id]);
}
Copy the code

Let’s see what arguments the delete function takes in detail:

Future<int> delete(String table, {String where, List<dynamic> whereArgs});
Copy the code
  • tableYes Specifies the name of the table where data is to be deleted. For example, testTable
  • whereIs a string that represents the statement to be deleted such as “id =?”
  • whereArgsIt’s an array, it’s a complementwhereIn-statement?For example: [2]

When we pass the above example parameter, we want to delete the data in the testTable whose id = 2

4. Update data

The code for deleting data is as follows

Future<int> updateData(Map<String.dynamic> value, int id) async {
    return await db.update(
        tableName,
        value,
        where: "id = ?",
        whereArgs: [id],
    );
}
Copy the code

The update functions are detailed below

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

These are basically the same parameters as insert, so I don’t need to go into them here

5. Query data

Look directly at the query parameters

Future<List<Map<String.dynamic>>> query(
    String table,  /// The table name is a mandatory parameter
    {bool distinct,  // Whether to contain duplicate data
    List<String> columns,  // The column to query
    String where,       // Query conditions
    List<dynamic> whereArgs, // Query condition parameters
    String groupBy,  // Group column names by column
    String having,   // Set conditions for groups
    String orderBy,   // Sort by column asC /desc
    int limit,     // Limit the number of query results
    int offset});  // start the query with the first item
Copy the code

The parameters of the query statement are relatively rich, which can basically meet the query requirements of some complex scenes

Well, that concludes the use of SQflite.

To see how the above example works, go to my Github repository project flutter_app->lib->routes->sqflite_page.dart and download it and run it.