This article helps you to understand how to interact with SQLite database using Phone gap API used for developing android application. Below contexts are explained in this article using simple Registration form for better understanding. 1. Creating SQLite database 2. Insert/Update data into
SQLite database 3. Select data from SQLite
database 4. Delete data from SQLite
database
Below given the HTML which is having below controls 1. First Name (Text box) 2. Last Name (Text box) 3. Submit and Cancel button 4. Table to Show data
Add SQLite Plugin: In order to use SQlite database on your app, you need add "SQLite" plugin into your project. Below is the "CORDOVA CLI" command will help you to add it. Cordova plugin add cordova-sqlite-storage --save (--save flag is not needed on Cordova CLI 7.0 and greater)
HTML: <table style="width: 100%;"> <tr> <td class="auto-style1" colspan="2" style="font-size: 15px"><strong>Registration</strong></td> </tr> <tr> <td style="font-weight: bold">First Name</td> <td> <input type="text" id="txtFirstName" placeholder="FirstName"> </td> </tr> <tr> <td style="font-weight: bold">Last Name</td> <td> <input type="text" id="txtLastName" placeholder="LastName"> </td> </tr> <tr> <td align="Right"> <input id="btnSubmit" type="button" value="Submit" /> </td> <td> <input id="btnCancel" type="button" value="Cancel" /> </tr> <tr> <td align="Right" class="auto-style1"></td> <td class="auto-style1"></td> </tr> <tr> <td align="Left" colspan="2"> <table class="table"> <thead> <th>Id</th> <th>FirstName</th> <th>LastName</th> <th>Action</th> </thead> <tbody id="TableData"></tbody> </table> </td> </tr> <tr> <td align="Right"></td> <td> </td> </tr> </table> Javascript: $(document).ready(function () { var myDB; var registrationId = 0; document.addEventListener("deviceready", onDeviceReady, false); function onDeviceReady() { myDB =
window.sqlitePlugin.openDatabase({ name: "mySQLite.db", location: 'default' }); CreatePhoneGapPro(); RefreshRegistration(); } // Create Registration table in Sql Lite DB. function CreatePhoneGapPro() { myDB.transaction(function
(transaction) { transaction.executeSql('CREATE TABLE IF NOT EXISTS Registration (Id integer
primary key, FirstName text, LastName text)',
[], function (tx, result) { alert("Table created successfully"); }, function (error) { alert("Error occurred while creating the table."); }); }); } //Insert New Details (into
SQLite Database) $("#btnSubmit").click(function () { var
firstName = $("#txtFirstName").val(); var lastName = $("#txtLastName").val(); myDB.transaction(function
(transaction) { if (registrationId > 0) { myDB.transaction(function
(transaction) { var
executeQuery = "UPDATE Registration
SET FirstName=?, LastName=? WHERE Id=?";
transaction.executeSql(executeQuery, [firstName, lastName,
registrationId], //On
Success function (tx,
result) {
RefreshRegistration(); ClearControl(); alert('Updated successfully'); }, function (error)
{ alert('Details not updated'); }); }); } else { var executeQuery = "INSERT
INTO Registration (FirstName, LastName) VALUES (?,?)";
transaction.executeSql(executeQuery, [firstName, lastName] , function (tx,
result) { RefreshRegistration(); ClearControl(); alert('Inserted successfully'); }, function (error)
{ alert('Details not Inserted'); }); } }); }); // Load
Data in Table from SQLite Database. function RefreshRegistration() { $("#TableData").html(""); myDB.transaction(function
(transaction) { transaction.executeSql('SELECT * FROM Registration', [], function (tx, results) { var len = results.rows.length, i; $("#rowCount").html(len); for (i = 0; i < len; i++) { $("#TableData").append("<tr><td>" + results.rows.item(i).Id + "</td><td>" + results.rows.item(i).FirstName + "</td><td>" + results.rows.item(i).LastName + "</td><td><a class='edit'
href='#' id='edit_" +
results.rows.item(i).Id + "'>Edit</a>
<a class='delete' href='#' id='" + results.rows.item(i).Id + "'>Delete</a></td></tr>"); } }, null); }); } // Get and
Set Current Selected data. $(document.body).on('click', '.edit', function () { var delString = this.id; var splitId = delString.split("_"); var curId = splitId[1]; //alert(curId); registrationId = parseInt(curId); if (registrationId > 0) { myDB.transaction(function
(transaction) { transaction.executeSql('SELECT * FROM Registration where Id=?', [registrationId], function (tx, results) { var len =
results.rows.length, i; if (len
> 0) { $("#txtFirstName").val(results.rows.item(0).FirstName); $("#txtLastName").val(results.rows.item(0).LastName); } else { ClearControl(); } }, null); }); } }); // Cancel
the current Operation. $("#btnCancel").click(function () { ClearControl(); }); // Clear
Control function ClearControl() { registrationId = 0; $("#txtFirstName").val(""); $("#txtLastName").val(""); } //Delete Selected
Registration data from SQLite Database. $(document.body).on('click', '.delete', function () { if (confirm("Do
you want to delete")) { var id = this.id; myDB.transaction(function
(transaction) { var executeQuery = "DELETE
FROM Registration where Id=?"; transaction.executeSql(executeQuery,
[id], //Success function (tx,
result) { RefreshRegistration(); ClearControl(); alert('Delete successfully'); }, //Error function (error)
{ alert('Data not deleted.'); }); }); } }); });
Screenshot: 
|