In this blog post, I will show you how to use SQLite to store data on the user device in flutter. As previous post, I have describe how to use Shared preference into user device. SQLite is other way to store data in user device.
To use SQLite in Flutter, we need to install the sqlite package or found it in https://pub.dev/packages/sqlite.
Execute the command to install in flutter : flutter pub add sqlite
Open Database
SQLite can open by the code below:
import 'package:sqflite/sqflite.dart';
...
var db = await openDatabase('my_first_db.db');
...
When the above code execute DB file will created but if the DB file exists on the directory which is set on openDatabase. Then the DB file is opened. A basically, the DB file is stored in default database directory on Android and the document directory on iOS.
Close Database
While the SQLite DB was opened, it will be closed automatically when the app is closed.
However, if we want to close the DB in specific time, we can use the below code:
...
await db.close();
...
Use an Existing Database
We can use existing SQLite DB to the assets/ folder, then open the pubspec.yaml file and modify it:
assets:
- assets/my_first_data.db
Below code, it will copy the DB if it doesn't exsit.
import 'dart:io';
import 'package:flutter/services.dart';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
Future<Database> getDB() async {
var databasesPath = await getDatabasesPath();
var path = join(databasesPath, '~www/data.db');
var exists = await databaseExists(path);
if (!exists) {
try {
await Directory(dirname(path)).create(recursive: true);
} catch (_) {}
var data = await rootBundle.load(join('assets', 'data.db'));
List<int> bytes = data.buffer.asUint8List(
data.offsetInBytes,
data.lengthInBytes,
);
await File(path).writeAsBytes(bytes, flush: true);
}
return await openDatabase(path);
}
Create Model Person for Database
Let's I show you for CRUD operation on SQLite.
Before starting CRUD operation, we can define a model class to store and use its data. Below I will create simple model as Person.
class Person {
final int id;
final String name;
final int age;
Person({
required this.id,
required this.name,
required this.age,
});
Map<String, dynamic> toMap() {
return {
'id': id,
'name': name,
'age': age,
};
}
@override
String toString() {
return 'Person{id: $id, name: $name, age: $age}';
}
}
Query person data from Database
We can get the data from SQLite DB with a Person model like below code:
final List<Map<String, dynamic>> maps = await db.query('persons');
// final List<Map<String, dynamic>> maps = await db.rawQuery(
// 'SELECT id, name, age FROM persons',
// );
return List.generate(maps.length, (i) {
return Dog(
id: maps[i]['id'],
name: maps[i]['name'],
age: maps[i]['age'],
);
});
Create a person record to Database
We can create the data to SQLite DB with a Person model as below:
var person = Person(
id: 0,
name: 'Fido',
age: 35,
);
await db.insert('persons', person.toMap());
// await db.rawInsert('INSERT INTO persons(id, name, age)
// VALUES (${person.id}, "${person.name}", ${person.age})');
Update a person record in Database
await db.update('persons', person.toMap(), where: 'id = ?',
whereArgs: [person.id]);
// await db.rawUpdate('UPDATE persons SET age = ${person.age}
WHERE id = ${person.id}');
Delete a person record in Database
await db.delete('persons', where: 'id = ?', whereArgs: [id]);
// await db.rawDelete('DELETE FROM persons WHERE id = ?', [id]);
Full Source Code
import 'package:flutter/material.dart';
import 'package:sqlite_example_project/person.dart';
import 'DatabaseHelper.dart';
void main() {
runApp(const MyApp());
}
class MyApp extends StatelessWidget {
const MyApp({Key? key}) : super(key: key);
// This widget is the root of your application.
@override
Widget build(BuildContext context) {
return MaterialApp(
title: 'Flutter Demo',
theme: ThemeData(
primarySwatch: Colors.blue,
),
home: const MyHomePage(title: 'Flutter SQLite Demo'),
);
}
}
class MyHomePage extends StatefulWidget {
const MyHomePage({Key? key, required this.title}) : super(key: key);
final String title;
@override
State<MyHomePage> createState() => _MyHomePageState();
}
class _MyHomePageState extends State<MyHomePage> {
final dbHelper = DatabaseHelper.instance;
final GlobalKey<ScaffoldState> _scaffoldKey = GlobalKey<ScaffoldState>();
List<Person> people = [];
List<Person> peopleByName = [];
//controllers used in insert operation UI
TextEditingController nameController = TextEditingController();
TextEditingController phoneController = TextEditingController();
@override
void initState() {
// TODO: implement initState
super.initState();
setState(() {
_queryAll();
});
}
@override
Widget build(BuildContext context) {
return DefaultTabController(
length: 2,
child: Scaffold(
key:_scaffoldKey,
appBar: AppBar(
// Here we take the value from the MyHomePage object that was created by
// the App.build method, and use it to set our appbar title.
title: Text(widget.title),
bottom: TabBar(
tabs: [
Tab(
text: "Insert",
),
Tab(
text: "View",
),
],
),
),
body: TabBarView(
children: [
_buildInsertWidget(),
_buildViewWidget(),
]
),
));
}
Widget _buildInsertWidget(){
return Center(
child: Column(
children: <Widget>[
Container(
padding: EdgeInsets.all(20),
child: TextField(
controller: nameController,
decoration: InputDecoration(
border: OutlineInputBorder(),
labelText: 'Name',
),
),
),
Container(
padding: EdgeInsets.all(20),
child: TextField(
controller: phoneController,
decoration: InputDecoration(
border: OutlineInputBorder(),
labelText: 'Phone',
),
),
),
OutlinedButton(
child: Text('Insert Person'),
onPressed: () {
String name = nameController.text;
String phone = phoneController.text;
_insert(name, phone);
},
),
],
),
);
}
void _showMessageInScaffold(String message){
_scaffoldKey.currentState?.showSnackBar(
SnackBar(
content: Text(message),
)
);
}
void _insert(String name, String phone) async{
// row to insert
Map<String, dynamic> row = {
DatabaseHelper.colName: name,
DatabaseHelper.colPhone: phone
};
Person person = Person.fromMap(row);
final id = await dbHelper.insert(person);
_showMessageInScaffold('inserted row id: $id');
nameController.text = "";
phoneController.text = "";
setState(() {
_queryAll();
});
}
Widget _buildViewWidget(){
return Container(
child: ListView.builder(
padding: const EdgeInsets.all(8),
itemCount: people.length,
itemBuilder: (BuildContext context, int index) {
// if (index == people.length) {
// return TextButton(
// child: Text('Refresh'),
// onPressed: () {
// setState(() {
// _queryAll();
// });
// },
// );
// }
return Card(
color: Colors.white,
elevation: 2.0,
child: ListTile(
leading: CircleAvatar(child: Text('${people[index].id}'),),
trailing: GestureDetector(
child: Icon(
Icons.delete,
color: Colors.grey,
),
onTap: () {
setState(() {
_delete(context, people[index]);
});
},
),
title: Text('${people[index].name}'),
subtitle: Text('${people[index].phone}'),
),
);
},
),
);
}
void _queryAll() async {
final allRows = await dbHelper.queryAllRows();
people.clear();
allRows.forEach((row) => people.add(Person.fromMap(row)));
_showMessageInScaffold('Query done.');
setState(() {});
}
void _delete(context, person) async {
await dbHelper.delete(person.id);
_showMessageInScaffold('delete row id: ${person.id}');
setState(() {
_queryAll();
});
}
}
0 Comments