如何访问和更新存储在本地[资产文件夹]在项目文件中使用cordova的Sqlite数据库文件 [英] How to access and update Sqlite db file stored locally[asset folder] in project file using cordova

问题描述

我有一个 sqlite db 文件存储在 assets 文件夹中 cordova 项目，我想在我的应用程序内部访问，也想更新db。

到目前为止，我看到我发现有 cordova s​​qlite插件，它可以创建一个sqlite数据库并更新它，但不知道如何从cordova中的资产文件夹更新现有的数据库文件

这是我的下面的代码：

 <！DOCTYPE html> 
< html> 
< head> 
< title>存储示例< / title> 
 
< script type =text / javascriptcharset =utf-8src =cordova-2.0.0.js>< / script& 
< script type =text / javascriptcharset =utf-8src =index.js>< / script> 
< meta name =viewportcontent =width = device-width，initial-scale = 1> 
< link href =css / conf-room1.min.css =stylesheet/> 
< link href =css / jquery.mobile.icons.min.css =stylesheet/> 
< link href =css / jquery.mobile.structure-1.4.4.min.css =stylesheet/> 
< link href =css / app.css =stylesheet/> 
< link href =css / custom_color.css =stylesheet/> 
< script src =js / jquery-2.1.3.min.js>< / script> 
< script src =js / jquery.mobile-1.4.4.min.js>< / script> 
< script src =cordova.js>< / script> 
< script src =js / jquery-2.1.3.min.js>< / script> 
< script src =js / jquery.mobile-1.4.4.min.js>< / script> 
< script src =// ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js\"> ;</script> 
< script src =http://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js>< / script> 
 
 
< script> 
 
 document.addEventListener（deviceready，onDeviceReady，false）; 
 
 var db =; 
 
 function populateDB（tx）{
 tx.executeSql（'DROP TABLE IF EXISTS SoccerPlayer'）; 
 tx.executeSql（'CREATE TABLE IF NOT EXISTS SoccerPlayer（Name TEXT NOT NULL，Club TEXT NOT NULL）'）; 
 tx.executeSql（'INSERT INTO SoccerPlayer（Name，Club）VALUES（Test1，Team1）'）; 
 tx.executeSql（'INSERT INTO SoccerPlayer（Name，Club）VALUES（Test2，Team2）'）; 
} 
 
 function queryDB（tx）{
 tx.executeSql（'SELECT * FROM SoccerPlayer'，[]，querySuccess，errorCB）; 
} 
 
 
 function querySuccess（tx，result）{
 var playerlist = document.getElementById（SoccerPlayerList）; 
 var players =; 
 alert（The show is on）; 
 var len = result.rows.length; 
 for（var i = 0; i< len; i ++）{
 alert（result.rows.item（i）.Name + result.rows.item（i）.Club）; 
 players = players +'< li>< a href =＃>< p class =record>'+ result.rows.item（i）.Name +'< / p> ;< p class =small> Club'+ result.rows.item（i）.Club +'< / p>< / a>< / li> 
} 
 
 playerlist.innerHTML = players; 
 $（＃SoccerPlayerList）。listview（refresh）; 
} 
 
 
 function errorCB（err）{
 alert（Error processing SQL：+ err.code）; 
} 
 
 
 function successCB（）{
 db.transaction（queryDB，errorCB）; 
} 
 
 
 function onDeviceReady（）{
<！ -  db = window.openDatabase（SimpleTestOne，1.0，PhoneGap Demo 200000）;  - 
 db = window.openDatabase（{name：SimpleTestOne.db，createFromLocation：1}）; 
 db.transaction（populateDB，errorCB，successCB）; 
} 
< / script> 
 
 
< / head> 
< body> 
 
< div data-role =page> 
< div data-role =headerdata-position =fixeddata-theme =b> 
< h1>足球运动员< / h1> 
< / div> 
< div data-role =content> 
< ul id =SoccerPlayerList> 
< / ul> 
< / div> 
< / div> 
<！ - 足球运动员的结束页---> 
< / body> 
< / html> 
  

注意：我的资产文件夹中有SimpleTestOne.db >

解决方案

您可以尝试使用下面的代码段。 / p>

此代码段包含从数据库创建到插入，编辑，删除，更新和显示数据的所有部分数据库。

