博客> iOS 源生SQLite3 “增删改查”(Swift-OC混编)
iOS 源生SQLite3 “增删改查”(Swift-OC混编)
2019-08-20 20:18 评论:0 阅读:1161 ichampion
ios sqlite3

因为SQLite3是由C语言编写的,包含在C库中,所以Swift要使用源生的SQLite3需要Swift-OC混编 此处不再重复SQL可视化工具和如何添加SQLite3,如有需要,可以参考《iOS SQLite3 “增删改查”(Objective-C)》 创建完工程,并已添加SQLite3,则创建桥接文件Bridging-Header,导入sqlite3.h Bridge

跟OC一样,需要创建一个全局的句柄

 var db: COpaquePointer = nil

由于Swift中没有C语言的指针概念,所以这里不能使用

static sqlite3 * db = nil

而我们不知道sqlite3在Swift是什么类型,因为Swift没有这个东西,这是C语言提供的,所以只能用一个模糊不清的概念,使用一个前置声明的指针COpaquePointer。关于COpaquePointer的资料请看《在Swift中使用历史遗留的C API》 然后按部就班:创建表格——插入数据——修改数据——删除数据——查看数据 打开数据库并创建表格

func openDB() {
        /*
         directory 目录类型 比如Documents目录 就是NSDocumentDirectory
         domainMask 在iOS的程序中这个取NSUserDomainMask
         expandTilde YES,表示将~展开成完整路径
         lastObject 取NSSearchPathForDirectoriesInDomains数组最后一个元素
         */
//        let test: NSArray = NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask,  YES)
        let test: NSArray = NSSearchPathForDirectoriesInDomains(NSSearchPathDirectory.CachesDirectory, NSSearchPathDomainMask.UserDomainMask, true)
        let fileName = (test.lastObject! as! String)+"/"+"DB_ichampion.sqlite"
        print("fileName:",fileName);
        //打开数据库 如果没有打开的数据库就建立一个
        //第一个参数是数据库的路径 注意要转换为c的字符串
        if (sqlite3_open(fileName, &db) == SQLITE_OK) {
            print("打开数据库成功")
            //打开数据库成功后建立数据库内的表
            //操作命令的字符串
            //注意SQL语句结束处有 ; 号
            let sql = "create table if not exists Table_ichampion (id integer primary key autoincrement,name text,age intger);"

            if(sqlite3_exec(db,sql.cStringUsingEncoding(NSUTF8StringEncoding)!, nil, nil, nil)
                == SQLITE_OK)
            {
                print("建表成功")
            }else{
                print("建表失败")
            }
        }else{
            print("打开数据库失败")
        }
    }

插入数据

func insertDB() {
        let sql = "insert into Table_ichampion(id,name,age) values(1,'Zhan',18);"
        print("sql",sqlite3_exec(db, sql.cStringUsingEncoding(NSUTF8StringEncoding)!, nil, nil, nil))

        let sql1 = "insert into Table_ichampion(id,name,age) values(3,'Michael',8);"
        print("sql1",sqlite3_exec(db, sql1.cStringUsingEncoding(NSUTF8StringEncoding)!, nil, nil, nil))

        let sql2 = "insert into Table_ichampion values(NULL,'abc',6);"
        print("sql2",sqlite3_exec(db, sql2.cStringUsingEncoding(NSUTF8StringEncoding)!, nil, nil, nil))

        let tableName = "Table_ichampion"
        let Name = "name"
        let Age = "age"
        let NameValue = "abcd"
        let AgeValue = 3

        let sql3 = "INSERT INTO " + tableName + "(id," + Name + "," + Age + ") VALUES(" + String(5) + ",'" + NameValue + "'," +  String(AgeValue) + ");"
        print("sql3",sqlite3_exec(db, sql3.cStringUsingEncoding(NSUTF8StringEncoding)!, nil, nil, nil))
    }

插入数据这里,我们采用打印返回值的写法,可以看到程序执行是否成功(返回值为0表示成功) insert

到这里,我们可以通过SQLite可视化工具查看一下,刚刚的两个函数有没有按照我们的想法执行 sql1

接下来更新数据

func updateDB() {
        let sql = "update Table_ichampion set name = 'xyz' where id = 5;"
        print(sqlite3_exec(db, sql.cStringUsingEncoding(NSUTF8StringEncoding)!, nil, nil, nil))
    }

再删除一条记录

func deleteDB() {
        let sql = "DELETE FROM Table_ichampion WHERE name = 'abc';"
        if (sqlite3_exec(db, sql.cStringUsingEncoding(NSUTF8StringEncoding)!, nil, nil, nil) == SQLITE_OK) {
            print("OK!")
        }
    }

