Android > Phone Gap >> Code Snippet Views : 1716
Rate This Article :

CRUD Operation using Phonegap and SQLite

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

HTML:

    <table style="width100%;">

        <tr>

            <td class="auto-style1" colspan="2" style="font-size15px"><strong>Registration</strong></td>

        </tr>

        <tr>

            <td style="font-weightbold">First Name</td>

            <td>

                <input type="text" id="txtFirstName" placeholder="FirstName">

            </td>

        </tr>

        <tr>

            <td style="font-weightbold">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>&nbsp;</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> &nbsp;&nbsp; <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:

Phonegap_Android_Regdetails.png 

About Author
Raj Kumar
Total Posts 30
Developer in .Net!
Comment this article
Name*
Email Address* (Will not be shown on this website.)
Comments*
Enter Image Text*
   
View All Comments
Chris Brody
Article is missing the directions to add the sqlite plugin. For Cordova CLI: cordova plugin add cordova-sqlite-storage --save (--save flag not needed on Cordova CLI 7.0 and greater)
ayoub
not work
Nithin
Nice article...
  Privacy   Terms Of Use   Advertise With Us   Contact Us
© 2016 Developerin.Net. All rights reserved.
Trademarks and Article Images mentioned in this site may belongs to Microsoft and other respective trademark owners.
Articles, Tutorials and all other content offered here is for educational purpose only and its author copyrights.