SQLite Database CRUD Operation in Ionic 4

-
Student Management Application:  SQLite Database CRUD Operation in Ionic 4

Let's start with this project.

Install node.js in your system
Go to link - https://nodejs.org/en/ and install the latest version of ndoe.js
After installing,
   Check npm is working properly using the Command Prompt -

   C:\Users\admin>npm -version
   6.10.2


Install Ionic in your system
Open Command Prompt and run below command.

  npm install -g ionic

Run below command before going ahead.

  ionic start sqlLiteExample sidemenu
  cd sqlLiteExample
  ionic g service services/database
  ionic g page pages/students
  ionic g page pages/student
  npm install @ionic-native/sqlite @ionic-native/sqlite-porter
  ionic cordova plugin add cordova-sqlite-storage
  ionic cordova plugin add uk.co.workingedge.cordova.plugin.sqliteporter

You need a SQL script file to create a database and table.
Open your application in file explorer and create studentScript.sql in assets directory as below.
  sqlLiteExample\src\assets\studentScript.sql

Update sqlLiteExample\src\assets\studentScript.sql file with below details:
 CREATE TABLE IF NOT EXISTS Students(studId INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,class TEXT,mark TEXT);
 INSERT or IGNORE INTO Students VALUES (1, 'Paresh', 'X', '550');
 INSERT or IGNORE INTO Students VALUES (2, 'Rahul', 'XI', '650');
 INSERT or IGNORE INTO Students VALUES (3, 'Ramesh', 'XII', '525');


Update the sqlLiteExample\src\app\app.module.ts file with the below entries.

 import { NgModule } from '@angular/core';
 import { BrowserModule } from '@angular/platform-browser';
 import { RouteReuseStrategy } from '@angular/router';
 import { IonicModule, IonicRouteStrategy } from '@ionic/angular';
 import { SplashScreen } from '@ionic-native/splash-screen/ngx';
 import { StatusBar } from '@ionic-native/status-bar/ngx';
 import { AppComponent } from './app.component';
 import { AppRoutingModule } from './app-routing.module';
 import { SQLitePorter } from '@ionic-native/sqlite-porter/ngx';
 import { SQLite } from '@ionic-native/sqlite/ngx';
 import { HttpClientModule } from '@angular/common/http';
 @NgModule({
   declarations: [AppComponent],
   entryComponents: [],
   imports: [
     BrowserModule,
     IonicModule.forRoot(),
     AppRoutingModule,
     HttpClientModule
   ],
   providers: [
     StatusBar,
     SplashScreen,
     SQLitePorter,
     SQLite,
     { provide: RouteReuseStrategy, useClass: IonicRouteStrategy }
   ],
   bootstrap: [AppComponent]
 })
 export class AppModule {}


Update sqlLiteExample\src\app\app.component.ts as below.

 import { Component } from '@angular/core';
 import { Platform } from '@ionic/angular';
 import { SplashScreen } from '@ionic-native/splash-screen/ngx';
 import { StatusBar } from '@ionic-native/status-bar/ngx';

 @Component({
   selector: 'app-root',
   templateUrl: 'app.component.html',
   styleUrls: ['app.component.scss']
 })
 export class AppComponent {
   public appPages = [
     {
       title: 'Home',
       url: '/home',
       icon: 'home'
     },
     {
       title: 'Students',
       url: '/students',
       icon: 'people'
     }
   ];

   constructor(
     private platform: Platform,
     private splashScreen: SplashScreen,
     private statusBar: StatusBar
   ) {
     this.initializeApp();
   }

   initializeApp() {
     this.platform.ready().then(() => {
       this.statusBar.styleDefault();
       this.splashScreen.hide();
     });
   }
 }


Update sqlLiteExample\src\app\app-routing.module.ts as below.

 import { NgModule } from '@angular/core';
 import { PreloadAllModules, RouterModule, Routes } from '@angular/router';

 const routes: Routes = [
   {
     path: '',
     redirectTo: 'home',
     pathMatch: 'full'
   },
   {
     path: 'home',
     loadChildren: () => import('./home/home.module').then(m => m.HomePageModule)
   },
   {
     path: 'students',
     loadChildren: () => import('./pages/students/students.module').then( m => m.StudentsPageModule)
   },
   {
     path: 'students/:id',
     loadChildren: () => import('./pages/student/student.module').then( m => m.StudentPageModule)
   }
 ];

 @NgModule({
   imports: [
     RouterModule.forRoot(routes, { preloadingStrategy: PreloadAllModules })
   ],
   exports: [RouterModule]
 })
 export class AppRoutingModule {}


