使用Android和Phonegap存储SQLite数据库 [英] Storage of SQLite database using Android and Phonegap
问题描述
我正在开发一个Android Cordova / Phonegap应用程序，其中我想使用SQLite数据库。我使用官方文档中的示例。
//等待设备API库加载
//
document.addEventListener（deviceready ，onDeviceReady，false）;
//填充数据库
//
函数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）'）;
}
//查询数据库
//
函数queryDB（tx）{
tx.executeSql（'SELECT * FROM DEMO'，[ ]，querySuccess，errorCB）;
}
//查询成功回调
//
function querySuccess（tx，results）{
var len = results.rows.length;
console.log（DEMO table：+ len +rows found。）;
for（var i = 0; i
}
}
//事务错误回调
//
function errorCB（err）{
console.log（Error processing SQL：+ err.code）;
}
//事务成功回调
//
function successCB（）{
var db = window.openDatabase（Database，1.0 ，Cordova Demo，200000）;
db.transaction（queryDB，errorCB）;
}
//设备API可用
//
函数onDeviceReady（）{
var db = window.openDatabase（Database， 1.0，Cordova Demo，200000）;
db.transaction（populateDB，errorCB，successCB）;
}
虽然这看起来工作（数据库创建和填充没有错误，我得到书面数据与查询），我想知道如何数据库存储在我的设备上。对于调试我使用硬件手机与Android 4.1.1。
数据库位于 /data/data/<myapppackage>/app_database/file__0/0000000000000001.db 。现在我想导出数据库，并在我的电脑上使用SQLiteManager手动分析，但似乎更改没有写入db文件。
但是，当检查目录 / data / data /< myapppackage> / app_database / file__0 / 两个临时文件 0000000000000001.db-shm 和 0000000000000001.db-wal ，其时间戳每次执行时都会更改一个数据库操作，但从来没有db文件本身。
我的问题是，为什么更改不会写入持久性数据库文件？似乎没有办法关闭与phonegap的数据库连接，甚至手动杀死应用程序不写入的更改.db文件。
任何人在这里看到问题？
tx.executeSql('DROP TABLE IF EXISTS DEMO'）;
上述行每次启动PhoneGap移动应用程序时删除名为DEMO的表
我只是想告诉你我爱你的代码。它给任何人的PhoneGap或Cordova应用程序的做什么一个非常好的线索。它将极大地帮助任何第一次进入SQLite世界的人。
与在Cordova / PhoneGap SQLite插件官方网站GitHub上编写的代码相比，您的代码很容易阅读和理解。
我的朋友，也是一个公司的CTO，并且对SQLite有很多经验，告诉我没有必要关闭SQLite数据库连接
对于任何人来说，如果是其他人寻找SQLite for PhoneGap / Cordova信息，假设您有一个名为mytable的表格，并要存储值beautiful和dolphin。
当您想对移动设备（例如平板电脑或手机）的SQLite执行操作时，请记住以这种方式调用
在您的源代码中有以下内容
function insertNewLine（tx）
{
tx.executeSql（INSERT INTO mytable ）VALUES（α，？），[var1，var2]）; $ var $ $ $ $ $ $
$ b $ p>
执行以下语句以执行SQL insert语句，然后保存在设备中。
db.transaction（insertNewLine）;
不要直接调用insertNewLine（tx）
不要直接调用tx.executeSql（/ * SQL INSERT STATEMENT * /）;在您的JavaScript源代码中
并且不要将值直接包含在SQL查询语句中，然后运行包含所需值的SQL语句
<$> 换句话说，以下是不正确 p $ p> tx.executeSql（'INSERT INTO mytable（word，meaning）values（beautiful，dolphin）'）;
以上是不正确的，因为您要存储的值，美丽和海豚里面的SQL语句。它们应该是分开的。
以下是运行INSERT SQL的正确方法
tx.executeSql（INSERT INTO mytable（word，meaning）VALUES（？，？），[var1，var2]）;
//注意，要存储的值，美丽的和海豚
//与SQL INSERT INTO语句分开
然后通过在您的JavaScript代码中包含以下内容来执行整个数据库事务
db.transaction（insertNewLine）;
不是以下代码：
tx.executeSql（INSERT .....）; //这不会将您的值保存到设备上
strong>
insertNewLine（tx）; //这不会将您的值保存到设备上。
要使用SELECT SQL语句，请具有以下代码
//获取表中的所有行
//
function viewthelastglory（tx）
{
tx.executeSql （'SELECT * FROM CUSTOMTABLE'，[]，querySuccess，errorcode）;
}
//处理行
//
function querySuccess（tx，results）
{
var len = results。 rows.length; var queryresult =all entries;
for（var i = 0; i
queryresult = queryresult +
Row - + i +
Word - + results.rows.item（i）.word +
Meaning - + results.rows.item（i）.meaning;
}
//现在，您可以使用queryresult变量来使用值
}
函数errorcode（errorhaha）
{
alert（错误代码+ errorhaha.code +错误消息+ errorhaha.message）;
}
然后，执行数据库事务
db.transaction（viewthelastglory）;
如果您尝试从SQLite，WebSQL和IndexedDB中选择一个，请记住我在stackoverflow一段时间，并且了解到
- 没有人喜欢IndexedDB因为它的复杂性
- IndexedDB不兼容许多类型和版本的移动操作系统
- WebSQL已被W3C弃用
- WebSQL返回673K结果，但SQLite返回18万个结果。 IndexedDB在Google上返回300K个结果
- 在IndexedDB，SQLite和WebSQL中，SQLite是唯一具有官方网站的结果。
当您位于Cordova项目的目录中时，以下命令将在您的Cordova项目中安装SQLite插件
cordova插件添加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屋！
