How to compact SQL instructions in Cordova?

问题:

I'm making a tiny app that will use Databases, I want to know how to convert this:

function testDB()
{
    var db = window.openDatabase("Database", "1.0", "mydatabase", 2000000);
    db.transaction(queryNames, errorDB);
}
function queryNames(tx) 
{
    tx.executeSql('SELECT name FROM people WHERE id=13', [], listNames, errorDB);
}
function listNames(tx, results) 
{
    for (var i=0;i<results.rows.length;i++)
    {
        alert(results.rows.item(i).name);
    }
}
function errorDB(err)
{
    alert("Fatal error");
}

To something like this (either if it is possible more compacted):

function testDB()
{
    var db = window.openDatabase("Database", "1.0", "mydatabase", 2000000);
    db.transaction(queryNames('SELECT name FROM people WHERE id=13'), errorDB);
}
function queryNames(tx, query) 
{
    tx.executeSql(, [], 
    function listNames(tx, results) 
    {
        for (var i=0;i<results.rows.length;i++)
        {
            alert(results.rows.item(i).name);
        }
    },
    errorDB);
}
function errorDB(err)
{
    alert("Fatal error");
}

In few words, I'm trying to "recycle" the code. Thanks for reading.

回答1:

The SQLite call is an event. I have created the following function by using jQuery (The function is also logging the results in the console, so you need the console plugin for working):

var db = window.openDatabase("Database", "1.0", "mydatabase", 2000000);

function queryDB(query) {

    var start    = Date.now();
    var deferred = $.Deferred();
    db.transaction(function (tx) {
        tx.executeSql(query, [], function (tx, results) {
            console.log("Rows: " + results.rows.length + " Time: " + (Date.now() - start) + "ms Query: " + query);
            deferred.resolve(results);
        }, function (error) {
            console.log("Error processing SQL: " + error.code + " " + error.no + " Query: " + query);
        });
    }, function (error) {
        console.log("Transaction Error: " + error.code + " " + error.no + " Query: " + query);
    });
    return deferred.promise();
}

And the function call is:

$.when(queryDB("SELECT * FROM table")).done(function (results) {
        // Here your logic with the results
});

The advantage of this way is, that you can call multiple queries the same time:

$.when(queryDB("SELECT * FROM table1"), queryDB("SELECT * FROM table2")).done(function (results1, results2) {
        // Here your logic with the results
});

回答2:

Here's what I would suggest. Make a separate database object that just has a generic query method. That way, you can reuse it from anywhere else in your application. Something like the following:

Here's the database object:

var DB = function () {
    this.db = window.openDatabase("Database", "1.0", "mydatabase", 2000000);
};

DB.prototype.error = function () {
    alert("Fatal Error");
};

DB.prototype.query = function (query, callback) {
    this.db.transaction(function (tx) {
        tx.executeSql(query, [], function (tx, results) {
            callback(results);
        });
    }, this.error); 
};

And now you can call it to do your names query like so:

var myDB = new DB();
var namesQuery = 'SELECT name FROM people WHERE id=13';

myDB.query(namesQuery, function (results) {
    for (var i=0;i<results.rows.length;i++) {
        alert(results.rows.item(i).name);
    }
});

This is just a basic example. You would probably want to add extra stuff for error handling/named arguments/etc.

Hope this helps