HTML5 tutorial html 5 local database (Web Sql Database)

HTML5 tutorial html 5 local database (Web Sql Database)

HTML5's Web SQL Databases (html5 local database) is indeed very tempting. When you find that you can use the same query statement as mysql query to operate the local database, you will I found this thing quite interesting. Today, let’s learn about HTML 5’s Web SQL Database API: openDatabase, transaction, executeSql.

The Web SQL Database API is not actually part of the HTML5 specification, but a separate specification. It manipulates the client's database through a set of APIs. Mainstream browsers such as Safari, Chrome, Firefox, and Opera already support Web SQL Database. HTML5's Web SQL Databases are indeed very tempting. When you find that you can use the same query statement as a mysql query to operate a local database, you will find this thing quite interesting. Today, let’s learn about HTML 5’s Web SQL Database API.

The following will introduce one by one how to create and open a database, create a table, add data, update data, delete data, and delete a table.

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 submission or rollback according to the situation.

3. executeSql: This method is used to execute real SQL queries.

Step one: Open the connection and create the database

The code is as follows:

var dataBase = openDatabase("student", "1.0", "学生表", 1024 * 1024, function () { });
if (!dataBase) {
alert("数据库创建失败！");
} else {
alert("数据库创建成功！");
}

Explain the openDatabase method to open an existing database. If the database does not exist, it can also create a database. The meanings of several parameters are:
1, database name.
2. The version number is currently 1.0. Leave it alone and just write it to death.
3. Description of the database.
4. Set the data size.
5, callback function (can be omitted).
Create the database when calling for the first time, and then establish the connection.
The created database exists locally, and the path is as follows:
C:UsersAdministratorAppDataLocalGoogleChromeUser DataDefaultdatabaseshttp_localhost_4987.
What is created is a sqllite database. You can open the file with SQLiteSpy and see the data inside. SQLiteSpy is a green software. You can download it from Baidu or the official SQLiteSpy download address: SQLiteSpy.


Step 2: Create a data table

The code is as follows:

this.createTable=function() {
dataBase.transaction( function(tx) { 
tx.executeSql(
"create table if not exists stu (id REAL UNIQUE, name TEXT)", 
[], 
function(tx,result){ alert('创建stu表成功'); }, 
function(tx, error){ alert('创建stu表失败:' + error.message); 
});
});
}

Explain,
executeSql function has four parameters, their meanings are:
1) Represents the query string, the SQL used The language is SQLite 3.6.19.
2) Insert the string data into the query where the question mark is.
3) Callback function executed when successful. Returns two parameters: tx and the result of execution.
4) A callback function executed when failure occurs. Returns two parameters: tx and failure error message.



Step 3: Perform CRUD

1) Add data:

The code is as follows:

this.insert = function () {
dataBase.transaction(function (tx) {
tx.executeSql(
"insert into stu (id, name) values(?, ?)",
[id, '徐明祥'],
function () { alert('添加数据成功'); },
function (tx, error) { alert('添加数据失败: ' + error.message); 
} );
});

2) Query the data

The code is as follows:

this.query = function () {
dataBase.transaction(function (tx) {
tx.executeSql(
"select * from stu", [],
function (tx, result) { //执行成功的回调函数
//在这里对result 做你想要做的事情吧...........
},
function (tx, error) {
alert('查询失败: ' + error.message);
} );
});
}

Explain
The callback function that executes successfully in the above code has a parameter result.

result: The queried data set. Its data type is SQLResultSet, just like DataTable in C#.
SQLResultSet is defined as:

The code is as follows:

interface SQLResultSet {
readonly attribute long insertId;
readonly attribute long rowsAffected;
readonly attribute SQLResultSetRowList rows;
};

The most important attribute—rows of type SQLResultSetRowList is the “row” of the data set.
rows has two attributes: length and item.
So, get the value of a certain row and column of the query result: result.rows[i].item[fieldname].

3) Update data

The code is as follows:

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('更新失败: ' + error.message);
});
});
}

4) Delete data

The code is as follows:

this.del = function (id) {
dataBase.transaction(function (tx) {
tx.executeSql(
"delete from stu where id= ?",
[id],
function (tx, result) {
},
function (tx, error) {
alert('删除失败: ' + error.message);
});
});
}

5) Delete data table

The code is as follows:

this.dropTable = function () {
dataBase.transaction(function (tx) {
tx.executeSql('drop table stu');
});
}