注意： 在此处更改您的数据库名称和表名称以及数据库字段。

 < script type =text / javascriptcharset =utf-8> 
 
 //等待Cordova加载
 // 
 document.addEventListener（deviceready，onDeviceReady，false）; 
 
 var currentRow; 
 //填充数据库
 // 
函数populateDB（tx）{
 tx.executeSql（'CREATE TABLE IF NOT EXISTS DEMO（id INTEGER PRIMARY KEY AUTOINCREMENT，name，number ）'）; 
} 
 
 //查询数据库
 // 
 function queryDB（tx）{
 tx.executeSql（'SELECT * FROM DEMO'，[ ]，querySuccess，errorCB）; 
} 
 
 function searchQueryDB（tx）{
 tx.executeSql（SELECT * FROM DEMO where name like（'％）+ document.getElementById（txtName）。value +％'），
 []，querySuccess，errorCB）; 
} 
 //查询成功回调
 // 
 function querySuccess（tx，results）{
 var tblText ='< table id =t01> ;< tr>< th> ID< / th> < th>名称< / th> < th>号< / th>< / tr>'; 
 var len = results.rows.length; 
 for（var i = 0; i< len; i ++）{
 var tmpArgs = results.rows.item（i）.id +，'+ results.rows.item（i） .name 
 +'，'+ results.rows.item（i）.number +'; 
 tblText + ='< tr onclick =goPopup（'+ tmpArgs +'）;>< td>'+ results.rows.item（i）.id +'< / td> ; td>'
 + results.rows.item（i）.name +'< / td>< td>'+ results.rows.item（i）.number +'< / td> ; / tr>'; 
} 
 tblText + =< / table>; 
 document.getElementById（tblDiv）。innerHTML = tblText; 
} 
 
 //删除查询
 function deleteRow（tx）{
 tx.executeSql（'DELETE FROM DEMO WHERE id ='+ currentRow，[]，queryDB ，errorCB）; 
} 
 
 //事务错误回调
 // 
函数errorCB（err）{
 alert（Error processing SQL：+ err.code ）; 
} 
 
 //事务成功回调
 // 
 function successCB（）{
 var db = window.openDatabase（Database，1.0 ，Cordova Demo，200000）; 
 db.transaction（queryDB，errorCB）; 
} 
 
 // Cordova准备好了
 // 
 function onDeviceReady（）{
 var db = window.openDatabase（Database，1.0 ，Cordova Demo，200000）; 
 db.transaction（populateDB，errorCB，successCB）; 
} 
 
 //插入查询
 // 
 function insertDB（tx）{
 tx.executeSql（'INSERT INTO DEMO（name，number） VALUES（'+ document.getElementById（txtName）。value 
 +'，'+ document.getElementById（txtNumber）。value +'）'）; 
} 
 
 function goInsert（）{
 var db = window.openDatabase（Database，1.0，Cordova Demo，200000）; 
 db.transaction（insertDB，errorCB，successCB）; 
} 
 
 function goSearch（）{
 var db = window.openDatabase（Database，1.0，Cordova Demo，200000）; 
 db.transaction（searchQueryDB，errorCB）; 
} 
 
 function goDelete（）{
 var db = window.openDatabase（Database，1.0，Cordova Demo，200000）; 
 db.transaction（deleteRow，errorCB）; 
 document.getElementById（'qrpopup'）。style.display ='none'; 
} 
 
 //点击表中的一行后显示弹出窗口
 // 
 function goPopup（row，rowname，rownum）{
 currentRow =行; 
 document.getElementById（qrpopup）。style.display =block; 
 document.getElementById（editNameBox）。value = rowname; 
 document.getElementById（editNumberBox）。value = rownum; 
} 
 
 function editRow（tx）{
 tx.executeSql（'UPDATE DEMO SET name ='+ document.getElementById（editNameBox）。value + 
 ''，number ='+ document.getElementById（editNumberBox）。value +'WHERE id ='
 + currentRow，[]，queryDB，errorCB）; 
} 
 function goEdit（）{
 var db = window.openDatabase（Database，1.0，Cordova Demo，200000）; 
 db.transaction（editRow，errorCB）; 
 document.getElementById（'qrpopup'）。style.display ='none'; 
} 
 
< / script> 
  

