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
Contents
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.
Project 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<String, dynamic> toMap() { var map = new Map<String, dynamic>(); if (_id != null) { map['id'] = _id; } map['title'] = _title; map['description'] = _description; return map; } Note.fromMap(Map<String, dynamic> 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<Database> 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<int> 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<List> 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<int> getCount() async { var dbClient = await db; return Sqflite.firstIntValue(await dbClient.rawQuery('SELECT COUNT(*) FROM $tableNote')); } Future<Note> getNote(int id) async { var dbClient = await db; List<Map> 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<int> 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<int> 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<ListViewNote> { List<Note> 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: <Widget>[ 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: <Widget>[ 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<StatefulWidget> createState() => new _NoteScreenState(); } class _NoteScreenState extends State<NoteScreen> { 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: <Widget>[ 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(), ), ); |
Hello. waiting new tutorial…
Hi enhusld,
Thank you for your visit 🙂
We will post as soon as possible.
Regards,
grokonez.
Starting flutter, and found the sample i just needed.
Going to try it…
hey .. Great Post Buddy..
Can you tell me where should we close our DB connection.
Thanks.
Hi Shubham,
You can close DB connection inside dispose() method 🙂
Regards,
grokonez.
Please can you implement searchdelegate in search bar to search from listview
Thanks
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.
wonderful thank u so much
join functions how to use because error in this datbase create time.