HTML5 Web SQL Database
The Web SQL Database API is not part of the HTML5 specification, but it 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 SQL operations.
You can also refer to our SQL tutorial to learn more about database operations.
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:
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 commit or rollback based on this situation.
executeSql: This method is used to execute the actual SQL query.
Open the database
If the database already exists, the openDatabase method is responsible for opening the database. If it does not exist, this method will create it.
Use the following code to create and open a database:
var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
The above method accepts the following five parameters:
Database name
Version number
Description text
Database size
Create callback
The last and fifth parameter, the create callback will be called after the database is created. However, even without this feature, the runtime will still create the database and the correct version.
Execute query
You need to use the database.transaction() function to execute queries. This function requires one parameter, which is a function responsible for actually executing the query, as shown below:
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)');
});The above query statement will create a table called LOGS in the 'mydb' database.
Insert operation
In order to create entries in the table, we add a simple SQL query to the above example, as follows:
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, "foobar")');
tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "logmsg")');
});You can also pass when creating entries Dynamic values as shown below:
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];
});Here e_id and e_log are external variables, executeSql will map each entry in the array parameter to "?".
Read operation
To read an already existing record, we can use a callback to capture the result, as shown below:
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, "foobar")');
tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "logmsg")');
});
db.transaction(function (tx) {
tx.executeSql('SELECT * FROM LOGS', [], function (tx, results) {
var len = results.rows.length, i;
msg = "<p>Found rows: " + len + "</p>";
document.querySelector('#status').innerHTML += msg;
for (i = 0; i < len; i++){
alert(results.rows.item(i).log );
}
}, null);
});Final example
<!DOCTYPE HTML>
<html>
<head>
<meta charset="UTF-8">
<title>web SQL</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, "")');
tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "www.php.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);
});
</script>
</head>
<body>
<div id="status" name="status">状态信息</div>
</body>
</html>Delete records
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 to update the record 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]);});