Flutter & SQLite
Développement Flutter avec une base de données SQLite.
Ne pas oublier d'ajouter le composant sqflite pour gérer la base de données au sein de l'application Flutter.
sqflite: ^1.1.6+3
Code pour gérer la base de données
L'exemple de code fournit ci-dessous permet de gérer les bouteilles dans une cave à vin.
class DatabaseHelper {
static final DatabaseHelper _instance = new DatabaseHelper.internal();
factory DatabaseHelper() => _instance;
static Database _db;
DatabaseHelper.internal();
///
/// get db
///
Future<Database> get db async {
if (_db != null) {
return _db;
}
_db = await BaseDbHelper().createDb();
return _db;
}
///
/// getAllBouteille
///
Future<List<BottleModel>> getAllBouteille() async {
var dbClient = await db;
var result = await dbClient.rawQuery(
'select id, appellation, cepage, chateau, classification, color, emplacement, nb, note, offeredby, region, year, garde from bouteille order by year');
return result.map((c) => BottleModel.fromMap(c)).toList();
}
///
/// Recherche de bouteilles basé sur un filtre
///
Future<List<BottleModel>> filterBouteille(FilterModel filter) async {
var dbClient = await db;
String sql =
"select id, appellation, cepage, chateau, classification, color, emplacement, nb, note, offeredby, region, year, garde from bouteille ";
if (filter.year != null && filter.year < DateTime.now().year) {
sql += "where year < " + filter.year.toString();
} else if (filter.note != null && filter.note > 0) {
sql += "where note > " + filter.note.toString();
} else if (filter.appellation != null && filter.appellation != '') {
sql += "where appellation = '${filter.appellation}'";
} else if (filter.color != null && filter.color != '') {
sql += "where color = '${filter.color}'";
} else if (filter.region != null && filter.region != '') {
sql += "where region = '${filter.region}'";
}
sql += " order by year";
var result = await dbClient.rawQuery(sql);
return result.map((c) => BottleModel.fromMap(c)).toList();
}
///
/// getBouteille
///
Future<BottleModel> getBouteille(int id) async {
var dbClient = await db;
var result = await dbClient.rawQuery(
'select id, appellation, cepage, chateau, classification, color, emplacement, nb, note, offeredby, region, year from bouteille where id = $id');
return result.isNotEmpty ? BottleModel.fromMap(result.first) : null;
}
///
/// deleteBouteille
///
deleteBouteille(int id) async {
var dbClient = await db;
await dbClient.rawQuery('delete from bouteille where id = $id');
}
///
/// decreaseBouteille
///
decreaseBouteille(int id) async {
var dbClient = await db;
await dbClient.rawQuery('update bouteille set nb = (nb -1) where id = $id');
}
///
/// updateBouteille
///
updateBouteille(BottleModel bottle) async {
var dbClient = await db;
await dbClient.rawQuery(
"update bouteille set appellation = '${bottle.appellation}', cepage = '${bottle.cepage}', chateau = '${bottle.chateau}'" +
", classification = '${bottle.classification}', color = '${bottle.color}', emplacement = '${bottle.emplacement}'" +
", nb = ${bottle.nb}, note = ${bottle.note}, offeredby = '${bottle.offeredby}', region = '${bottle.region}'" +
", year = ${bottle.year}, garde = ${bottle.garde} where id = ${bottle.id}");
}
///
/// createBouteille
///
createBouteille(BottleModel bottle) async {
var dbClient = await db;
await dbClient.rawQuery(
"insert into bouteille (appellation, cepage, chateau, classification, color, emplacement, nb, note, offeredby, region, year,garde) values (" +
" '${bottle.appellation}', '${bottle.cepage}', '${bottle.chateau}', '${bottle.classification}', '${bottle.color}'" +
", '${bottle.emplacement}', ${bottle.nb}, ${bottle.note}, '${bottle.offeredby}', '${bottle.region}' , ${bottle.year}" +
", ${bottle.nb})");
}
///
/// close
///
Future close() async {
var dbClient = await db;
return dbClient.close();
}
}
