Cordova method of invoking local SQLite database

The first technical blog, write down and share what you learned today, and then consolidate yourself.

The whole afternoon was spent studying how to invoke the mobile local SQLite database using Cordova. First I did not use Eclipse to program, but to use Cordova to build project directly with Notepad edit the index file under www.

First, Cordova download installation and build Project method

This part is not the focus of the article, the time to open another article detailed introduction. Official web-related tutorials: http://cordova.apache.org/docs/en/5.0.0/guide_cli_index.md.html#The%20Command-Line%20Interface (insert a sentence, Try to use the real machine when testing, the virtual machine is too slow)

Second, plug-in download

Call SQLite need to use Cordova plug-in Cordova-sqlite-storage, download method to open cmd in the built project directory, enter Cordova plugin add Cordova-sqlite-storage Enter.

Third, the use of plug-ins

Reference resources: Https://www.npmjs.com/package/cordova-sqlite-storage

Basic steps for using the SQLite database

(1) Establishing a connection

There are two ways to create and open a connection to a database:

Var db = window.sqlitePlugin.openDatabase({name: "my.db"}); //my.db indicates the database name
Var db = window.sqlitePlugin.openDatabase("myDatabase.db", "1.0", "Demo", -1); //myDatabase.db represents the database name, 1.0 is the version number, Demo is the description, the fourth parameter Reference other information should be the estimated database size, -1 represents what I did not understand, please give pointers.

(2) Perform SQL operations

Use the Db.transaction () method to perform a SQL operation that has only one parameter. This parameter is a function and is usually anonymous and can be defined directly in Db.transaction (). Such as:

Db.transaction (function (TX)

{

...

});

This anonymous function has a parameter of the transaction type TX, which is a transaction object that has a method: ExecuteSQL (), which can execute the SQL statement.

The method has four parameters, of which the following three parameters are optional:

• Query string
• Replace the data in a parameterized query with placeholders
• callback function called when SQL execution succeeds
• callback function that is called when SQL execution fails

For example, the following code defines four parameters:

Tx. ExecuteSQL( ", [" Test ",  100] ,  function1,function2);

(3) Processing the results

3.1 When the SQL operation executes successfully, it executes a callback function, that is, funciton1 in the above, the function has two parameters, one is to execute his object TX, and the other is the result of the operation returned RES (detailed below), namely:

function Function1 (tx,res)

{

...

}

3.2 When the SQL operation fails, function2 is called, and the function has two parameters, one is TX and the other is an error object, and the Error object can be used to know the specific error message:

function function2 (tx,err)

{

alert (err.message);

}

3.3 The SQL statement that was executed successfully using the ExecuteSQL () method can return an execution result that is the res in the previous step, which is returned as a parameter to the callback function.

The execution result is a SqlResultSet object, defined by the SqlResultSet interface:

Interface sqlresultset{

readonly attribute long Insertid;

readonly attribute long rowsaffected;

readonly attribute sqlresultrowlist rows;

}；

1, the property Insertid returns the row ID of the record row, each SQL Insert insert operation automatically inserts a row ID, if multiple rows are inserted, the property returns the row ID of the last row, or a Invalid_access_err exception when the property is called if no record row exists.

2. Property Rowsaffect Returns the number of rows of records changed by the SQL statement, and returns 0 if there are no changes;

3. Property rows Returns a Sqlresultlist object that represents the row of records returned, the results are arranged in a sequence in the database, and if there is no data, an empty object is returned, that is, the Sqlresultlist.length zodiac value is 0;

The Sqlresultlist object is defined by the Sqlresultlist interface:

Interface sqlresultsetrowlist{

ReadOnly attribute unsigned long length;

Getter any item (in unsigned long index);

}；

1, the property length indicates the total number of rows to return records.

2. The item (x) method gets a specified row based on the index number x (the subscript starts at 0). If the specified index number does not exist, the method returns NULL.

A row is an object of JavaScript type, and the property name of each record row is equivalent to the property name of the object, such as Row={id:0,data: "Test", data_num:100}, using object syntax to get the value of each property, such as row[ Data].

The overall code is as follows:

The results shown on the phone are:

data_num=100

Data=test

Id=0

 
 
 1 <!DOCTYPE HTML>
 2 <html>
 3 <head>
 4 <meta charset="utf-8">
 5 <script src="cordova.js" type="text/javascript"></script>
 6 <link rel="stylesheet" href="http://code.jquery.com/ui/1.11.0/themes/smoothness/jquery-ui.css">
 7 <script src="http://code.jquery.com/jquery-1.10.2.js"></script>
 8 <script src="http://code.jquery.com/ui/1.11.0/jquery-ui.js"></script>
 9   <link rel="stylesheet" href="http://jqueryui.com/resources/demos/style.css">
10   <link rel="stylesheet" href="http://code.jquery.com/mobile/1.3.2/jquery.mobile-1.3.2.min.css">
11   <script src="http://code.jquery.com/mobile/1.3.2/jquery.mobile-1.3.2.min.js"></script>
12 </head>
13 <body>
14 <script>
15 document.addEventListener("deviceready", onDeviceReady, false);
16 
17 function onDeviceReady() {
18   var db = window.sqlitePlugin.openDatabase("Database", "1.0", "Demo", -1);
19  
20   db.transaction(function(tx) {
21     tx.executeSql(‘DROP TABLE IF EXISTS test_table‘);
22     tx.executeSql(‘CREATE TABLE IF NOT EXISTS test_table (id integer primary key, data text, data_num integer)‘);
23  
24     tx.executeSql("INSERT INTO test_table (data, data_num) VALUES (?,?)", ["test", 100], function(tx, res) {
25       tx.executeSql("select * from test_table;", [], function(tx, res) {
26         alert("hello world");
27         var row=res.rows.item(0);
28         for(var j in row){
29          document.write(j+"="+row[j]+"<br />");
30         }
31       });
32     }, function(e) {
33       alert("ERROR: " + e.message);
34     });
35   });
36 }
37 </script>
38 </body>
39 </html>

Cordova method of invoking local SQLite database