PhoneGap下Web SQL实践

HTML5里的Web SQL数据库，内置了SQLite数据库，

对数据库的操作使用executeSql执行增删改查

1. 创建数据库

function creatDatabase(){
      db = openDatabase('Student', '1.0', 'StuManage', 2 * 1024 * 1024);
 }

2. 创建表

function createTable(){

	if (db) {
        var strSQL = "create table if not exists StuInfo ";
        strSQL += " (StuID unique,Name text,Sex text,Score int)";
        db.transaction(function(tx) {
            tx.executeSql(strSQL)
        },
        function() {
            console.log("创建表错误");
        },
        function() {
            console.log("创建表成功");
        })
    }

3. 增加数据

function addData() {
    if (db) {

        var strSQL = "insert into StuInfo values";
        strSQL += "(?,?,?,?)";
        db.transaction(function(tx) {
            tx.executeSql(strSQL,[
			    $$("txtStuID").value,$$("txtName").value,
			    $$("selSex").value,$$("txtScore").value
			],
			function(){
				$$("txtName").value="";
	            $$("txtScore").value="";
			    alert("成功增加1条记录!");
			},
			function(tx,ex){
			     console.log(ex.message)
			})
        })

    }

}

4. 查询数据

function queryData(){
    if(db){
        var sql = "select * from StuInfo";
         db.transaction(function(tx) {
            tx.executeSql(sql,[],
			function(tx, results){
				var len = results.rows.length,i;
				var htmlData = "";
				for(i = 0; i < len; i++){
				    var item = results.rows.item(i);
				    var htmlitem = item.StuID + " " + item.Name + " " + item.Sex + " " + item.Score;
				    htmlData += htmlitem + "</br> ";

				}
				$$("info").innerHTML = htmlData;
			},
			function(tx,ex){
			     console.log(ex.message)
			})
        })
    }
}

5. 删除数据

function deleteData(){
    if (db) {

        var strSQL = "delete from StuInfo where StuID = ?";
        db.transaction(function(tx) {
            tx.executeSql(strSQL,[
			    $$("txtStuID").value
			],
			function(){
			    alert("成功删除1条记录!");
			},
			function(tx,ex){
			     console.log(ex.message)
			})
        })

    }

     queryData();
}

完整的Code如下

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <meta name="format-detection" content="telephone=no" />
    <meta name="msapplication-tap-highlight" content="no" />
    <meta name="viewport" content="user-scalable=no, initial-scale=1, maximum-scale=1, minimum-scale=1, width=device-width" />
    <meta http-equiv="Content-Security-Policy" content="default-src * 'unsafe-inline'; style-src 'self' 'unsafe-inline'; media-src *" />
    <link rel="stylesheet" href="css/jquery.mobile-1.4.5.min.css">
    <script type="text/javascript" src="js/jquery.js"></script>
    <script  type="text/javascript" src="js/jquery.mobile-1.4.5.min.js"></script>
    <script type="text/javascript" charset="utf-8" src="cordova.js" ></script>
    <title>Hello World</title>

<script type="text/javascript" charset="utf-8">
function $$(id) {
    return document.getElementById(id);
}
        var db;
        document.addEventListener('deviceready', onDeviceReady, false);
        function onDeviceReady(){
            var db = creatDatabase();
            createTable();
            queryData();
        }

 function creatDatabase(){
      db = openDatabase('Student', '1.0', 'StuManage', 2 * 1024 * 1024);
 }

 function createTable(){

	if (db) {
        var strSQL = "create table if not exists StuInfo ";
        strSQL += " (StuID unique,Name text,Sex text,Score int)";
        db.transaction(function(tx) {
            tx.executeSql(strSQL)
        },
        function() {
            console.log("创建表错误");
        },
        function() {
            console.log("创建表成功");
        })
    }
}


function addData() {
    if (db) {

        var strSQL = "insert into StuInfo values";
        strSQL += "(?,?,?,?)";
        db.transaction(function(tx) {
            tx.executeSql(strSQL,[
			    $$("txtStuID").value,$$("txtName").value,
			    $$("selSex").value,$$("txtScore").value
			],
			function(){
				$$("txtName").value="";
	            $$("txtScore").value="";
			    alert("成功增加1条记录!");
			},
			function(tx,ex){
			     console.log(ex.message)
			})
        })

    }

    queryData();
}

function queryData(){
    if(db){
        var sql = "select * from StuInfo";
         db.transaction(function(tx) {
            tx.executeSql(sql,[],
			function(tx, results){
				var len = results.rows.length,i;
				var htmlData = "";
				for(i = 0; i < len; i++){
				    var item = results.rows.item(i);
				    var htmlitem = item.StuID + " " + item.Name + " " + item.Sex + " " + item.Score;
				    htmlData += htmlitem + "</br> ";

				}
				$$("info").innerHTML = htmlData;
			},
			function(tx,ex){
			     console.log(ex.message)
			})
        })
    }
}

function deleteData(){
    if (db) {

        var strSQL = "delete from StuInfo where StuID = ?";
        db.transaction(function(tx) {
            tx.executeSql(strSQL,[
			    $$("txtStuID").value
			],
			function(){
			    alert("成功删除1条记录!");
			},
			function(tx,ex){
			     console.log(ex.message)
			})
        })

    }

     queryData();
}


    </script>
</head>

<body>
<div id="page1" data-role="page" data-add-back-btn="true">
    <div data-role="content">
        <fieldset>
            <legend>新增学生资料</legend>
            <span class="spanl">
            学号：<input type="text" id="txtStuID" size="10"><br>
            姓名：<input type="text" id="txtName"  size="15">
            </span>
            <span>
            性别：<select id="selSex">
              <option value="男">男</option>
              <option value="女">女</option>
            </select><br>
            总分：<input type="text" id="txtScore"  size="8">
            </span>
            <p class="btn">
                <input id="btnAdd" type="button" value="提交"  onClick="addData();">
            </p>

        </fieldset>
        <p id="info">显示结果</p>
        <input  type="button" value="删除记录"  onClick="deleteData();">
    </div>
</div>

</body>

</html>