Flutter Database | RRTutors

Flutter Database & Local Storage

In Android we have different ways to store data

  • SharedPreferences

  • Local database

  • Files

 

Similarly in Flutter also we can handle the data by above ways

 

SharedPreferences

Shared Preferences allow us to save and retrieve data in the form of key,value pair

 

How to access Shared Preferences in Flutter?

In Flutter, we can access this feature by using the plugin Shared_Preferences

 

Example

 

class MySharedPref extends StatefulWidget{

 @override

 State<StatefulWidget> createState() {

   // TODO: implement createState

   return MySharedPrefState();

 }

 

}

 

class MySharedPrefState extends State<MySharedPref>{

 @override

 Widget build(BuildContext context) {

   // TODO: implement build

   return Scaffold(

     appBar: AppBar(title: Text("SharedPreference"),backgroundColor: Colors.pink,),

     floatingActionButton: FloatingActionButton(

       backgroundColor: Colors.pink,

         child: Icon(Icons.add),

         onPressed: (){

       _incrementCounter();

     }),

     body: Center(

       child:  FutureBuilder(

         future: _getIncrementCounter(),

         builder:(BuildContext context, AsyncSnapshot snapshot) {

           return Column(

             mainAxisSize: MainAxisSize.min,

             children: <Widget>[

               Text("The Incremented Value is ",style: TextStyle(color: Colors.pink,fontSize: 20),),

               SizedBox(height: 20,),

               Text((snapshot.data==null)?"":snapshot.data.toString(),style: TextStyle(color: Colors.pink,fontSize: 20),),

             ],

           );

         },

       ),

     ),

   );

 }

 

 _incrementCounter() async {

   SharedPreferences prefs = await SharedPreferences.getInstance();

   int counter = (prefs.getInt('counter') ?? 0) + 1;

   setState(() {

     prefs.setInt('counter', counter);

   });

 

 }

 

 Future<int>_getIncrementCounter() async {

 

   SharedPreferences prefs = await SharedPreferences.getInstance();

   int counter = (prefs.getInt('counter') ?? 0) + 1;

   return counter;

 }

}

 

SharedPreference



 

Database

In Android, we can use SQLite to store structured data that is queried via SQL.

How to access SQLite in Flutter?

 

In Flutter, we can use the SQFlite plugin to access this feature of SQFlite

 

SQFlite Features

  • Supports transactions and batch operations

  • Automatic version management during program opening

  • Addition, deletion, change, and help program

  • Perform DB operations in iOS and Android background threads

 

SQFlite does not perform a type check on the value, that is, a column of type INTEGER can store TEXT, but when we parse the result of the query and map it, it will report a type exception. So still avoid storing inconsistent data

 

SQFlite supports 5 data types: NULL , INTEGER , REAL , TEXT , BLOB

 

NULL

When a column does not store data, the default value is NULL.

 

INTEGER

Int type in dart, the value range is -2 ^ 63 to 2 ^ 63-1

 

REAL

num type in dart, namely int and double types

 

TEXT

String type in dart

BLOB

The Uint8List type in dart, although it can store List <int>, is not officially recommended because the conversion is slow

 

bool

Stores INTEGER type, 0 is false, 1 is true

 

If we need to store other types of data, such as bool, DateTime, List <String>, etc., we need to handle it by ourselves. Everyone may have their own unique method. I hope you can make some suggestions. We can encapsulate entity classes and parsing classes. From the perspective of external code, these types of storage, such as bool, DateTime, and List <String>, are implemented

 

DateTime

Store INTEGER type, the creation time and update time of a column of data is generally more important. Of course, there are other information, such as the payment time, delivery time, and cancellation time of an order. If the TEXT type is stored, the program is inconvenient if it supports multiple languages.

 

List

To store TEXT type, we can combine the data into String and store it in the database according to the special separator. It is then parsed into a List <String> according to the split of the String. There are still many things to note, such as the elements of List must not contain the defined delimiters. It is troublesome to modify a certain Item of List, and it can only cover List as a whole.

 

Map, json, entity classes

Store the TEXT type. Generally, I use the toMap method of the entity class to convert the entity class into a Map. The entity class is converted to a String through jsonEncode. In turn, the string is converted to a Map using jsonDecode, and the entity class is converted from the Map to the entity class


 

Database operations

 

Database creation

Open the database based on the name and version number

 

createDB(VERSION) async {

 String databasesPath = await getDatabasesPath();

 // Database Path: /data/user/0/com.package.name/databases

 String path = join(databasesPath, 'db_name.db');

 // Path: /data/user/0/com.package.name/databases/db_name.db

 Database database = await openDatabase(

   path,

   version: VERSION,

   onCreate: (Database db, int version) async {

 

   },

   onUpgrade: (Database db, int oldVersion, int newVersion) async {

 

   },

 );

}


 

