Flutter create listview with local sqlite file

Question

Create a listview from a local sql file(chinook.db) using sqflite Initial issue solved: I/flutter ( 5084): Instance of 'Future' Reference code: https://github.com/tekartik/sqflite/blob/master/sqflite/doc/opening_asset_db.md Thanks @aakash for the help

main.dart
body: Container(
        child: FutureBuilder(
          future: getSQL("albums"),
          builder: (BuildContext context, AsyncSnapshot snapshot) {
            print(snapshot.data);
            if (snapshot.data == null) {
              return Container(child: Center(child: Text("Loading...")));
            } else {
              return ListView.builder(
                itemCount: snapshot.data.length,
                itemBuilder: (BuildContext context, int index) {
                  return ListTile(
                    title: Text(snapshot.data[index].title),
                  );
                },
              );
            }
          },
        ),
      ),

getSQL.dart 
Future getSQL(String tableName) async {
  var databasesPath = await getDatabasesPath();
  var path = join(databasesPath, "chinook.db");
// Check if the database exists
  var exists = await databaseExists(path);
  if (!exists) {
    // Should happen only the first time you launch your application
    print("Creating new copy from asset");
    // Make sure the parent directory exists
    try {
      await Directory(dirname(path)).create(recursive: true);
    } catch (_) {}
    // Copy from asset
    ByteData data = await rootBundle.load(join("assets", "chinook.db"));
    List<int> bytes =
        data.buffer.asUint8List(data.offsetInBytes, data.lengthInBytes);
    // Write and flush the bytes written
    await File(path).writeAsBytes(bytes, flush: true);
  } else {
    print("Opening existing database");
  }
// open the database
  var db = await openDatabase(path, readOnly: true);

  List<Map> list = await db.rawQuery('SELECT * FROM $tableName');
  List theList = [];
  for (var n in list) {
    theList.add(MyCategoryFinal(n["Title"]));
  }
  return (theList);
}
class MyCategoryFinal {
  final String title;
  MyCategoryFinal(this.title);
}

Aakash Kumar · Accepted Answer · 2019-09-10T06:13:02.383

1

I solved this by creating a class for the sqflite table, run a loop on that map list & convert those map items to object list.

Example code;

List<ItemBean> items = new List();
    list.forEach((result) {
      ItemBean story = ItemBean.fromJson(result);
      items.add(story);
    });

To create the object you can use https://app.quicktype.io/. Here you can pass the json to generate the class for it.

After that you can use FutureBuilder to create your listview like this

       FutureBuilder(
          future: MyService.getAllItems(),
          builder: (context, snapshot) {
            if (!snapshot.hasData) {
              return Center(child: CircularProgressIndicator());
            }

            return ListView.builder(
              controller: listScrollController,
              itemCount: snapshot.data.length,
              reverse: true,
              itemBuilder: (context, index) {
                return Text(snapshot.data[index].itemName);
              },
            );
          },
        ),

edited Sep 10 '19 at 06:13

answered Sep 09 '19 at 14:26

Aakash Kumar

1,069
7
11

Having trouble adding items in class from the orig code, below are added Future ....List list = await db.rawQuery('SELECT * FROM $tableName'); //print(list); List theList = []; for (var n in list) { MyCategory theList = MyCategory(n["CategoryName"], n["CategoryDescription"]); theList.add(theList); } return (theList); } class MyCategory { final String categoryName; final String categoryDescription; MyCategory(this.categoryName, this.categoryDescription); } Am i doing it wrong? – Scott Summers Sep 10 '19 at 12:46
You have the same name of list & MyCategory object. Thats why you are facing issue while adding items. When your create `MyCategory theList` within for loop its creating a local object which is replacing the original `theList`. Give your object a different name inside for loop or you can simple add the item without creating an object like this `theList.add(MyCategory(n["CategoryName"], n["CategoryDescription"])); ` – Aakash Kumar Sep 11 '19 at 07:53
Thanks @aakash, solved the issue. Edited orig post for reference – Scott Summers Sep 13 '19 at 10:48

Flutter create listview with local sqlite file

1 Answers1