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();
  }
}