Detailed explanation of HTML5 local database instance

Web SQL Databases of HTML5 (html5 local database) are indeed very 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 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,updatedata,deletedata, and delete a table.
First introduce the three core methods
1. openDatabase: This method uses an existing database or creates a new databaseCreate a databaseObject.
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 Doesn't 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:\Users\Administrator\AppData\Local\Google\Chrome\User Data\Default\databases\http_localhost_4987.
What is created is a sqllite database. You can useSQLiteSpy 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 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 thestringof the query, and the SQL language used is SQLite 3.6.19.
2) Insert the string data where the question mark is in the query.
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 failureerror message.
Step 3: Perform add, delete, modify and query
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 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 it
The callback function that executes successfully in the above code has a parameter result.

result: The querieddata set. Itsdata typeis SQLResultSet, just like the DataTable inC#.
SQLResultSet is defined as:

The code is as follows:

interface SQLResultSet { readonly attribute long insertId; readonly attribute long rowsAffected; readonly attribute SQLResultSetRow List rows; };

The most importantproperty—rows of SQLResultSetRowList type is the "rows" of the data set.
rows has two attributes: length, 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 the data table

The code is as follows:

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

[Related recommendations]

1.Html5 free video tutorial

2.H5 code demonstration for making a timer

3.H5 detailed example of completing multiple image uploads

4.Detailed explanation of H5’s custom attribute data-*

5.Teach you how to implement an H5 micro-scene

The above is the detailed content of Detailed explanation of HTML5 local database instance. For more information, please follow other related articles on the PHP Chinese website!