Flutter SQLite example – CRUD Operations with ListView & sqflite plugin

Flutter SQLite example – CRUD Operations with ListView & sqflite plugin

In previous post, we knew how to make SQLite CRUD Operations using sqflite plugin. In this tutorial, we’re gonna build a Flutter App that allows us to make CRUD interactions with SQLite in a ListView.

Related Post:
Flutter ListView example with ListView.builder
Flutter Navigator example – Send/Return data to/from new Screen
Flutter SQLite example – CRUD operations with sqflite plugin

Firebase:
Flutter Firebase Database example – Firebase Database CRUD with ListView
Flutter Firestore example – Firebase Firestore CRUD with ListView

Example Overview

Goal

Our Flutter App will retrieve list of Notes from SQLite Database and show it in ListView widget. Then we can click on Add button to create new Note, click on a Note item to update that Note, or delete a Note with remove Button.

flutter-sqlite-example-listview-sqflite-overview

Project Structure

flutter-sqlite-example-listview-sqflite-structure

Note class (note.dart) is a Data Model.
DatabaseHelper class (database_helper.dart) handles CRUD Operations with SQLite using sqflite plugin.
ListViewNote (listview_note.dart) is a StatefulWidget with List<Note> is state variable. It shows the list using ListView.builder, sends/gets data to/from NoteScreen using Navigator.
NoteScreen (note_screen.dart) is the user interface for adding/updating Note.
– We add sqflite dependency in pubspec.yaml.

For more detailsa about ListView and Navigator, please visit:
Flutter ListView example with ListView.builder
Flutter Navigator example – Send/Return data to/from new Screen

Practice

Import sqflite

Add sqflite plugin to the dependencies section of our pubspec.yaml:


dependencies:
  sqflite: "^0.11.0+1"

Create Data Model

lib/model/note.dart


class Note {
  int _id;
  String _title;
  String _description;

  Note(this._title, this._description);

  Note.map(dynamic obj) {
    this._id = obj['id'];
    this._title = obj['title'];
    this._description = obj['description'];
  }

  int get id => _id;
  String get title => _title;
  String get description => _description;

  Map toMap() {
    var map = new Map();
    if (_id != null) {
      map['id'] = _id;
    }
    map['title'] = _title;
    map['description'] = _description;

    return map;
  }

  Note.fromMap(Map map) {
    this._id = map['id'];
    this._title = map['title'];
    this._description = map['description'];
  }
}

Create Database Helper Class

lib/util/database_helper.dart


import 'dart:async';

import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
import 'package:sqlite_listview_example/model/note.dart';

class DatabaseHelper {
  static final DatabaseHelper _instance = new DatabaseHelper.internal();

  factory DatabaseHelper() => _instance;

  final String tableNote = 'noteTable';
  final String columnId = 'id';
  final String columnTitle = 'title';
  final String columnDescription = 'description';

  static Database _db;

  DatabaseHelper.internal();

  Future get db async {
    if (_db != null) {
      return _db;
    }
    _db = await initDb();

    return _db;
  }

  initDb() async {
    String databasesPath = await getDatabasesPath();
    String path = join(databasesPath, 'notes.db');

//    await deleteDatabase(path); // just for testing

    var db = await openDatabase(path, version: 1, onCreate: _onCreate);
    return db;
  }

  void _onCreate(Database db, int newVersion) async {
    await db.execute(
        'CREATE TABLE $tableNote($columnId INTEGER PRIMARY KEY, $columnTitle TEXT, $columnDescription TEXT)');
  }

  Future saveNote(Note note) async {
    var dbClient = await db;
    var result = await dbClient.insert(tableNote, note.toMap());
//    var result = await dbClient.rawInsert(
//        'INSERT INTO $tableNote ($columnTitle, $columnDescription) VALUES (\'${note.title}\', \'${note.description}\')');

    return result;
  }

  Future getAllNotes() async {
    var dbClient = await db;
    var result = await dbClient.query(tableNote, columns: [columnId, columnTitle, columnDescription]);
//    var result = await dbClient.rawQuery('SELECT * FROM $tableNote');

    return result.toList();
  }

  Future getCount() async {
    var dbClient = await db;
    return Sqflite.firstIntValue(await dbClient.rawQuery('SELECT COUNT(*) FROM $tableNote'));
  }

  Future getNote(int id) async {
    var dbClient = await db;
    List result = await dbClient.query(tableNote,
        columns: [columnId, columnTitle, columnDescription],
        where: '$columnId = ?',
        whereArgs: [id]);
//    var result = await dbClient.rawQuery('SELECT * FROM $tableNote WHERE $columnId = $id');

    if (result.length > 0) {
      return new Note.fromMap(result.first);
    }

    return null;
  }

  Future deleteNote(int id) async {
    var dbClient = await db;
    return await dbClient.delete(tableNote, where: '$columnId = ?', whereArgs: [id]);
//    return await dbClient.rawDelete('DELETE FROM $tableNote WHERE $columnId = $id');
  }

  Future updateNote(Note note) async {
    var dbClient = await db;
    return await dbClient.update(tableNote, note.toMap(), where: "$columnId = ?", whereArgs: [note.id]);
//    return await dbClient.rawUpdate(
//        'UPDATE $tableNote SET $columnTitle = \'${note.title}\', $columnDescription = \'${note.description}\' WHERE $columnId = ${note.id}');
  }

  Future close() async {
    var dbClient = await db;
    return dbClient.close();
  }
}

Create UI Widgets

lib/ui/listview_note.dart


import 'package:flutter/material.dart';
import 'package:sqlite_listview_example/model/note.dart';
import 'package:sqlite_listview_example/util/database_helper.dart';
import 'package:sqlite_listview_example/ui/note_screen.dart';

