当前位置: > > > Flex手机项目 - 使用SQLite存储数据(附:电话簿实例)

Flex手机项目 - 使用SQLite存储数据(附:电话簿实例)

SQLite是一个实现了SQL(Structured Query Language,结构化查询语言)数据库引擎的软件库,允许在基于Flash/Flex的移动应用程序中储存并使用复杂的数据。由于AIR的跨平台特性,可以轻松的运行在android和ios系统下。

下面通过一个电话簿的例子,展示as对SQLite的各种操作,功能如下:
1,创建SQLite数据库directory.db,以及联系人表Users
2,加载联系人并显示在List上
3,可新增联系人
4,可修改联系人
5,可删除联系人

效果图如下:
 

 

 

代码结构如下:


代码如下:
--- 主应用文件 MaintainingDataApp.mxml ---
<?xml version="1.0" encoding="utf-8"?>
<s:ViewNavigatorApplication xmlns:fx="http://ns.adobe.com/mxml/2009" 
					 xmlns:s="library://ns.adobe.com/flex/spark" 
					 firstView="views.MaintainingDataAppHome"
					 applicationDPI="240"> 
	<fx:Style> 
		@namespace s "library://ns.adobe.com/flex/spark"; 
		s|View
		{ 
			backgroundColor:#999999; 
			color:#454545; 
		} 
	</fx:Style>
</s:ViewNavigatorApplication>   

--- 数据库连接页面 MaintainingDataAppHome.mxml ---
<?xml version="1.0" encoding="utf-8"?>
<s:View xmlns:fx="http://ns.adobe.com/mxml/2009"
		xmlns:s="library://ns.adobe.com/flex/spark"
		title="数据管理-电话簿">
	    
      <fx:Script>
            <![CDATA[
				import flash.data.SQLConnection;
				import flash.data.SQLStatement;
				import flash.events.SQLErrorEvent;
				import flash.events.SQLEvent;
				import flash.filesystem.File;
				import flash.net.URLLoader;
				import flash.net.URLRequest;
				
				import air.net.URLMonitor;
				
				private var urlMonitor:URLMonitor;
				private var urlLoader:URLLoader;  
				private var urlRequest:URLRequest;  
				private var sqlConnection:SQLConnection;
				private var db:File;	
	
				/**
				 * 打开数据库
				 */
	            private function openDb():void
				{
					db = File.applicationStorageDirectory.resolvePath("directory.db");
					
					sqlConnection = new SQLConnection();   
					sqlConnection.addEventListener(SQLErrorEvent.ERROR, onSQLError);
	
					//判断市数据库是否存在
					if(db.exists) 
					{
					      sqlConnection.addEventListener(SQLEvent.OPEN, onOpenDb);
						  dbPath.text = db.nativePath; 
						  
					} else {
						sqlConnection.addEventListener(SQLEvent.OPEN, onCreateDb);
					}
					sqlConnection.openAsync(db);
				}

				/**
				 * 创建数据库成功
				 */
				private function onCreateDb(event:SQLEvent):void 
				{
					dbStatus.text = "数据库创建成功...";
					createUsersTable(); 
				}
				
				/**
				 * 连接数据库成功
				 */
				private function onOpenDb(e:SQLEvent):void 
				{      
				      dbStatus.text = "数据库连接成功...";
					
				      if(SQLConnection(e.target).connected)
					  {
					        viewBtn.visible = true; 
					  }
					  
					  dbBtn.enabled = false;
				} 
				
				/**
				 * SQL执行失败
				 */
				private function onSQLError(e:SQLErrorEvent):void 
				{     
					var err:String = "Error id:" + e.error.errorID 
						           + "\nDetails:" + e.error.details;
					dbStatus.text = err + "Error";
					
					dbBtn.enabled = false;
				}  
				
				/**
				 * 创建用户表
				 */
				private function createUsersTable():void
				{
					var sqlText:String = "CREATE TABLE " 
					                   + "Users(ID INTEGER PRIMARY KEY, "
									   + "NAME TEXT, TELEPHONE TEXT)";
					
					var createTableSQL:SQLStatement = new SQLStatement();
					createTableSQL.addEventListener(SQLEvent.RESULT, onUsersTable);
					createTableSQL.addEventListener(SQLErrorEvent.ERROR, onSQLError);
					createTableSQL.sqlConnection = sqlConnection;
					createTableSQL.text = sqlText;
					createTableSQL.execute();
				}
				
				/**
				 * 用户表创建成功响应
				 */
				private function onUsersTable(e:SQLEvent):void 
				{ 
				      dbStatus.text = "数据表Users创建成功。";      
					  dbBtn.setStyle('chromeColor', '#51B22F' );
				}
				
				/**
				 * 进入电话簿视图
				 */
				private function viewDirectory():void
				{
					navigator.pushView(views.UserView, sqlConnection);
				}
				
			]]>
		  
	</fx:Script>
	
	<s:layout>
		<s:VerticalLayout paddingLeft="20" paddingRight="20" 
						  paddingTop="20" paddingBottom="20"/>
	</s:layout>
	
	<s:VGroup width="100%">
		<s:Label id="dbStatus" width="100%" height="25" 
				 paddingLeft="5" fontSize="18" text="数据库状态"/>
		<s:TextArea id="dbPath" width="100%" height="140" 
					fontSize="18" prompt="数据库路径"/>
	</s:VGroup>

	<s:HGroup width="100%" height="65" verticalAlign="middle">
		<s:Button id="dbBtn" label="连接数据库" height="50" click="openDb()"/>
	</s:HGroup>

	<s:HGroup width="100%" height="50">
		<s:Button id="viewBtn" height="50" visible="false"
				  label="查看电话簿" click="viewDirectory()"/>
	</s:HGroup>
