Flex内嵌数据库SQLite的使用

 

public class DBClass

{

import flash.data.SQLConnection;  

import flash.data.SQLStatement;  

import flash.data.SQLResult;  

import flash.errors.SQLError;

import flash.events.SQLErrorEvent; 

import flash.filesystem.File; //数据库文件 

import mx.controls.Alert;

import com.xidigo.model.*;

import com.xidigo.model.ModelManager;

private var con:SQLConnection;

private var createStmt:SQLStatement;

public function DBClass()

{

}

public function initDB():void{

var file:File = File.applicationStorageDirectory.resolvePath(ModelManager.DBPATH);

  ModelManager.db = this;

con = new SQLConnection();

createStmt = new SQLStatement();

try

{

  con.open(file);

createStmt.sqlConnection = con; 

}

catch(error:SQLError)

{

 Alert.show(error.message);

 Alert.show(error.details);

}

}

public function CreateDB():void

{

  var userSql:String =  

    "CREATE TABLE IF NOT EXISTS user (" +  

    "    Id INTEGER PRIMARY KEY AUTOINCREMENT, " +  

    "    Name TEXT, " +  

    "    Password TEXT, " +  

    "    Privilege INTEGER" +  

    ")"; 

     

   var logSql:String =  

    "CREATE TABLE IF NOT EXISTS log (" +  

    "    Id INTEGER PRIMARY KEY AUTOINCREMENT, " +  

    "    UserName TEXT, " +  

    "    LogTime TEXT, " +

    "    Content TEXT, " +

    "    PreContent TEXT, " +  

    "    Type INTEGER" +  

    ")"; 

CreateTable(userSql);

   CreateTable(logSql);

if (QueryUsers() == null){

var insertUser:String = "INSERT INTO user(Name,Password,Privilege) VALUES ('"+String("admin")+"','"+String("admin")+"',"+int(1)+")"

   createStmt.text = insertUser;

createStmt.execute();

}

}

private function CreateTable(str:String):void{

createStmt.text = str;

createStmt.execute();

}

public function QueryUsers():Array{

createStmt.text = "select * from user";

createStmt.execute();  

 

var result:SQLResult = createStmt.getResult();

var p:Array = new Array();

if ( result.data!=null )

{

return result.data;

}

return null;

}

public function QueryUser(user:User):Boolean{

createStmt.text = "select * from user where Name='"+ user.name +"' AND Password='"+ user.password +"'";

createStmt.execute();  

 

var result:SQLResult = createStmt.getResult();

 

if ( result.data!=null )

{

return true;

}

return false;

}

public function FindUser(user:User):Array{

createStmt.text = "select * from user where Name='"+ user.name +"'";

createStmt.execute();  

 

var result:SQLResult = createStmt.getResult();

 

if ( result.data!=null )

{

return result.data;

}

return null;

}

public function SaveUser(user:Object):void{

createStmt.text = "INSERT INTO user(Name,Password,Privilege) VALUES ('"+user.Name+"','"+user.Password+"','"+user.Privilege+"')";

createStmt.execute();

}

public function UpdateUser(user:User):void{

createStmt.text = "UPDATE user set Password='"+ user.password+ "' where Name='"+ user.name +"'";

createStmt.execute();

}

public function DelUser(user:Object):void{

createStmt.text = "delete from user where Id='"+ user.Id+ "'";

createStmt.execute();

}

public function QueryLogs():Array{

createStmt.text = "select * from log order by LogTime desc";

createStmt.execute();  

 

var result:SQLResult = createStmt.getResult();

var p:Array = new Array();

if ( result.data!=null )

{

return result.data;

}

return null;

}

public function SaveLog(log:Logged):void{

createStmt.text = "INSERT INTO log(UserName,LogTime,Content,PreContent,Type) VALUES ('"+log.userName+"','"+log.time+"','"+log.content+"','"+log.preContent+"',"+log.type+")";

createStmt.execute();

}

public function DelLog(delStr:String):void{

createStmt.text = "DELETE FROM log WHERE LogTime<'"+ delStr +"'";

createStmt.execute();  

}

}

 

 

两点说明：

1、这里查询的结果是Array的，而不是ArrayCollection。

2、Array中的数据是Object类型的，而不是自己定义的类

 

参考文章：

http://www.cnblogs.com/aierong/archive/2009/01/22/flex_Sqlite_1.html