我使用这个代码为我的一个应用程序，它的工作正常。

注意： 请将您的数据库文件放在wwww目录中，例如：projectpath / www / dbname.db 。

用于从资源文件夹访问db文件。

  openDatabase（{name：dbname.db，createFromLocation：1}）; 
   

编辑2 ：

$ b b

只需在您的应用程式中安装下列插件。

  plugin add https://github.com/brodysoft/Cordova-SQLitePlugin.git 
  

尝试这个。

  var db = window.sqlitePlugin.openDatabase my.db，createFromLocation：1}）; 
  

I have a sqlite db file stored in assets folder of my cordova project ,which i want to access inside my application and also want to update the db.

So far i had a look on i found there cordova sqlite plug-in which can create a sqlite db and update it,but not sure how to update a existing db file from asset folder in cordova

Here is my below code:

<!DOCTYPE html>
<html>
<head>
    <title>Storage Example</title>

    <script type="text/javascript" charset="utf-8" src="cordova-2.0.0.js"></script>
    <script type="text/javascript" charset="utf-8" src="index.js"></script>
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link href="css/conf-room1.min.css" rel="stylesheet" />
    <link href="css/jquery.mobile.icons.min.css" rel="stylesheet" />
    <link href="css/jquery.mobile.structure-1.4.4.min.css" rel="stylesheet" />
    <link href="css/app.css" rel="stylesheet" />
    <link href="css/custom_color.css" rel="stylesheet" />
    <script src="js/jquery-2.1.3.min.js"></script>
    <script src="js/jquery.mobile-1.4.4.min.js"></script>
    <script src="cordova.js"></script>
    <script src="js/jquery-2.1.3.min.js"></script>
    <script src="js/jquery.mobile-1.4.4.min.js"></script>
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
    <script src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>


    <script>

    document.addEventListener("deviceready", onDeviceReady, false);

var db = "";

function populateDB(tx) {
    tx.executeSql('DROP TABLE IF EXISTS SoccerPlayer');
    tx.executeSql('CREATE TABLE IF NOT EXISTS SoccerPlayer (Name TEXT NOT NULL, Club TEXT NOT NULL)');
    tx.executeSql('INSERT INTO SoccerPlayer(Name,Club) VALUES ("Test1", "Team1")');
    tx.executeSql('INSERT INTO SoccerPlayer(Name,Club) VALUES ("Test2", "Team2")');
}

function queryDB(tx) {
    tx.executeSql('SELECT * FROM SoccerPlayer', [], querySuccess, errorCB);
}


function querySuccess(tx,result){
        var playerlist = document.getElementById("SoccerPlayerList");
        var players = "";
        alert("The show is on");
        var len = result.rows.length;
        for (var i=0; i<len; i++){
            alert(result.rows.item(i).Name + result.rows.item(i).Club);
            players = players + '<li><a href="#"><p class="record">'+result.rows.item(i).Name+'</p><p class="small">Club '+result.rows.item(i).Club+'</p></a></li>';
        }

        playerlist.innerHTML = players;
        $("#SoccerPlayerList").listview("refresh");
}


function errorCB(err) {
    alert("Error processing SQL: "+err.code);
}


function successCB() {
    db.transaction(queryDB, errorCB);
}


function onDeviceReady() {
    <!--db = window.openDatabase("SimpleTestOne", "1.0", "PhoneGap Demo", 200000);-->
   db =  window.openDatabase({name: "SimpleTestOne.db", createFromLocation: 1});
    db.transaction(populateDB, errorCB, successCB);
}
    </script>


</head>
<body>

<div data-role="page">
    <div data-role="header" data-position="fixed" data-theme="b">
        <h1>Soccer Player</h1>
    </div>
    <div data-role="content">
        <ul id="SoccerPlayerList">
        </ul>
    </div>
</div>
<!--end of Soccer Player Page--->
</body>
</html>

Note: I have SimpleTestOne.db in my assets folder

解决方案

You can try with below Snippet.

This snippet contains all the part from Data base creation to Insert , Edit , Delete , Update and Display data of your database.

Note: Change your Database name and Table name and Database fields here whatever you want to keep.