</s:View>

--- 电话簿页面 UserView.mxml ---
<?xml version="1.0" encoding="utf-8"?>
<s:View xmlns:fx="http://ns.adobe.com/mxml/2009" 
            xmlns:s="library://ns.adobe.com/flex/spark" 
            title="电话薄"
            viewActivate="onViewActive()">
	<fx:Script>
		<![CDATA[
			import mx.collections.ArrayCollection;
			
			private var sqlConnection:SQLConnection;
			
			/**
			 * 页面激活响应
			 */
			private function onViewActive():void
			{
				selectUsers();//加载用户列表
			}
			
			/**
			 * 加载用户列表
			 */
			private function selectUsers():void
			{
				sqlConnection = SQLConnection(data);
				
				var sqlText:String = "SELECT * FROM Users";
				var selectAllSQL:SQLStatement = new SQLStatement();
				selectAllSQL.addEventListener(SQLEvent.RESULT, onSelectUsers);
				selectAllSQL.addEventListener(SQLErrorEvent.ERROR, onSQLError);
				selectAllSQL.sqlConnection = sqlConnection;
				selectAllSQL.text = sqlText;
				selectAllSQL.execute();
			}
			
			/**
			 * 用户列表加载成功
			 */
			private function onSelectUsers(e:SQLEvent):void
			{
				var selectUsersSQL:SQLStatement = SQLStatement(e.target); 
				var result:SQLResult = selectUsersSQL.getResult();        
				
				if(result.complete)
				{
					if(result.data)
					{
						usersList.dataProvider = new ArrayCollection(result.data);
					}
				}
			}
			
			/**
			 * 添加联系人
			 */
			private function addUser():void 
			{
				var dataObj:Object = 
					{  
						sqlConnection:sqlConnection,
						sqlType:"INSERT"
					};
				
				navigator.pushView(views.UpdateUserView, dataObj); 
			}
			
			/**
			 * 修改联系人
			 */
			private function updateUser(i:int):void
			{    
				var userObj:Object = usersList.dataProvider.getItemAt(i);
				
				var dataObj:Object = 
					{  
						sqlConnection:sqlConnection,
						id:userObj.ID,
							name:userObj.NAME,
							telephone:userObj.TELEPHONE,
							sqlType:"UPDATE"
					};
				
				navigator.pushView(views.UpdateUserView, dataObj); 
			}
			
			/**
			 * 删除联系人
			 */
			private function deleteUser(i:int):void
			{      
				var userObj:Object = usersList.dataProvider.getItemAt(i);
				
				var sqlText:String = "DELETE FROM Users WHERE ID = :Id";
				
				var deleteUserSQL:SQLStatement = new SQLStatement();
				deleteUserSQL.sqlConnection = sqlConnection;
				deleteUserSQL.addEventListener(SQLEvent.RESULT, onDeleteUser);
				deleteUserSQL.addEventListener(SQLErrorEvent.ERROR, onSQLError);
				deleteUserSQL.text = sqlText;
				deleteUserSQL.parameters[":Id"] = userObj.ID;
				deleteUserSQL.execute();                  
			}
			
			/**
			 * 联系人删除响应
			 */
			private function onDeleteUser(e:SQLEvent):void 
			{
				dbStatus.text = "数据删除成功";
				selectUsers();
			} 
			
			/**
			 * SQL执行失败响应
			 */
			private function onSQLError(e:SQLErrorEvent):void 
			{
				dbStatus.text = e.error.errorID.toString();
			}
			
			/**
			 * 显示隐形底部菜单
			 */
			private function toggleMenu(toggle:Boolean):void 
			{
				mx.core.FlexGlobals.topLevelApplication.viewMenuOpen = toggle;
			}
		]]>
	</fx:Script>
	 
      <s:layout>
            <s:VerticalLayout paddingLeft="20" paddingRight="20" 
                              paddingTop="10" paddingBottom="10">
            </s:VerticalLayout>
      </s:layout>
	
      <s:Label id="dbStatus" width="100%" height="40" 
			   color="#454545" text="加载联系人....." verticalAlign="middle"/>
      
      <s:Label width="100%" height="50" fontSize="14"
               text="SELECT * FROM Users" verticalAlign="middle"/>
      
      <s:List id="usersList"
			  itemRenderer="views.components.renderers.UserItemRenderer"
			  click="toggleMenu(true)" width="100%" height="55%" 
			  enabled="true" selectedIndex="0"/>
      
      <s:HGroup width="100%" height="50" gap="16">
            <s:Button click="addUser()" label="新增联系人"/>
      </s:HGroup>
	
	<s:viewMenuItems>
		<s:ViewMenuItem label="修改" focusColor="#51B22F"
						click="updateUser(usersList.selectedIndex)"/>
		<s:ViewMenuItem label="取消" click="toggleMenu(false)"/>
		<s:ViewMenuItem label="删除" focusColor="#CB0909"
						click="deleteUser(usersList.selectedIndex)"/>
	</s:viewMenuItems>
