Hello friend,
Tired of searching the Cordova SQLite storage example? No worries, I have written a step by step tutorial for integrating SQLite with your Cordova (PhoneGap) apps.
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.
What we’ll learn
- Create a new SQLite Database
- Create a New Table
- Insert Data
- Read Data
- Update Data
- Delete Data
- Drop table
- 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 cd SQLiteExample
Step 2: Let’s add platform and plugin to your project
cordova platform add android cordova plugin add cordova-sqlite-storage
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 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.');
}
);
});