HTML 5 Web SQL Database

The Web SQL database API is not part of the HTML5 specification, but it is an independent specification that introduces a set of APIs for operating client databases using SQL.

If you are a web back-end programmer, it should be easy to understand the operation of SQL.

You can also refer to our SQL tutorial to learn more about database operations.

The Web SQL database can work in the latest versions of Safari, Chrome and Opera browsers.

Core method

The following are the three core methods defined in the specification:

  1. openDatabase : This method uses an existing database or a newly created database to create a database object.
  2. transaction : This method allows us to control a transaction, and perform commit or rollback based on this situation.
  3. executeSql : This method is used to execute the actual SQL query.

Open the database

We can use the openDatabase() method to open an existing database. If the database does not exist, a new database will be created. The code is as follows:

var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);

The five parameter descriptions corresponding to the openDatabase() method:

  1. Name database
  2. version number
  3. Description text
  4. Database size
  5. Create callback

The fifth parameter, the creation callback will be called after the database is created.

Perform query operations

To perform operations, use the database.transaction() function:

var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
db.transaction(function (tx) {  
   tx.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)');
});

After the above statement is executed, a table named LOGS will be created in the’mydb’ database.

Insert data

After executing the above create table statement, we can insert some data:

var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
db.transaction(function (tx) {
   tx.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)');
   tx.executeSql('INSERT INTO LOGS (id, log) VALUES (1, "菜鸟教程")');
   tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "www.runoob.com")');
});

We can also use dynamic values ​​to insert data:

var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
db.transaction(function (tx) {  
  tx.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)');
  tx.executeSql('INSERT INTO LOGS (id,log) VALUES (?, ?)', [e_id, e_log]);
});

The e_id and e_log in the example are external variables, and executeSql will map each entry in the array parameter to “?”.

Read data

The following example demonstrates how to read data that already exists in the database:

var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
 
db.transaction(function (tx) {
   tx.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)');
   tx.executeSql('INSERT INTO LOGS (id, log) VALUES (1, "Novice Tutorial")');
   tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "www.runoob.com")');
});
 
db.transaction(function (tx) {
   tx.executeSql('SELECT * FROM LOGS', [], function (tx, results) {
      var len = results.rows.length, i;
      msg = "<p>Number of query records: " + len + "</p>";
      document.querySelector('#status').innerHTML +=  msg;
    
      for (i = 0; i < len; i++){
         alert(results.rows.item(i).log );
      }
    
   }, null);
});

Complete example

Example

<!DOCTYPE HTML>
<html>
   <head>
      <meta charset="UTF-8">
      <title>Novice Tutorial</title> 
      <script type="text/javascript">
		
         var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
         var msg;
			
         db.transaction(function (tx) {
            tx.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)');
            tx.executeSql('INSERT INTO LOGS (id, log) VALUES (1, "Novice Tutorial")');
            tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "hackerthink.com")');
            msg = '<p>The data table has been created, and two pieces of data have been inserted.</p>';
            document.querySelector('#status').innerHTML =  msg;
         });

         db.transaction(function (tx) {
            tx.executeSql('SELECT * FROM LOGS', [], function (tx, results) {
               var len = results.rows.length, i;
               msg = "<p>Number of query records: " + len + "</p>";
               document.querySelector('#status').innerHTML +=  msg;
					
               for (i = 0; i < len; i++){
                  msg = "<p><b>" + results.rows.item(i).log + "</b></p>";
                  document.querySelector('#status').innerHTML +=  msg;
               }
            }, null);
         });
			
      </script>
		
   </head>
	
   <body>
      <div id="status" name="status">status information</div>
   </body>
	
</html>

Delete Record

The format used to delete records is as follows:

db.transaction(function (tx) {
    tx.executeSql('DELETE FROM LOGS  WHERE id=1');
});

Deleting the specified data id can also be dynamic:

db.transaction(function(tx) {
    tx.executeSql('DELETE FROM LOGS WHERE id=?', [id]);
});

update record

The format used for update records is as follows:

db.transaction(function (tx) {
    tx.executeSql('UPDATE LOGS SET log='www.w3cschool.cc' WHERE id=2');
});

Update the specified data id can also be dynamic:

db.transaction(function(tx) {
    tx.executeSql('UPDATE LOGS SET log='www.w3cschool.cc' WHERE id=?', [id]);
});

Complete example

Example

