AIR에서 로컬 SQL 데이터베이스로 작업하기

AIR 런타임에는 표준 SQL 지원하는 데이터베이스(Sqlight)가 내장되어있다.

SQLite는 자기완비, 임베디드, 무설정 SQL 데이터 베이스엔진으로 구성된 작은 C라이브러이다.

 

AIR 사용자 가이드를 참조해서 로컬데이터 베이스 사용에 관해 간략하게 작성해 보았습니다.

1.    가장 먼저 데이터베이스 작업을 하기위해서는  SQLConnection 인스턴스로 데이터 베이스 파일에 연결해야 한다.

2.    SQLConnection 인스턴스 open()메서드로 데이터베이스를 오픈해야 한다.

3.   필요한 SQLStatement 인스턴스를 생성하고 sqlconnection 속성에  접속하려는 SQLConnection 인스턴스를 할당한다.

4.   SQLStatement 인스턴스에 SQLEvent를 등록과 필요한 함수를 구현한다.

5. 마지막으로 SQLStatement 인스턴스 execute()메서드를 실행한다.

 

<?xml version="1.0" encoding="utf-8"?>

<mx:WindowedApplication xmlns:mx="http://www.adobe.com/2006/mxml" applicationComplete="init()"

backgroundColor="0x003030" focusRect="false" layout="vertical">

  

