CODEWITHSUNDEEP
sqliteflutterdart
Lakshmi Vara Prasad Guduru
Lakshmi Vara Prasad Guduru

Reputation: 13

Is there any way to add large list items in SQLite table in flutter?

I am learning flutter, I am making an app that has checkbox list items on the home page. So, User can check the items and save them. I want to use the SQLite database to save user checked items. But how can I create a database table if I am having lots of items? Should I add each item in the column?

void _createDb(Database db, int newVersion) async {
  await db.execute('CREATE TABLE $AppTable($Id INTEGER PRIMARY KEY AUTO INCREMENT, $Item1 Integer, $Item2 Integer, : : $Itemn Integer )' ); 
}

Or is there any way to do this? Because I am having so many list items how can I create a data table for this? Suggest me a way to do this?

Upvotes: 0

Views: 1739

Answers (1)

Lars Skaug
Lars Skaug

Reputation: 1386

First of all, welcome to Stack Overflow. SQLite works for small amounts of data and is a great introduction to database management.

You should take some time to read up on database design and normalization in particular, but you will want to create a table with the options, another table with users, and a third one with the options chosen by the users.

create table user (user_id integer, name varhcar, other varchar);

create table options (option_id integer, display_text varchar);

create table options_chosen ( user_id integer, option_id integer);

insert into user values (1, 'Charles Aznavour', 'Famous French singer');

insert into options values (1, 'Rock');
insert into options values (2, 'Pop');
insert into options values (3, 'Hip Hop');


insert into options_chosen values (1, 2); 
insert into options_chosen values (1, 3);

You then access the data as follows:

select usr.name, opt.display_text options_chosen
from user usr
 inner join options_chosen chn
  on chn.user_id = usr.user_id
 inner join options opt
  on opt.option_id = chn.option_id;

You can insert many rows at a time into SQLite as follows:

insert into options_chosen values (1, 2), (1, 3), (2, 1), (2, 2), <...> ;

In Dart, you're going to want to use Batch to insert multiple records. Check out this example, modified as follows for your need:

batch = db.batch();
batch.rawInsert('INSERT INTO options_chosen VALUES (?, ?)', ['item1', 'item2']);
batch.insert('options_chosen ', {'user_id': 1, 'option_id': 2});
results = await batch.commit();

Upvotes: 1

Related Questions