[ Flex ][ Source ] SQLite 기본 소스 - AIR 전용

■ SQLite : AIR 전용

 

< ?xml version="1.0" encoding="utf-8"?>
< mx:Panel
      layout="vertical"
      width="100%" height="100%"
      fontSize="14"
      creationComplete="initApp()"
      xmlns:mx="http://www.adobe.com/2006/mxml">
 
      <mx:Label text="SQLite TEST" />
  
      <mx:ArrayCollection id="resultAC"
            source="{ArrayUtil.toArray(resultData)}" />
 
      <mx:VBox height="100%">
           <mx:DataGrid id="dg"
                  width="100%" height="100%"
                  editable="true"
                  dataProvider="{resultAC}">
                  <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:Script>
            <![CDATA[
  
                  import flash.data.*;
  
                  import mx.utils.ArrayUtil;
   
                  [Bindable]
                  private var resultData:Array;
                  private var conn:SQLConnection = new SQLConnection();
                  private var selectStmt:SQLStatement = new SQLStatement();
   
   
                  // Main Function
                  private function initApp():void {
                        //stage.displayState = StageDisplayState.FULL_SCREEN;
                        connectDB();
                        trace("경로 : " + File.applicationDirectory.nativePath);
                  }
   
                  // 로컬 데이터베이스 존재 확인 그리고 열려져 있는지 또는 새 테이블 만들기
                  private function connectDB():void {
                        var dbFile:File = File.applicationStorageDirectory.resolvePath(File.applicationDirectory.nativePath + "/Employee.db");

                        // dbFile = File.applicationDirectory.resolvePath("kr/co/dtitech/assets/b.swf"); // 검증안됨
                        if(dbFile.exists) {
                              conn.addEventListener(SQLEvent.OPEN, openHandler);
                              conn.openAsync(dbFile); 
                        } else {
                              conn.addEventListener(SQLEvent.OPEN, newDatabaseHandler);
                              conn.openAsync(dbFile, "create");
                        }
                  }
   
                  private function openHandler(event:SQLEvent):void {
                        selectData();
                        trace("데이타베이스 오픈");
                  }
   
                  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("테이블 생성");
                  }
   
                  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 테이블 생성");
                  }
   
                  // 에러처리
                  private function errorHandler(event:SQLErrorEvent):void {
                        var errMessage:String;
                        errMessage = "Error code : " + event.error.message + "
";
                        errMessage += "Details : " + event.error.details;
                        trace(errMessage);
                  }
   
                  // 데이터 삽입
                  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 성공");
                        selectData();
                  }
   
                  // 데이터 업데이트
                  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 성공");
                        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 성공");
                        selectData();
                  }
            ]]>
      </mx:Script>
 
< /mx:Panel>