HTML5 web storage-web SQL sample code analysis-H5 Tutorial-php.cn

HTML5 web storage-web SQL sample code analysis

黄舟

Release： 2017-03-16 16:16:10

Original

1535 people have browsed it

What is web SQL?

We often deal with structured data in large quantities in our applications, HTML5 introduces the concept of web SQL database, which Allows applications to access SQLlite databases through the asynchronous Javascript interface. But currently web SQL is not in the HTML5 specification, but a separate specification. Safari, Chr#ome, and Oprea browsers support web SQL.

Three core methods

Three core methods defined in the Web SQL Database specification:

##op
enDatabase: This method uses an existing database or a new database to create a database object
trans
action: This method allows us Control transaction commit or rollback according to the situation
executeSql: This method is used to execute SQL
query

openDatabase

Syntax: var db = openDatabase(name,version,

displayName,estimatedSize,creationCallback); name: database name
version: database version number
displayName:database description
estimatedSize: database storage data size, in bytes
creationCallback:
Callback function, optional

transaction

The transaction method is used to process transactions. When a statement fails to execute, the entire transaction is rolled back. The method has three parameters: a method containing transaction content, a callback function for success (optional), and a callback function for failure (optional).

db.transaction(function (context) {
           context.executeSql(&#39;CREATE TABLE IF NOT EXISTS testTable (id unique, name)&#39;);
           context.executeSql(&#39;INSERT INTO testTable (id, name) VALUES (0, "Byron")&#39;);
           context.executeSql(&#39;INSERT INTO testTable (id, name) VALUES (1, "Casper")&#39;);
           context.executeSql(&#39;INSERT INTO testTable (id, name) VALUES (2, "Frank")&#39;);
         });

Copy after login

In this example, we create a table testTable and insert three pieces of data into the table. If any of the four execution statements fails, the entire transaction will be rolled back.

executeSql

The executeSql method is used to execute SQL statements and return results. The method has four parameters:

- Query
String - Parameters used to replace question marks in the query string
- Execution success callback function (optional)
- Execution failure callback function (optional)
grammar:
trans.executeSql(sql,[],function(){trans,data},function(trans,msg){});

Example

<!doctype html>
<html>
<head>
    <meta charset="utf-8">
    <title>web SQL demo</title>
    <script src="jquery-3.0.0.min.js"></script>
    <script>
        //创建或连接数据库
        function getDb()
        {
            var db = openDatabase("data.db","1.0","demo",1024*1024);
            return db;
        }
        //初始化数据库
        function initDb()
        {
            var db = getDb();
            if(db == null)
            {
                alert("你的浏览器不支持web SQL");
                return null;
            }
            db.transaction(function(trans){
                trans.executeSql("create table if not exists demo(name text null,title text null,content text null)",[],function(trans,result){},
                function(trans,error){alert(error)})
            });
        }

        $(function(){
                var db = getDb();
                initDb();
                $("#btnSave").click(function(){
                    var name = $("#name").val();
                    var title = $("#title").val();
                    var content = $("#content").val();
                    //执行sql脚本来插入数据
                    db.transaction(function(trans){
                        trans.executeSql("insert into demo(name,title,content)values(?,?,?)",
                                        [name,title,content],
                                        function(trans,success){},
                                        function(trans,error){alert(error);}
                                        );
                    showAll();
                    });

                });             
            })

        //显示所有数据
        function showAll()
        {
            $("#tblData").empty();
            var db = getDb();
            db.transaction(function(trans){
                trans.executeSql("select * from demo",[],function(trans,data){
                    //获取查询到的数据
                    if(data)
                    {
                        for(var i=0;i<data.rows.length;i++)
                        {
                            //获取一行json数据，将数据拼接成表格
                            appendDataToTable(data.rows.item(i));
                        }
                    }
                },function(trans,error){alert(error);});
            });
        }

        function appendDataToTable(data)
        {
            var name = data.name;
            var title = data.title;
            var content = data.content;
            var strHtml = "";
            strHtml += "<tr>";
            strHtml += "<td>"+name+"</td>";
            strHtml += "<td>"+title+"</td>";
            strHtml += "<td>"+content+"</td>";
            strHtml += "</tr>";

            $("#tblData").append(strHtml);
        }
    </script>
</head>
<body>
    <table>
        <tr>
            <td>用户名：</td>
            <td><input type="text" name="name" id="name" required/></td>
        </tr>
        <tr>
            <td>标题</td>
            <td><input type="text" name="title" id="title" required/></td>
        </tr>
        <tr>
            <td>留言</td>
            <td><input type="text" name="content" id="content" required/></td>
        </tr>
    </table>
    <input type="button" value="保存" id="btnSave"/>
    <br/>
    <input type="button" value="展示所有数据" onclick="showAll();"/>
    <table id="tblData"></table>
</body>
</html>

Copy after login

The running results are as follows:

HTML5 web storage-web SQL sample code analysis

The above is the detailed content of HTML5 web storage-web SQL sample code analysis. For more information, please follow other related articles on the PHP Chinese website!