<!DOCTYPE HTML>
<html>
   <head>
      <meta charset="UTF-8">  
	   <title>Novice Tutorial</title> 
      <script type="text/javascript">
      
         var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
         var msg;
         
         db.transaction(function (tx) {
            tx.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)');
            tx.executeSql('INSERT INTO LOGS (id, log) VALUES (1, "Novice Tutorial")');
            tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "hackerthink.com")');
            msg = '<p>The data table has been created, and two pieces of data have been inserted.</p>';
            document.querySelector('#status').innerHTML =  msg;
         });

         db.transaction(function (tx) {
              tx.executeSql('DELETE FROM LOGS  WHERE id=1');
              msg = '<p>Delete the record with id 1.</p>';
              document.querySelector('#status').innerHTML =  msg;
         });

         db.transaction(function (tx) {
             tx.executeSql('UPDATE LOGS SET log='runoob.com' WHERE id=2');
              msg = '<p>Update the record with id 2.</p>';
              document.querySelector('#status').innerHTML =  msg;
         });

         db.transaction(function (tx) {
            tx.executeSql('SELECT * FROM LOGS', [], function (tx, results) {
               var len = results.rows.length, i;
               msg = "<p>Number of query records:" + len + "</p>";
               document.querySelector('#status').innerHTML +=  msg;
               
               for (i = 0; i < len; i++){
                  msg = "<p><b>" + results.rows.item(i).log + "</b></p>";
                  document.querySelector('#status').innerHTML +=  msg;
               }
            }, null);
         });
         
      </script>
      
   </head>
   
   <body>
      <div id="status" name="status">status information</div>
   </body>
   
</html>

sessionStorage object

The sessionStorage method stores data for a session. When the user closes the browser window, the data will be deleted.

How to create and access a sessionStorage:

Example

<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8"> 
<title>Novice Tutorial</title> 
<script>
function clickCounter()
{
	if(typeof(Storage)!=="undefined")
	{
		if (sessionStorage.clickcount)
		{
			sessionStorage.clickcount=Number(sessionStorage.clickcount)+1;
		}
		else
		{
			sessionStorage.clickcount=1;
		}
		document.getElementById("result").innerHTML="You have clicked the button in this conversation" + sessionStorage.clickcount + "Times";
	}
	else
	{
		document.getElementById("result").innerHTML="Sorry, your browser does not support web storage";
	}
}
</script>
</head>
<body>
<p><button onclick="clickCounter()" type="button">Check</button></p>
<div id="result"></div>
<p>Click this button to see the increase of the counter.</p>
<p>Close the browser tab (or window), reopen this page, and the counter will be reset.</p>
</body>
</html>

Web Storage develops a simple website listing program

The website list program implements the following functions:

  • You can enter the website name and URL, and save it in localStorage with the website name as the key;
  • According to the website name, find the URL;
  • List all currently saved websites;

The following code is used to save the search data:

// Save data  
function save ( ) { var siteurl = document . getElementById ( " siteurl " ) . value ;  
     var sitename = document . getElementById ( " sitename " ) . value ;  
     localStorage . setItem ( sitename , siteurl ) ;
     alert ( " Added successfully " ) ;
 }   
      
// Find data  
function find ( ) { var search_site = document . getElementById ( " search_site " ) . value ;  
     var sitename = localStorage . getItem ( search_site ) ;  
     var find_result = document . getElementById ( " find_result " ) ;  
     find_result . innerHTML = search_site +   
       " The URL is: " + sitename ;  
 }

The complete example demonstration is as follows:

Example

<!DOCTYPE html>
<html>  
<head>  
    <meta charset="utf-8">  
    <title>Web Storage of HTML5 Local Storage</title>  
</head>  
<body>  
    <div style="border: 2px dashed #ccc;width:320px;text-align:center;">     
        <label for="sitename">Site name(key):</label>  
        <input type="text" id="sitename" name="sitename" class="text"/>  
        <br/>  
        <label for="siteurl">Website(value):</label>  
        <input type="text" id="siteurl" name="siteurl"/>  
        <br/>  
        <input type="button" onclick="save()" value="Add record"/>  
        <hr/>  
        <label for="search_phone">Enter the site name:</label>  
        <input type="text" id="search_site" name="search_site"/>  
        <input type="button" onclick="find()" value="Find a website"/>  
        <p id="find_result"><br/></p>  
    </div>  
    <br/>  
    <div id="list">  
    </div>  
    <script>
	// Load all data stored in localStorage
	loadAll(); 	
		
    //save data
    function save(){  
        var siteurl = document.getElementById("siteurl").value;  
        var sitename = document.getElementById("sitename").value;  
        localStorage.setItem(sitename, siteurl);
        alert("Added successfully");
    }
    //Find data 
    function find(){  
        var search_site = document.getElementById("search_site").value;  
        var siteurl = localStorage.getItem(search_site);  
        var find_result = document.getElementById("find_result");  
        find_result.innerHTML = search_site + "The URL is:" + siteurl;  
    }
    //Extract all objects stored in localStorage and display them on the interface
    function loadAll(){  
        var list = document.getElementById("list");  
        if(localStorage.length>0){  
            var result = "<table border='1'>";  
            result += "<tr><td>key</td><td>value</td></tr>";  
            for(var i=0;i<localStorage.length;i++){  
                var sitename = localStorage.key(i);  
                var siteurl = localStorage.getItem(sitename);  
                result += "<tr><td>"+sitename+"</td><td>"+siteurl+"</td></tr>";  
            }  
            result += "</table>";  
            list.innerHTML = result;  
        }else{  
            list.innerHTML = "Data is empty...";  
        }  
    }      
    </script>