Update sqlLiteExample\src\app\services\database.service.ts as below.

 import { Platform } from '@ionic/angular';
 import { Injectable } from '@angular/core';
 import { SQLitePorter } from '@ionic-native/sqlite-porter/ngx';
 import { HttpClient } from '@angular/common/http';
 import { SQLite, SQLiteObject } from '@ionic-native/sqlite/ngx';
 import { BehaviorSubject, Observable } from 'rxjs';

 export interface Student {
   studId: number;
   name: string;
   class: string;
   mark: string;
 }

 @Injectable({
   providedIn: 'root'
 })
 export class DatabaseService {
   private database: SQLiteObject;
   private dbReady: BehaviorSubject<boolean> = new BehaviorSubject(false);

   students = new BehaviorSubject([]);

   constructor(private plt: Platform, private sqlitePorter: SQLitePorter, private sqlite: SQLite, private http: HttpClient) {
     this.plt.ready().then(() => {
       this.sqlite.create({
         name: 'studentsDatabase.db',
         location: 'default'
       })
       .then((db: SQLiteObject) => {
           this.database = db;
           this.seedDatabase();
       });
     });
   }

   seedDatabase() {
     this.http.get('assets/studentScript.sql', { responseType: 'text'})
     .subscribe(sql => {
       this.sqlitePorter.importSqlToDb(this.database, sql)
         .then(_ => {
           this.loadStudents();
           this.dbReady.next(true);
         })
         .catch(e => console.error(e));
     });
   }

   getDatabaseState() {
     return this.dbReady.asObservable();
   }

   getStudents(): Observable<Student[]> {
     return this.students.asObservable();
   }
   loadStudents() {
     return this.database.executeSql('SELECT * FROM Students', []).then(data => {
       let students: Student[] = [];

       if (data.rows.length > 0) {
         for (let i = 0; i < data.rows.length; i++) {
           students.push({
             studId: data.rows.item(i).studId,
             name: data.rows.item(i).name,
             class: data.rows.item(i).class,
             mark: data.rows.item(i).mark
            });
         }
       }
       this.students.next(students);
     });
   }
   addStudentData(studName, studClass, studMark) {
     let data = [studName, studClass, studMark];
     return this.database.executeSql('INSERT INTO Students (name, class, mark) VALUES (?, ?, ?)', data).then(data => {
       this.loadStudents();
     });
   }
   getStudentById(id): Promise<Student> {
     return this.database.executeSql('SELECT * FROM Students WHERE studId = ?', [id]).then(data => {
       return {
         studId: data.rows.item(0).studId,
         name: data.rows.item(0).name,
         class: data.rows.item(0).class,
         mark: data.rows.item(0).mark
       };
     });
   }
   updateStudent(student: Student) {
     let data = [student.name, student.class, student.mark];
     return this.database.executeSql(`UPDATE Students SET name = ?, class = ?, mark = ? WHERE studId = ${student.studId}`, data).then(data => {
       this.loadStudents();
     });
   }
   deleteStudent(studId) {
     console.log('Inside Deleting DB Student Id '+ studId);
     return this.database.executeSql('DELETE FROM Students WHERE studId = ?', [studId]).then(_ => {
       this.loadStudents();
     });
   }
 }


Update sqlLiteExample\src\app\pages\students\students.page.ts as below.

 import { DatabaseService, Student } from './../../services/database.service';
 import { Component, OnInit } from '@angular/core';

 @Component({
   selector: 'app-students',
   templateUrl: './students.page.html',
   styleUrls: ['./students.page.scss'],
 })
 export class StudentsPage implements OnInit {

   constructor(private db: DatabaseService) { }

   studentData = {};
   students: Student[] = [];
   ngOnInit() {
     this.db.getDatabaseState().subscribe(rdy => {
       if (rdy) {
         this.db.getStudents().subscribe(studs => {
           this.students = studs;
           console.log(this.students);
         });
       }
     });
   }
   addStudentDetails() {
     this.db.addStudentData(this.studentData['name'], this.studentData['class'], this.studentData['mark']).then(_ => {
       this.studentData = {};
     });
   }
  }


