HTML 5

CRUD Operations Using WebSQL In HTML5 And jQuery

Introduction

"Web SQL" feature is new in HTML5. Web SQL is an API, which helps the developers to do some database operations in the client side, like creating a database, opening the transaction, creating the tables, inserting values to the tables, deleting the values and reading the data. If you need any other way to save some data in the client side, you can use the storage mechanisms, introduced in HTML5.

Browser Support

A Web SQL database will work in the latest version of Safari, Chrome and Opera.

Differents types of Methods are,

  1. openDatabase
    Creates the database object, either using an existing database or creating a new one.

  2. transaction
    Controls a transaction and performs either a commit or rollback on the basis of the situation.

  3. executeSql
    Executes a SQL query.

Create/Open Web SQL Database

To create a Web SQL database, we can use a function called open database, which has four parameters as follows:

  • Database name
  • Version Number
  • Description
  • Size
  • Creation callback.

The last argument (Creation callback) will be called, if the database is being created.

  1. var Database_Name = 'MyDatabase';  
  2.         var Version = 1.0;  
  3.         var Text_Description = 'My First Web-SQL Example';  
  4.         var Database_Size = 2 * 1024 * 1024;  
  5.         var dbObj = openDatabase(Database_Name, Version, Text_Description, Database_Size);  
Creating transaction

To create a transaction we can use the following syntax. We can use transaction method from our database instance.
  1. dbObj.transaction(function (tx)  
  2.  {  
  3. // do SQL here using the tx object  
  4. });
Execute Sql

