Xiao CAI tried the application of SQL database a long time ago, but it was seldom used in actual scenarios, so there was no follow-up study. Today small dish according to the actual application of SQL for a simple small package;

SQL

Small dish continue to use sqflite plug-in to complete the operation of the database;

  • Small dishes need to operate on multiple tables and have the same method for different tables
  • Operations on multiple pages are required for single tables

According to these two requirements, the side dish is divided into two steps. The first step is to extract a common abstract class for multiple table type operations. The second step is to use a singleton for specific table operation;

1. Extract abstract classes

For the operation of the database table, the basic is to add, delete, change and check, small vegetable only abstract class extraction of the public method; The side dish extracts the paging query or inserts and updates the database table according to Map/Json.

[SQL] insertSQL Future<int> insertSQL(String SQL); /// [Map] insert Map Future<int> insertByMap(String tableName, Map<String, dynamic> Map); // [json] insert JSON Future<int> insertByJson(String tableName, String json); // [key] key- ColumnName /// [value] value -ColumnValue Future<int> deleteByParams(String tableName, String key, Object value); [tableName] Future<int> deleteAll(String tableName); // [SQL] updateSQL(String tableName, String SQL); UpdateByMap (String tableName, Map<String, dynamic> Map); [orderBy] Future<List<Map<String, dynamic>>> queryList(String tableName, {int count, String orderBy}); Future<List<Map<String, // [limitCount] {// [Order Derby]; // dynamic>>> queryListByPage( String tableName, int limitCount, int pageSize, {String orderBy}); // close the database closeDB(); }Copy the code

2. The singleton

For the operation of a single table, the use of a single routine is very convenient, can be used globally; The abstracted classes are then implemented separately;

class BillSQLManager extends SQLMethod { static BillSQLManager _instance; Database _db; static BillSQLManager getInstance() { if (_instance == null) { _instance = new BillSQLManager(); } return _instance; }}Copy the code

Initializing the database

Future<Database> initDB() async {
  final databasePath = await getDatabasesPath();
  print('SQLManager.initDB -> databasePath = $databasePath');
  final path = join(databasePath, ConstantUtils.DB_NAME);
  _db = await openDatabase(path,
      version: ConstantUtils.DB_VERSION,
      onCreate: (_db, _) => _db.execute(ConstantUtils.CREATE_BILL_SQL));
  return _db;
}
Copy the code

Inserting a database

@override Future<int> insertByMap(String tableName, Map<String, dynamic> map) async { int result; if (map ! = null) { result = await _db.insert(tableName, map); } return result; }Copy the code

Querying the database

@override Future<List<Map<String, dynamic>>> queryList(String tableName, {int count, String orderBy}) async { List<Map<String, dynamic>> list = await _db.query(tableName, orderBy: 'updateTime ${orderBy ?? 'DESC'}'); if (list ! = null) { if (count ! = null && count > 0) { if (count >= list.length) { return list; } else { return list.sublist(0, count); } } else { return list; } } return null; }Copy the code

Updating the database

@override updateByParams(String tableName, String key, Object value, Map<String, dynamic> map) async { if (key ! = null) { return await _db.update(tableName, map, where: '$key=? ', whereArgs: [value]); } return null; }Copy the code

Deleting a Database

@override
Future<int> deleteByParams(String tableName, String key, Object value) async {
  if (key != null) {
    return await _db.delete(tableName, where: '$key=?', whereArgs: [value]);
  } else {
    return -1;
  }
}
Copy the code

3. Precautions

1. Join () method not found

Join () method not found The reason for this is that dishes only introduced a package: sqflite/sqflite dart, also need to introduce package: path/path. The dart, this introduction will not need to be reminded that automatically;

2. Unhandled Exception: type ‘_InternalLinkedHashMap<dynamic, dynamic>’ is not a subtype of type ‘Map<String, dynamic>’

When bill bean.tomap () is defined, Map

needs to match the type stored in the database. Map

. From (Map);
,>
,>

The map = map < String, dynamic >. The from ({' billName: _tableController. Text. The trim (), 'billExp' : '0.0', 'billInc' : '0.0', 'billCount' : '0', 'createTime: DateTime. Now () millisecondsSinceEpoch, "updateTime" : DateTime.now().millisecondsSinceEpoch });Copy the code

3. How does whereArgs send a reference

Select * from whereArgs (whereArgs, whereArgs, whereArgs, whereArgs, whereArgs, whereArgs); Where (key=key=value) where (key=value) where ($key=? To invoke the;

UpdateByParams (String tableName, String Key, Object Value, Map<String, dynamic> Map) async {if (key! = null) { return await _db.update(tableName, map, where: '$key=? ', whereArgs: [value]); } return null; UpdateByParams (String tableName, String Key, Object Value, Map<String, dynamic> Map) async {if (key! = null) {return await _db. update(tableName, map, WHERE: '$key=¥value'); } return null; }Copy the code

SQL case source

Small dish for the small package database is not perfect, only according to the business, part of the extraction, etc., will continue to improve according to the business; If there are mistakes, please guide!

Source: Little Monk A Ce