cordova sqlite storage tutorial
Hello friend,
Tired of searching 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 I'm going to do now ?
- Create a new SQLite Database
- Create 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 : Add required platform such as android, ios
cordova platform add android
Step 3 : Add Plugin for SQLite
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 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.');
}
);
});