Update sqlLiteExample\src\app\pages\students\students.page.html as below.

 <ion-header>
   <ion-toolbar color = "primary">
   <ion-buttons slot="start">
     <ion-menu-button></ion-menu-button>
   </ion-buttons>
   <ion-title>
     Students Details
   </ion-title>
 </ion-toolbar>
 </ion-header>

 <ion-content class="ion-padding">
       <ion-item>
         <ion-label position="stacked">Student Name </ion-label>
         <ion-input [(ngModel)]="studentData.name" placeholder="Name"></ion-input>
       </ion-item>
       <ion-item>
         <ion-label position="stacked">Class </ion-label>
         <ion-input [(ngModel)]="studentData.class" placeholder="Student Class"></ion-input>
       </ion-item>
       <ion-item>
         <ion-label position="stacked">Obtained Marks</ion-label>
         <ion-input [(ngModel)]="studentData.mark" placeholder="Mark"></ion-input>
       </ion-item>

       <ion-button expand="block" (click)="addStudentDetails()">Add Student</ion-button>

   <ion-list>
     <ion-item button *ngFor="let student of students" [routerLink]="['/', 'students', student.studId]">
       <ion-label>
         <h2>Student Name :: {{ student.name }}</h2>
         <p>Student Class :: {{ student.class }}</p>
         <p>Student Marks :: {{ student.mark }}</p>
       </ion-label>
     </ion-item>
   </ion-list>
 </ion-content>


Update sqlLiteExample\src\app\pages\student\student.page.ts as below.

 import { Component, OnInit } from '@angular/core';
 import { ActivatedRoute, Router } from '@angular/router';
 import { DatabaseService, Student } from 'src/app/services/database.service';
 import { ToastController } from '@ionic/angular';

 @Component({
   selector: 'app-student',
   templateUrl: './student.page.html',
   styleUrls: ['./student.page.scss'],
 })
 export class StudentPage implements OnInit {
   student: Student = null;
   constructor(private router: Router, private route: ActivatedRoute, private db: DatabaseService, private toast: ToastController) { }
   ngOnInit() {
     this.route.paramMap.subscribe(params => {
       let studId = params.get('id');

       this.db.getStudentById(studId).then(data => {
         this.student = data;
       });
     });
   }

   updateStudentData() {
     this.db.updateStudent(this.student).then(async (res) => {
       let toast = await this.toast.create({
         message: 'Student Details Updated Successfully..',
         duration: 3000
       });
       toast.present();
     }).then(() => this.router.navigateByUrl('students'));
   }
   delete() {
     console.log('Deleting Student Id '+this.student.studId);
     this.db.deleteStudent(this.student.studId).then(() => {
       this.router.navigateByUrl('students');
     });
   }
 }


Update sqlLiteExample\src\app\pages\student\student.page.html as below

 <ion-header>
   <ion-toolbar color="primary">
     <ion-buttons slot="start">
       <ion-menu-button></ion-menu-button>
     </ion-buttons>
     <ion-title>Student</ion-title>
     <ion-buttons slot="end">
       <ion-button (click)="delete()">
         <ion-icon name="trash"></ion-icon>
       </ion-button>
     </ion-buttons>
   </ion-toolbar>
 </ion-header>

 <ion-content class="ion-padding">
   <div *ngIf="student">
     <ion-item>
       <ion-label position="stacked">Student Name </ion-label>
       <ion-input [(ngModel)]="student.name" placeholder="Name"></ion-input>
     </ion-item>
     <ion-item>
       <ion-label position="stacked">Class </ion-label>
       <ion-input [(ngModel)]="student.class" placeholder="Student Class"></ion-input>
     </ion-item>
     <ion-item>
       <ion-label position="stacked">Obtained Marks</ion-label>
       <ion-input [(ngModel)]="student.mark" placeholder="Mark"></ion-input>
     </ion-item>

     <ion-button expand="block" (click)="updateStudentData()">Update Student</ion-button>
    </div>
 </ion-content>


