Mind the Flex

Returning Data from SQLite by Using the Responder Object

Adobe Air offers two ways to return data from local SQLite database: by SQLEvent and by Responder object. As the SQLEvent method is more popular since it follows the general logic of events, this article sheds light on the the use of Responder object that is quite useful in certain cases. In the end of this short article we will have an Air application that allows to enter users from an application form and displays them by using a Responder object.

At first we will define a User class with the attributes name and age that will be save to the database:

package users{
	public class User{
		public var id:int;
		public var name:String;
		public var age:int;
 
		public function User(){
		}
 
		public function getName():String{
			return name;
		}
		public function getAge():int{
			return age;
		}
 
		public function setName(name:String):void{
			this.name=name;
		}
 
		public function setAge(age:int):void{
			this.age=age;
		}
	}
}

As the next step we will create the data access layer objects. Here is the IUserDao interface that defines two methods: one for querying data and another for saving data. The getUserData method takes in a parameter of type UserResponder that we will pay more attention in a moment.

package dao{
	import mx.collections.ArrayCollection;
	import responder.UserResponder;
	import users.User;
 
	public interface IUserDao{
		function getUserData(userResponder:UserResponder):Array;
 
		function saveUserData(user:User):void;
	}
}

Here is the implementation of IUserDao. As can be seen, it has two SQLStatement objects - one for saving and one for querying the data. The method getUserData returns an array of objects, which is queried from userResponder object. One interesting aspect should be mentioned on line _stmtGetUserData.itemClass=User;, where you can define the automatic mapping type of the query result. The only thing you should pay attention is that the names of the columns in the database and the attributes of your object match. Everything else is taken care by Air.

package dao{
	import flash.data.SQLConnection;
	import flash.data.SQLStatement;
	import mx.collections.ArrayCollection;
	import responder.UserResponder;
	import users.User;
 
	public class UserDaoImpl implements IUserDao{
		private var _sqlConnection:SQLConnection;
 
		private var _stmtGetUserData:SQLStatement;
		private var _stmtSaveUserData:SQLStatement;
 
		public function UserDaoImpl(sqlConnection:SQLConnection){
			this._sqlConnection=sqlConnection;
			_stmtGetUserData=new SQLStatement();
			_stmtGetUserData.sqlConnection=sqlConnection;
			_stmtGetUserData.itemClass=User;
			_stmtGetUserData.text="SELECT * FROM user";
 
			_stmtSaveUserData=new SQLStatement();
			_stmtSaveUserData.sqlConnection=sqlConnection;
			_stmtSaveUserData.text="INSERT INTO user(name, age) VALUES(@name, @age)";
		}
 
		public function getUserData(userResponder:UserResponder):Array{
			_stmtGetUserData.execute(-1, userResponder);
			return userResponder.getUsers();
		}
 
		public function saveUserData(user:User):void{
			_stmtSaveUserData.parameters["@name"] = user.getName();
			_stmtSaveUserData.parameters["@age"] = user.getAge();
			_stmtSaveUserData.execute();
		}
	}
}

Here is the responder object which takes care of handling the query result. As you can see in the constructor of UserResponder, it calls its super class constructor super(buildUserObjectList, logError);. The constructor takes two parameters as strings. The first one is the name of the method that handles the result of the query (buildUserObjectList) and the second is the name of the method for the case everything does not go according to the plan (logError). The method buildUserObjectList has one parameter and this is the resultset returned from the query. At first you have to check that the result object isn't null and also that the data inside the result is not null. After these checks you'll just loop over the data and do with the resultset whatever you like. In our case we wanted the result to return the list of the users in the database. As we had previously defined the itemclass _stmtGetUserData.itemClass=User;, we can take the objects from the resultset and add to the array we created previously.

package responder{
	import flash.data.SQLResult;
 
	import flash.errors.SQLError;
	import flash.net.Responder;
 
	import users.User;
 
	public class UserResponder extends Responder{
		private var usersList:Array=new Array();
 
		public function UserResponder(){
			super(buildUserObjectList, logError);
		}
 
		public function buildUserObjectList(result:SQLResult):void{
			var user:User;
			usersList=new Array();
 
			if(result != null && result.data != null) {
				for(var i:Number = 0; i < result.data.length; i++) {
					user = User(result.data[i]);
					usersList.push(user);
				}
			}
		}
 
		public function logError(error:SQLError):void{
			trace(error);
		}		
 
		public function getUsers():Array{
			return usersList;
		}
	}
}

Here is the main application with the form where you can enter and save the data. The most important point of this file is in the method createDatabaseHandler that is responsible of the database creation. It is executed in creationComplete and it is also responsible for the creation of the dao object _dao=new UserDaoImpl(_sqlconnection);. The two buttons on the form call the methods saveUser and viewUsers, which are quite self-explanatory. The method viewUsers could also be modified in a way that it is of type void and you could query the data directly from the responder object.

<?xml version="1.0" encoding="utf-8"?>
<mx:WindowedApplication xmlns:mx="http://www.adobe.com/2006/mxml" layout="absolute" creationComplete="createDatabaseHandler()">
	<mx:Script>
		<![CDATA[
			import responder.UserResponder;
			import dao.IUserDao;
			import dao.UserDaoImpl;
			import users.User;
			private var _dao:IUserDao;
			private var _sqlconnection:SQLConnection;
			private var _userProfile:User;
			private const DATABASE_NAME:String='responderexample';
 
			public function createDatabaseHandler():void {
				var file:File = File.userDirectory.resolvePath(DATABASE_NAME);
				_sqlconnection = new SQLConnection();
				_sqlconnection.addEventListener(SQLEvent.OPEN, openHandler);
				_sqlconnection.open(file, SQLMode.CREATE);
				_dao=new UserDaoImpl(_sqlconnection);
			}
 
			public function openHandler(event:Event):void {
				var stmtCreateDatabase:SQLStatement;
				stmtCreateDatabase=new SQLStatement();
				stmtCreateDatabase.sqlConnection=_sqlconnection;
				stmtCreateDatabase.text="CREATE TABLE IF NOT EXISTS user(id INTEGER PRIMARY KEY AUTOINCREMENT, age INTEGER, name TEXT)";
				stmtCreateDatabase.execute();
			}		
 
			public function viewUsers():void{
				var userResponder:UserResponder=new UserResponder();
				usersList.dataProvider=_dao.getUserData(userResponder);
			}		
 
			public function saveUser():void{
				var user:User=new User();
				user.setName(username.text);
				user.setAge(userage.value);
				_dao.saveUserData(user);
			}
		]]>
	</mx:Script>
	<mx:Form >
		<mx:FormItem label="Name">
			<mx:TextInput id="username"/>
		</mx:FormItem>
		<mx:FormItem label="Age">
			<mx:NumericStepper id="userage"/>
		</mx:FormItem>
		<mx:FormItem>
			<mx:Button label="Save" click="saveUser()"/>
		</mx:FormItem>
		<mx:FormItem>
			<mx:Button label="View Users" click="viewUsers()"/>
		</mx:FormItem>
		<mx:DataGrid id="usersList">
			<mx:columns>
				<mx:DataGridColumn headerText="Name" dataField="name"/>
				<mx:DataGridColumn headerText="Age" dataField="age"/>
			</mx:columns>
		</mx:DataGrid>
	</mx:Form>
</mx:WindowedApplication>

Download source code in Flexbuilder project format