package sqlTest{
    import flash.data.SQLConnection;
    import flash.data.SQLResult;
    import flash.data.SQLStatement;
    import flash.events.SQLErrorEvent;
    import flash.events.SQLEvent;
    import flash.filesystem.File;
    import flash.net.Responder;

    import mx.controls.Alert;

    public class SQLTest{
        private var con:SQLConnection;
        private var stmt:SQLStatement;
        private var creatTableSQL:String ="CREATE TABLE IF NOT EXISTS user ( "+
                                        "userId INTEGER PRIMARY KEY AUTOINCREMENT, " +
                                        "userName TEXT, " +
                                        "password TEXT )"; 

        private var insertSQL:String = "INSERT INTO user (userName, password) VALUES ('admin', 'admin')";
        //语句参数       :itemName  @firstName 或者  ?  使用问号时只能用索引方式设值 索引从0开始
        private var insertSQLparams:String = "INSERT INTO user (userName, password) VALUES (:userName, @password)";
        private var deleteSQLparams:String = "delete from user where userName=:userName"
        private var updateSQLparams:String = "update user set password= ? where userName= ?";
        private var selectSQL:String = "select userName,password from user"
        public function SQLTest(){
            initApp();

        }
        private function initApp():void{
            var file:File = File.applicationStorageDirectory.resolvePath("MySQLite.sqlite")
            con = new SQLConnection();
            //异步
//          asyncOpen(con,file);
            //同步
            open(con,file);
        }

        private function open(con:SQLConnection,file:File):void{
            try{
                //若参数为空则建立在内存上 若文件不存在则自动创建
                con.open(file);
                con.begin();
                Alert.show("Create/open database finished");
                stmt = new SQLStatement();
                stmt.sqlConnection = con;
                //1.create table
                stmt.text = creatTableSQL;
                //2.insert data
                stmt.text = insertSQL;
                //带占位符insert
//              stmt.text = insertSQLparams;
//              stmt.parameters[":userName"] = "aaa";
//              stmt.parameters["@password"] = "admin";
                //3.delete
//              stmt.text = deleteSQLparams;
//              stmt.parameters[0] = "admin";
                //4.update
//              stmt.text = updateSQLparams;
//              stmt.parameters[0] = "aaa";
//              stmt.parameters[1] = "aaa";
                //5.select
//              stmt.text = selectSQL;
//              var responder:Responder = new Responder("resultHandler", "errorHandler");
//              stmt.execute(1,responder);//此处可以指定返回行数 ，若不使用responder则添加2个监听器如下
//              stmt.addEventListener(SQLEvent.RESULT,resultHandler);
//              stmt.addEventListener(SQLErrorEvent.ERROR,errorHandler);
                stmt.execute();
                var result:SQLResult = stmt.getResult();
                //row
                if(result!=null){
                    //上一次INSERT 操作的行标识符  如果执行的语句不是 INSERT 语句，则该值为 0
                    var row:Number = result.lastInsertRowID;
                     row = result.rowsAffected;
                    Alert.show("Row"+row.toString());
                }
                //data
                if(result.data!=null){
                    var numResults:int =result.data.length;

                    for (var i:int = 0; i < numResults; i++) {
                        var re:Object = result.data[i];
                        var output:String = "userName: " + re.userName;
                        output += "; password: " + re.password;
                        Alert.show(output);
                    }
                }

                Alert.show("finished");
                con.commit();
            }catch(error:SQLErrorEvent){
                con.rollback();
                errorHandler(error);
            }
        }

        private function asyncOpen(con:SQLConnection,file:File):void{
            // openAsync SQLConnection 对象操作导致错误时调度
            con.addEventListener(SQLErrorEvent.ERROR,errorHandler);
            // openAsync 方法调用操作成功完成时调度
            con.addEventListener(SQLEvent.OPEN,openHandler);
            //若参数为空则建立在内存上 若文件不存在则自动创建
            con.openAsync(file);
        }

        private function openHandler(evt:SQLEvent):void{
            Alert.show("Create/open database finished");
            stmt = new SQLStatement();
            stmt.sqlConnection = con;
            stmt.text = creatTableSQL;
            stmt.addEventListener(SQLEvent.RESULT, createResult);
            stmt.addEventListener(SQLErrorEvent.ERROR, createError);
            stmt.execute();
        }

        private function errorHandler(evt:SQLErrorEvent):void{
            Alert.show("SQLErrorEvent");
            Alert.show(evt.error.message);
            Alert.show(evt.error.details);
        }

        private function createResult(event:SQLEvent):void {
            Alert.show("create table finished");
        }

        private function createError(event:SQLErrorEvent):void {
            Alert.show("Error message:", event.error.message);
            Alert.show("Details:", event.error.details);
        }
    }
}