PhoneGap SQLite

What is SQLite?

SQLite is an embedded database software for local database / client-side storage.

Features

Serverless: It doesn’t follow the client-server model.
Zero-Configuration: No headache of configuration or administration because of residing on the client side.
Transactional: It is transactional in nature.
Self Contained: It is self-contained in nature, i.e, no external dependencies.
Reads/Writes: Read/Write operation performed to an single disk file (ordinary file) as there is no server.
Replacement for fopen() : It is no replacement for database like Oracle, but for fopen().
High Performance: It has a high performance for low memory environments like embedded devices, PDA’s, Cell phones etc.
Portable: Because of file format ‘db’, it is portable across cross platforms.
Language Support : It supports multiple programming languages.

Idea Of The App:

Features

Performs CRUD Operations

Create : Inserts a new row in the database.
Read: View all data stored in the database and display it in the table.
Update: Updates any value of any row stored in the database.
Delete: Deletes a row from the database.
Clear All: It empties all rows from the database.

Technologies Used

jQuery Mobile : For Interface / UI Designing

We’re going to use jQuery Mobile for designing interface.

Database Details

1. Database Name: my.db
2. Table Name: mydata
3. Table Columns / Fields

Column :	Id	Name	Email
Type :	integer primary key	text	text

Let’s Take a look at the SQLite Plugin.

SQLite Plugin

SQLite Plugin provides the native interface to sqlite in a PhoneGap/Cordova plugin for all 3 platforms(Android, IOS and WP8).
API’s are similar to that of HTML5’s Web SQL API.

Methods

openDatabase() : It opens a database if already exists and creates a new database if don’t exists.
```
window.sqlitePlugin.openDatabase({name: "mydata.db"});
```

transaction() : It runs a transaction.

db.transaction(function(transaction) {});

executeSQL() : It executes a SQL statement.

transaction.executeSql('SELECT name,email FROM mydata where id=?', [id], function (tx, results) {});

Process To Follow While Adding SQLite Plugin(3rd Party Plugin):

You need to open your Node.js command prompt and do as mentioned below:

Create a new phonegap project.

phonegap create SQLiteExample --id "org.sqliteexample.sample" --name "SQLiteExample"

Add the platform for which you want to create the application.
```
phonegap platform add android
```

Add the SQLite Plugin.

phonegap plugin add cordova-sqlite-storage

Now you need to add the following line in the config.xml file. There are three ways to define plugin

<gap:plugin name="SQLitePlugin" value="io.liteglue.SQLitePlugin"/>
<gap:plugin name="SQLitePlugin" value="SQLitePlugin" />
<gap:plugin name="SQLitePlugin" value="org.sqliteexample.sample.sqlitePlugin.SQLitePlugin"/>

Open up your index.html file. Add cordova.js and SQLitePlugin.js to it.

<script type="text/javascript" src="js/cordova.js"></script>
<script type="text/javascript" charset="utf-8" src="js/SQLitePlugin.js"></script>

Copy SQLitePlugin.js from

Project/plugins/cordova-sqlite-storage/www

Project/www/js

Copy cordova.js from

Project/platforms/android/assets/www

Project/www/js

Building Process:

Important Points Related To Compilation

If you’re dealing with PhoneGap 3rd party plugins, I will strongly recommend you to install Android Studio.
At least one virtual device must be present in the AVD (Android Virtual Devices). I will recommend you to have one with API Level 22 or below.

Open your Node.js command prompt and type following commands:

Prepare your project
```
cordova prepare android
```
Compile your project
```
cordova compile android
```
It will compile the project.

Build your project

cordova build android

It will generate APK in

Project/platforms/android/build/outputs/apk

Emulate your project
```
cordova emulate android
```
As soon as the command runs, an android emulator will get opened, build the app, generate apk, install it in the emulator device and run it.
Now you just need to emulate it every time, first 3 steps are just for the first time.

Files :

HTML File : Index.html

<!DOCTYPE html>
<html>
<head>

<!--Stylesheet Files : jQuery Mobile CSS File, Customized CSS File and Font Awesome for icons -->
<link rel="stylesheet" href="css/jquery.mobile-1.4.5.css">
<link rel="stylesheet" href="css/my.css">
<link rel="stylesheet" href="font-awesome/css/font-awesome.min.css">
</head>

<!--Beginning of the Body-->
<body>
<div data-role="page">

<!--Header Bar-->
<div data-role="header" data-position="fixed" class="ui-header ui-bar-a ui-header-fixed slidedown" role="banner">
<h1>Store Me</h1>
</div>

<!--Beginning of the Name and Email Field Div-->
<div data-role="main" class="ui-content" id="main">
<center><p id="heading">CRUD Operations Using SQLite Plugin</p></center>
<center><img src="img/phonegap-sqlite-plugin-img.png"/></center>
<div class="ui-grid-a">
<div class="ui-block-a"><div class="ui-bar ui-bar-a">
<center><label for="text-basic">Name</label></center>
<input type="text" name="text-basic" id="name" placeholder="Name">
</div></div>
<div class="ui-block-b"><div class="ui-bar ui-bar-a">
<center><label for="text-basic">Email</label></center>
<input type="text" name="text-basic" id="email" placeholder="Email">
</div></div>
</div>
<!--End of the Name and Email Field Div-->

<!--Beginning of the Create and Clear All Div-->
<fieldset class="ui-grid-a" id="btndiv">
<div class="ui-block-a"><div class="ui-bar ui-bar-a">
<a href="#" id ="creat" onclick="create()">
<center><i class="fa fa-pencil"> Create</i></center>
</a></div>
</div>
<!--End of the Create and Clear All Button Div-->

<!--Dialog Box when clicked on Clear All button-->
<div class="ui-block-b"><div class="ui-bar ui-bar-a">
<a id ="bt" href="#popupDialog" data-rel="popup" data-position-to="window" data-transition="pop" class="ui-btn ui-corner-all ui-shadow " >
<center><i class="fa fa-scissors"> Clear All</i></center>
</a></div>
</div>
</fieldset>
<div data-role="popup" id="popupDialog" data-dismissible="false" style="max-width:400px;">
<div data-role="header" data-theme="a">
<h1>Clear All?</h1>
</div>
<div role="main" class="ui-content">
<h3 class="ui-title">Are you sure you want to clear local storage?</h3>
<center><b><p>This action cannot be undone.</p></b></center>
<center> <a href="#" id ="clearall" class="ui-btn ui-corner-all ui-shadow ui-btn-inline ui-btn-b" data-rel="back" data-transition="flow">Yes! I'm Sure</a><a href="#" class="ui-btn ui-corner-all ui-shadow ui-btn-inline ui-btn-b" data-rel="back">Cancel</a></center>
</div>
</div>
<!-- End of the Clear All Dialog Box-->

<!--Table Displaying Database Rows-->
<table data-role="table" data-mode="reflow" class="ui-responsive ui-shadow" id="myTable">
</table>

<!--Dialog Box when clicked on update button-->
<div data-role="main" class="ui-content">
<div data-role="popup" id="myPopupDialog">
<div data-role="header">
<h1>Update</h1>
</div>
<div data-role="main" class="ui-content">
<form method="post">
<div class="ui-field-contain">
<table id="utable">
<tr>
<td><label for="id">Id:</label></td>
<td><input disabled="disabled" type="text" name="id" id="id" placeholder="Id" value=""></td>
</tr>
<tr>
<td><label for="name">Name:</label></td>
<td><input type="text" name="name" id="uname" placeholder="Name" value=""></td>
</tr>
<tr>
<td><label for="email">Email:</label></td>
<td><input type="email" name="email" id="uemail" placeholder="Enter Your New Email"></td>
</tr>
</table>
</div>
<center><a href="#" class="ui-btn ui-corner-all ui-shadow ui-btn-inline ui-btn-b" data-rel="back" id="upd" data-transition="flow">Update</a> <a href="#" class="ui-btn ui-corner-all ui-shadow ui-btn-inline ui-btn-b" data-rel="back">Cancel</a>
</center>
</form>
</div>
</div>
</div>
</div>
<!--End of the Update Dialog Box -->

