cordova sqlite storage tutorial

In this tutorial, we’re going to see how to use the sqlite plugin with PhoneGap /Cordova with the example.

What is SQLite ?

SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private.

Supported Platform : Android, iOS, Windows Universal(8.1), Amazon Fire-OS, WP(7/8)

What I’m going to do now ?

1. Create a new SQLite Database
2. Create New Table
3. Insert Data
4. Read Data
5. Update Data
6. Delete Data
7. Drop table
8. Drop Database

Step by Step Guide for PhoneGap SQLite tutorial

Step 1 : Create a new PhoneGap / Apache Cordova Project

cordova create SQLiteExample com.codesundar.sqlite SQLiteExample

Step 2 : Add required platform such as android, ios

cordova platform add android

Step 3 : Add Plugin for SQLite

cordova plugin add cordova-sqlite-storage --save

Dependancies : I used Ionic CSS Components (ionic.css) & Jquery (jquery.js) with this example

Now we’re going to perform CRUD operation with apache Cordova / PhoneGap SQLite.

Database Name : mySQLite.db

Table Name : codesundar

Table Fields : id, title, desc

Functions / Methods for database Operations

openDatabase – Create database connection

transaction – create transactions

executeSql – executes Query  with database

SYNTAX

myDB.transaction(function(transaction) {
 var executeQuery = "QUERY";
 transaction.executeSql(executeQuery, [ ],
 function(tx, result) {
//Success
},
function(error){
// Error
 });

Creating New Database using PhoneGap with SQLite Plugin

First, we need to create / open a database.

var myDB = window.sqlitePlugin.openDatabase({name: "mySQLite.db", location: 'default'});

Creating New Table using PhoneGap with SQLite Plugin

Using the myDB variable we need to execute the command. use CREATE a query for creating a table

myDB.transaction(function(transaction) {
transaction.executeSql('CREATE TABLE IF NOT EXISTS codesundar (id integer primary key, title text, desc text)', [],
function(tx, result) {
alert("Table created successfully");
},
function(error) {
alert("Error occurred while creating the table.");
});
});

Insert data into `codesundar` table

Inserting a data to the table. Here I used static data, instead of that you can pass the data in dynamic. Use INSERT query for inserting data to SQLite database

var title="sundaravel";
var desc="phonegap freelancer";
myDB.transaction(function(transaction) {
var executeQuery = "INSERT INTO codesundar (title, desc) VALUES (?,?)";
transaction.executeSql(executeQuery, [title,desc]
, function(tx, result) {
alert('Inserted');
},
function(error){
alert('Error occurred');
});
});

View All Data

for reading all data from table execute SELECT the query

myDB.transaction(function(transaction) {
transaction.executeSql('SELECT * FROM codesundar', [], function (tx, results) {
var len = results.rows.length, i;
$("#rowCount").append(len);
for (i = 0; i < len; i++){
$("#TableData").append("<tr><td>"+results.rows.item(i).id+"</td><td>"+results.rows.item(i).title+"</td><td>"+results.rows.item(i).desc+"</td></tr>");
}
}, null);
});

Update Data to SQLite Database Table:

use UPDATE Query for updating the database with id

$("#update").click(function(){
 var id=$("#id").text();
 var title=$("#title").val();
 var desc=$("#desc").val()
 myDB.transaction(function(transaction) {
 var executeQuery = "UPDATE codesundar SET title=?, desc=? WHERE id=?";
 transaction.executeSql(executeQuery, [title,desc,id],
 //On Success
 function(tx, result) {alert('Updated successfully');},
 //On Error
 function(error){alert('Something went Wrong');});
 });
});

Delete Data from table

use DELETE Query for updating the database with id

myDB.transaction(function(transaction) {
 var executeQuery = "DELETE FROM codesundar where id=?";
 transaction.executeSql(executeQuery, [id],
 //On Success
 function(tx, result) {alert('Delete successfully');},
 //On Error
 function(error){alert('Something went Wrong');});
 });

Delete entire table

use DROP Query for updating the database with id

myDB.transaction(function(transaction) {
 var executeQuery = "DROP TABLE IF EXISTS codesundar";
 transaction.executeSql(executeQuery, [],
 function(tx, result) {alert('Table deleted successfully.');},
 function(error){alert('Error occurred while droping the table.');}
 );
 });