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);- Clicking on the
insertDemoBtnbutton 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++){ - The following code shows using the
connectmethod with a transaction parameter oftrueto obtain theTi.Databaseobject 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);
- When the
closemethod 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);- Click on the
selectDemoBtnbutton to create a newDbLazyProviderobject, and run a select statement:selectDemoBtn.addEventListener('click',function(e){ var db = new my.dbProvider("myDb"); var selectSQL = 'SELECT * FROM MY_TEST '; - The highlighted code shows how to use the
connectmethod without providing any parameters. This will, by default, avoid using transactions.var getReader = db.connect().execute(selectSQL); var rowCount = getReader.getRowCount(); - On using the
closemethod, 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();