Learn the four basic operations of HTML5 WebSQL through 90 lines of code

Web SQL database API is an independent specification, which provides local storage of structured data at the browser level, and has been supported by many modern browsers.

Let's use a simple example to see how to use Web SQL API to create database tables and store data on the browser side.

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") }); 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; ln +) {[xss_clean] ln ('id:'+ oResults.rows [I] .id); [xss_clean] ln ('user:' + oResults.rows [I] .user); [xss_clean] ln ('passwd:' + oResults.rows[ .passwd);} end = true } function work () {if (end) {clearInterval (handle);} else {console.log ("working..." + Date.now ());}} setupDB (); var handle = setInterval (work, 200)

Executing the application in a browser creates a database called mydb, in which a database table called "user" is created, and a record is inserted, then read from the database table and printed on the browser.

Let's analyze these 90 lines of code.

The entry of the program is the setupDB function, and the following setInterval executes the function with an interval of 200ms. In order to simulate the current browser in addition to the Web SQL database, there are other tasks to be executed.

SetupDB

A chained call is implemented with promise, and the ninth line of code is semantically easy to understand: first create a database (createDatabase), then create a database table (createTable), then insert a record into the database table (insertEntry), and then immediately read out the record you just inserted into the table (readEntry). Finally print it to the browser.

Take a look at my _ createDatabaseOK function on line 16, which delays execution by 1 second, just to demonstrate the best practices of WebSQL API asynchronous calls.

Line 15 of the createDatabase function calls Web SQL API's openDatabase to create a database called mydb. OpenDatabase returns a database handle, which we save in the property _ db for later use.

CreateTable

Using the database handle obtained in the previous step, perform a database transaction through the API transaction exposed by the database handle. The specific content of the transaction is a SQL statement that creates the database table through an executeSql call:

Create table if not exists user (id unique, user, passwd)

Friends who have used JDBC should be familiar with this method of writing.

The database indicates user, the primary key is id, and there are two columns user and passwd.

InsertEntry

Insert a row of records with an id of 1 and a user value of 1234 in the user database table created in the previous step.

Insert into user values (1 recordable Jerryboy page1234')

ReadEntry

Again, through the database handle _ db created in the first step, execute a database transaction with a SELECT statement that reads all records from the user table.

If you want to clear the database tables in Web SQL, click Clear storage in the Chrome developer tool:

Select the Storage type you want to clear and click "Clear Site Data".

For more original Jerry technical articles, please follow the official account "Wang Zixi" or scan the following QR code: