HTML5 tutorial html 5 local database (Web Sql Database)_html5 tutorial skills

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 1: Open the connection and create the database

Copy code

The code is as follows:

var dataBase = openDatabase("student", "1.0", "Student Table", 1024 * 1024, function () { });
if (!dataBase) {
alert("Database creation failed!");
} else {
alert("Database created successfully!");
}

Explain it The openDatabase method opens an existing database and can also create a database if it does not exist. 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 use SQLiteSpy to open the file 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 data table

Copy code

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 table created successfully'); },
function(tx, error){ alert( 'Failed to create stu table:' error.message);
});
});
}

Explain,
executeSql function has four Parameters, their meanings are:
1) Represents the query string, and the SQL language used is SQLite 3.6.19.
2) The string data inserted 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 addition, deletion and modification check

1) Add data:

Copy code

The code is as follows:

this.insert = function () {
dataBase.transaction(function (tx) {
tx.executeSql(
"insert into stu (id, name) values(?, ?)",
[id, 'Xu Mingxiang'],
function () { alert('Data added successfully' ); },
function (tx, error) { alert('Failed to add data: ' error.message);
} );
});

2) Query data

Copy code

The code is as follows:

this .query = function () {
dataBase.transaction(function (tx) {
tx.executeSql(
"select * from stu", [],
function (tx, result) { / /Callback function for successful execution
//Do what you want to do with the 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 queried data set. Its data type is SQLResultSet, just like DataTable in C#.
SQLResultSet is defined as:

Copy code

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 SQLResultSetRowList type 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

Copy code

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('Update failed: ' error.message);
});
});
}

4) Delete data

Copy code

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('Deletion failed: ' error.message);
});
});
}

5) Delete data table

Copy code

The code is as follows:

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

web sql database added Delete and modify the demo, click to download.