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, update data, delete data, 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 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 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 string of 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 failure error 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 queried data set. Its data type is SQLResultSet, just like the DataTable in C#.
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 important property—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!