使用Android和PhoneGap的SQLite数据库的存储 [英] Storage of SQLite database using Android and Phonegap
问题描述
我正在开发一款Android科尔多瓦/ PhoneGap的应用程序，我想使用SQLite数据库。我用这个例子从官方的<一个href="http://cordova.apache.org/docs/en/edge/cordova_storage_storage.md.html#SQLResultSetRowList">documentation.
//等待设备的API库加载
//
document.addEventListener（deviceready，onDeviceReady，假）;
//填充数据库
//
功能populateDB（德克萨斯州）{
tx.executeSql（DROP TABLE IF EXISTS DEMO）;
tx.executeSql（CREATE TABLE IF NOT EXISTS DEMO（ID唯一，数据）'）;
tx.executeSql（INSERT INTO DEMO（ID，数据）VALUES（1，第一行）'）;
tx.executeSql（INSERT INTO DEMO（ID，数据）VALUES（2，第二排））;
}
//查询数据库
//
功能queryDB（德克萨斯州）{
tx.executeSql（SELECT * FROM DEMO，[]，querySuccess，errorCB）;
}
//查询成功回调
//
功能querySuccess（德克萨斯州，结果）{
VAR的len = results.rows.length;
的console.log（DEMO表：+ LEN +发现行。）;
对于（VAR I = 0; I＆LT; LEN;我++）{
的console.log（行=+ 1 +ID =+ results.rows.item（I）.ID +数据=+ results.rows.item（我）.DATA）;
}
}
//交易错误回调
//
功能errorCB（ERR）{
的console.log（错误处理SQL：+ ERR code）;
}
//交易成功回调
//
功能successCB（）{
变种DB = window.openDatabase（数据库，1.0，科尔多瓦演示，200000）;
db.transaction（queryDB，errorCB）;
}
//设备API可用
//
传播onDeviceReady（）{
变种DB = window.openDatabase（数据库，1.0，科尔多瓦演示，200000）;
db.transaction（populateDB，errorCB，successCB）;
}
尽管这似乎工作（在创建和填充不出现错误的数据库，我得到的数据写回与查询），我不知道如何在数据库存储设备上。对于调试我使用的是硬件的手机采用Android 4.1.1。
该数据库位于 /data/data/<myapppackage>/app_database/file__0/0000000000000001.db 。现在，我想导出数据库，并在我的电脑用的SQLiteManager手动分析它，但它似乎所做的更改不会写入到数据库文件。
不过，检查目录 /数据/数据/＆LT时，myapppackage＆GT; / app_database / file__0 / 我找到了两个临时文件 0000000000000001。 DB-SHM 和 0000000000000001.db-沃尔玛，其时间戳我每次执行数据库操作时被改变，但从来没有在数据库文件本身。
我的问题是，为什么改变永远不会写入持久性数据库文件？目前似乎没有一种方法来关闭与PhoneGap的数据库连接，甚至杀害应用程序手动不会写入更改.db文件。我不知道我做错了什么。
这里有人看到这个问题？
tx.executeSql（DROP TABLE IF EXISTS DEMO）;
本线以上删除该表名为DEMO每次启动您的PhoneGap移动应用程序
和我只是想告诉你，我爱你的code。它提供了有关做什么的人的PhoneGap的或科尔多瓦申请一个很好的线索。这将大大有助于谁是进入SQLite的世界首次任何人。
您code是非常干净的阅读和理解相比，codeS写在科尔多瓦/ PhoneGap的SQLite的插件在GitHub上的官方网站。
我的朋友，谁也作为一个公司的CTO，并拥有丰富的经验与SQLite的，告诉我，这是没有必要手动关闭SQLite数据库连接，并且也大大建议SQLite的。
的以及其它任何人寻找SQLite，让PhoneGap的/科尔多瓦信息 - 的
假设你有一个表名为mytable的和想要存储的值美丽和海豚
当你想在移动设备的SQLite的，如平板电脑或手机进行操作，记得打电话这样说
有以下的源$ C $ C
函数insertNewLine（TX）
{
tx.executeSql（INSERT INTO mytable的（字，意）VALUES（，）？，[VAR1，VAR2]）;
}
和商店美丽里面VAR1和海豚里VAR2和
执行以下语句以执行SQL INSERT语句，然后保存在设备里。
db.transaction（insertNewLine）;
不要直接调用insertNewLine（TX）
不要直接调用tx.executeSql（/ * SQL INSERT语句* /）;在你的JavaScript源$ C $ C
并没有包含的值直入SQL查询语句，然后运行，其中包括要存储在数据库中。值的SQL语句
在换句话说，下面是不正确
tx.executeSql（INSERT INTO mytable的（字，意思）值（美，海豚）'）;
以上是不正确，因为要存储的值，美丽和海豚被包括在SQL语句中。他们应该是分开的。
以下是运行INSERT SQL的正确方法
tx.executeSql（INSERT INTO mytable的（字，意思）VALUES（，）？，[VAR1，VAR2]）;
//注意，要存储，美丽的海豚值
//独立于SQL INSERT INTO语句
，然后通过在你的JavaScript code以下执行整个数据库事务
db.transaction（insertNewLine）;
不低于code
tx.executeSql（INSERT ......）; //这不会救你的价值观到设备
不低于code或者
insertNewLine（TX）; //这不会救你的价值观到设备无论是。
和使用SELECT SQL语句，有如下code
//获取表中的所有行
//
功能viewthelastglory（TX）
{
tx.executeSql（SELECT * FROM CUSTOMTABLE'，[]，querySuccess，错误code）;
}
//处理线
//
功能querySuccess（德克萨斯州，结果）
{
VAR的len = results.rows.length; VAR QueryResult中=所有条目;
对于（VAR I = 0; I＆LT; LEN;我++）
{
QueryResult中= QueryResult中+
行 - + I +
字 - + results.rows.item（I）.word +
意 - + results.rows.item（我）.meaning;
}
//现在，您可以使用QueryResult中变量使用的值
}
功能错误code（errorhaha）
{
警报（错误code+ errorhaha code +错误信息+ errorhaha.message。）;
}
然后，执行数据库事务
db.transaction（viewthelastglory）;
如果你想选择一个从SQLite的，WebSQL和索引型数据库，请记得，我四处寻找计算器了一会儿，得知
- 在没人喜欢，因为它的复杂性 的索引型
- 在索引型与多种类型和移动操作系统的版本不兼容
- 在WebSQL已经由W3C pcated德$ P $
- 在WebSQL返回673K的结果，但SQLite的返回1800K的结果。索引型数据库返回的谷歌300K结果
- 在索引型数据库时，SQLite和WebSQL，SQLite是唯一一个官方网站。
下面的命令在命令行中，而你在你的科尔多瓦项目的目录将安装SQLite的插件安装到您的科尔多瓦的项目
科尔多瓦插件添加https://github.com/brodysoft/Cordova-SQLitePlugin
I'm developing a Android Cordova/Phonegap app where I want to use a SQLite database. I used the example from the official documentation.
// Wait for device API libraries to load
//
document.addEventListener("deviceready", onDeviceReady, false);
// Populate the database
//
function populateDB(tx) {
tx.executeSql('DROP TABLE IF EXISTS DEMO');
tx.executeSql('CREATE TABLE IF NOT EXISTS DEMO (id unique, data)');
tx.executeSql('INSERT INTO DEMO (id, data) VALUES (1, "First row")');
tx.executeSql('INSERT INTO DEMO (id, data) VALUES (2, "Second row")');
}
// Query the database
//
function queryDB(tx) {
tx.executeSql('SELECT * FROM DEMO', [], querySuccess, errorCB);
}
// Query the success callback
//
function querySuccess(tx, results) {
var len = results.rows.length;
console.log("DEMO table: " + len + " rows found.");
for (var i=0; i<len; i++){
console.log("Row = " + i + " ID = " + results.rows.item(i).id + " Data = " + results.rows.item(i).data);
}
}
// Transaction error callback
//
function errorCB(err) {
console.log("Error processing SQL: "+err.code);
}
// Transaction success callback
//
function successCB() {
var db = window.openDatabase("Database", "1.0", "Cordova Demo", 200000);
db.transaction(queryDB, errorCB);
}
// device APIs are available
//
function onDeviceReady() {
var db = window.openDatabase("Database", "1.0", "Cordova Demo", 200000);
db.transaction(populateDB, errorCB, successCB);
}
Although this seems to work (the database is created and filled without errors, and I get the written data back with the query), I'm wondering how the database is stored on my device. For debugging I use a hardware phone with Android 4.1.1.
The database is located under /data/data/<myapppackage>/app_database/file__0/0000000000000001.db. Now I wanted to export the database and analyze it manually on my pc with SQLiteManager, but it seems the changes are not written to the db file.
However, when examining the directory /data/data/<myapppackage>/app_database/file__0/ i found the two temporary files 0000000000000001.db-shm and 0000000000000001.db-wal, whose timestamps are changed every time I perform a database operation, but never the db file itself.
My question is, why are the changes never written to the persistent database file? There does not seem to be a way to close a database connection with phonegap, and even killing the app manually doesn't write the changes to the .db file. I'm not sure what I did wrong.
Anyone seeing the problem here?
tx.executeSql('DROP TABLE IF EXISTS DEMO');
This line above deletes the table named DEMO everytime you start your PhoneGap mobile application
And I just wanted to tell you I love your code. It gives a very good clue about "what to do" for anyone's PhoneGap or Cordova application. It will greatly help anyone who is entering the world of SQLite for the first time.
Your code is very clean to read and understand compared to the codes written on Cordova/PhoneGap SQLite plugin official website on GitHub.
My friend, who also works as the CTO of a company, and has a plenty of experience with SQLite, told me that it is not necessary to close a SQLite database connection manually, and also greatly recommended SQLite.
And for anyone else looking for SQLite for PhoneGap/Cordova information -
Let's say you have a table named mytable and want to store values "beautiful" and "dolphin"
When you want to perform an operation on the SQLite of a mobile device, such as a tablet or phone, remember to call it this way
Have the following in your source code
function insertNewLine(tx)
{
tx.executeSql("INSERT INTO mytable (word, meaning) VALUES (?,?)", [ var1 , var2 ]);
}
and store "beautiful" inside var1 and "dolphin" inside var2 and
do the following statement in order to execute the SQL insert statement and then save inside the device.
db.transaction(insertNewLine);
Do not directly call insertNewLine(tx)
Do not directly call tx.executeSql( /* SQL INSERT STATEMENT */ ); in your JavaScript sourcecode
And do not include the values straight into the SQL query statement and then run the SQL statement that includes the values you want to store in the database.
In other words, the following is incorrect
tx.executeSql('INSERT INTO mytable (word, meaning) values (beautiful, dolphin)');
The above is incorrect because the values you want to store, "beautiful" and "dolphin" are included inside the SQL statement. They should be separate.
The following is the correct way to run the INSERT SQL
tx.executeSql("INSERT INTO mytable (word, meaning) VALUES (?,?)", [ var1 , var2 ]);
// Notice that the values you want to store, beautiful and dolphin
// are separate from the SQL INSERT INTO statement
and then perform the entire database transaction by including the following in your JavaScript code
db.transaction(insertNewLine);
not the below code
tx.executeSql("INSERT....."); // this will not save your values onto the device
not the below code either
insertNewLine(tx); // this will not save your values onto the device either.
And to use the SELECT SQL statement, have the following code
// Get all lines in the table
//
function viewthelastglory(tx)
{
tx.executeSql( 'SELECT * FROM CUSTOMTABLE', [], querySuccess, errorcode );
}
// Deal with the lines
//
function querySuccess(tx, results)
{
var len = results.rows.length; var queryresult = "all entries ";
for (var i = 0 ; i < len ; i++)
{
queryresult = queryresult +
" Row - " + i +
" Word - " + results.rows.item(i).word +
" Meaning - " + results.rows.item(i).meaning;
}
// and now, you can use the queryresult variable to use the values
}
function errorcode(errorhaha)
{
alert("Error Code " + errorhaha.code + " Error Message " + errorhaha.message);
}
And then, perform the database transaction
db.transaction(viewthelastglory);
If you are trying to choose one from SQLite, WebSQL and IndexedDB, please remember that I searched around stackoverflow for a while and learned that
- Nobody likes IndexedDB because of its complexity
- IndexedDB is incompatible with many types and versions of mobile OS
- WebSQL has been deprecated by W3C
- WebSQL returns 673K results but SQLite returns 1800K results. IndexedDB returns 300K results on Google
- Among IndexedDB, SQLite and WebSQL, SQLite is the only one with an official website.
The following command at the command line while you are in the directory of your Cordova project will install the SQLite plugin into your Cordova project
cordova plugin add https://github.com/brodysoft/Cordova-SQLitePlugin
这篇关于使用Android和PhoneGap的SQLite数据库的存储的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