<!--jQuery File : Library, Mobile Library, Cordova JS, SQLite Plugin JS and Customized JS File -->
<script type="text/javascript" charset="utf-8" src="js/jquery-1.11.3.min.js"></script>
<script type="text/javascript" charset="utf-8" src="js/jquery.mobile-1.4.5.js"></script>
<script type="text/javascript" src="cordova.js"></script>
<script type="text/javascript" charset="utf-8" src="SQLitePlugin.js"></script>
<script type="text/javascript" src="js/my.js"></script>

</body>
<!--End of the Body-->
</html>

JavaScript File : My.js

<!--Calling onDeviceReady method-->
document.addEventListener("deviceready", onDeviceReady, false);
function onDeviceReady() {

<!--window.sqlitePlugin.openDatabase creates/open a non existing/existing database-->
var db = window.sqlitePlugin.openDatabase({name: "my.db"});
show();
db.transaction(function(tx) {
tx.executeSql('CREATE TABLE IF NOT EXISTS mydata (id integer primary key, name text, email text)');
});

<!--Method to insert new row in the database-->
$(document).on('click', '#creat', function(){
var name = $("#name").val();
var email = $("#email").val();
db.transaction(function(transaction) {
var executeQuery = "INSERT INTO mydata (name, email) VALUES (?,?)";
transaction.executeSql(executeQuery, [name,email]
, function(tx, result) {
show();
},
function(error){
//filter(function(aSome) {alert('Error occurred');
});
});
});

<!--Display all rows stored in the database-->
function show(){
db.transaction(function(transaction) {
transaction.executeSql('SELECT * FROM mydata', [], function (tx, results) {
var key = "";

<!--Display the table head-->
var pair="<tr><th data-priority=\"1\"><center>Id</center></th><th data-priority=\"1\"><center>Name</center></th><th data-priority=\"2\"><center>Email</center></th><th><center>Update</center></th><th><center>Delete</center></th></tr>";
var i=0;

<!--results.rows.length to get the total number of rows stored in the database-->
var len = results.rows.length, i;
for (i=0; i<=len-1; i++) {

<!--Fetching the 'name' from the database-->
key = results.rows.item(i).name;

<!--Fetching the 'id' from the database-->
id = results.rows.item(i).id;

<!--Displaying all rows of the database in the table-->
pair += "<tr><td><center>"+id+"</center></td><td><center>"+key+"</center></td><td><center>"+results.rows.item(i).email+"</center></td><td><a class=\"update\" href=\"#myPopupDialog\" data-custom="+"'"+ id+ "'" +"data-rel=\"popup\" data-position-to=\"window\" data-transition=\"pop\"><center><i class='fa fa-pencil-square-o'></i></center></a></td><td><a id=\"delete\" data=\""+id+"\"><center><i class='fa fa-trash'></i></center></a></td></tr>";
}
if (pair == "<tr><th>Name</th><th>Email</th></tr>") {
pair += "<tr><td><i>empty</i></td><td><i>empty</i></td></tr>";
}
$("#myTable").html(pair);
}, null);
});
}

<!--Method to delete any row from the database-->
$(document).on('click', '#delete', function(){
var id = $(this).attr("data");
db.transaction(function(transaction) {
transaction.executeSql("DELETE FROM mydata where id=?", [id],
function(tx, result) {
show();
},
function(error){
// alert('Something went Wrong');
});
});
});

<!--Method to update the values of any row in the database-->
$(document).on('click', '#upd', function(){
var id = $("#id").val();
var name = $("#uname").val();
var email = $("#uemail").val();
db.transaction(function(transaction) {
var executeQuery = "";
transaction.executeSql("UPDATE mydata SET name=?, email=? WHERE id=?", [name,email,id],
function(tx, result) {alert('Updated successfully');
show();
},
function(error){alert('Something went Wrong');});
});
});
$(document).on('click', '.update', function(){
var id = $(this).attr('data-custom');
$("#id").val(id);
db.transaction(function(transaction) {
transaction.executeSql('SELECT name,email FROM mydata where id=?', [id], function (tx, results) {
var name = results.rows.item(0).name;
var email = results.rows.item(0).email;
$("#uname").val(name);
$("#uemail").val(email);
},
function(error){
alert('Something went Wrong');
});
});
});

<!--Method to clear all rows from the database-->
$(document).on('click', '#clearall', function(){
db.transaction(function(transaction) {
transaction.executeSql("DELETE FROM mydata", [],
function(tx, result) {alert('Delete successfully');
show();
},
function(error){alert('Something went Wrong');});
});
});
}

