Introduction:
Few days ago I received a comment from one of the reader of my blog to show an example on CRUD (Create, Read, Update and Delete) with the database. So in this post we will explore the use of CRUD operation using SQLite database in PhoneGap application.

Description:
CRUD can be described as a process, which helps the user to add, view, search and modify the information inside the database.

Here are the basic steps which demonstrates CRUD operation.

  • Create or add new entries
  • Read, retrieve, search or view existing entries
  • Update, modify or edit existing entries
  • Delete, deactivate or destroy existing entries

Before doing any operation on database let’s initialize the database first, either using HTML5 local database concept or using SQLite plugin.

Let’s add the following javascript to initilze the database.

1
2
3
4
5
6
7
8
var databaseName =  "DummyDB";
var databaseVersion = "1.0";
var databaseDisplayName = "DummyDatabase";
var databaseSize =  2 * 1024 * 1024;
var myDB = "";
 
//Accessing with HTML5 local database
myDB = window.openDatabase(databaseName, databaseVersion , databaseDisplayName, databaseSize);

window.openDatabase helps to return the new database object. This method will create a the new SQLite database and also can be use for other data manipulation.

1
2
//Using SQLite plugin
myDB = window.sqlitePlugin.openDatabase({name : databaseName});

Above method will create the SQLite datebase with unlimited storage capacity. To integrate the SQLite plugin we can follow this post.

Now lets focus on the basic 4 steps of CRUD operation by using the database object already created by above step.

CREATE: Initializing or creating the table. We normally use two keywords CREATE and INSERT for this operation.

Example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
function CreateBusinessTable() {
            myDB.transaction(function(transaction) {
                transaction.executeSql('CREATE ' +
                           'TABLE IF NOT EXISTS ' +
                           'Business_Table (id integer primary key, business_id integer, business_name text)', [],
                    function(tx, result) {
                        console.log("Business table created successfully.");
                    },
                    function(error) {
                          console.log("Error occurred while creating the table.");
                    });
                });
}
function InsertBusinessData(){
        myDB.transaction(function(transaction) {
            // Define insert query
            var executeQuery = "INSERT INTO " +
                                "Business_Table" +
                                "(business_id, business_name) "+
                                "VALUES(?,?)";
            Helper.log(executeQuery);             
            transaction.executeSql(executeQuery, ['1234', 'Samdoit']
                , function(tx, result) {   // On success
                     console.log('Business data inserted successfully.');
                },
                function(error){     // On error                              
                     console.log('Error occurred while inserting business data.');
                });
        });
}

READ: Use to get, search or retrieve entries from a table inside the database.

Example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
function GetBusinessData() {
           myDB.transaction(function(transaction) {
                transaction.executeSql("SELECT business_id, business_name FROM Business_Table                                                   WHERE business_id =?", ['1234'],
                function(tx, result) {
                      var dataLength = result.rows.length;
                       console.log(dataLength);
                      if(dataLength  > 0){
                           var businessName = result.rows.item(0).business_name;
            console.log(businessName);
                       }else{
                             console.log("No business found having this business id.);
                       }
                  },
                  function(error) {
                        console.log("Error occurred while getting the data.);
                  });
          });
}

UPDATE: This operation is used to update or modify the entries in the table.

Example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
function UpdateBusinssTable(){
    myDB.transaction(
                    function(transaction) {
                    // Define update query
                    var executeQuery = "UPDATE " +
                                       "Business_Table " +
                                       "SET business_name = ?  WHERE  business_id =?";
                    transaction.executeSql(executeQuery, ['Samdoit Infotech', '1234']
                        , function(tx, result) {   // On success
                             console.log('Business updated successfully.');
                        },
                        function(error){     // On error                              
                            console.log('Error occurred while updating the business.');
                        });
           });
}

DELETE: This is the last and final method of CRUD operation which is used to delete/drop the entries in table.

Example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
function DeleteBusinessTable(){
            myDB.transaction(
                function(transaction) {
                // Define delete query
                var executeQuery = "DELETE FROM Business_Table";
                transaction.executeSql(executeQuery, []
                    , function(tx, result) {   // On success
                         console.log('All business data deleted successfully.');
                    },
                    function(error){     // On error                              
                         console.log('Error occurred while deleting the business data.');
                    });
            });
}
 
function DropBusinessTable(){
            myDB.transaction(
                function(transaction) {
                // Define delete query
                var executeQuery = "DROP TABLE  IF EXISTS Business_Table";
                transaction.executeSql(executeQuery, []
                    , function(tx, result) {   // On success
                         console.log('Table deleted successfully.');
                    },
                    function(error){     // On error                              
                         console.log('Error occurred while droping the table.');
                    });
            });
}

Summary:
I hope the above content will give some idea to start the basic CRUD operation with database and will make the life easy.