因为SQLite3是由C语言编写的,包含在C库中,所以Swift要使用源生的SQLite3需要Swift-OC混编
此处不再重复SQL可视化工具和如何添加SQLite3,如有需要,可以参考《iOS SQLite3 “增删改查”(Objective-C)》
创建完工程,并已添加SQLite3,则创建桥接文件Bridging-Header,导入sqlite3.h
跟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表示成功)
到这里,我们可以通过SQLite可视化工具查看一下,刚刚的两个函数有没有按照我们的想法执行
接下来更新数据
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)
}
}
}
查询结果
跟SQLite Professional中得截图对比一下
在调试过程中,总是会不可避免的出现问题,所以尽量打印返回值对照错误原因去解决问题
#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 */