</body>  
</html>

Next we will use JSON.stringify to store object data, JSON.stringify can convert objects into strings.

var Site = new new Object ; ...
var STR = the JSON . the stringify ( Site ) ; // convert the object to a string   

Then we use the JSON.parse method to convert the string into a JSON object:

var Site = the JSON . the parse ( STR ) ; 

JavaScript implementation code:

// Save data  
function save ( ) { var site = new Object ;
     site . keyname = document . getElementById ( " keyname " ) . value ;
     site . sitename = document . getElementById ( " sitename " ) . value ;  
     site . siteurl = document . getElementById ( "   
      siteurl " ) . value ;
     var str = JSON . stringify ( site ) ; // convert the object to a string 
    localStorage . setItem ( site . keyname , str ) ;  
     alert ( " Saved successfully " ) ;
 } // Find data    

function find ( ) { var search_site = document . getElementById ( " search_site " ) . value ;  
     var str = localStorage . getItem ( search_site ) ;  
     var find_result = document . getElementById ( " find_result " ) ;
     var site = JSON . parse ( str   
        ) ;  
     find_result . innerHTML = search_site + " The site name is: " + site . sitename + " and the URL is: " + site . siteurl ;  
 }

The complete example is as follows:

Example

<!DOCTYPE html>
<html>  
<head>  
    <meta charset="utf-8">  
    <title>Web Storage of HTML5 Local Storage</title>  
</head>  
<body>  
    <div style="border: 2px dashed #ccc;width:320px;text-align:center;">
        <label for="keyname">Alias(key):</label>  
        <input type="text" id="keyname" name="keyname" class="text"/>  
        <br/>  
        <label for="sitename">Site name:</label>  
        <input type="text" id="sitename" name="sitename" class="text"/>  
        <br/>  
        <label for="siteurl">Website:</label>  
        <input type="text" id="siteurl" name="siteurl"/>  
        <br/>  
        <input type="button" onclick="save()" value="Add record"/>  
        <hr/>  
        <label for="search_phone">Enter alias(key):</label>  
        <input type="text" id="search_site" name="search_site"/>  
        <input type="button" onclick="find()" value="Find a website"/>  
        <p id="find_result"><br/></p>  
    </div>  
    <br/>  
    <div id="list">  
    </div>  
    <script>
    //save data  
    function save(){  
        var site = new Object;
        site.keyname = document.getElementById("keyname").value;
        site.sitename = document.getElementById("sitename").value;  
        site.siteurl = document.getElementById("siteurl").value;
        var str = JSON.stringify(site); // Convert object to string
        localStorage.setItem(site.keyname,str);  
        alert("Saved successfully");
    }  
    //Find data
    function find(){  
        var search_site = document.getElementById("search_site").value;  
        var str = localStorage.getItem(search_site);  
        var find_result = document.getElementById("find_result");
        var site = JSON.parse(str);  
        find_result.innerHTML = search_site + "The site name is:" + site.sitename + ",the website is:" + site.siteurl;  
    }  
    
    //Extract all objects stored in localStorage and display them on the interface
	// Make sure that the value corresponding to the stored keyname is the conversion object, otherwise JSON.parse will report an error
    function loadAll(){  
        var list = document.getElementById("list");  
        if(localStorage.length>0){  
            var result = "<table border='1'>";  
            result += "<tr><td>Alias</td><td>Site name</td><td>URL</td></tr>";  
            for(var i=0;i<localStorage.length;i++){ 
                var keyname = localStorage.key(i);  
                var str = localStorage.getItem(keyname);  
                var site = JSON.parse(str);  
                result += "<tr><td>"+site.keyname+"</td><td>"+site.sitename+"</td><td>"+site.siteurl+"</td></tr>";  
            }  
            result += "</table>";  
            list.innerHTML = result;  
        }else{  
            list.innerHTML = "Data is empty...";  
        }  
    }  
    </script>
</body>  
</html>