Things that you don’t know about

PS: bloggers play written in Flutter android: https://github.com/codingmancui/flutter_frolo welcome to star

Introduction to the Floor database

The Floor library is the SQLite abstract support for the Flutter application. The Floor library provides a lightweight SQLite abstraction that automatically maps between in-memory objects and the database, while giving the database full control through SQL.

Floor Quick Start

1. Add dependencies

Dependencies: flutter: SDK: flutter floor: ^0.17.0 dev_dependencies: floor_generator: ^0.17.0 build_runner: ^1.10.3Copy the code

2. Create an entity

It will represent a database table and the framework of the business object. The @entity flag is a class that corresponds to a database table. You can specify the table name if you do not specify the default table name Entity name. @primarykey is used to indicate that this is the primaryKey of the data table and needs to be an int property. @ignore is used to ignore fields

@Entity(tableName:'history')
class Article {
  @primaryKey
  int id;
  List<Tags> tags;
  String title;
  @ignore
  int itemType = 0;
  Article(
      {this.id,
      this.tags,
      this.title,
      this.itemType});
}
Copy the code

3. Create a DAO(Data Access Object)

This component is responsible for managing access to the underlying SQLite database, and the abstract classes contain the method signatures for querying the database, which must return a Future or Stream

You can define a Query by adding an @Query annotation to the method, which must return the Future or Stream of the entity you are querying. @insert marks the method as an insert method.

import 'package:floor/floor.dart';
import 'package:frolo/data/protocol/models.dart';

@dao
abstract class ArticleDao{
  @Query('SELECT * FROM HISTORY')
  Future<List<Article>> findAllArticles();

  @insert
  Future<void> insertArticle(Article article);
}
Copy the code

4. Create a database

It must be an abstract class that inherits FloorDatabase, and you need to add @Database() to the class’s signature to ensure that 2. The entities created in this step are added to the @database entities attribute

import 'dart:async';
// required package imports
import 'package:floor/floor.dart';
import 'package:sqflite/sqflite.dart' as sqflite;

import 'package:frolo/data/db/article_dao.dart';
import 'package:frolo/data/protocol/models.dart';

import 'article_tag_converter.dart';
part 'database.g.dart'; // the generated code will be there

@TypeConverters([ArticleTagConverter])
@Database(version: 1, entities: [Article])
abstract class WanAndroidDatabase extends FloorDatabase{
  ArticleDao get articleDao;
}
Copy the code

Note:

1. Make sure to add part ‘database.g.art ‘; Under the import of this file, note that the database must be swapped with the file name of the database definition. In this case, the file is called database.dart, so part ‘database.g.art ‘;

2. Run the following command flutter packages pub run build_runner build. If necessary, run the command flutter packages pub run build_runner watch automatically

5. Use generated code

To get an instance of the database, use the generated $FloorWanAndroidDatabase class, which allows access to the database builder. The name consists of $Floor and the database class name. The string passed to databaseBuilder() will be the database file name. To initialize the database, call build() and ensure the result with await.

 final database = await $FloorWanAndroidDatabase.databaseBuilder('wan_android_database.db').build();
 final articleDao = database.articleDao;

 await articleDao.insertArticle(article);
 final result = await articleDao.findAllArticles();
Copy the code

Floor mining pit

Entity Type storage

1. TypeConverters type

SQLite allows you to store only a few types of values. When you need to store more complex Dart objects in memory, you sometimes need to convert between Dart and SQLite compatible types.

In the demo above, the tags field of Article class is of type List< tags >. If TypeConverters is not used, an error will be reported when the flutter packages pub run build_runner build is executed. Lists do not support the

* column type
*>

The Column type is not supported for the List < > Tags * *. Package: frolo/data/protocol/models. The dart: 63:14 ╷ 63 │ a List < Tags > Tags. │ ^ ^ ^ ^Copy the code

2. TypeConverters implementation and usage

1. Creating implementation abstractionsTypeConverterAnd provide in-memory object types and database types as parameterized types. This class inheritsdecode()andencode()Functions that define conversions from one type to another. In the heart of the DemoList<Tags>Type conversion toStringDatabase storage

class ArticleTagConverter extends TypeConverter<List<Tags>, String> { @override List<Tags> decode(String databaseValue) { List list = json.decode(databaseValue); List<Tags> tags = new List(); list.map((value) { tags.add(Tags.fromJson(value)); }); return tags; } @override String encode(List<Tags> value) { String v = json.encode(value); return v; }}Copy the code

2. Through the use of@TypeConvertersThe annotation applies the created type converter to the database and makes sure that additional type converter files are imported here. Importing it in the database file is always necessary because the generated code will be part of the database file, and this is where the type converter is instantiated.

@TypeConverters([ArticleTagConverter])
@Database(version: 1, entities: [Article])
abstract class WanAndroidDatabase extends FloorDatabase{
  ArticleDao get articleDao;
}
Copy the code

Generate the filedatabase.g.dartparsing