After all this code you need to add the Android platform in your application.
 ionic cordova platform add android

For creating apk we need Android SDK Manager needs to installed in your system.
After checking the Android SDK run below command.
 ionic cordova build android


Please let me know in case of any issue.
If you have any doubt then please put in the comment section, I will definitely try to resolve it.


Youtube link for your reference :



Comments

  1. SabirSeptember 16, 2020 at 11:54 PM

    Hy, this is a lovely project exactly what i need to get started with ionic. I try your code exactly but i ran into some issues inside app-routing.module.ts:

    1. Cannot find module './home/home.module' at

    {
    path: 'home',
    loadChildren: () => import('./home/home.module').then(m => m.HomePageModule)
    },

    2. Property 'StudentsPageModule' does not exist at

    {
    path: 'students',
    loadChildren: () => import('./pages/students/students.module').then( m => m.StudentsPageModule)
    },

    It will be a pleasure to get a solution to that. Thank you.

    ReplyDelete
  2. slashcodeSeptember 17, 2020 at 1:41 AM

    Please check the video link as well for the same.

    ReplyDelete
    Replies
    1. slashcodeSeptember 17, 2020 at 1:43 AM

      Please let me know in-case you are not getting expected output for the same.

      Delete
    2. SabirSeptember 17, 2020 at 11:42 PM

      I used the video but the error is still there. May be it is because my version (V6) is different from the one you used from this tutorial (V4) and I really want to get it working. Thanks!

      Delete
  3. slashcodeSeptember 20, 2020 at 9:32 PM

    Please let me know in-case you need any help from my end.

    ReplyDelete
  4. el robeOctober 23, 2020 at 11:57 AM

    Hello, if you could tell me how I compile the app in the chrome browser, it gives me an error with the create () method of sqlite, thanks

    ReplyDelete

Post a Comment

Popular posts from this blog

How to read XLS and XLSX Excel files in Java

-
Image
Sometimes we get requirements like to load XLS or XLSX files in Java Project. Where need to convert each column and row values in Java Objects using POJO class. To make this project we need a POJO class of columns present in the CSV file. In this tutorial, we are going to use the Customer details XLS and XLSX file as below. Here we have a Customer ID, Name, City, Pincode, and State Code. We may have more than that data and might be there are multiple fields that are blank. So ID-103 having Customer Name - Geetha having blank City name, same with others as well for other fields. We are going to handle these scenarios as well using the try-catch block. In the beginning, we need to add some dependencies in pom.xml for XLS and XLSX files. <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd&q
Read more

How to Read CSV File in Java

-
Image
Sometimes we get requirements like to load CSV files in Java Project. Where need to convert each column and row values in Java Objects using POJO class. The CSV stands for Comma-Separated Values, this is a very simple file where each element are separated by comma(,). CSV can be directly open in any text editor or in Microsoft Excel. To make this project we need a POJO class of columns present in the CSV file. In this tutorial, we are going to use the Customer details CSV file as below. Here we have a Customer ID, Name, City, Pincode, and State Code. We may have more than that data and might be there are multiple fields that are blank. So ID-103 having Customer Name - Geetha having blank City name, same with others as well for other fields. We are going to handle these scenarios as well using the try-catch block. In the beginning, we will create a Customer Pojo class as below. File Location: src/com/slash/code/ Customer.java package com.slash.code; public
Read more

User Login and Logout with the session

-
Image
Now we are going to do the Login and Logout session part for this Project. Login Page: We will go one by one in each file and update some Code-Igniter files. So I am considering my Project directory name is "CRUD Project". Open file : CRUDProject\application\config\autoload.php $autoload['libraries'] = array('database','session'); $autoload['helper'] = array('url'); Open file : CRUDProject\application\config\config.php - configure base url of the project $config['base_url'] = 'http://localhost:8089/CRUDProject/'; Open file : CRUDProject\application\config\database.php - Here you need to provide your database related credentials. $db['default'] = array( 'dsn' => '', 'hostname' => 'localhost', 'username' => 'root', 'password' => '', 'database' => 'crudsession', 'dbdriv
Read more