WebSQL Class Save Half Time in CRUD Programming

Estimated reading time: 7 minutes

 

 

 

Download Guideline

• Prepare HTTP server such as XAMPP or WAMP in your windows environment.
• Download and unzip into a folder that http server can access.

 DOWNLOAD SOURCE

 

 

Browser Built-In Database

WebSQL is browser built-in, so you can store local data by SQL syntax. Wikipedia describes it as the following,

Web SQL Database is a web page API for storing data in databases that can be queried using a variant of SQL. The API is supported by Google Chrome, Opera, and the Android Browser.

However, unlike sync APIs in MySQL, WebSQL with asynchronous APIs such as openDatabase(), transaction(), executeSql() can not provides intuitive programming in getting or setting data storages. Therefore, here we suggest an object-oriented way to overcome these disadvantages

 

Available in Cordova

If you use Cordova in developing mobile APPs, WebSQL is very well for Cordova storage, though it allows limited space around 5MB.

You don’t have to install any Cordova plugin for WebSQL. When you test javascript programs successfully in browsers, these functions will be automatically valid in Cordova-converted Android or iOS APPs.

 

 

Javascript Database Class

Don’t be surprised at few codes in the WebSQL class, which consists of only the constructor() and a method execute(). That is enough for CRUD database operations.

/* A database class for WebSQL */
class dbClass {
    constructor(dbName, dbDisplay="", callback=function(){}) {
        this.conn = openDatabase(dbName, '1.0', dbDisplay, 2 * 1024 * 1024); //create or open database
        this.conn.transaction(function (tx) { callback(); });
    }
    execute(sql, arr=[], callback=function(){}) {
        console.log(sql);
        console.log(arr);
        this.conn.transaction(function (tx) {
            tx.executeSql(
                sql,
                arr,
                function (tx, stmt) {
                    console.log(tx);
                    console.log(stmt);
                    callback(stmt.rows, stmt.rows.length, stmt.rowsAffected);
                },
                null
            );
        });
    }
}

Before going to derive objects from WebSQL class, you have to prepare database name and description. But the callback function is optional. When an object is created, use execute() method with a standard SQL statement and arguments to fulfill data access.

 

How CRUD Perform

Most commonly used operations are listed below along with explanations.

• Init:
  db=new dbClass("school", "my school"). The parameters are for database name and description.
• Table:
  db.execute("CREATE TABLE IF NOT EXISTS student (name, score)"). The created table contains two fields.
• Insert:
  db.execute("INSERT INTO student (name, score) VALUES (?, ?)", ["john", 70]). Insertion of rows can use placeholders and corresponding values.
• Select:
  db.execute("SELECT * FROM student", [], displayData). The callback function displays resulting key-value pairs.

function displayData(rows, n, rowsAffected) {
    result = "";
    for(var i=0; i<n; i++) {
        result += "name=" + rows[i].name + " score=" + rows[i].score + "<br>";
    }
    $("#display").html(result);
}

• Update:
  db.execute("UPDATE student SET score=75 WHERE name=?", ["john"], checkAffected). The callback function can check if any rows is affected. It is optional.

function checkAffected(rows, n, rowsAffected) {
    if(rowsAffected == 0) alert("No Rows Affected");
}

• Delete:
  db.execute("DELETE FROM student WHERE name=?", ["john"], checkAffected).

 

 

Using jQuery Mobile

jQuery Mobile makes browser layout similar to that of mobile APPs. You can convert it to Android APPs by Cordova if needed. Here jQuery Mobile Grid deploys data fields and buttons as below.

<div data-role="page" class="page">
<div data-role="header">
    <h1>STUDENT SCORES</h1>
</div>
<div data-role="content" class="my-content">
    <div class="ui-grid-a">
        <div class="ui-block-a center">
        <p>Enter Name: <input type="text" id="name"></p>
        </div>
        <div class="ui-block-b center">
        <p>Enter Score: <input type="text" id="score"></p>
        </div>
    </div>
    <div class="ui-grid-a">
        <div class="ui-block-a center">
        <button type="button" data-inline="true" id="insert">CREATE</button>
        </div>
        <div class="ui-block-b center">
        <button type="button" data-inline="true" id="select">READ</button>
        </div>
    </div>
    <div class="ui-grid-a">
        <div class="ui-block-a center">
        <button type="button" data-inline="true" id="update">UPDATE</button>
        </div>
        <div class="ui-block-b center">
        <button type="button" data-inline="true" id="delete">DELETE</button>
        </div>
    </div>
    <ul data-role="listview" data-inset="true" id="display"></ul>
</div>
<div data-role="footer" data-position="fixed">
    <p style="text-align:center;">How to Use WebSQL Database Class</p>
</div>
</div>

First, new a database school or open it if already existed. The callback function Init() generate a new table student if not existed.

db = new dbClass("school", "my school", Init);
function Init() {
    sql="CREATE TABLE IF NOT EXISTS student (name, score)";
    db.execute(sql);
    $("#select").click();
}

 

CRUD – Create

The following is captured on F12 Inspect screen.

Enter name and score in the fields and click CREATE button to insert a row.

$("#insert").click(function() {
    sql="INSERT INTO student (name, score) VALUES (?, ?)";
    db.execute(sql, [$("#name").val(), $("#score").val()]);
    $("#select").click();
});

 

CRUD – Read

Clicking READ button will display row data by a callback function. Note that all the CREATE, UPDATE, and DELETE buttons bring READ button click at last, so names and scores are always listed below buttons.

$("#select").click(function() {
    sql="SELECT * FROM student";
    db.execute(sql, [], displayData);
});
function displayData(rows, n, rowsAffected) {
    result = "";
    for(var i=0; i<n; i++) {
        result += "name=" + rows[i].name + " score=" + rows[i].score + "<br>";
    }
    $("#display").html(result);
}

 

CRUD – Upate

Enter name and changed score in the fields and click UPDATE button to update a row.

$("#update").click(function() {
    sql="UPDATE student SET score=? WHERE name=?";
    db.execute(sql, [$("#score").val(), $("#name").val()], checkAffected);
    $("#select").click();
});
function checkAffected(rows, n, rowsAffected) {
    if(rowsAffected == 0) alert("No Rows Affected");
}

If no rows are affected, the callback function will alert with a message.

 

CRUD – Delete

Enter name in the field and click DELETE button to delete a row. If no rows are affected, the callback function will alert with a message.

$("#delete").click(function() {
    sql="DELETE FROM student WHERE name=?";
    db.execute(sql, [$("#name").val()], checkAffected);
    $("#select").click();
});

 

 

Learning Tips

Let us suggest a excellent way to learn HTML scripts here. Using Google Chrome F12 Inspect or Inspect Element will help you study the codes.

In Google Chrome, there are two ways to inspect a web page using the browser built-in Chrome DevTools:

• Right-click an element on the page or in a blank area, then select Inspect.
• Go to the Chrome menu, then select More Tools > Developer Tools.

 

Suggested Reading

• Small PHP CRUD Class for OOP using MySQL
• 3 Steps Cordova Build APPs from jQuery Mobile

 

 

Try It Yourself

Click here to execute the source code, thus before studying the downloaded codes, you can check whether it is worthy.