<?xml version="1.0" encoding="utf-8"?>
<s:WindowedApplication xmlns:fx="http://ns.adobe.com/mxml/2009"
xmlns:s="library://ns.adobe.com/flex/spark"
xmlns:mx="library://ns.adobe.com/flex/mx">
<fx:Declarations>
<!-- 将非可视元素(例如服务、值对象)放在此处 -->
</fx:Declarations>
<fx:Script>
<![CDATA[
import mx.collections.ArrayCollection;
private var con:SQLConnection;
private var stmt:SQLStatement;
private var dbFile:File = File.documentsDirectory.resolvePath("MacroEconomicResearchData.sqlite.db");
/**
* 打开文件选择对话框,选择 DB 文件
*/
protected function selectDBFile(root:File):void
{
try
{
root.browseForSave("打开 DB 文件");
root.addEventListener(Event.SELECT, checkDBFileFormat);
}
catch(error:Error)
{
trace("打开失败:" + error.message);
}
}
/**
* 以字节流方式读取文件内容,检查文件是否 SQLite 格式
*/
protected function checkDBFileFormat(event:Event):void
{
if(this.dbFile.exists)
{
var stream:FileStream = new FileStream();
stream.open(this.dbFile, FileMode.READ);
var s:String = stream.readUTFBytes(20);
if(s.substr(0,16) == "SQLite format 3"){
stream.close();
trace("db file format ok fit");
this.openDBFile();
}else{
stream.close();
trace("db file format not fit");
this.dbFile = File.documentsDirectory.resolvePath("MacroEconomicResearchData.sqlite.db");
}
}
else
{
trace("new file");
this.openDBFile();
}
}
/**
* 以数据库方式异步打开文件
*/
private function openDBFile():void{
con = new SQLConnection();
con.addEventListener(SQLEvent.OPEN, dbInitStruct);
con.addEventListener(SQLErrorEvent.ERROR, dbErrorHandler);
con.openAsync(this.dbFile);
}
/**
* 数据库访问错误处理程式
*/
private function dbErrorHandler(event:SQLErrorEvent):void
{
trace("Error message:", event.error.message);
trace("error Details:", event.error.details);
}
/**
* 批量SQL集合的游标指针
*/
private var _execSQLCount:int = 0;
/**
* 批量SQL集合数组,不能用 ArrayList
*/
private var _execSQLBatch:Array = new Array();
/**
* 执行完毕全部SQL语句后要调用的函数(无参数)
*/
private var _execSQLCallLatency:Function;
/**
* 批量执行SQL语句
*/
private function dbExecuteBatchSQL(evt:SQLEvent):void{
this._execSQLCount++;
trace(this._execSQLCount , this._execSQLBatch.length);
if(this._execSQLCount > this._execSQLBatch.length){
this._execSQLCount = 0;
this.stmt.removeEventListener(SQLEvent.RESULT, dbExecuteBatchSQL);
if(this._execSQLCallLatency != null)
{
this._execSQLCallLatency.call();
//this._execSQLCallLatency = null; DO NOT SET TO NULL, OR IT WILL NOT RUN
}
}else{
var sql:String = this._execSQLBatch[this._execSQLCount-1] as String;
trace(sql);
this.stmt.text = sql;
this.stmt.execute();
}
}
/**
* 数据结构初始化,建立三个数据表
*/
private function dbInitStruct(evt:SQLEvent):void
{
this._execSQLBatch = new Array();
this._execSQLBatch.push("CREATE TABLE IF NOT EXISTS stockdaily (" +
" ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
" StatDate DATE," +
" Symbol VARCHAR(30)," +
" FinalPrice FLOAT," +
" HighPrice FLOAT," +
" LowPrice FLOAT," +
" DealVolume FLOAT," +
" DealMoney FLOAT," +
" RunDate DATETIME" +
");");
this._execSQLBatch.push("CREATE TABLE IF NOT EXISTS stockdailywatch (" +
" ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
" Symbol VARCHAR(30)," +
" Name VARCHAR(30)" +
");");
this._execSQLBatch.push("CREATE TABLE IF NOT EXISTS stockmarketdaily (" +
" ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
" StatDate DATE," +
" SHDealMoney FLOAT(8,2)," +
" SZDealMoney FLOAT(8,2)," +
" SHDealMoneyComp FLOAT(6,2)," +
" SZDealMoneyComp FLOAT(6,2)," +
" SHAvgPE FLOAT(6,2)," +
" SZAvgPE FLOAT(6,2)," +
" SHAvgPB FLOAT(6,2)," +
" SZAvgPB FLOAT(6,2)," +
" SHAvgPrice FLOAT(7,2)," +
" SZAvgPrice FLOAT(7,2)," +
" RunDateTime DATETIME" +
");");
this.stmt = new SQLStatement();
this.stmt.sqlConnection = con;
this.stmt.addEventListener(SQLErrorEvent.ERROR, dbErrorHandler);
this.stmt.addEventListener(SQLEvent.RESULT, dbExecuteBatchSQL);
this._execSQLCallLatency = this.dbInitData;
this.dbExecuteBatchSQL(null);
}
/**
* 数据初始化,插入两条记录
*/
private function dbInitData(event:SQLEvent=null):void
{
trace("SQLite 数据库初始化完成");
stmt.addEventListener(SQLEvent.RESULT, dbExecuteBatchSQL);
this._execSQLBatch = new Array();
this._execSQLBatch.push("INSERT INTO stockdailywatch (Symbol,Name) VALUES ('1A0001','上证指数')");
this._execSQLBatch.push("INSERT INTO stockdaily (StatDate,Symbol,FinalPrice,RunDate) VALUES (date('2010-7-26'),'600066',16.00,date('now'))");
this._execSQLCallLatency = this.dbQueryData;
this.dbExecuteBatchSQL(null);
}
/**
* 从两个数据表中查询内容
*/
private function dbQueryData(event:SQLEvent=null):void
{
trace("SQLite 数据插入完成");
this.dbQueryONE();
this.dbQueryTWO();
}
/**
* 发起查询第一个数据表的内容
*/
private function dbQueryONE():void{
var stmt:SQLStatement = new SQLStatement();
stmt.sqlConnection = this.con;
stmt.text = "SELECT Symbol,Name FROM stockdailywatch";
stmt.execute(-1, new Responder(dbShowDataONE, dbErrorHandler));
}
/**
* 发起查询第二个数据表的内容
*/
private function dbQueryTWO():void{
var stmt:SQLStatement = new SQLStatement();
stmt.sqlConnection = this.con;
stmt.text = "SELECT Symbol,FinalPrice,RunDate FROM stockdaily";
stmt.execute(-1, new Responder(dbShowDataTWO, dbErrorHandler));
}
/**
* 获得第一个数据表的查询结果,导入 datagrid
*/
private function dbShowDataONE(result:SQLResult):void
{
trace("ONE 查询成功, 影响"+result.rowsAffected+"条记录");
if(result.data!=null )
{
trace("ONE 得到"+result.data.length + "个结果");
var c:ArrayCollection = new ArrayCollection(result.data);
var numResults:int =result.data.length;
this.dgOne.dataProvider = c;
}
}
/**
* 获得第二个数据表的查询结果,导入 datagrid
*/
private function dbShowDataTWO(result:SQLResult):void
{
trace("TWO 查询成功, 影响"+result.rowsAffected+"条记录");
if(result.data!=null )
{
trace("TWO 得到"+result.data.length + "个结果");
var c:ArrayCollection = new ArrayCollection(result.data);
var numResults:int =result.data.length;
this.dgTwo.dataProvider = c;
}
}
/**
* 按钮点击事件,触发以上全部过程
*/
protected function btnOK_clickHandler(event:MouseEvent):void
{
this.selectDBFile(this.dbFile);
}
]]>
</fx:Script>
<s:Button x="210" y="10" label="按钮" id="btnOK" click="btnOK_clickHandler(event)"/>
<mx:DataGrid x="10" y="46" width="426" id="dgOne">
<mx:columns>
<mx:DataGridColumn headerText="ID" dataField="ID"/>
<mx:DataGridColumn headerText="Symbol" dataField="Symbol"/>
<mx:DataGridColumn headerText="Name" dataField="Name"/>
</mx:columns>
</mx:DataGrid>
<mx:DataGrid x="10" y="210" width="426" id="dgTwo">
<mx:columns>
<mx:DataGridColumn headerText="RunDate" dataField="RunDate"/>
<mx:DataGridColumn headerText="Symbol" dataField="Symbol"/>
<mx:DataGridColumn headerText="FinalPrice" dataField="FinalPrice"/>
</mx:columns>
</mx:DataGrid>
</s:WindowedApplication>
<s:WindowedApplication xmlns:fx="http://ns.adobe.com/mxml/2009"
xmlns:s="library://ns.adobe.com/flex/spark"
xmlns:mx="library://ns.adobe.com/flex/mx">
<fx:Declarations>
<!-- 将非可视元素(例如服务、值对象)放在此处 -->
</fx:Declarations>
<fx:Script>
<![CDATA[
import mx.collections.ArrayCollection;
private var con:SQLConnection;
private var stmt:SQLStatement;
private var dbFile:File = File.documentsDirectory.resolvePath("MacroEconomicResearchData.sqlite.db");
/**
* 打开文件选择对话框,选择 DB 文件
*/
protected function selectDBFile(root:File):void
{
try
{
root.browseForSave("打开 DB 文件");
root.addEventListener(Event.SELECT, checkDBFileFormat);
}
catch(error:Error)
{
trace("打开失败:" + error.message);
}
}
/**
* 以字节流方式读取文件内容,检查文件是否 SQLite 格式
*/
protected function checkDBFileFormat(event:Event):void
{
if(this.dbFile.exists)
{
var stream:FileStream = new FileStream();
stream.open(this.dbFile, FileMode.READ);
var s:String = stream.readUTFBytes(20);
if(s.substr(0,16) == "SQLite format 3"){
stream.close();
trace("db file format ok fit");
this.openDBFile();
}else{
stream.close();
trace("db file format not fit");
this.dbFile = File.documentsDirectory.resolvePath("MacroEconomicResearchData.sqlite.db");
}
}
else
{
trace("new file");
this.openDBFile();
}
}
/**
* 以数据库方式异步打开文件
*/
private function openDBFile():void{
con = new SQLConnection();
con.addEventListener(SQLEvent.OPEN, dbInitStruct);
con.addEventListener(SQLErrorEvent.ERROR, dbErrorHandler);
con.openAsync(this.dbFile);
}
/**
* 数据库访问错误处理程式
*/
private function dbErrorHandler(event:SQLErrorEvent):void
{
trace("Error message:", event.error.message);
trace("error Details:", event.error.details);
}
/**
* 批量SQL集合的游标指针
*/
private var _execSQLCount:int = 0;
/**
* 批量SQL集合数组,不能用 ArrayList
*/
private var _execSQLBatch:Array = new Array();
/**
* 执行完毕全部SQL语句后要调用的函数(无参数)
*/
private var _execSQLCallLatency:Function;
/**
* 批量执行SQL语句
*/
private function dbExecuteBatchSQL(evt:SQLEvent):void{
this._execSQLCount++;
trace(this._execSQLCount , this._execSQLBatch.length);
if(this._execSQLCount > this._execSQLBatch.length){
this._execSQLCount = 0;
this.stmt.removeEventListener(SQLEvent.RESULT, dbExecuteBatchSQL);
if(this._execSQLCallLatency != null)
{
this._execSQLCallLatency.call();
//this._execSQLCallLatency = null; DO NOT SET TO NULL, OR IT WILL NOT RUN
}
}else{
var sql:String = this._execSQLBatch[this._execSQLCount-1] as String;
trace(sql);
this.stmt.text = sql;
this.stmt.execute();
}
}
/**
* 数据结构初始化,建立三个数据表
*/
private function dbInitStruct(evt:SQLEvent):void
{
this._execSQLBatch = new Array();
this._execSQLBatch.push("CREATE TABLE IF NOT EXISTS stockdaily (" +
" ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
" StatDate DATE," +
" Symbol VARCHAR(30)," +
" FinalPrice FLOAT," +
" HighPrice FLOAT," +
" LowPrice FLOAT," +
" DealVolume FLOAT," +
" DealMoney FLOAT," +
" RunDate DATETIME" +
");");
this._execSQLBatch.push("CREATE TABLE IF NOT EXISTS stockdailywatch (" +
" ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
" Symbol VARCHAR(30)," +
" Name VARCHAR(30)" +
");");
this._execSQLBatch.push("CREATE TABLE IF NOT EXISTS stockmarketdaily (" +
" ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
" StatDate DATE," +
" SHDealMoney FLOAT(8,2)," +
" SZDealMoney FLOAT(8,2)," +
" SHDealMoneyComp FLOAT(6,2)," +
" SZDealMoneyComp FLOAT(6,2)," +
" SHAvgPE FLOAT(6,2)," +
" SZAvgPE FLOAT(6,2)," +
" SHAvgPB FLOAT(6,2)," +
" SZAvgPB FLOAT(6,2)," +
" SHAvgPrice FLOAT(7,2)," +
" SZAvgPrice FLOAT(7,2)," +
" RunDateTime DATETIME" +
");");
this.stmt = new SQLStatement();
this.stmt.sqlConnection = con;
this.stmt.addEventListener(SQLErrorEvent.ERROR, dbErrorHandler);
this.stmt.addEventListener(SQLEvent.RESULT, dbExecuteBatchSQL);
this._execSQLCallLatency = this.dbInitData;
this.dbExecuteBatchSQL(null);
}
/**
* 数据初始化,插入两条记录
*/
private function dbInitData(event:SQLEvent=null):void
{
trace("SQLite 数据库初始化完成");
stmt.addEventListener(SQLEvent.RESULT, dbExecuteBatchSQL);
this._execSQLBatch = new Array();
this._execSQLBatch.push("INSERT INTO stockdailywatch (Symbol,Name) VALUES ('1A0001','上证指数')");
this._execSQLBatch.push("INSERT INTO stockdaily (StatDate,Symbol,FinalPrice,RunDate) VALUES (date('2010-7-26'),'600066',16.00,date('now'))");
this._execSQLCallLatency = this.dbQueryData;
this.dbExecuteBatchSQL(null);
}
/**
* 从两个数据表中查询内容
*/
private function dbQueryData(event:SQLEvent=null):void
{
trace("SQLite 数据插入完成");
this.dbQueryONE();
this.dbQueryTWO();
}
/**
* 发起查询第一个数据表的内容
*/
private function dbQueryONE():void{
var stmt:SQLStatement = new SQLStatement();
stmt.sqlConnection = this.con;
stmt.text = "SELECT Symbol,Name FROM stockdailywatch";
stmt.execute(-1, new Responder(dbShowDataONE, dbErrorHandler));
}
/**
* 发起查询第二个数据表的内容
*/
private function dbQueryTWO():void{
var stmt:SQLStatement = new SQLStatement();
stmt.sqlConnection = this.con;
stmt.text = "SELECT Symbol,FinalPrice,RunDate FROM stockdaily";
stmt.execute(-1, new Responder(dbShowDataTWO, dbErrorHandler));
}
/**
* 获得第一个数据表的查询结果,导入 datagrid
*/
private function dbShowDataONE(result:SQLResult):void
{
trace("ONE 查询成功, 影响"+result.rowsAffected+"条记录");
if(result.data!=null )
{
trace("ONE 得到"+result.data.length + "个结果");
var c:ArrayCollection = new ArrayCollection(result.data);
var numResults:int =result.data.length;
this.dgOne.dataProvider = c;
}
}
/**
* 获得第二个数据表的查询结果,导入 datagrid
*/
private function dbShowDataTWO(result:SQLResult):void
{
trace("TWO 查询成功, 影响"+result.rowsAffected+"条记录");
if(result.data!=null )
{
trace("TWO 得到"+result.data.length + "个结果");
var c:ArrayCollection = new ArrayCollection(result.data);
var numResults:int =result.data.length;
this.dgTwo.dataProvider = c;
}
}
/**
* 按钮点击事件,触发以上全部过程
*/
protected function btnOK_clickHandler(event:MouseEvent):void
{
this.selectDBFile(this.dbFile);
}
]]>
</fx:Script>
<s:Button x="210" y="10" label="按钮" id="btnOK" click="btnOK_clickHandler(event)"/>
<mx:DataGrid x="10" y="46" width="426" id="dgOne">
<mx:columns>
<mx:DataGridColumn headerText="ID" dataField="ID"/>
<mx:DataGridColumn headerText="Symbol" dataField="Symbol"/>
<mx:DataGridColumn headerText="Name" dataField="Name"/>
</mx:columns>
</mx:DataGrid>
<mx:DataGrid x="10" y="210" width="426" id="dgTwo">
<mx:columns>
<mx:DataGridColumn headerText="RunDate" dataField="RunDate"/>
<mx:DataGridColumn headerText="Symbol" dataField="Symbol"/>
<mx:DataGridColumn headerText="FinalPrice" dataField="FinalPrice"/>
</mx:columns>
</mx:DataGrid>
</s:WindowedApplication>