做完这两步,我们直接写查看部分,看看abc这条记录是否删除,abcd是否改名为xyz 这里写了两个查询方式,一个是整张表格查询,一个是单条记录查询

func selectDB() {
        let sql = "select * from Table_ichampion"
        var stmt : COpaquePointer = nil
        if (sqlite3_prepare_v2(db, sql.cStringUsingEncoding(NSUTF8StringEncoding)!, -1, &stmt, nil) == SQLITE_OK) {
            while (sqlite3_step(stmt) == SQLITE_ROW) {

                let id = UnsafePointer<CChar>(sqlite3_column_text(stmt, 0))
                let idStr = String.fromCString(id)

                let name = UnsafePointer<CChar>(sqlite3_column_text(stmt, 1))
                let nameStr = String.fromCString(name)

                let age = UnsafePointer<CChar>(sqlite3_column_text(stmt, 2))
                let ageStr = String.fromCString(age)

                let info = idStr! + " " + nameStr! + " " + ageStr!
                print("ALL -- ",info)
            }
        }
        let sql1 = "SELECT * FROM Table_ichampion WHERE name = 'Zhan';"
        if (sqlite3_prepare_v2(db, sql1.cStringUsingEncoding(NSUTF8StringEncoding)!, -1, &stmt, nil) == SQLITE_OK) {
            while (sqlite3_step(stmt) == SQLITE_ROW) {

                let id = UnsafePointer<CChar>(sqlite3_column_text(stmt, 0))
                let idStr = String.fromCString(id)

                let name = UnsafePointer<CChar>(sqlite3_column_text(stmt, 1))
                let nameStr = String.fromCString(name)

                let age = UnsafePointer<CChar>(sqlite3_column_text(stmt, 2))
                let ageStr = String.fromCString(age)

                let info = idStr! + " " + nameStr! + " " + ageStr!
                print("Zhan -- ",info)
            }
        }
    }

查询结果 select

跟SQLite Professional中得截图对比一下 SQL2

在调试过程中,总是会不可避免的出现问题,所以尽量打印返回值对照错误原因去解决问题

#define SQLITE_OK           0   /* Successful result */
/* beginning-of-error-codes */
#define SQLITE_ERROR        1   /* SQL error or missing database */
#define SQLITE_INTERNAL     2   /* Internal logic error in SQLite */
#define SQLITE_PERM         3   /* Access permission denied */
#define SQLITE_ABORT        4   /* Callback routine requested an abort */
#define SQLITE_BUSY         5   /* The database file is locked */
#define SQLITE_LOCKED       6   /* A table in the database is locked */
#define SQLITE_NOMEM        7   /* A malloc() failed */
#define SQLITE_READONLY     8   /* Attempt to write a readonly database */
#define SQLITE_INTERRUPT    9   /* Operation terminated by sqlite3_interrupt()*/
#define SQLITE_IOERR       10   /* Some kind of disk I/O error occurred */
#define SQLITE_CORRUPT     11   /* The database disk image is malformed */
#define SQLITE_NOTFOUND    12   /* Unknown opcode in sqlite3_file_control() */
#define SQLITE_FULL        13   /* Insertion failed because database is full */
#define SQLITE_CANTOPEN    14   /* Unable to open the database file */
#define SQLITE_PROTOCOL    15   /* Database lock protocol error */
#define SQLITE_EMPTY       16   /* Database is empty */
#define SQLITE_SCHEMA      17   /* The database schema changed */
#define SQLITE_TOOBIG      18   /* String or BLOB exceeds size limit */
#define SQLITE_CONSTRAINT  19   /* Abort due to constraint violation */
#define SQLITE_MISMATCH    20   /* Data type mismatch */
#define SQLITE_MISUSE      21   /* Library used incorrectly */
#define SQLITE_NOLFS       22   /* Uses OS features not supported on host */
#define SQLITE_AUTH        23   /* Authorization denied */
#define SQLITE_FORMAT      24   /* Auxiliary database format error */
#define SQLITE_RANGE       25   /* 2nd parameter to sqlite3_bind out of range */
#define SQLITE_NOTADB      26   /* File opened that is not a database file */
#define SQLITE_NOTICE      27   /* Notifications from sqlite3_log() */
#define SQLITE_WARNING     28   /* Warnings from sqlite3_log() */
#define SQLITE_ROW         100  /* sqlite3_step() has another row ready */
#define SQLITE_DONE        101  /* sqlite3_step() has finished executing */
/* end-of-error-codes */

源码:iOS源生SQLite3基本操作(Swift-OC混编)

收藏
0
sina weixin mail 回到顶部