How to Create Sqlite Database in Phonegap

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

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.

C ordova 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 > &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:

How to Create Sqlite Database in Phonegap

Source: http://www.developerin.net/a/75-Code-Snippet/115-CRUD-Operation-using-Phonegap-and-SQLite

Share this post