Flutter SQFLite - INSERT,SELECT,SHOW


Flutter SQFLite Example Tutorial

This is a simple Dart Flutter Example Tutorial demonstrating how to perform basic CRUD: Insert, Select and Show data in a flutter application. We will be using the SQFLite SQLite plugin. This is because our database is the SQLite

SQflite is a plugin for flutter. It allows us store, retrieve and manipulate our SQLite databases via flutter code. SQflite supports both Android and iOS platforms.

Here are some of the features of SQFlite:

  1. SQFlite provides for both database transactions as well as batches.
  2. SQlite has inbuilt automatic version managment.
  3. SQFlite provides easy to use methods for inserting, querying, updating as well as deleting data from database.
  4. These CRUD operations are performed in the background thread on both iOS and Android. This frees the UI to remain responsive.

How to Install SQFlite

SQFlite installation is as easy as any other flutter packages. It's hosted inDart Package Repository and can be added as a dependenncy.

To do that:

1. Go to Your pubspec.yaml file:

And add it as a dependency:

dependencies:
  sqflite: ^0.12.1

You may check the latest version here.

Full Example Project

Here's the full source code.

(a). pubspec.yaml

dependencies:
  flutter:
    sdk: flutter

  cupertino_icons: ^0.1.2
  path_provider: '>=0.3.0'
  sqflite: any

dev_dependencies:
  flutter_test:
    sdk: flutter

(b). employee.dart

class Employee {
  String firstName;
  String lastName;
  String mobileNo;
  String emailId;

  Employee(this.firstName, this.lastName, this.mobileNo, this.emailId);
  Employee.fromMap(Map map) {
    firstName = map[firstName];
    lastName = map[lastName];
    mobileNo = map[mobileNo];
    emailId = map[emailId];
  }
}

(c). dbhelper.dart

import 'dart:async';
import 'dart:io' as io;
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path_provider/path_provider.dart';
import 'package:flutter_crud/model/employee.dart';

class DBHelper {
  static Database _db;

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

  initDb() async {
    io.Directory documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentsDirectory.path, "test.db");
    var theDb = await openDatabase(path, version: 1, onCreate: _onCreate);
    return theDb;
  }

  void _onCreate(Database db, int version) async {
    // When creating the db, create the table
    await db.execute(
        "CREATE TABLE Employee(id INTEGER PRIMARY KEY, firstname TEXT, lastname TEXT, mobileno TEXT,emailId TEXT )");
    print("Created tables");
  }

  void saveEmployee(Employee employee) async {
    var dbClient = await db;
    await dbClient.transaction((txn) async {
      return await txn.rawInsert(
          'INSERT INTO Employee(firstname, lastname, mobileno, emailid ) VALUES(' +
              '\'' +
              employee.firstName +
              '\'' +
              ',' +
              '\'' +
              employee.lastName +
              '\'' +
              ',' +
              '\'' +
              employee.mobileNo +
              '\'' +
              ',' +
              '\'' +
              employee.emailId +
              '\'' +
              ')');
    });
  }

  Future<List<Employee>> getEmployees() async {
    var dbClient = await db;
    List<Map> list = await dbClient.rawQuery('SELECT * FROM Employee');
    List<Employee> employees = new List();
    for (int i = 0; i < list.length; i++) {
      employees.add(new Employee(list[i]["firstname"], list[i]["lastname"], list[i]["mobileno"], list[i]["emailid"]));
    }
    print(employees.length);
    return employees;
  }
}

(d). emloyeelist.dart

import 'package:flutter/material.dart';
import 'package:flutter_crud/model/employee.dart';
import 'dart:async';
import 'package:flutter_crud/database/dbhelper.dart';

Future<List<Employee>> fetchEmployeesFromDatabase() async {
  var dbHelper = DBHelper();
  Future<List<Employee>> employees = dbHelper.getEmployees();
  return employees;
}

class MyEmployeeList extends StatefulWidget {
  @override
  MyEmployeeListPageState createState() => new MyEmployeeListPageState();
}

class MyEmployeeListPageState extends State<MyEmployeeList> {
  @override
  Widget build(BuildContext context) {
    return new Scaffold(
      appBar: new AppBar(
        title: new Text('Employee List'),
      ),
      body: new Container(
        padding: new EdgeInsets.all(16.0),
        child: new FutureBuilder<List<Employee>>(
          future: fetchEmployeesFromDatabase(),
          builder: (context, snapshot) {
            if (snapshot.hasData) {
              return new ListView.builder(
                  itemCount: snapshot.data.length,
                  itemBuilder: (context, index) {
                    return new Column(
                        crossAxisAlignment: CrossAxisAlignment.start,
                        children: <Widget>[
                          new Text(snapshot.data[index].firstName,
                              style: new TextStyle(
                                  fontWeight: FontWeight.bold, fontSize: 18.0)),
                          new Text(snapshot.data[index].lastName,
                              style: new TextStyle(
                                  fontWeight: FontWeight.bold, fontSize: 14.0)),
                          new Divider()
                        ]);
                  });
            } else if (snapshot.hasError) {
              return new Text("${snapshot.error}");
            }
            return new Container(alignment: AlignmentDirectional.center,child: new CircularProgressIndicator(),);
          },
        ),
      ),
    );
  }
}