<script type="text/javascript" charset="utf-8">

        // Wait for Cordova to load
        //
        document.addEventListener("deviceready", onDeviceReady, false);

        var currentRow;
        // Populate the database
        //
        function populateDB(tx) {
            tx.executeSql('CREATE TABLE IF NOT EXISTS DEMO (id INTEGER PRIMARY KEY AUTOINCREMENT, name,number)');
        }

        // Query the database
        //
        function queryDB(tx) {
            tx.executeSql('SELECT * FROM DEMO', [], querySuccess, errorCB);
        }

        function searchQueryDB(tx) {
            tx.executeSql("SELECT * FROM DEMO where name like ('%"+ document.getElementById("txtName").value + "%')",
                    [], querySuccess, errorCB);
        }
        // Query the success callback
        //
        function querySuccess(tx, results) {
            var tblText='<table id="t01"><tr><th>ID</th> <th>Name</th> <th>Number</th></tr>';
            var len = results.rows.length;
            for (var i = 0; i < len; i++) {
                var tmpArgs=results.rows.item(i).id + ",'" + results.rows.item(i).name
                        + "','" + results.rows.item(i).number+"'";
                tblText +='<tr onclick="goPopup('+ tmpArgs + ');"><td>' + results.rows.item(i).id +'</td><td>'
                        + results.rows.item(i).name +'</td><td>' + results.rows.item(i).number +'</td></tr>';
            }
            tblText +="</table>";
            document.getElementById("tblDiv").innerHTML =tblText;
        }

        //Delete query
        function deleteRow(tx) {
          tx.executeSql('DELETE FROM DEMO WHERE id = ' + currentRow, [], queryDB, errorCB);
        }

        // Transaction error callback
        //
        function errorCB(err) {
            alert("Error processing SQL: "+err.code);
        }

        // Transaction success callback
        //
        function successCB() {
            var db = window.openDatabase("Database", "1.0", "Cordova Demo", 200000);
            db.transaction(queryDB, errorCB);
        }

         // Cordova is ready
        //
        function onDeviceReady() {
            var db = window.openDatabase("Database", "1.0", "Cordova Demo", 200000);
            db.transaction(populateDB, errorCB, successCB);
        }

        //Insert query
        //
        function insertDB(tx) {
            tx.executeSql('INSERT INTO DEMO (name,number) VALUES ("' +document.getElementById("txtName").value
                    +'","'+document.getElementById("txtNumber").value+'")');
        }

        function goInsert() {
            var db = window.openDatabase("Database", "1.0", "Cordova Demo", 200000);
            db.transaction(insertDB, errorCB, successCB);
        }

        function goSearch() {
            var db = window.openDatabase("Database", "1.0", "Cordova Demo", 200000);
            db.transaction(searchQueryDB, errorCB);
        }

        function goDelete() {
             var db = window.openDatabase("Database", "1.0", "Cordova Demo", 200000);
             db.transaction(deleteRow, errorCB);
             document.getElementById('qrpopup').style.display='none';
        }

        //Show the popup after tapping a row in table
        //
        function goPopup(row,rowname,rownum) {
            currentRow=row;
            document.getElementById("qrpopup").style.display="block";
            document.getElementById("editNameBox").value = rowname;
            document.getElementById("editNumberBox").value = rownum;
        }

        function editRow(tx) {
            tx.executeSql('UPDATE DEMO SET name ="'+document.getElementById("editNameBox").value+
                    '", number= "'+document.getElementById("editNumberBox").value+ '" WHERE id = '
                    + currentRow, [], queryDB, errorCB);
        }
        function goEdit() {
            var db = window.openDatabase("Database", "1.0", "Cordova Demo", 200000);
            db.transaction(editRow, errorCB);
            document.getElementById('qrpopup').style.display='none';
        }

    </script>

I am using this code for one of my app and it's working fine.

Edit 1 :

NOTE : Please place your db file in wwww directory like for e.g projectpath/www/dbname.db.

For accessing db file from asset folder.

window.openDatabase({name: "dbname.db", createFromLocation: 1}); 

Edit 2:

Just install below plugin in your app.

cordova plugin add https://github.com/brodysoft/Cordova-SQLitePlugin.git

And Try this.

 var db = window.sqlitePlugin.openDatabase({name: "my.db", createFromLocation: 1});

这篇关于如何访问和更新存储在本地[资产文件夹]在项目文件中使用cordova的Sqlite数据库文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！