Create Table

The Create Table query should be inside onCreate() method

 

"CREATE TABLE users(id INTEGER PRIMARY KEY autoincrement, name TEXT, email TEXT, password TEXT, mobile TEXT)";


 

Delete Table

 

db.execute('DROP table users');

 

Clear Table

db.execute('DELETE FROM users');

 

Rename Table

db.execute('ALTER TABLE users RENAME TO users_1');

 

Add Field

db.execute('ALTER TABLE users ADD gender TEXT');

 

Delete Field

db.execute('ALTER TABLE users DROP COLUMN gender');

 

Modify Field Type

db.execute('ALTER TABLE users ALTER COLUMN value integer');


 

Insert

Insert will returns the last inserted record id

int id = await database.rawInsert('INSERT INTO user(name, email, password, mobile) VALUES("shiva", "test@tes.com", "1234#","1234567899")');

 

Delete

returns the number of records affected

 

int count = await database.rawDelete('DELETE FROM user WHERE email = ?', ['test@test.com']);


 

Update

int count = await database.update( 'user',

   {'name': 'Name 2'}

   where: 'email = ?',

   whereArgs: ['test@test.com']

 

Query

Query is the most complicated one in SQL statements. The keywords include distinct, where, group by, having, count, order by asc / desc, limit, offset, in, join, as, union and so on

 

List<Map<String, dynamic>> result = await database.query(

 'user'?

 distinct: true,

 columns: ['name','mobile'],

 where: 'email = ?',

 whereArgs: ['test@test.com'],

 groupBy: 'name',

 limit: 5,

 offset: 2,

);

 

List<Map<String, dynamic>> result = await database.rawQuery(

 'SELECT * FROM user WHERE email=test@test.com  order by name asc limit 5 offset 2',

 [16],

);

 

Example

Add Plugins

Add below plugins to pubspec.yaml file

sqflite:

path:

path_provider: ^0.4.1

 

Create User Model class

class User{

  int id;

  String name;

  String email;

  String pasword;

  String mobile;

  User(this.name,this.email,this.pasword,this.mobile);

  Map<String,dynamic>toUserMap(){

    return {

      'name':name,

      'email':email,

      'password':pasword,

      'mobile':mobile,

 

    };

  }

 

 static  fromMap(Map<String, dynamic> c) {

 

    return User(c['name'],c['email'],c['passowrd'],c['mobile']);

 }

 

}

 

Create Database

 

import 'dart:io';

 

import 'package:flutter_firebase_app/models/user.dart';

import 'package:path/path.dart';

import 'package:sqflite/sqflite.dart';

 

import 'package:path_provider/path_provider.dart';

 

class UserDatabase{

 static String path;

 static final _databaseName = "mydb.db";

 static final _databaseVersion = 1;

 

 static final _table_user = 'users';

 static final _table_logins = 'logins';

 

 UserDatabase._privateConstructor();

 static final UserDatabase instance = UserDatabase._privateConstructor();

 

 // only have a single app-wide reference to the database

 static Database _database;

 

 Future<Database> get database async {

   if (_database != null) return _database;

   // lazily instantiate the db the first time it is accessed

   _database = await _initDatabase();

   return _database;

 }

 

 // this opens the database (and creates it if it doesn't exist)

 _initDatabase() async {

   Directory documentsDirectory = await getApplicationDocumentsDirectory();

   String path = join(documentsDirectory.path, _databaseName);

   return await openDatabase(path,

       version: _databaseVersion,

       onCreate: _onCreate);

 }

 

 // SQL code to create the database table

 Future _onCreate(Database db, int version) async {

  await db.execute(

     "CREATE TABLE users(id INTEGER PRIMARY KEY autoincrement, name TEXT, email TEXT, password TEXT, mobile TEXT)",

   );

  await db.execute(

     "CREATE TABLE logins(name TEXT, email TEXT, mobile TEXT,password TEXT)",

   );

 }




 

  static Future<String> getFileData() async {

    return getDatabasesPath().then((s){

      return path=s;

    });

  }

 

  Future<int> insertUser(User user) async{

    Database db = await instance.database;

 

  var users=await  db.rawQuery("select * from users where mobile = "+user.mobile);

    if(users.length>0)

      {

        return -1;

      }

      return  await db.insert("users",user.toUserMap(),conflictAlgorithm: ConflictAlgorithm.ignore

    );

  }

 

  Future<User> checkUserLogin(String mobile, String password) async

  {

    Database db = await instance.database;

    var res=await  db.rawQuery("select * from users where mobile = '$mobile' and password = '$password'");

    if(res.length>0)

      {

        List<dynamic> list =

        res.toList().map((c) => User.fromMap(c)).toList() ;

 

        print("Data "+list.toString());

        await  db.insert("logins",list[0].toUserMap());

        return list[0];

      }

      return null;

  }

 

  Future<int> getUser() async{

    Database db = await instance.database;

   var logins=await  db.rawQuery("select * from logins");

   if(logins==null)

     return 0;

   return logins.length;

 

  }

 

 Future<User> getUserData() async{

   Database db = await instance.database;

   var res=await  db.rawQuery("select * from logins");

   print("result user data $res");

   print("result user data "+res.toString());

   List<dynamic> list =

   res.toList().map((c) => User.fromMap(c)).toList() ;

   return list[0];

 

 }

 

 Future<int> deleteUser(String mobile) async{

   Database db = await instance.database;

  var logins= db.delete(_table_logins, where: "mobile = ?", whereArgs: [mobile]);

     return logins;

 

 }

}

 

Signup Page

 

import 'package:flutter/material.dart';

import 'package:flutter_firebase_app/databases/UserDatabase.dart';

import 'package:flutter_firebase_app/models/user.dart';

 

class SignupPage extends StatefulWidget{

 @override

 State<StatefulWidget> createState() {

   // TODO: implement createState

   return SignUpState();

 }

 

}

 

class SignUpState extends State<SignupPage>{

 final _formKey = GlobalKey<FormState>();

 final _scafoldKey = GlobalKey<ScaffoldState>();

 final _nameEditController=TextEditingController();

 final _emailEditController=TextEditingController();

 final _mobileEditController=TextEditingController();

 final _passwordEditController=TextEditingController();

  String email_pattern = r'^(([^<>()[\]\\.,;:\s@\"]+(\.[^<>()[\]\\.,;:\s@\"]+)*)|(\".+\"))@(([09]1,3\.[09]1,3\.[09]1,3\.[09]1,3)|(([a-zA-Z\-0-9]+\.)+[a-zA-Z]{2,}))$';

  String password_pattern = r'^[a-zA-Z0-9]{6,}$';

  String mobile_pattern = r'^?([09]3)?[-. ]?([0-9]{3})[-. ]?([0-9]{4})$';

Size size;

 

 @override

 Widget build(BuildContext context) {

   size=MediaQuery.of(context).size;

   return new Scaffold(

     key: _scafoldKey,

     body: Stack(

       children:<Widget>[

     Image.asset("splash_img.png",fit: BoxFit.cover, width: size.width,height: size.height,),

     Container(color: const Color(0x99FFFFFF),),

     Container(

       height: 120,

       decoration: new BoxDecoration(

         border:  Border.all(color: Colors.teal),

         borderRadius: BorderRadius.only(bottomLeft: Radius.circular(size.width/2),topRight: Radius.circular(size.width/2)),

         color: Colors.teal,

 

       ),

     ),

 

     Center(

       child: SingleChildScrollView(

         child: Padding(

            padding: EdgeInsets.only(left: 20,right: 20),

           child: Form(

               key: _formKey,

               child:

               Column(

                 mainAxisSize: MainAxisSize.max,

                 mainAxisAlignment: MainAxisAlignment.center,

                 children: <Widget>[

                   SizedBox(height: 20,),

                   Container(

                     decoration: new BoxDecoration(

                         border: new Border.all(color: Colors.teal),

                       borderRadius: BorderRadius.circular(10),

                       color: Colors.teal,

 

                     ),

 

                     child: Padding(

                       padding: const EdgeInsets.all(8.0),

                       child: Text("Registration Form",style: TextStyle(color: Colors.white,

                         fontSize: 22

                       ),),

                     ),

                   ),

                   SizedBox(height: 40,),

                 //--------------Name FormFiled------------------------------------------

                   TextFormField(

                     controller: _nameEditController,

                     textInputAction: TextInputAction.next,

 

                     validator: (value){

                       if(value.isEmpty)

                       {

                         return "Enter Name";

                       }

                       return null;

                     },

                     style: getTextStyle(),

                     decoration: customInputDecoration("Enter Name"),

                   ),

                   SizedBox(height: 20,),

                   //--------------Email FormFiled------------------------------------------

                   TextFormField(

                     controller: _emailEditController,

                     textInputAction: TextInputAction.next,

                     validator: (value){

                       RegExp regex =RegExp(email_pattern);

                       if (!regex.hasMatch(value))

                         return 'Enter Valid Email';

                       else

                         return null;

                     },

                     keyboardType: TextInputType.emailAddress,

                     style: getTextStyle(),

                     decoration: customInputDecoration("Enter email id"),

                   ),

                   SizedBox(height: 20,),

 

                   //--------------Mobile FormFiled------------------------------------------

                   TextFormField(

                     controller: _mobileEditController,

                     textInputAction: TextInputAction.next,

                     validator: (value){

                       RegExp regex =RegExp(mobile_pattern);

                       if (!regex.hasMatch(value))

                         return 'Enter valid mobile number';

                       else

                         return null;

                       return null;

                     },

                     keyboardType: TextInputType.number,

                     maxLength: 10,

                     style: getTextStyle(),

                     decoration: customInputDecoration("Enter mobile number"),

                   ),

                   SizedBox(height: 20,),

                   //--------------Password FormFiled------------------------------------------

                   TextFormField(

                     controller: _passwordEditController,

                     textInputAction: TextInputAction.done,

                     validator: (value){

                       RegExp regex =RegExp(password_pattern);

                       if (!regex.hasMatch(value))

                         return 'Password should be in alphanumaric with 6 characters';

                       else

                         return null;

                     },

                     obscureText: true,

                     style: getTextStyle(),

                     decoration: customInputDecoration("Enter password"),

                   ),

 

                   SizedBox(height: 20,),

                   RaisedButton(onPressed: (){

 

                     if(_formKey.currentState.validate())

                     {

                        UserDatabase.instance.insertUser(User(_nameEditController.text,_emailEditController.text,_passwordEditController.text,_mobileEditController.text)).then((result){

                         if(result==-1)

                           {

                             _scafoldKey.currentState

                                 .showSnackBar(SnackBar(content: Text('User with same number already existed $result')));

                           }else

                             {

                               _scafoldKey.currentState

                                   .showSnackBar(SnackBar(content: Text('User Registered Succesfully $result')));

                               Navigator.pushReplacementNamed(context, "/login");

                             }

 

                        }) ;

 

                     }

 

                   }, shape: RoundedRectangleBorder(

                     borderRadius: BorderRadius.circular(18),

                   ),

                     color: Colors.pink,

                     child: Text("Signup", style: TextStyle(color: Colors.white,fontSize: 20),),

                   ),

 

                   FlatButton(

                     child: Text("Already have account, Sign In?"),

                     onPressed: (){

 

                       Navigator.pushReplacementNamed(context, "/login");

                     },

                   )

                 ],

               )

           ),

         ),

       ),

     )

     ],

     ),

   );;

 }

 

 TextStyle getTextStyle(){

   return TextStyle(

       fontSize: 18,

       color: Colors.pink

       );

 }

 

 InputDecoration customInputDecoration(String hint)

 {

 

   return InputDecoration(

     hintText: hint,

     hintStyle: TextStyle(

         color: Colors.teal

     ),

     contentPadding: EdgeInsets.all(10),

     enabledBorder: OutlineInputBorder(

         borderRadius: BorderRadius.circular(12),

         borderSide: BorderSide(

             color: Colors.pink

         )

     ),

     focusedBorder: OutlineInputBorder(

         borderRadius: BorderRadius.circular(8),

         borderSide: BorderSide(

             color: Colors.pink

         )

     ),

 

   );

 }

 

}


 

Login Page

import 'package:flutter/material.dart';

import 'package:flutter_firebase_app/databases/UserDatabase.dart';

 

class LoginPage extends StatefulWidget{

 @override

 State<StatefulWidget> createState() {

   // TODO: implement createState

   return LoginState();

 }

 

}

class LoginState extends State<LoginPage>

{

 final _formKey = GlobalKey<FormState>();

 final _scaffoldKey = GlobalKey<ScaffoldState>();

 final _mobileController=TextEditingController();

 final _passwordController=TextEditingController();

 final FocusNode _mobileFocus = FocusNode();

 final FocusNode _passwordFocus = FocusNode();

 Size size;

 @override

 Widget build(BuildContext context) {

   size = MediaQuery.of(context).size;

   return new Scaffold(

     key: _scaffoldKey,

     body: Stack(

       children:<Widget>[

         Image.asset("splash_img.png",fit: BoxFit.cover,

         width: size.width,

         height: size.height,

         ),

         Padding(

           padding: EdgeInsets.only(left: 20,right: 20),

           child: Form(

               key: _formKey,

               child:

               Column(

                 mainAxisAlignment: MainAxisAlignment.center,

                 children: <Widget>[

                   SizedBox(height: 20,),

 

                   TextFormField(

                     controller: _mobileController,

                     keyboardType: TextInputType.number,

                     textInputAction: TextInputAction.next,

 

                     focusNode: _mobileFocus,

                     onFieldSubmitted: (term){

                       FocusScope.of(context).requestFocus(_passwordFocus);

                     },

                     validator: (value){

                       if(value.isEmpty)

                       {

                         return "Enter mobile number";

                       }

                       return null;

                     },

                     style: getTextStyle(),

                     decoration: customInputDecoration("Enter mobile number"),

                   ),

                   SizedBox(height: 20,),

                   TextFormField(

                     textInputAction: TextInputAction.done,

                     controller: _passwordController,

                     keyboardType: TextInputType.text,

 

                     obscureText: true,

                     focusNode: _passwordFocus,

                     validator: (value){

                       if(value.isEmpty)

                       {

                         return "Enter Password";

                       }

                       return null;

                     },

                     style: getTextStyle(),

                     decoration: customInputDecoration("Enter password"),

                   ),

                   SizedBox(height: 20,),

                   RaisedButton(onPressed: (){

 

                     if(_formKey.currentState.validate())

                     {

 

                       UserDatabase.instance.checkUserLogin(_mobileController.text,_passwordController.text).then((result){

 

                         if(result==null)

                         {

                           _scaffoldKey.currentState.showSnackBar(SnackBar(content: Text("Please enter valid details")));

                         }

                         else

                         {

                           Navigator.pushReplacementNamed(context, "/home");

                         }

                       });

                     }

 

                   }, shape: RoundedRectangleBorder(

                     borderRadius: BorderRadius.circular(18),

                   ),

                     color: Colors.pink,

                     child: Text("Login", style: TextStyle(color: Colors.white,fontSize: 20),),

                   ),

 

                   FlatButton(

                     child: Text("Don't have account, Signup?"),

                     onPressed: (){

                       Navigator.pushReplacementNamed(context, "/signup");

                     },

                   )

                 ],

               )

           ),

         )

       ] ,

     ),

   );

 }

 TextStyle getTextStyle(){

   return TextStyle(

       fontSize: 18,

       color: Colors.pink

   );

 }

 

 InputDecoration customInputDecoration(String hint)

 {

   return InputDecoration(

     hintText: hint,

     hintStyle: TextStyle(

         color: Colors.teal

     ),

     contentPadding: EdgeInsets.all(10),

     enabledBorder: OutlineInputBorder(

         borderRadius: BorderRadius.circular(12),

         borderSide: BorderSide(

             color: Colors.pink

         )

     ),

     focusedBorder: OutlineInputBorder(

         borderRadius: BorderRadius.circular(8),

         borderSide: BorderSide(

             color: Colors.pink

         )

     ),

 

   );

 }

}

Home Page

 

import 'package:flutter/material.dart';

import 'package:flutter_firebase_app/databases/UserDatabase.dart';

import 'package:flutter_firebase_app/models/user.dart';

 

class Homepage extends StatefulWidget{

 @override

 State<StatefulWidget> createState() {

   // TODO: implement createState

 

   return HomeState();

 }

 

}

 

class HomeState extends State<Homepage>{

 Size size;

 User user;

 @override

 void initState() {

   // TODO: implement initState

   super.initState();

   UserDatabase.instance.getUserData().then((result){

     setState(() {

       user=result;

     });

 

   });

 }

 @override

 Widget build(BuildContext context) {

   size=MediaQuery.of(context).size;

   return Scaffold(

     appBar: AppBar(

       title: Text("Home"),

     ),

     body: Column(

       mainAxisSize: MainAxisSize.max,

       children: <Widget>[

 

         Row(

 

           mainAxisAlignment: MainAxisAlignment.end,

 

           children: <Widget>[

                Padding(

                  padding: const EdgeInsets.all(12.0),

                  child: RaisedButton(

                    onPressed: (){

 

                      UserDatabase.instance.deleteUser(user.mobile).then((res){

                        if(res==1)

                          {

                            Navigator.pushReplacementNamed(context, "/login");

                          }

 

                      });

                    },

                     shape: RoundedRectangleBorder(

                       borderRadius: BorderRadius.circular(15),

 

                     ),

                       color:Colors.pink,

                      child: Text("Logout", style: TextStyle(color: Colors.white

                      ),)

                   ),

                )

           ],

         ),

         Container(

 

           height:size.height-200 ,

           child: Center(

             child: (user==null)?null:Text("Welcome User "+user.name),

           ),

         ),

       ],

     )

   );

 }

 

}

 

Check sample at https://rrtutors.com/tutorials/Flutter-SQFLite-Database-Tutorial


 

Advertisements