In this tutorial, we shall learn basic SQLite operations with the help of a complete Flutter Application.
About UI
Following example application contains a TabBar with TabBarView for operations Insert, View, Query, Update and Delete operations that we shall perform on the car_table.
In Insert Screen, we shall take car name and number of miles it has gone through TextField widgets. Then when you click on the Insert button, we shall prepare the Car object through _insert() method of main() and call insert() method of Database Helper.
In View Screen, there a Refresh button. When you click on it, it queries all the rows and displays them in a ListView.
In Query Screen, there is a TextField to get the name of the car from user. We have attached onChanged() method, so that, when user starts typing, it dynamically queries the table and shows the rows below the button in a ListView.
In Update Screen, we have three TextFields for reading id, name and miles from user. When user enters these fields and click update button, we shall update name and miles for the id provided. You can go the View tab, press on Refresh button to see if the update happened.
In Delete Screen, we have a TextField to read id. When user provides the id and presses Delete button, the row shall be deleted, based on id, if present in the database table.
Dependencies
Under dependencies section, below flutter property, add sqlite and path packages.
dependencies: flutter: sdk: flutter sqflite: path: |
And click on Packages get button on the top right corner of editor.
No need to mention the version number for the packages sqflite and path.
Class File
Under lib folder, create a file named car.dart with class as shown below.
Users.dart
class UserData{
int? id;
String? fname;
String? lname;
UserData(this.id, this.fname, this.lname);
}Database Helper Class
Create a Database helper Class as shown below.
It contains methods to create a Database if not present, connect to the database, perform SQLite operations like query rows from table, update a row, delete a row, insert a row, etc.
DatabaseHelper.dart
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
import 'package:sqlite/main.dart';
class DatabaseHelper{
Database? databse;
DatabaseHelper(){
init();
}
static String table="Student";
static String fname="first_name";
static String rollNo="roll";
static String lname="last_name";
void init() async{
// Get a location using getDatabasesPath
var databasesPath = await getDatabasesPath();
String path = join(databasesPath, 'demo.db');
//check db exit or not
var exists = await databaseExists(path);
if(!exists){
print("Create Database");
databse = await openDatabase(path, version: 1,
onCreate: (Database db, int version) async {
// When creating the db, create the table
await db.execute(
'CREATE TABLE $table (id INTEGER PRIMARY KEY, $fname TEXT, $rollNo INTEGER, $lname TEXT)');
});
}else{
print("DAtabase Available");
databse=await openDatabase(path,readOnly: false);
}
}
InsertData(Map<String,dynamic> values)async{
var result=await databse!.insert(table, values );
print(" result $result");
}
ViewData()async{
var result=await databse!.rawQuery("select * from $table");
List<UserData> users=[];
if(result.length!=0){
for(var i=0;i<result.length;i++){
Map<String,dynamic> data=result[i];
users.add(UserData(data[rollNo], data[fname], data[lname]));
}
return users;
}else{
return users;
}
}
UpdateData(Map<String,dynamic> values)async{
var result=await databse!.update(table,values,where: "$rollNo='${values[rollNo]}'");
print(" result $result");
}
DeleteData(int roll)async{
var result=await databse!.delete(table, where: "$rollNo='$roll'");
print(" result $result");
}
}Observe that we have imported the sqflite and path at the start of our main.dart file.
Flutter UI
Following is the complete main.dart file.
Users.dart
import 'package:flutter/material.dart';
import 'package:sqlite/DatabaseHelper.dart';
void main() {
runApp(const MyApp());
}
class MyApp extends StatelessWidget {
const MyApp({super.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: 'Sqlite demo'),
);
}
}
class MyHomePage extends StatefulWidget {
const MyHomePage({super.key, required this.title});
final String title;
@override
State<MyHomePage> createState() => _MyHomePageState();
}
class _MyHomePageState extends State<MyHomePage> {
var name=TextEditingController();
var lastname=TextEditingController();
var id=TextEditingController();
DatabaseHelper? dbHelper;
List<UserData> listdata=[];
@override
void initState() {
// TODO: implement initState
super.initState();
setState(() {
dbHelper=DatabaseHelper();
});
}
@override
Widget build(BuildContext context) {
return Scaffold(
appBar: AppBar(title: Text("Firebase CRUD"),),
body: Padding(
padding: const EdgeInsets.all(20.0),
child: Column(
children: [
SizedBox(height: 100,),
TextField(
decoration: InputDecoration(
hintText: "Enter Roll No."
),
controller: id,
),
SizedBox(height: 20,),
TextField(
decoration: InputDecoration(
hintText: "Enter Name"
),
controller: name,
),
SizedBox(height: 20,),
TextField(
decoration: InputDecoration(
hintText: "Enter last name"
),
controller: lastname,
),
SizedBox(height: 20,),
Row(
children: [
ElevatedButton(onPressed: () async {
var data={
'roll':int.parse(id.text),
'first_name':name.text,
'last_name':lastname.text
};
dbHelper!.InsertData(data);
//set method use to insert new record
}, child: Text("INSERT")),
Spacer(),
ElevatedButton(onPressed: () async {
List<UserData> users=await dbHelper!.ViewData();
setState(() {
listdata.clear();
listdata.addAll(users);
});
}, child: Text("View")),
Spacer(),
ElevatedButton(onPressed: ()async{
var data={
'roll':int.parse(id.text),
'first_name':name.text,
'last_name':lastname.text
};
dbHelper!.UpdateData(data);
//update method use to update record
}, child: Text("UPDATE")),
Spacer(),
ElevatedButton(onPressed: ()async{
//remove method delete that record in child
dbHelper!.DeleteData(int.parse(id.text));
}, child: Text("DELETE")),
],
),
SizedBox(height: 20,),
Expanded(child:
listdata.length!=0?ListView.builder(
itemCount: listdata.length,
itemBuilder: (BuildContext context, int index) {
return ListTile(
leading: Text(listdata[index].id.toString()),
title: Text(listdata[index].fname.toString()),
subtitle: Text(listdata[index].lname.toString()),
);
},
):Container())
],
),
),
);
}
}