The Web SQL database API is a standalone specification that provides native storage of structured data at the browser level and is already supported by many modern browsers.
Let’s look at a simple example of how to use the Web SQL API to create a database table and store data on the browser side.
<! doctypehtml>
<html>
<head>
<script>
var end;
function setupDB() {
return this.createDatabase().then(createTable).then(insertEntry).then(readEntry).then(printResult);
}
function createTable() {
return new Promise(function(resovle, reject) {
console.log("prepare to create table..." + Date.now());
this._db.transaction(function(query) {
query.executeSql('create table if not exists user(id unique, user, passwd)');
});
setTimeout(_createTableOK.bind(this, resovle), 1000);
});
}
function _createTableOK(resovle) {
console.log("table created successfully..." + Date.now());
resovle();
}
function createDatabase() {
return new Promise(function(resovle, reject) {
console.log("prepare to create database..." + Date.now());
this._db = openDatabase('mydb'.'1.0'.'JerryTestdb'.1024);
setTimeout(_createDatabaseOK.bind(this, resovle), 1000);
});
}
function _createDatabaseOK(resovle) {
console.log("database created successfully..." + Date.now());
resovle(this._db);
}
function insertEntry() {
return new Promise(function(resolve, reject) {
this._db.transaction(function(query) {
query.executeSql("insert into user values (1,'Jerry','1234')");
});
setTimeout(_insertEntryOK.bind(this, resolve), 1000);
});
}
function _insertEntryOK(resolve) {
console.log("entry inserted to table successfully..." + Date.now());
resolve();
}
function readEntry() {
return new Promise(function(resolve, reject) {
this._db.transaction(function(query) {
query.executeSql('select * from user'[],function(u, results) {
setTimeout(_readEntryOK.bind(this, resolve, results), 1000);
}); // end of query.executeSql
} // end of function(query)
); // end of this._db.transaction
});
}
function _readEntryOK(resolve, oResult) {
console.log("entry readed from DB successfully..." + Date.now());
resolve(oResult);
}
function printResult(oResults) {
for (var i = 0; i < oResults.rows.length; i++) {
document.writeln('id: ' + oResults.rows[i].id);
document.writeln('user: ' + oResults.rows[i].user);
document.writeln('passwd: ' + oResults.rows[i].passwd);
}
end = true;
}
function work() {
if (end) {
clearInterval(handle);
} else {
console.log(" working..." + Date.now());
}
}
setupDB();
var handle = setInterval(work, 200);
</script>
</head>
</html>
Copy the codeRunning the application in a browser creates a database named myDB, a database table named “user”, and inserts a record into it, which is then read from the database table and printed to the browser.
Let’s examine those 90 lines of code.
The entry point of the program is the function setupDB. The following setInterval executes the function at an interval of 200 milliseconds, in order to simulate the current browser in addition to the Web SQL database, there are other tasks to execute.
setupDB
A chain call is implemented with promise, and line 9 is semantically easy to understand: First create a createDatabase, then create a createTable, then insert a record into the database table, and then immediately read out the entry. Finally, print it to the browser.
As you can see from my _createDatabaseOK function on line 16, the execution is delayed by one second, just to demonstrate best practices for WebSQL API asynchronous calls.
Line 15 of the createDatabase function calls openDatabase of the Web SQL API to create a database named myDB. OpenDatabase returns a database handle, which we store in the _db property for later use.
createTable
Using the database handle obtained in the previous step, a database transaction is executed through the API transaction exposed by the database handle. The transaction is a SQL statement that creates database tables through executeSql calls:
create table if not exists user(id unique, user, passwd)
Those of you who have used JDBC should be familiar with this approach.
The database representation is user, the primary key is ID, and there are two columns user and passwd.
insertEntry
Insert a row with id 1, user value Jerry, and passwd 1234 into the user database table created in the previous step.
Insert into user values (1, ‘Jerry’, ‘1234’)
readEntry
Using the database handle _db created in the first step, a database transaction is executed with a SELECT statement reading all records from the user table.
To Clear a database table from Web SQL, click Clear Storage in Chrome Developer Tools:
Select the type of Storage you want to Clear and click Clear Site Data.
For more of Jerry’s original technical articles, please follow the public account “Wang Zixi” or scan the following QR code:
