The Web SQL Database API is not actually an integral part of the HTML5 specification, but a separate specification. It uses a set of APIs to manipulate the client's database. Mainstream browsers such as Safari, Chrome, Firefox, and Opera have already supported Web SQL Database. HTML5's Web SQL Databases are really tempting. When you find that you can use the same query statement as mysql query to operate the local database, you will find this thing very interesting. Today, let’s learn about the Web SQL Database API of HTML 5.
The following will introduce how to create and open a database, create a table, add data, update data, delete data, and delete tables.
First introduce three core methods
1. openDatabase: This method uses an existing database or creates a new database to create a database object.
2. Transaction: This method allows us to control transaction commit or rollback according to the situation.
3. executeSql: This method is used to execute real SQL queries.
Step 1: Open the connection and create the database
var dataBase = openDatabase("student", "1.0", "Student Table", 1024 * 1024, function () { });
if (!dataBase) {
alert("Database creation failed!");
} else {
alert("Database creation successfully!");
}
Explain the openDatabase method opens an existing database, and if the database does not exist, it can also create a database. Several parameters are:
1. Database name.
2. The version number is currently 1.0. No matter what, it is OK if you write it to death.
3. Description of the database.
4. Set the size of the data.
5. Callback function (can be omitted).
Create a database on the first call, and then establish a connection.
The created database is local, with the path as follows:
C:/Users/Administrator/AppData/Local/Google/Chrome/User Data/Default/databases/http_localhost_4987 .
The created SQLite database can be used to open the file with SQLiteSpy and you can see the data inside. SQLiteSpy is a green software. You can download the address on Baidu or the official SQLiteSpy download: SQLiteSpy.
Step 2: Create a data table
this.createTable=function() {
dataBase.transaction( function(tx) {
tx.executeSql(
"create table if not exists stu (id REAL UNIQUE, name TEXT)",
[],
function(tx,result){ alert('created stu table successfully'); },
function(tx, error){ alert('Create stu table failed:' + error.message);
});
});
}
Explain it,
The executeSql function has four parameters, and its meaning is:
1) Represents the query string, and the SQL language used is SQLite 3.6.19.
2) Insert string data into the query where the question mark is located.
3) Callback function executed when successful. Returns two parameters: tx and the result of execution.
4) A callback function executed on failure. Returns two parameters: tx and failed error message.
Step 3: Perform addition, deletion, modification and search
1) Add data:
this.insert = function () {
dataBase.transaction(function (tx) {
tx.executeSql(
"insert into stu (id, name) values(?, ?)",
[id, 'Xu Mingxiang'],
function () { alert('Add data successfully'); },
function (tx, error) { alert('Add data failed: ' + error.message);
} );
});
2) Query data
this.query = function () {
dataBase.transaction(function (tx) {
tx.executeSql(
"select * from stu", [],
function (tx, result) { //Execute successful callback function
//Do what you want to do to result here.........
},
function (tx, error) {
alert('Query failed: ' + error.message);
} );
});
}
Explain it
The successfully executed callback function in the above code has a parameter result.
result: The queryed data set. Its data type is SQLResultSet, just like the DataTable in C#.
The definition of SQLResultSet is:
interface SQLResultSet {
readonly attribute long insertId;
readonly attribute long rowsAffected;
readonly attribute SQLResultSetRowList rows;
};
The most important property—SQLResultSetRowList type rows are the rows of the dataset.
rows has two properties: length and item.
Therefore, get the value of a row and a column of the query result: result.rows[i].item[fieldname].
3) Update data
this.update = function (id, name) {
dataBase.transaction(function (tx) {
tx.executeSql(
"update stu set name = ? where id= ?",
[name, id],
function (tx, result) {
},
function (tx, error) {
alert('Update failed: ' + error.message);
});
});
}
4) Delete data
this.del = function (id) {
dataBase.transaction(function (tx) {
tx.executeSql(
"delete from stu where id= ?",
[id],
function (tx, result) {
},
function (tx, error) {
alert('Deletion failed: ' + error.message);
});
});
}
5) Delete the data table
this.dropTable = function () {
dataBase.transaction(function (tx) {
tx.executeSql('drop table stu');
});
}
web sql database demo for adding, deleting, modifying and checking, download it quickly.