class ListViewNote extends StatefulWidget {
  @override
  _ListViewNoteState createState() => new _ListViewNoteState();
}

class _ListViewNoteState extends State {
  List items = new List();
  DatabaseHelper db = new DatabaseHelper();

  @override
  void initState() {
    super.initState();

    db.getAllNotes().then((notes) {
      setState(() {
        notes.forEach((note) {
          items.add(Note.fromMap(note));
        });
      });
    });
  }

  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      title: 'JSA ListView Demo',
      home: Scaffold(
        appBar: AppBar(
          title: Text('ListView Demo'),
          centerTitle: true,
          backgroundColor: Colors.blue,
        ),
        body: Center(
          child: ListView.builder(
              itemCount: items.length,
              padding: const EdgeInsets.all(15.0),
              itemBuilder: (context, position) {
                return Column(
                  children: [
                    Divider(height: 5.0),
                    ListTile(
                      title: Text(
                        '${items[position].title}',
                        style: TextStyle(
                          fontSize: 22.0,
                          color: Colors.deepOrangeAccent,
                        ),
                      ),
                      subtitle: Text(
                        '${items[position].description}',
                        style: new TextStyle(
                          fontSize: 18.0,
                          fontStyle: FontStyle.italic,
                        ),
                      ),
                      leading: Column(
                        children: [
                          Padding(padding: EdgeInsets.all(10.0)),
                          CircleAvatar(
                            backgroundColor: Colors.blueAccent,
                            radius: 15.0,
                            child: Text(
                              '${items[position].id}',
                              style: TextStyle(
                                fontSize: 22.0,
                                color: Colors.white,
                              ),
                            ),
                          ),
                          IconButton(
                              icon: const Icon(Icons.remove_circle_outline),
                              onPressed: () => _deleteNote(context, items[position], position)),
                        ],
                      ),
                      onTap: () => _navigateToNote(context, items[position]),
                    ),
                  ],
                );
              }),
        ),
        floatingActionButton: FloatingActionButton(
          child: Icon(Icons.add),
          onPressed: () => _createNewNote(context),
        ),
      ),
    );
  }

  void _deleteNote(BuildContext context, Note note, int position) async {
    db.deleteNote(note.id).then((notes) {
      setState(() {
        items.removeAt(position);
      });
    });
  }

  void _navigateToNote(BuildContext context, Note note) async {
    String result = await Navigator.push(
      context,
      MaterialPageRoute(builder: (context) => NoteScreen(note)),
    );

    if (result == 'update') {
      db.getAllNotes().then((notes) {
        setState(() {
          items.clear();
          notes.forEach((note) {
            items.add(Note.fromMap(note));
          });
        });
      });
    }
  }

  void _createNewNote(BuildContext context) async {
    String result = await Navigator.push(
      context,
      MaterialPageRoute(builder: (context) => NoteScreen(Note('', ''))),
    );

    if (result == 'save') {
      db.getAllNotes().then((notes) {
        setState(() {
          items.clear();
          notes.forEach((note) {
            items.add(Note.fromMap(note));
          });
        });
      });
    }
  }
}

lib/ui/note_screen.dart


import 'package:flutter/material.dart';
import 'package:sqlite_listview_example/model/note.dart';
import 'package:sqlite_listview_example/util/database_helper.dart';

class NoteScreen extends StatefulWidget {
  final Note note;
  NoteScreen(this.note);

  @override
  State createState() => new _NoteScreenState();
}

class _NoteScreenState extends State {
  DatabaseHelper db = new DatabaseHelper();

  TextEditingController _titleController;
  TextEditingController _descriptionController;

  @override
  void initState() {
    super.initState();

    _titleController = new TextEditingController(text: widget.note.title);
    _descriptionController = new TextEditingController(text: widget.note.description);
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(title: Text('Note')),
      body: Container(
        margin: EdgeInsets.all(15.0),
        alignment: Alignment.center,
        child: Column(
          children: [
            TextField(
              controller: _titleController,
              decoration: InputDecoration(labelText: 'Title'),
            ),
            Padding(padding: new EdgeInsets.all(5.0)),
            TextField(
              controller: _descriptionController,
              decoration: InputDecoration(labelText: 'Description'),
            ),
            Padding(padding: new EdgeInsets.all(5.0)),
            RaisedButton(
              child: (widget.note.id != null) ? Text('Update') : Text('Add'),
              onPressed: () {
                if (widget.note.id != null) {
                  db.updateNote(Note.fromMap({
                    'id': widget.note.id,
                    'title': _titleController.text,
                    'description': _descriptionController.text
                  })).then((_) {
                    Navigator.pop(context, 'update');
                  });
                }else {
                  db.saveNote(Note(_titleController.text, _descriptionController.text)).then((_) {
                    Navigator.pop(context, 'save');
                  });
                }
              },
            ),
          ],
        ),
      ),
    );
  }
}

Main App

lib/main.dart


import 'package:flutter/material.dart';
import 'package:sqlite_listview_example/ui/listview_note.dart';

void main() => runApp(
      MaterialApp(
        title: 'Returning Data',
        home: ListViewNote(),
      ),
    );

Source Code

sqlite_listview_example



By grokonez | July 21, 2018.

Last updated on April 3, 2021.



Related Posts


10 thoughts on “Flutter SQLite example – CRUD Operations with ListView & sqflite plugin”

  1. good afternoon.
    I’m new to flutter and couldn’t fix the error below:
    listview_note.dart

    void _navigateToNote (BuildContext context, Note note) async {
    String result = await Navigator.push (
    context,
    MaterialPageRoute (builder: (context) => NoteScreen (note)),
    );

    In materialpage, “note”, not assigned parameter to Note.

Got Something To Say:

Your email address will not be published. Required fields are marked *

*