</s:View>

--- List渲染器 UserItemRenderer.mxml ---
<?xml version="1.0" encoding="utf-8"?>
<s:IconItemRenderer xmlns:fx="http://ns.adobe.com/mxml/2009" 
					xmlns:s="library://ns.adobe.com/flex/spark" 
					labelField="NAME" 
					messageField="TELEPHONE" 
					alternatingItemColors="[0xCCCCCC, 0xEEEEEE]"
					selectionColor="#68bafa">
</s:IconItemRenderer>

--- 联系人编辑页面 UpdateUserView.mxml ---
<?xml version="1.0" encoding="utf-8"?>
<s:View xmlns:fx="http://ns.adobe.com/mxml/2009" 
            xmlns:s="library://ns.adobe.com/flex/spark" 
            title="电话薄"
            viewActivate="onViewActive()">
	<fx:Script>
		<![CDATA[
			import mx.collections.ArrayCollection;
			
			private var sqlConnection:SQLConnection;
			
			/**
			 * 页面激活响应
			 */
			private function onViewActive():void
			{
				selectUsers();//加载用户列表
			}
			
			/**
			 * 加载用户列表
			 */
			private function selectUsers():void
			{
				sqlConnection = SQLConnection(data);
				
				var sqlText:String = "SELECT * FROM Users";
				var selectAllSQL:SQLStatement = new SQLStatement();
				selectAllSQL.addEventListener(SQLEvent.RESULT, onSelectUsers);
				selectAllSQL.addEventListener(SQLErrorEvent.ERROR, onSQLError);
				selectAllSQL.sqlConnection = sqlConnection;
				selectAllSQL.text = sqlText;
				selectAllSQL.execute();
			}
			
			/**
			 * 用户列表加载成功
			 */
			private function onSelectUsers(e:SQLEvent):void
			{
				var selectUsersSQL:SQLStatement = SQLStatement(e.target); 
				var result:SQLResult = selectUsersSQL.getResult();        
				
				if(result.complete)
				{
					if(result.data)
					{
						usersList.dataProvider = new ArrayCollection(result.data);
					}
				}
			}
			
			/**
			 * 添加联系人
			 */
			private function addUser():void 
			{
				var dataObj:Object = 
					{  
						sqlConnection:sqlConnection,
						sqlType:"INSERT"
					};
				
				navigator.pushView(views.UpdateUserView, dataObj); 
			}
			
			/**
			 * 修改联系人
			 */
			private function updateUser(i:int):void
			{    
				var userObj:Object = usersList.dataProvider.getItemAt(i);
				
				var dataObj:Object = 
					{  
						sqlConnection:sqlConnection,
						id:userObj.ID,
							name:userObj.NAME,
							telephone:userObj.TELEPHONE,
							sqlType:"UPDATE"
					};
				
				navigator.pushView(views.UpdateUserView, dataObj); 
			}
			
			/**
			 * 删除联系人
			 */
			private function deleteUser(i:int):void
			{      
				var userObj:Object = usersList.dataProvider.getItemAt(i);
				
				var sqlText:String = "DELETE FROM Users WHERE ID = :Id";
				
				var deleteUserSQL:SQLStatement = new SQLStatement();
				deleteUserSQL.sqlConnection = sqlConnection;
				deleteUserSQL.addEventListener(SQLEvent.RESULT, onDeleteUser);
				deleteUserSQL.addEventListener(SQLErrorEvent.ERROR, onSQLError);
				deleteUserSQL.text = sqlText;
				deleteUserSQL.parameters[":Id"] = userObj.ID;
				deleteUserSQL.execute();                  
			}
			
			/**
			 * 联系人删除响应
			 */
			private function onDeleteUser(e:SQLEvent):void 
			{
				dbStatus.text = "数据删除成功";
				selectUsers();
			} 
			
			/**
			 * SQL执行失败响应
			 */
			private function onSQLError(e:SQLErrorEvent):void 
			{
				dbStatus.text = e.error.errorID.toString();
			}
			
			/**
			 * 显示隐形底部菜单
			 */
			private function toggleMenu(toggle:Boolean):void 
			{
				mx.core.FlexGlobals.topLevelApplication.viewMenuOpen = toggle;
			}
		]]>
	</fx:Script>
	 
      <s:layout>
            <s:VerticalLayout paddingLeft="20" paddingRight="20" 
                              paddingTop="10" paddingBottom="10">
            </s:VerticalLayout>
      </s:layout>
	
      <s:Label id="dbStatus" width="100%" height="40" 
			   color="#454545" text="加载联系人....." verticalAlign="middle"/>
      
      <s:Label width="100%" height="50" fontSize="14"
               text="SELECT * FROM Users" verticalAlign="middle"/>
      
      <s:List id="usersList"
			  itemRenderer="views.components.renderers.UserItemRenderer"
			  click="toggleMenu(true)" width="100%" height="55%" 
			  enabled="true" selectedIndex="0"/>
      
      <s:HGroup width="100%" height="50" gap="16">
            <s:Button click="addUser()" label="新增联系人"/>
      </s:HGroup>
	
	<s:viewMenuItems>
		<s:ViewMenuItem label="修改" focusColor="#51B22F"
						click="updateUser(usersList.selectedIndex)"/>
		<s:ViewMenuItem label="取消" click="toggleMenu(false)"/>
		<s:ViewMenuItem label="删除" focusColor="#CB0909"
						click="deleteUser(usersList.selectedIndex)"/>
	</s:viewMenuItems>
</s:View>

源码下载:MaintainingDataApp.zip
评论0