This is the sixth day of my participation in the August Text Challenge.More challenges in August

The local database under APP is generally used to cache search history records and news lists. The purpose is to cache a screen of data without network. Although most functions cannot be operated by users without network, it is user-friendly in terms of experience. Using a database search, you can convert the results to the JSON format of the corresponding model, which makes it easier to use. In fact, iOS local cache in addition to the database can also be used to unfile operations, but there is no search function, only what is stored, take the result is what, not very flexible. Flutter database operations also rely on native file processing, but encapsulate a higher-level API to facilitate development.

Step 1 add sqflite third-party library dependencies

Sqflite: ^ 2.0.0 + 3Copy the code
flutter packages get 
Copy the code

Step 2. Initialize the DATABASE of SQflite.

Before using a database, you need to create a DATABASE DB file. You can create tables and add, delete, modify, and query tables only after the db file is created.

Static Future<Database> _openDB() async {Database db = await openDatabase('DB/base.db',onCreate:_onCreate,version: 1); return db; }Copy the code

The openDatabase method is called. The first parameter is the path to the DB file, and the second parameter is the table creation and other operations that need to be performed after the database is created. The third parameter is the database version number. So let’s solve for these three parameters once.

1. Path of the DB file

What is the file address before “DB/base.db”? This question is left to the Path_provider to answer. The internal data creation DB file needs path_provider as a dependency support, path_Provider will give a mobile app file storage path. Then, just add DB/base.db after this path to generate the corresponding file.

Path_provider Provides the method for storing mobile phone files:

Directory document = await getApplicationDocumentsDirectory();
Copy the code

Print document.path and the output below is the default file storage path, which is actually the Documents folder on iOS.

/Users/ganggu/Library/Developer/CoreSimulator/Devices/A5C799DF-DFA1-4186-954A-07CEBDE8FB77/data/Containers/Data/Applicat ion/7CA6AC43-3899-471C-8A03-94C81C6F13C4/Documents/Copy the code

So, where is the DB file you just created?

DB folder:

Base. db Database file

OpenDatabase creates a file in openDatabase.

static Future<String> _createNewDB() async { Directory document = await getApplicationDocumentsDirectory(); String path = join(document.path,'DB'); debugPrint('path = $path'); if(await new File(join(path,'base.db')).exists() == false){ try { await new File(join(path,'base.db')).create(recursive:  true); DebugPrint (' Database file created successfully '); } catch(e){debugPrint(' failed to create database file $e'); }} else {debugPrint(' database file already exists '); } return path; }Copy the code

Note: the recursive parameter in the create file method means that if no previous folders are to be created sequentially, if this value is false, it must ensure that all previous folders are present. So, if the value is true, it will be created automatically if a folder in the current path is not present.

2. OnCreate: Creates a table after the database is created

Only one history search table is created, and only one column is keyword.

3. Version: indicates the database version.

Finally, add a method to close the database

Static Future<void> _closeDB(Database db) async {await db.close(); }Copy the code

Step 3: Add, delete, change, and query simple operations.

Start by creating a history search record class that provides the data model for subsequent data operations.

class HistorySearchKeyWord { String keyword; HistorySearchKeyWord({required this.keyword}); Map<String,String> toMap(){ return {'keyword':this.keyword}; }}Copy the code

1,

WSLDB. InsertHistorySearchKeyWord (historySearchKeyWord: new historySearchKeyWord (keyword: 'I love the nature'));Copy the code
/ / search records the insert static Future < void > insertHistorySearchKeyWord ({required HistorySearchKeyWord HistorySearchKeyWord}) async { Database db = await _openDB(); await db.insert("search_keyword", historySearchKeyWord.toMap(), conflictAlgorithm: ConflictAlgorithm.fail); _closeDB(db); }Copy the code

2, delete

WSLDB.deleteAllHistorySearchKeyWord();
Copy the code
/ / delete all search records static deleteAllHistorySearchKeyWord () is async {Database db = await _openDB (); db.delete('search_keyword'); _closeDB(db); }Copy the code

The db.delete method has two parameters: where and whereArgs, which are used for conditional search.

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

3, change

WSLDB. UpdateHistorySearchKeyWord (historySearchKeyWord: new historySearchKeyWord (keyword: 'I am very fond of nature'));Copy the code
/ / update the search data static updateHistorySearchKeyWord ({required HistorySearchKeyWord HistorySearchKeyWord}) async {Database db = await _openDB(); db.update("search_keyword",historySearchKeyWord.toMap(),where: 'keyword = ? ',whereArgs: [' I love Nature '], conflictAlgorithm: conflictAlgorithm.fail); _closeDB(db); }Copy the code

4,

WSLDB.selectAllHistorySearchKeyWord();
Copy the code
/ / search records query static selectAllHistorySearchKeyWord () async {Database db = await _openDB (); List<Map<String, dynamic>> result = await db.query('search_keyword',columns: ['keyword']); result.forEach((Map<String, dynamic> e) { debugPrint(e.toString()); }); _closeDB(db); }Copy the code

The following is a set of search parameters, including the column, sorting mode, and limit on the number of items to be queried. You can perform operations based on service scenarios.

Future<List<Map<String, Object? >>> query(String table, {bool? distinct, List<String>? columns, String? where, List<Object? >? whereArgs, String? groupBy, String? having, String? orderBy, int? limit, int? offset});Copy the code

In fact, both native and flutter database operations are the same, so there is not too much technical content, just a summary of the use of personal different scenarios, maybe some friends have also had questions about how to use the database under Flutter. Write it down, write it down, bad code, don’t spray, and if it helps, I’m glad.