13

How to store a list of object data in SQLite on Flutter? Json data coming with API.

{
     "images": [
        {
          "id": 10,
          "name": "img1"
        },
        {
          "id": 11,
          "name": "img2"
        }
      ]
}
  • https://www.tutorialspoint.com/sqlite/sqlite_insert_query.htm, https://stackoverflow.com/questions/625029/how-do-i-store-and-retrieve-a-blob-from-sqlite – Robert Harvey Mar 19 '19 at 02:09

1 Answers1

5

You need to serialize the list of objects before storing them using SQLite.

Firstly, you cannot store Map or List in the database directly, you need to convert Map or List to JSON String first, check out https://dart.dev/guides/json to learn how to use JSON in Dart

import 'dart:convert';

final data = {
     "images": [
        {
          "id": 10,
          "name": "img1"
        },
        {
          "id": 11,
          "name": "img2"
        }
      ],
};

final String dataAsJson = json.encode(data);

Secondly, use Flutter sqflite package to create an SQLite database and create a table with the following columns: id auto increment data to store your data fetched from API as JSON dataAsJson

import 'package:sqflite/sqflite.dart';

// 1. open the database first. check the documentation of `sqflite` package

// 2. insert data to the table
await db.insert(
    'images', # the name of the table
    {'data': dataAsJson}, # `data` is the column's name
);

Lastly, get the data from the database using await db.query(..)

final List<Map> maps = await db.query('images', columns: ['id', 'data']);

// now let's get the first item in the table then convert it back as it was fetched from the API.

final dataFromJsonToMap = json.decode(maps[0]);

If you only want to store the images from the API, you do not need to convert to JSON, create a table with columns id and name and insert.

await db.insert('images', {'id': 10, 'name': 'img1'});
await db.insert('images', {'id': 11, 'name': 'img2'});
HasanAlyazidi
  • 570
  • 1
  • 5
  • 20
  • at 2 point when passing json value then get it show error which is Unhandled Exception: DatabaseException(table class_time_table has no column named data (Sqlite code 1 SQLITE_ERROR): , while compiling: INSERT OR REPLACE INTO class_time_table (data) VALUES (?), (OS error - 2:No such file or directory)) sql 'INSERT OR REPLACE IN TO class_time_table (data) VALUES (?)' args [[{"class_name":"9TH","section_name":"A","subject_n...] – Adnan haider Oct 13 '21 at 06:21
  • The error says there is no column named `data` in the table `class_time_table`. Add the mentioned column then try again. – HasanAlyazidi Oct 13 '21 at 15:54
  • i already add it but still facing the problem here is code db.execute( 'CREATE TABLE time_table(id INTEGER PRIMARY KEY AUTO_INCREMENT, data TEXT,)' ); – Adnan haider Oct 14 '21 at 04:22
  • Updated code db.execute( 'CREATE TABLE class_time_table(id INTEGER PRIMARY KEY AUTO_INCREMENT, data TEXT,)' ) – Adnan haider Oct 14 '21 at 04:32
  • If the previous sqlite database file is still existing, you need to delete it first, create a new database with a new filename, or only add the missing column `data` using `db.execute('ALTER TABLE ..')` see https://www.sqlitetutorial.net/sqlite-alter-table – HasanAlyazidi Oct 14 '21 at 12:03
  • @HasanAlyazidi suppose If I want to change name "img2" to "img5" , then how to update it using sqfLite Update query – Deepak Jul 26 '23 at 16:54
  • @Deepak Please create a new question and inform me to reply. – HasanAlyazidi Jul 26 '23 at 23:19