// GENERATED CODE - DO NOT MODIFY BY HAND part of 'database.dart'; // ************************************************************************** // FloorGenerator // ************************************************************************** class $FloorWanAndroidDatabase { /// Creates a database builder for a persistent database. /// Once a database is built, you should keep a reference to it and re-use it. static _$WanAndroidDatabaseBuilder databaseBuilder(String name) => _$WanAndroidDatabaseBuilder(name); /// Creates a database builder for an in memory database. /// Information stored in an in memory database disappears when the process is killed. /// Once a database is built, you should keep a reference to it and re-use it. static _$WanAndroidDatabaseBuilder inMemoryDatabaseBuilder() => _$WanAndroidDatabaseBuilder(null); } class _$WanAndroidDatabaseBuilder { _$WanAndroidDatabaseBuilder(this.name); final String name; final List<Migration> _migrations = []; Callback _callback; /// Adds migrations to the builder. _$WanAndroidDatabaseBuilder addMigrations(List<Migration> migrations) { _migrations.addAll(migrations); return this; } /// Adds a database [Callback] to the builder. _$WanAndroidDatabaseBuilder addCallback(Callback callback) { _callback = callback; return this; } /// Creates the database and initializes it. Future<WanAndroidDatabase> build() async { final path = name ! = null ? await sqfliteDatabaseFactory.getDatabasePath(name) : ':memory:'; final database = _$WanAndroidDatabase(); database.database = await database.open( path, _migrations, _callback, ); return database; } } class _$WanAndroidDatabase extends WanAndroidDatabase { _$WanAndroidDatabase([StreamController<String> listener]) { changeListener = listener ?? StreamController<String>.broadcast(); } ArticleDao _articleDaoInstance; Future<sqflite.Database> open(String path, List<Migration> migrations, [the Callback Callback]) async {final databaseOptions = sqflite. OpenDatabaseOptions (version: 1, / / onConfigure version: (database) async { await database.execute('PRAGMA foreign_keys = ON'); }, onOpen: (database) async { await callback? .onOpen? .call(database); }, // Database upgrade onUpgrade: (database, startVersion, endVersion) async { await MigrationAdapter.runMigrations( database, startVersion, endVersion, migrations); await callback? .onUpgrade? .call(database, startVersion, endVersion); }, // database create onCreate: (database, version) async { await database.execute( 'CREATE TABLE IF NOT EXISTS `history` (`id` INTEGER, `apkLink` TEXT, `audit` INTEGER, `author` TEXT, `canEdit` INTEGER, `chapterId` INTEGER, `chapterName` TEXT, `collect` INTEGER, `courseId` INTEGER, `desc` TEXT, `descMd` TEXT, `envelopePic` TEXT, `fresh` INTEGER, `link` TEXT, `niceDate` TEXT, `niceShareDate` TEXT, `origin` TEXT, `prefix` TEXT, `projectLink` TEXT, `publishTime` INTEGER, `realSuperChapterId` INTEGER, `selfVisible` INTEGER, `shareDate` INTEGER, `shareUser` TEXT, `superChapterId` INTEGER, `superChapterName` TEXT, `tags` TEXT, `title` TEXT, `type` INTEGER, `userId` INTEGER, `visible` INTEGER, `zan` INTEGER, PRIMARY KEY (`id`))'); await callback? .onCreate? .call(database, version); }); return sqfliteDatabaseFactory.openDatabase(path, options: databaseOptions); } @override ArticleDao get articleDao { return _articleDaoInstance ?? = _$ArticleDao(database, changeListener); } } class _$ArticleDao extends ArticleDao { _$ArticleDao(this.database, this.changeListener) : _queryAdapter = QueryAdapter(database), Encode = InsertionAdapter(database, 'history', (Article item) => <String, dynamic>{ 'id': item.id, 'tags': _articleTagConverter.encode(item.tags), 'title': item.title, }); final sqflite.DatabaseExecutor database; final StreamController<String> changeListener; final QueryAdapter _queryAdapter; final InsertionAdapter<Article> _articleInsertionAdapter; @override Future<List<Article>> findAllArticles() async {// This method returns an empty object by default, QueryList ('SELECT * FROM history', mapper: (Map<String, dynamic> row) => Article(id: row['id'] as int, tags: _articleTagConverter.decode(row['tags']), title: row['title'] as String, )); } @override Future<void> insertArticle(Article article) async { await _articleInsertionAdapter.insert(article, OnConflictStrategy.replace); }} // ignore_for_file: unused_element converter creates final _articleTagConverter = ArticleTagConverter();Copy the code

1. Note here that since this file is created automatically, some of it needs to be modified manually. In the Demo, the blogger encountered this pit, and the data queried was all empty objects. The final problem was in the automatically generated codefindAllArticlesThe method is as follows: Convert the queried row toArticleIs actually creating an empty object

@override Future<List<Article>> findAllArticles() async {return _queryAdapter. QueryList ('SELECT * FROM history',  mapper: (Map<String, dynamic> row) => Article()); }Copy the code

2. If during database insertprimaryKeyConflict, we can specify the conflict policy, there are five different policies, the one we use in the DemoOnConflictStrategy.replace. Does this look familiar? It looks like a reject policy in a thread pool

  @override
  Future<void> insertArticle(Article article) async {
    await _articleInsertionAdapter.insert(article, OnConflictStrategy.replace);
  }
Copy the code

PS: bloggers play written in Flutter android: https://github.com/codingmancui/flutter_frolo welcome to star