SQLiteHelper

创建SQLiteHelper类

            ///  SQLite数据库处理帮助类
///
///  此类中封装了关于SQLite数据库处理的业务函数
class SQLiteHelper
{
    // 业务代码...
}
        

单例

            
                private
                static
                let instance = SQLiteHelper()
/// 单例  全局的数据访问接口
class var sharedInstance: SQLiteHelper
{
    return instance
}
        

全局变量

            
                var db: COpaquePointer = nil
        

打开数据库

            
                
                    ///  打开数据库
                
                    ///
                
                
                    ///  :param: dbName 数据库名称
                
                    ///
                
                
                    ///  :returns: 返回 是否打开成功
func openDatabase(dbName: String) -> Bool
{
    let path = dbName.documentPath()
    println(path)
    return sqlite3_open(path, &db) == SQLITE_OK
}
        

创建示例数据表

            
                ///  创建 T_Department 和 T_Employee 表
                ///
                ///  :returns: 返回 是否创建成功
func createTable() -> Bool
{
    let sql = "CREATE TABLE n" +
        "IF NOT EXISTS T_Department (n" +
        "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,n" +
        "DepartmentNo CHAR(10) NOT NULL DEFAULT ‘‘,n" +
        "Name CHAR(50) NOT NULL DEFAULT ‘‘ n" +
        "); n" +
        "CREATE TABLE IF NOT EXISTS T_Employee ( n" +
        "‘id‘ INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, n" +
        "‘name‘ TEXT NOT NULL, n" +
        "‘age‘ INTEGER NOT NULL, n" +
        "‘department_id‘ INTEGER, n" +
        "CONSTRAINT ‘FK_DEP_ID‘ FOREIGN KEY (‘department_id‘) REFERENCES ‘T_Department‘ (‘id‘) n" +
    ");"
    // 返回结果
    return execSql(sql)
}
        

执行INSERT、UPDATE、DELETE 语句

            
                
                    ///  执行INSERT、UPDATE、DELETE SQL语句
                
                    ///
                
                
                    ///  :param: sql SQL语句
                
                    ///
                
                
                    ///  :returns: 返回 是否执行成功
func execSql(sql: String) -> Bool
{
    // 返回结果return sqlite3_exec(db, sql.cStringUsingEncoding(NSUTF8StringEncoding)!, nil, nil, nil) == ITE_OK
}
        

执行SQL语句 返回结果数量

            
                
                    ///  执行SQL语句 返回结果数量
                
                    ///
                
                
                    ///  :param: sql SQL语句
                
                    ///
                
                
                    ///  :returns: 返回 结果
func execCount(sql: String) -> Int
{
    let record = execRecordSet(sql)
    // 返回结果return (record[0] as! [AnyObject])[0] as! Int
}
        

执行返回单条记录

            
                
                    ///  执行返回单条记录
                
                    ///
                
                
                    ///  :param: sql SQL语句
                
                    ///
                
                
                    ///  :returns: 返回 单条记录
func execRow(sql: String) -> [AnyObject]?
{
    let record = execRecordSet(sql)

    if record.count > 0
    {
        return (record[0] as! [AnyObject])
    }
    else
    {
        return nil
    }
}
        

执行 SQL 返回结果集合

            
                
                    ///  执行 SQL 返回结果集合
                
                    ///
                
                
                    ///  :param: sql SQL语句
                
                    ///
                
                
                    ///  :returns: 返回 查询的结果集
func execRecordSet(sql: String) -> [AnyObject]
{
    var stmt: COpaquePointer = nil
    var recordList = [AnyObject]()
    if sqlite3_prepare_v2(db, sql.cStringUsingEncoding(NSUTF8StringEncoding)!, -1, &stmt, nil) == TE_OK
    {
        while sqlite3_step(stmt) == SQLITE_ROW
        {
            recordList.append(singleData(stmt)!)
        }
    }
    // 释放语句
    sqlite3_finalize(stmt)
    // 返回结果return recordList
}
        

执行一行数据

            
                
                    ///  执行一行数据
                
                    ///
                
                
                    ///  :param: stmt 执行的语句
                
                    ///
                
                
                    ///  :returns: 返回一行数据数组
func singleData(stmt: COpaquePointer) -> [AnyObject]?
{
    var result = [AnyObject]()
    // 返回该表的列数let count = sqlite3_column_count(stmt)

    //        #define SQLITE_INTEGER  1//        #define SQLITE_FLOAT    2//        #define SQLITE_BLOB     4//        #define SQLITE_NULL     5//        #ifdef SQLITE_TEXT//        # undef SQLITE_TEXT//        #else//        # define SQLITE_TEXT     3//        #endif//        #define SQLITE3_TEXT     3for index in0..<count
    {
        let type = sqlite3_column_type(stmt, index)
        // 根据字段的类型，提取对应列的值switch type {
        case SQLITE_INTEGER:
            result.append(Int(sqlite3_column_int64(stmt, index)))
        case SQLITE_FLOAT:
            result.append(sqlite3_column_double(stmt, index))
        case SQLITE_NULL:
            result.append(NSNull())
        case SQLITE_TEXT:
            let rrrrr: UnsafePointer<UInt8> = sqlite3_column_text(stmt, index)
            let chars = UnsafePointer<CChar>(sqlite3_column_text(stmt, index))
            let str = String(CString: chars, encoding: NSUTF8StringEncoding)!
            result.append(str)
        caselet type:
            println("不支持的类型 (type)")
        }
    }
    // 返回结果return result
}