The executeSql method is used for both read and write statements. To execute SQL query, we can use the following syntax: 
  • Table creation
    1. dbObj.transaction(function (tx)  
    2.  {  
    3. tx.executeSql('CREATE TABLE IF NOT EXISTS Employee_Table (id unique, Name, Location,did)');  
    4.             tx.executeSql('CREATE TABLE IF NOT EXISTS dept_Table (did unique, dName,estd)');  
    5. });  
    Table creation

    Note, if the database already exists, the transaction will fail. Thus, any successive SQL will not run. Thus, we can either use another transaction or we can only create the table, if it doesn't exist.

  • Insert

    Now, lets see, how to perform an insert operation.
    1. var id = document.getElementById("tbID").value;  
    2.             var name = document.getElementById("tbName").value;  
    3.             var location = document.getElementById("tbLocation").value;  
    4.             var did = document.getElementById("tbLdept").value;  
    5.             dbObj.transaction(function (tx) {  
    6.                 tx.executeSql('insert into Employee_Table(id, Name, Location,did) values(' + id + ',"' + name + '","' + location + '",' + did + ')');  
    7.    });  
    After insert

    After insert

  • Select

    Now, let's see, how to retrieve the inserted record.
    1. dbObj.transaction(function (tx) {  
    2.                 tx.executeSql('SELECT e.id,e.Name,e.Location,d.dName,d.did FROM Employee_Table e inner join dept_Table d on e.did=d.did ', [], function (tx, results) {  
    3.                     var len = results.rows.length, i;  
    4.                     // document.getElementById("tblGrid").innerHTML = '';  
    5.                     $("#tblGrid").find("tr:gt(0)").remove();  
    6.                     var str = '';  
    7.                     for (i = 0; i < len; i++) {  
    8.                         str += "<tr>";  
    9.                         str += "<td>" + results.rows.item(i).id + "</td>";  
    10.                         str += "<td>" + results.rows.item(i).Name + "</td>";  
    11.                         str += "<td>" + results.rows.item(i).Location + "</td>";  
    12.                         str += "<td>" + results.rows.item(i).dName + "</td>";  
    13.                         str += "</tr>";  
    14.                         document.getElementById("tblGrid").innerHTML += str;  
    15.                         str = '';  
    16.                     }  
  • Update
    1. var id = document.getElementById("ddlid").value;  
    2.             var name = document.getElementById("tbName").value;  
    3.             var location = document.getElementById("tbLocation").value;  
    4.             var did = document.getElementById("tbLdept").value;  
    5.   
    6.             dbObj.transaction(function (tx) {  
    7.                 tx.executeSql('update Employee_Table set Name="' + name + '",Location="' + location + '",did=' + did + ' where id=' + id + '');  
    8.             });  
    Update
     
    Now, let's see, how to perform an update operation.

  • Delete

    Now, lets see, how to perform delete operation.
    1. var id = document.getElementById("ddlid").value;  
    2.      //  alert(id);  
    3.        dbObj.transaction(function (tx) {  
    4.            tx.executeSql('delete from Employee_Table where id=' + id + '');  
    5.        });  
Example
  1. <!DOCTYPE html>  
  2. <html>  
  3. <head>  
  4.     <title>Open DataBase</title>  
  5.     <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>  
  6.      
  7.     <script>  
  8.   
  9.         var Database_Name = 'MyDatabase';  
  10.         var Version = 1.0;  
  11.         var Text_Description = 'My First Web-SQL Example';  
  12.         var Database_Size = 2 * 1024 * 1024;  
  13.         var dbObj = openDatabase(Database_Name, Version, Text_Description, Database_Size);  
  14.         dbObj.transaction(function (tx) {  
  15.   
  16.             tx.executeSql('CREATE TABLE IF NOT EXISTS Employee_Table (id unique, Name, Location,did)');  
  17.             tx.executeSql('CREATE TABLE IF NOT EXISTS dept_Table (did unique, dName,estd)');  
  18.   
  19.   
  20.             var today = new Date();  
  21.             var dd = today.getDate();  
  22.             var mm = today.getMonth() + 1; //January is 0!  
  23.   
  24.             var yyyy = today.getFullYear();  
  25.             if (dd < 10) {  
  26.                 dd = '0' + dd  
  27.             }  
  28.             if (mm < 10) {  
  29.                 mm = '0' + mm  
  30.             }  
  31.             var today = dd + '/' + mm + '/' + yyyy;  
  32.   
  33.             tx.executeSql('insert into dept_Table(did, dName, estd) values(1,"IT","' + today + '")');  
  34.             tx.executeSql('insert into dept_Table(did, dName, estd) values(2,"Accountant","' + today + '")');  
  35.             tx.executeSql('insert into dept_Table(did, dName, estd) values(3,"Claerk","' + today + '")');  
  36.             tx.executeSql('insert into dept_Table(did, dName, estd) values(4,"Management","' + today + '")');  
  37.             alldetails();  
  38.         });  
  39.   
  40.   
  41.         function Insert() {  
  42.             
  43.             var id = document.getElementById("tbID").value;  
  44.             var name = document.getElementById("tbName").value;  
  45.             var location = document.getElementById("tbLocation").value;  
  46.             var did = document.getElementById("tbLdept").value;  
  47.             dbObj.transaction(function (tx) {  
  48.                 tx.executeSql('insert into Employee_Table(id, Name, Location,did) values(' + id + ',"' + name + '","' + location + '",' + did + ')');  
  49.   
  50.   
  51.             });  
  52.   
  53.             alldetails();  
  54.         }  
  55.   
  56.         function del() {  
  57.             var id = document.getElementById("ddlid").value;  
  58.           //  alert(id);  
  59.             dbObj.transaction(function (tx) {  
  60.                 tx.executeSql('delete from Employee_Table where id=' + id + '');  
  61.             });  
  62.   
  63.             empidbind();  
  64.   
  65.             alldetails();  
  66.         }  
  67.   
  68.   
  69.         function myFunction()  
  70.         {  
  71.             var id = document.getElementById("ddlid").value;  
  72.   
  73.             dbObj.transaction(function (tx) {  
  74.                 tx.executeSql('SELECT * from Employee_Table where id=' + id + '', [], function (tx, results)  
  75.                 {  
  76.                     document.getElementById("tbName").value = results.rows.item(0).Name;  
  77.                     document.getElementById("tbLocation").value = results.rows.item(0).Location;  
  78.                     document.getElementById("tbLdept").value = results.rows.item(0).did;  
  79.   
  80.                       
  81.                 }, null);  
  82.             });  
  83.              
  84.         }  
  85.   
  86.         function showdel() {  
  87.   
  88.             empidbind();  
  89.   
  90.             $('#tdorginal').hide();  
  91.             $('#tdid').show();  
  92.             $('#btnupdate').hide();  
  93.             $('#btnInsert').hide();  
  94.             $('#btndel').show();  
  95.             $('#btninsertshw').show();  
  96.             $('#btnupdateshw').show();  
  97.             $('#btndeleteshw').hide();  
  98.             ////////////  
  99.             $('#rowName').hide();  
  100.             $('#rowLocation').hide();  
  101.             $('#rowdept').hide();  
  102.         }  
  103.   
  104.         function showin()  
  105.         {  
  106.             $('#tdid').hide();  
  107.             $('#tdorginal').show();  
  108.             
  109.             $('#btnupdate').hide();  
  110.             $('#btnInsert').show();  
  111.             $('#btndel').hide();  
  112.             $('#btninsertshw').hide();  
  113.             $('#btnupdateshw').show();  
  114.             $('#btndeleteshw').show();  
  115.             ////////////  
  116.             $('#rowName').show();  
  117.             $('#rowLocation').show();  
  118.             $('#rowdept').show();  
  119.   
  120.             /////////////  
  121.           document.getElementById("tbID").value='';  
  122.           document.getElementById("tbName").value='';  
  123.           document.getElementById("tbLocation").value='';  
  124.           document.getElementById("tbLdept").value='1';  
  125.   
  126.                empidbind();  
  127.         }  
  128.   
  129.   
  130.         function empidbind()  
  131.         {  
  132.             dbObj.transaction(function (tx) {  
  133.                 tx.executeSql('SELECT * from Employee_Table', [], function (tx, results) {  
  134.                     var len = results.rows.length, i;  
  135.                     document.getElementById("ddlid").innerHTML = '';  
  136.                     var str = '';  
  137.                     for (i = 0; i < len; i++) {  
  138.                         str += "<option value=" + results.rows.item(i).id + ">" + results.rows.item(i).id + "</option>";  
  139.                         document.getElementById("ddlid").innerHTML += str;  
  140.                         str = '';  
  141.                     }  
  142.                 }, null);  
  143.             });  
  144.   
  145.         }  
  146.   
  147.         function showupdte()  
  148.         {  
  149.             empidbind();  
  150.   
  151.   
  152.             $('#tdorginal').hide();  
  153.             $('#tdid').show();  
  154.             $('#btnupdate').show();  
  155.             $('#btnInsert').hide();  
  156.             $('#btndel').hide();  
  157.             $('#btninsertshw').show();  
  158.             $('#btnupdateshw').hide();  
  159.             $('#btndeleteshw').show();  
  160.             $('#rowName').show();  
  161.             $('#rowLocation').show();  
  162.             $('#rowdept').show();  
  163.               
  164.         }  
  165.   
  166.         function updte() {  
  167.             
  168.             var id = document.getElementById("ddlid").value;  
  169.             var name = document.getElementById("tbName").value;  
  170.             var location = document.getElementById("tbLocation").value;  
  171.             var did = document.getElementById("tbLdept").value;  
  172.   
  173.             dbObj.transaction(function (tx) {  
  174.                 tx.executeSql('update Employee_Table set Name="' + name + '",Location="' + location + '",did=' + did + ' where id=' + id + '');  
  175.             });  
  176.   
  177.             alldetails();  
  178.         }  
  179.   
  180.         function alldetails()  
  181.         {  
  182.             dbObj.transaction(function (tx) {  
  183.                 tx.executeSql('SELECT e.id,e.Name,e.Location,d.dName,d.did FROM Employee_Table e inner join dept_Table d on e.did=d.did ', [], function (tx, results) {  
  184.                     var len = results.rows.length, i;  
  185.                     // document.getElementById("tblGrid").innerHTML = '';  
  186.                     $("#tblGrid").find("tr:gt(0)").remove();  
  187.                     var str = '';  
  188.                     for (i = 0; i < len; i++) {  
  189.                         str += "<tr>";  
  190.                         str += "<td>" + results.rows.item(i).id + "</td>";  
  191.                         str += "<td>" + results.rows.item(i).Name + "</td>";  
  192.                         str += "<td>" + results.rows.item(i).Location + "</td>";  
  193.                         str += "<td>" + results.rows.item(i).dName + "</td>";  
  194.                         str += "</tr>";  
  195.                         document.getElementById("tblGrid").innerHTML += str;  
  196.                         str = '';  
  197.                     }  
  198.                 }, null);  
  199.             });  
  200.   
  201.         }  
  202.   
  203.         dbObj.transaction(function (tx) {  
  204.             tx.executeSql('SELECT * from dept_Table', [], function (tx, results) {  
  205.                 var len = results.rows.length, i;  
  206.                 var str = '';  
  207.                 for (i = 0; i < len; i++) {  
  208.                     str += "<option value=" + results.rows.item(i).did + ">" + results.rows.item(i).dName + "</option>";  
  209.                     document.getElementById("tbLdept").innerHTML += str;  
  210.                     str = '';  
  211.                 }  
  212.             }, null);  
  213.         });  
  214.   
  215.   
  216.          
  217.     </script>  
  218. </head>  
  219. <body>  
  220.     <p id="hh"></p>  
  221.     <form id="frm1">  
  222.         <table id="tblinsert">  
  223.             <tr>  
  224.                 <td>ID:</td>  
  225.                 <td id="tdorginal"><input type="text" id="tbID" /><span style="color:red">*ID must be unique</span></td>  
  226.                 <td id="tdid" style="display:none">  
  227.                     <select id="ddlid" onchange="myFunction()"></select>  
  228.                 </td>  
  229.             </tr>  
  230.             <tr id="rowName">  
  231.                 <td>Name:</td>  
  232.                 <td><input type="text" id="tbName" /></td>  
  233.   
  234.             </tr>  
  235.             <tr id="rowLocation">  
  236.                 <td>Location:</td>  
  237.                 <td><input type="text" id="tbLocation" /></td>  
  238.             </tr>  
  239.   
  240.             <tr id="rowdept">  
  241.                 <td>Dept:</td>  
  242.                 <td>  
  243.                     <select id="tbLdept"></select>  
  244.                 </td>  
  245.             </tr>  
  246.   
  247.             <tr>  
  248.                  
  249.             </tr>  
  250.         </table>  
  251.     </form>  
  252.     <br />  
  253.     <button id="btnInsert" onclick="Insert()" style="color:green;display:block">Save</button>  
  254.     <button id="btnupdate" onclick="updte()" style="color:blue;display:none">Update</button>  
  255.     <button id="btndel" onclick="del()" style="color:red;display:none">Delete</button>  
  256.       
  257.     <br /><br />  
  258.     <button id="btnupdateshw" onclick="showupdte()" style="color:red">update Employee details</button>  
  259.     <button id="btndeleteshw" onclick="showdel()" style="color:blue">Delete Employee details</button>  
  260.     <button id="btninsertshw" onclick="showin()" style="color:green;display:none">save Employee details</button>  
  261.     <br /><br />  
  262.     <table id="tblGrid" cellpadding="10px" cellspacing="0" border="1">  
  263.         <tr style="background-color:black;color:white;font-size:18px;">  
  264.             <td >  
  265.                 ID  
  266.             </td>  
  267.             <td >  
  268.                 Name  
  269.             </td>  
  270.             <td >  
  271.                 Location  
  272.             </td>  
  273.             <td >  
  274.                 Department  
  275.             </td>  
  276.         </tr>  
  277.     </table>  
  278.     <br />  
  279.      
  280.    
  281. </body>  
  282. </html>  

Up Next
    Ebook Download
    View all
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs