 SQLite SQL Language Syntax Diagrams.pdf |
SQLite 둘러보기
SQLite 를 사용한 간단한 AIR 예제(그러나 몇가지 오류가 있어서 수정)
<?xml version="1.0" encoding="utf-8"?>
<mx:WindowedApplication xmlns:mx="http://www.adobe.com/2006/mxml" applicationComplete="init()" layout="absolute">
<mx:Script>
<![CDATA[
import mx.validators.ValidationResult;
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 init():void{
stage.displayState = StageDisplayState.NORMAL;
connectDB();
}
//데이터 베이스가 있는지 확인, 없으면 새테이블 만들기
private function connectDB():void{
var dbFile:File = File.desktopDirectory.resolvePath("Employee.db");
if(dbFile.exists){
conn.addEventListener(SQLEvent.OPEN, openHandler);
conn.open(dbFile, "update");
} else {
conn.addEventListener(SQLEvent.OPEN, newDatabaseHandler);
conn.open(dbFile);
}
}
private function openHandler(event:SQLEvent):void{
selectData();
situation.text = "database already is 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();
situation.text = "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{
situation.text = "created table named - employees";
}
// 데이터 삽입
private function insertData():void {
var insertStmt:SQLStatement = new SQLStatement();
insertStmt.sqlConnection = conn;
var sql:String = "INSERT INTO employees "+"(firstName, lastName, salary) values ('eric','moon', 90000)";
insertStmt.text = sql;
insertStmt.addEventListener(SQLEvent.RESULT, insertHandler);
insertStmt.addEventListener(SQLErrorEvent.ERROR, errorHandler);
insertStmt.execute();
}
private function insertHandler(event:SQLEvent):void{
situation.text = "item is inserted";
selectData();
}
//데이터 삭제
private function deleteData():void {
if(resultAC.length > 0 && dg.selectedItem!=null){
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(empid);
trace(resultAC.length);
}
}
private function deleteHandler(event:SQLEvent):void{
situation.text = "item is deleted";
selectData();
}
//에러처리
private function errorHandler(event:SQLErrorEvent):void{
var errMessage:String;
situation.text += errMessage;
}
//데이터 업데이트
private function updateData():void {
var updateStmt:SQLStatement = new SQLStatement();
updateStmt.sqlConnection = conn;
if(dg.selectedItem != null){
var sql:String = "UPDATE employees SET firstName='"+ dg.selectedItem.firstName + "', "+
"lastName='"+ dg.selectedItem.lastName + "', salary=" + dg.selectedItem.salary + " "+
"WHERE empId=" + dg.selectedItem.empId;
trace(sql);
updateStmt.text = sql;
updateStmt.addEventListener(SQLEvent.RESULT, updateHandler);
updateStmt.addEventListener(SQLErrorEvent.ERROR, errorHandler);
updateStmt.execute();
}
}
private function updateHandler(event:SQLEvent):void{
situation.text = "modified";
selectData();
}
]]>
</mx:Script>
<mx:ArrayCollection id="resultAC" source="{ArrayUtil.toArray(resultData)}">
</mx:ArrayCollection>
<mx:Panel x="10" y="10" width="517" height="428" layout="absolute" title="SQLite example" fontSize="11" cornerRadius="0">
<mx:DataGrid x="10" y="10" editable="true" id="dg" dataProvider="{resultAC}">
<mx:columns>
<mx:DataGridColumn headerText="number" dataField="empId"></mx:DataGridColumn>
<mx:DataGridColumn headerText="sexual" dataField="lastName"></mx:DataGridColumn>
<mx:DataGridColumn headerText="name" dataField="firstName"></mx:DataGridColumn>
<mx:DataGridColumn headerText="salary" dataField="salary"></mx:DataGridColumn>
</mx:columns>
</mx:DataGrid>
<mx:Button x="10" y="210" label="create TABLE" click="createTable()" />
<mx:Button x="125" y="210" label="view" click="selectData()"/>
<mx:Button x="182" y="210" label="insert" click="insertData()"/>
<mx:Button x="250" y="210" label="modify" click="updateData()"/>
<mx:Button x="325" y="210" label="delete" click="deleteData()"/>
<mx:TextArea x="10" y="248" width="414" fontSize="14" color="#f80421" text="state" height="25" id="situation"/>
</mx:Panel>
</mx:WindowedApplication>
