Data access is a common challenge when building any Enterprise app. The DbLazyProvider
module provides a lightweight wrapper around the Ti.Database
API. This module provides helpers for commonly required operations such as managing transactions and lazy-loading connections.
Lazy loading is a common efficiency pattern that defers the initialization of an object until needed. By lazy-loading app-database connections, any memory usage or IO operation associated with creating a database connection is deferred until needed.
The following section demonstrates how to use the DbLazyProvider
module to implement a lazy-loading pattern in your app, while maintaining control over your database transactions.
Adding the DbLazyProvider
module to your project is easy. Simply copy the dblazyprovider.js
file into your project, as shown in the following screenshot:
The first step in this recipe is to create the application namespace and import the DbLazyProvider
module, as demonstrated here:
//Create our application namespace var my = { dbProvider : require('dblazyprovider') };
Next, a sample table named MY_TEST
is created. If the table already exists, any existing records will be purged so we can start a new test.
var dbSetup = new my.dbProvider("myDb"); var createSql = 'CREATE TABLE IF NOT EXISTS '; createSql += 'MY_TEST (TEST_ID INTEGER, '; createSql += 'TEST_NAME TEXT)'; dbSetup.connect().execute(createSql); dbSetup.connect().execute('DELETE FROM MY_TEST'), dbSetup.close();
Now, we create a Ti.UI.Window
for our recipe. This will be used to provide a launching point for our tests.
var win = Ti.UI.createWindow({ backgroundColor:'#fff',layout:'vertical' });
The insertDemoBtn
button is used to trigger our recipe, showing how to use transactions with DbLazyProvider
:
var insertDemoBtn = Ti.UI.createButton({ title:'Run Inserts', height:50, right:5, left:5, top: 20; }); win.add(insertDemoBtn);
insertDemoBtn
button will insert 1,000 rows into our table:insertDemoBtn.addEventListener('click',function(e){ var maxIteration = 1000; var sql = 'INSERT INTO MY_TEST '; sql+='(TEST_ID, TEST_NAME) VALUES(?,?)'; var db = new my.dbProvider("myDb"); for (var iLoop = 0; iLoop < maxIteration; iLoop++){
connect
method with a transaction parameter of true
to obtain the Ti.Database
object that is used to execute the SQL statement. This will automatically create a transaction on the first insert for you.db.connect(true).execute(sql,iLoop,'test ' + iLoop);
close
method is called, any transactions that were created will be committed, and the database connection will be closed: db.close();
alert('Test done ' + maxIteration + ' rows inserted'),
});
Not all SQL statements benefit from transactions. The following snippet demonstrates how to perform a select statement without using transactions:
var selectDemoBtn = Ti.UI.createButton({ title:'Run Select', height:50, right:5, left:5, top: 20 }); win.add(selectDemoBtn);
selectDemoBtn
button to create a new DbLazyProvider
object, and run a select statement:selectDemoBtn.addEventListener('click',function(e){ var db = new my.dbProvider("myDb"); var selectSQL = 'SELECT * FROM MY_TEST ';
connect
method without providing any parameters. This will, by default, avoid using transactions. var getReader = db.connect().execute(selectSQL);
var rowCount = getReader.getRowCount();
close
method, the database connection will be closed and all objects will be set to null:db.close(); alert('Rows available ' + rowCount); });
The DbLazyProvider
module is a lightweight, yet powerful, wrapper over Ti.Database
.
To create a new DbLazyProvider
object, simply use the require
method to import the module and create a new object using the reference in the following highlighted snippet. This will create a new object wrapper for the database name provided.
//Create our application namespace
var my = {
dbProvider : require('dblazyprovider')
};
//Create a new dbLazy object
var db = new my.dbProvider("myDb");
The most frequently used method of DbLazyProvder
is connect
. This method will create a new Ti.Database
connection if needed, and then return the database object:
db.connect().execute('Your SQL goes here'),
If you wish to have your SQL statement start a transaction, you simply pass in a Boolean parameter of true
when calling the connect
method, as shown here:
db.connect(true).execute('Your SQL goes here'),
The transaction created will be used until either the close
or commit
method is called on your DbLazyProvider
object.
By default, transactions are handled automatically for you, using the connect
and close
methods. You can also explicitly create a transaction at any time using the beginTransaction
method, as shown here:
db.beginTransaction();
By default, transactions are handled automatically for you, using the connect
and close
methods. You can also explicitly commit or finish a transaction at any time using the commit
method, as shown here:
db.commit();
The module, by default, will wait until a database connection is needed, before opening the Ti.Database
object. If a connection is needed in advance, you can call the open
method at any time:
db.open();
3.143.247.53