CSS File : My.css

.ui-bar-a, .ui-page-theme-a .ui-bar-inherit, html .ui-bar-a .ui-bar-inherit, html .ui-body-a .ui-bar-inherit, html body .ui-group-theme-a .ui-bar-inherit {
border: 1px solid #005994 !important;
background: #0093EA !important;
color: #fff !important;
font-weight: bold !important;
text-shadow: 0 0 #eee !important;
background-image: -webkit-gradient(linear, left top, left bottom, from( #0093EA), to( #007dcd ));
background-image: -webkit-linear-gradient( #0093EA , #007dcd );
background-image: -moz-linear-gradient( #0093EA, #007dcd );
background-image: -ms-linear-gradient( #0093EA , #007dcd );
background-image: -o-linear-gradient( #0093EA , #007dcd );
background-image: linear-gradient( #0093EA , #007dcd );
}
.ui-page-theme-a .ui-btn:hover, html .ui-bar-a .ui-btn:hover, html .ui-body-a .ui-btn:hover, html body .ui-group-theme-a .ui-btn:hover, html head + body .ui-btn.ui-btn-a:hover{
border: 1px solid #007dcd;
background: #333 ;
font-weight: bold;
text-shadow: 0 0 #eee !important;
color: #fff !important;
background-image: -webkit-gradient(linear, left top, left bottom, from( #0093EA ), to( #0093EA ));
background-image: -webkit-linear-gradient( #0093EA , #0093EA );
background-image: -moz-linear-gradient( #0093EA , #0093EA );
background-image: -ms-linear-gradient( #0093EA , #0093EA );
background-image: -o-linear-gradient( #0093EA , #0093EA );
background-image: linear-gradient( #0093EA , #0093EA );
}
.ui-page-theme-a .ui-btn.ui-btn-active, html .ui-bar-a .ui-btn.ui-btn-active, html .ui-body-a .ui-btn.ui-btn-active, html body .ui-group-theme-a .ui-btn.ui-btn-active, html head + body .ui-btn.ui-btn-a.ui-btn-active, .ui-page-theme-a .ui-checkbox-on:after, html .ui-bar-a .ui-checkbox-on:after, html .ui-body-a .ui-checkbox-on:after, html body .ui-group-theme-a .ui-checkbox-on:after, .ui-btn.ui-checkbox-on.ui-btn-a:after, .ui-page-theme-a .ui-flipswitch-active, html .ui-bar-a .ui-flipswitch-active, html .ui-body-a .ui-flipswitch-active, html body .ui-group-theme-a .ui-flipswitch-active, html body .ui-flipswitch.ui-bar-a.ui-flipswitch-active, .ui-page-theme-a .ui-slider-track .ui-btn-active, html .ui-bar-a .ui-slider-track .ui-btn-active, html .ui-body-a .ui-slider-track .ui-btn-active, html body .ui-group-theme-a .ui-slider-track .ui-btn-active, html body div.ui-slider-track.ui-body-a .ui-btn-active {
background-color: #0093EA !important ;
border-color:#0093EA !important;
color: #fff ;
text-shadow: 0 1px 0 #005599 ;
}
img{
padding: 25px;
}
button.ui-btn, .ui-controlgroup-controls button.ui-btn-icon-notext {
border-radius: 5px !important;
}
#searchbutton{
margin-bottom: 25px;
}
#main{
margin-top: 12% !important ;
}
.ui-collapsible-inset.ui-collapsible-themed-content .ui-collapsible-content
{
background-color: #ddd;
color: #111;
}
.ui-collapsible-content {
-webkit-transition: all 0.5s;
-moz-transition: all 0.5s;
-ms-transition: all 0.5s;
-o-transition: all 0.5s;
transition: all 0.5s;
//height: 2em;
overflow: hidden;
}
.ui-collapsible-content-collapsed {
display: block;
height: 0;
padding: 0 16px;
}
#bt i{
font-weight: bold;
}
th {
border-bottom: 1px solid #d6d6d6 !important;
}
tr:nth-child(even) {
background: #e9e9e9 !important;
}
.ui-table {
margin-top: 5% !important;
border: 1px solid grey !important;
border-radius: 5px !important;
border-collapse: initial !important;
}
label{
font-weight: bold !important;
}
#label{
border: 1px solid #0093EA !important;
background: #fff !important;
color: #005994 !important;
font-weight: bold !important;
text-shadow: 0 0 #eee !important;
background-image: -webkit-gradient(linear, left top, left bottom, from( #0093EA), to( #007dcd ));
background-image: -webkit-linear-gradient( #0093EA , #007dcd );
background-image: -moz-linear-gradient( #0093EA, #007dcd );
background-image: -ms-linear-gradient( #0093EA , #007dcd );
background-image: -o-linear-gradient( #0093EA , #007dcd );
background-image: linear-gradient( #0093EA , #007dcd );
}
#utable tr:nth-child(even){
background: inherit !important;
}
#heading{
font-weight: bold;
font-size: 40px;
}
#btndiv{
margin-top: 3%;
}
@media ( max-width: 35em ) {
.ui-table-reflow.ui-responsive td,
.ui-table-reflow.ui-responsive th {
width: auto;
float: none;
clear: none;
display: table-cell;
margin: 0;
padding:0;
}
}
#btndiv .ui-bar-a{
width: 50% !important;
margin: auto !important;
}

Conclusion:

With the help of SQLite plugin, you can enjoy the benefits of SQLite in mobile applications. As there is no server, accessing and writing the data is very fast which in result provides high performance. I hope this will help you.

Recommended blogs –

PhoneGap Configuration File : Config.xml

PhoneGap Camera Plugin

PhoneGap SQLite Plugin

Idea Of The App:

Process To Follow While Adding SQLite Plugin(3rd Party Plugin):

Building Process:

Files :

HTML File : Index.html

JavaScript File : My.js

CSS File : My.css

Conclusion:

Leave a Reply Cancel reply

Take Your Email Marketing To The Next Level!

Start using MailGet and experience world class email builder, create email newsletters in a snap and send great
emails to your audience. Do amazing email marketing at an affordable price.

PhoneGap SQLite Plugin

Idea Of The App:

Process To Follow While Adding SQLite Plugin(3rd Party Plugin):

Building Process:

Files :

HTML File : Index.html

JavaScript File : My.js

CSS File : My.css

Conclusion:

Related Posts:

Leave a Reply Cancel reply

Take Your Email Marketing To The Next Level!

Start using MailGet and experience world class email builder, create email newsletters in a snap and send great emails to your audience. Do amazing email marketing at an affordable price.

Start using MailGet and experience world class email builder, create email newsletters in a snap and send great
emails to your audience. Do amazing email marketing at an affordable price.