<mx:Script>

 <![CDATA[

 import mx.controls.List;

 import mx.controls.TextInput;

 import mx.events.DataGridEvent;

 import mx.collections.ArrayCollection; 

 import mx.utils.ArrayUtil;

 import mx.controls.Alert;

 import flash.data.SQLConnection;

 import flash.data.SQLResult;

 import flash.data.SQLStatement; 

 import flash.events.SQLErrorEvent;

 import flash.events.SQLEvent;

 import mx.events.ListEvent;

 import flash.filesystem.File;

 

 [Bindable]

 private var resultData:Array;

 

 private var conn:SQLConnection = new SQLConnection();

 private var selectStmt:SQLStatement =  new SQLStatement();

 

 private function connectDB():void{

   

// 로컬 데이터베이스 존재 확인 그리고 열려져 있는지 또는 새 테이블 만들기

  var dbFile:File = File.applicationStorageDirectory.resolve("Employee.db");

  if(dbFile.exists) {

   conn.addEventListener(SQLEvent.OPEN, openHandler);

   conn.open(dbFile);

  }else {

   conn.addEventListener(SQLEvent.OPEN, newDatabaseHandler);

   conn.open(dbFile);

  }

 }

 

 private function openHandler(event:SQLEvent):void{

    selectData();

  trace("The database was opened");

 }

 

 

 

// 데이터 가져오기

 private function selectData():void{ 

  selectStmt.sqlConnection = conn;

  var sql:String = "SELECT * FROM employees";

  selectStmt.text = sql;

 

  selectStmt.addEventListener(SQLEvent.RESULT, selectHandler);

  selectStmt.addEventListener(SQLErrorEvent.ERROR, errorHandler);

  selectStmt.execute();

 }

 

 private function selectHandler(event:SQLEvent):void{

  resultData = selectStmt.getResult().data;

 }

 

// 새 테이블 생성하기

 private function newDatabaseHandler(event:SQLEvent):void{

  createTable();

  trace("Table created");

 }

 

 private function createTable():void{

  var createStmt:SQLStatement =  new SQLStatement();

  createStmt.sqlConnection = conn;

  var sql:String =

  "CREATE TABLE IF NOT EXISTS employees (" +

  "empId INTEGER PRIMARY KEY AUTOINCREMENT, " +

  "firstName TEXT, " +

  "lastName TEXT, " +

  "salary NUMERIC CHECK (salary > 0)" +

  ")";

  createStmt.text = sql;

  createStmt.addEventListener(SQLEvent.RESULT, createTableResult);

  createStmt.addEventListener(SQLErrorEvent.ERROR, errorHandler);

  createStmt.execute();

 }

 

 private function createTableResult(event:SQLEvent):void{

          trace("Employees table created ");

    }

 

//데이터 삽입

 

private function insertData():void{

  var insertStmt:SQLStatement =  new SQLStatement();

  insertStmt.sqlConnection = conn;

  var sql:String = "INSERT INTO employees (firstName, lastName, salary) VALUES ('Eric', 'Moon', 80000)"; 

  insertStmt.text = sql;

 

  insertStmt.addEventListener(SQLEvent.RESULT, insertHandler);

  insertStmt.addEventListener(SQLErrorEvent.ERROR, errorHandler);

  insertStmt.execute();

 }

 

 private function insertHandler(event:SQLEvent):void{

  trace("INSERT statement succeeded");

  selectData();

 }

 

 

// 데이터 삭제

 private function deleteData():void{

  if (resultAC.length >0)

 

  var empid:int = dg.selectedItem.empId;

   

  var deleteStmt:SQLStatement =  new SQLStatement();

  deleteStmt.sqlConnection = conn;

 

  var sql:String = "DELETE FROM employees WHERE empID="+empid; 

  deleteStmt.text = sql;

 

  deleteStmt.addEventListener(SQLEvent.RESULT, deleteHandler);

  deleteStmt.addEventListener(SQLErrorEvent.ERROR, errorHandler);

  deleteStmt.execute();

  resultAC.removeItemAt( dg.selectedIndex);

 }

 

 private function deleteHandler(event:SQLEvent):void{

  trace("Delete statement succeeded");

  selectData();

 }     

 

 

 

 

 // 에러 처리

 private function errorHandler(event:SQLErrorEvent):void{

  var errMessage:String;

  errMessage  = "Error code: " + event.error.code  + "\r";

  errMessage += "Details   : " + event.error.message  ;

  Alert.show(errMessage);

 }

 

 private function init():void{

  stage.displayState = StageDisplayState.FULL_SCREEN;

  connectDB();

 }

 

// 데이터 업데이트   

  private function updateData():void{

  var updateStmt:SQLStatement =  new SQLStatement();

  updateStmt.sqlConnection = conn;

 

  var sql:String = "UPDATE employees " +

    "SET firstName = '" +  dg.selectedItem.firstName + "', " +

    "lastName = '" + dg.selectedItem.lastName + "', " +

    "salary = '" + dg.selectedItem.salary + "' " +

    "WHERE empID="+ dg.selectedItem.empId; 

 

  updateStmt.text = sql;

 

  updateStmt.addEventListener(SQLEvent.RESULT, updateHandler);

  updateStmt.addEventListener(SQLErrorEvent.ERROR, errorHandler);

  updateStmt.execute();

     }

 

 private function updateHandler(event:SQLEvent):void{

  trace("Update statement succeeded");

  selectData();

 }

 ]]>

</mx:Script>

 

 

 

<mx:ArrayCollection id="resultAC" source ="{ArrayUtil.toArray(resultData)}" />

 

<mx:VBox height="100%">

 

<mx:DataGrid id="dg"  dataProvider="{resultAC}" editable="true" height="100%" width="100%">

<mx:columns>

 <mx:DataGridColumn headerText="Emp ID" dataField="empId"/>

 <mx:DataGridColumn headerText="Last Name" dataField="lastName"/>

 <mx:DataGridColumn headerText="First Name" dataField="firstName"/>

 <mx:DataGridColumn headerText="Salary" dataField="salary"/>

</mx:columns>

</mx:DataGrid>

 

<mx:HBox width="100%">

 <mx:Button label="Create Table" click="createTable();"/>

 <mx:Button label="Select" click="selectData();"/>

 <mx:Button label="Insert" click="insertData()"/>

 <mx:Button label="Update" click="updateData();"/>

 <mx:Button label="Delete" click="deleteData();"/>

</mx:HBox>

</mx:VBox>

</mx:WindowedApplication>