(e). main.dart

import 'package:flutter/material.dart';
import 'package:flutter_crud/database/dbhelper.dart';
import 'package:flutter_crud/model/employee.dart';
import 'package:flutter_crud/employeelist.dart';

void main() => runApp(new MyApp());

class MyApp extends StatelessWidget {
  // This widget is the root of your application.
  @override
  Widget build(BuildContext context) {
    return new MaterialApp(
      title: 'SQFLite DataBase Demo',
      theme: new ThemeData(
        primarySwatch: Colors.blue,
      ),
      home: new MyHomePage(title: 'Flutter Demo Home Page'),
    );
  }
}

class MyHomePage extends StatefulWidget {
  MyHomePage({Key key, this.title}) : super(key: key);
  final String title;

  @override
  _MyHomePageState createState() => new _MyHomePageState();
}

class _MyHomePageState extends State<MyHomePage> {

Employee employee = new Employee("", "", "", "");

String firstname; 
String lastname;
String emailId;
String mobileno;
final scaffoldKey = new GlobalKey<ScaffoldState>();
final formKey = new GlobalKey<FormState>();

    @override
  Widget build(BuildContext context) {
    return new Scaffold(
      key: scaffoldKey,
      appBar: new AppBar(
        title: new Text('Saving Employee'),
        actions: <Widget>[
            new IconButton(
              icon: const Icon(Icons.view_list),
              tooltip: 'Next choice',
              onPressed: () {
              navigateToEmployeeList();
              },
            ),
          ]
      ),
      body: new Padding(
        padding: const EdgeInsets.all(16.0),
        child: new Form(
          key: formKey,
          child: new Column(
            children: [
              new TextFormField(
                keyboardType: TextInputType.text,
                decoration: new InputDecoration(labelText: 'First Name'),
                validator: (val) =>
                    val.length == 0 ?"Enter FirstName" : null,
                onSaved: (val) => this.firstname = val,
              ),
              new TextFormField(
                keyboardType: TextInputType.text,
                decoration: new InputDecoration(labelText: 'Last Name'),
                validator: (val) =>
                    val.length ==0 ? 'Enter LastName' : null,
                onSaved: (val) => this.lastname = val,
              ),
              new TextFormField(
                keyboardType: TextInputType.phone,
                decoration: new InputDecoration(labelText: 'Mobile No'),
                validator: (val) =>
                    val.length ==0 ? 'Enter Mobile No' : null,
                onSaved: (val) => this.mobileno = val,
              ),
              new TextFormField(
                keyboardType: TextInputType.emailAddress,
                decoration: new InputDecoration(labelText: 'Email Id'),
                validator: (val) =>
                    val.length ==0 ? 'Enter Email Id' : null,
                onSaved: (val) => this.emailId = val,
              ),
              new Container(margin: const EdgeInsets.only(top: 10.0),child: new RaisedButton(onPressed: _submit,
                child: new Text('Save Employee'),),)

            ],
          ),
        ),
      ),
    );
  }
  void _submit() {
     if (this.formKey.currentState.validate()) {
      formKey.currentState.save(); 
     }else{
       return null;
     }
    var employee = Employee(firstname,lastname,mobileno,emailId);
    var dbHelper = DBHelper();
    dbHelper.saveEmployee(employee);
    _showSnackBar("Data saved successfully");
  }

  void _showSnackBar(String text) {
    scaffoldKey.currentState
        .showSnackBar(new SnackBar(content: new Text(text)));
  }

  void navigateToEmployeeList(){
     Navigator.push(
    context,
    new MaterialPageRoute(builder: (context) => new MyEmployeeList()),
  );
  }
}

NB/= More explanations will follow soon.

Download

Let's go over and download the project, or browse it from github.

No. Location Link
1. GitHub Direct Download
2. GitHub Browse

Credit and Thanks to the Original Creator of the above project @payen83

How to Run

  1. Download the project above.
  2. Create your application in android studio/visual studio as normal.
  3. Edit the pubspec.yaml to add the appopriate dependencies.
  4. Copy paste the dart files above into your project.

Then:

Just make sure you device or emulator is running and click the run button in android studio, it will automatically pick the device and install the app.

Aletrnative you can use the terminal or command prompt. Navigate/Cd over to project root page and type this:

flutter.bat build apk

This will build the APK which you can then drag and install onto your device. The build process in my machine takes around three minutes which is not bad.

How do You Feel after reading this?

According to scientists, we humans have 8 primary innate emotions: joy, acceptance, fear, surprise, sadness, disgust, anger, and anticipation. Feel free to tell us how you feel about this article using these emotes or via the comment section. This feedback helps us gauge our progress.

Help me Grow.

I set myself some growth ambitions I desire to achieve by this year's end regarding this website and my youtube channel. Am halfway. Help me reach them by:




Recommendations


What do You Think


Previous Post Next Post