HTML5 Web SQL Database
Web SQL is a database impersonation on a browser that you can use JS to manipulate SQL to read and write to your data.
The Web SQL Database API is not part of the HTML5 specification, but it is a stand-alone specification that introduces a set of APIs that use SQL to manipulate client databases.
If you are a web back-end programmer, it should be easy to understand what SQL does.
You can also refer to our SQL tutorial to learn more about database operations.
Web SQL Database works in the latest versions of Safari, Chrome and Opera browsers.
The core approach
The following are the three core methods defined in the specification:
- OpenDatabase: This method creates a database object using an existing database or a new database.
- Transaction: This method allows us to control a transaction and perform commits or rollbacks based on this situation.
- ExecuteSql: This method is used to perform actual SQL queries.
Open the database
We can use the openDatabase() method to open an existing database and, if the database does not exist, create a new database with the following code:
var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
Description of the five parameters corresponding to the openDatabase() method:
- The name of the database
- Version number
- The description text
- The size of the database
- Create a callback
The fifth argument, creating a callback, is called after the database is created.
Perform a query operation
To do this, 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 is created in the 'mydb' database.
Insert the data
After executing the table statement above, 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, "W3Cschool教程")');
tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "www.w3cschool.cn")');
});
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 instance are external variables, and executeSql maps each entry in the array parameters to "?".
Read the data
The following example shows 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, "W3Cschool教程")');
tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "www.w3cschool.cn")');
});
db.transaction(function (tx) {
tx.executeSql('SELECT * FROM LOGS', [], function (tx, results) {
var len = results.rows.length, i;
msg = "查询记录条数: " + len + "
";
document.querySelector('#status').innerHTML += msg;
for (i = 0; i < len; i++){
alert(results.rows.item(i).log );
}
}, null);
});
The full instance
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, "W3Cschool教程")');
tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "www.w3cschool.cn")');
msg = '<p>数据表已创建，且插入了两条数据。</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>查询记录条数: " + 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);
});
The results of the above examples are shown in the following image:
Delete the record
The format in which records are deleted is as follows:
db.transaction(function (tx) {
tx.executeSql('DELETE FROM LOGS WHERE id=1');
});
Removing the specified data id can also be dynamic:
db.transaction(function(tx) {
tx.executeSql('DELETE FROM LOGS WHERE id=?', [id]);
});
Update the record
The format used to update records is as follows:
db.transaction(function (tx) {
tx.executeSql('UPDATE LOGS SET log=\'www.w3cschool.cn\' WHERE id=2');
});
Updating the specified data id can also be dynamic:
db.transaction(function(tx) {
tx.executeSql('UPDATE LOGS SET log=\'www.w3cschool.cn\' WHERE id=?', [id]);
});
The full instance
<!DOCTYPE HTML> <html> <head> <meta charset="UTF-8"> <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, "W3Cschool教程")'); tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "www.w3cschool.cn")'); msg = '<p>数据表已创建，且插入了两条数据。</p>'; document.querySelector('#status').innerHTML = msg; }); db.transaction(function (tx) { tx.executeSql('DELETE FROM LOGS WHERE id=1'); msg = '<p>删除 id 为 1 的记录。</p>'; document.querySelector('#status').innerHTML = msg; }); db.transaction(function (tx) { tx.executeSql('UPDATE LOGS SET log=\'www.w3cschool.cn\' WHERE id=2'); msg = '<p>更新 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>查询记录条数: " + 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">状态信息</div> </body> </html>
The results of the above examples are shown in the following image:
