SQLite简介
SQLite，是一款轻型的数据库，是遵守ACID的关系型数据库管理系统，它包含在一个相对小的C库中。它是D.RichardHipp建立的公有领域项目。它的设计目标是嵌入式的，而且目前已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix等等主流的操作系统，同时能够跟很多程序语言相结合，比如 Tcl、C#、PHP、Java等，还有ODBC接口，同样比起Mysql、PostgreSQL这两款开源的世界著名数据库管理系统来讲，它的处理速度比他们都快。
SQLite支持的存储类型
1、INTEGER - 整形
2、blob - 二进制
3、real - 浮点型
4、text - 字符串
5、NULL - 空
SQLite优势
1、占用资源低
2、速度快
3、与其他数据库相比，更适配移动平台、嵌入式平台
SQLite基本使用
//定义数据库变量
var db:OpaquePointer? = nil
//打开数据库
func openDB() ->Bool{
if db != nil {
print("数据库已经打开")
return true
}
//数据库文件路径
let dicumentPath = NSSearchPathForDirectoriesInDomains(FileManager.SearchPathDirectory.documentDirectory, FileManager.SearchPathDomainMask.userDomainMask, true).last
// let dbPath = (dicumentPath as NSString).appendingPathComponent("test.sqlite")
let dbPath = (dicumentPath! as NSString).appendingPathComponent("appDB.sqlite")
let cDBPath = dbPath.cString(using: String.Encoding.utf8)
print(dbPath)
//打开数据库
//第一个参数:数据库文件路径 第二个参数:数据库对象
if sqlite3_open(cDBPath, &db) != SQLITE_OK{
print("打开失败")
}
print("openDB")
return createTable()
}
//创建表
func createTable() -> Bool{
//建表的SQL语句
let creatUserTable = "CREATE TABLE IF NOT EXISTS t_cache ( equipmentID TEXT NOT NULL PRIMARY KEY,sswitch1 TEXT,sswitch2 TEXT,svolume TEXT,mp3 TEXT);"
// let creatCarTable = "CREATE TABLE IF NOT EXISTS 't_Car' ('ID' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,'type' TEXT,'output' REAL,'master' TEXT);"
//执行SQL语句-创建表 依然,项目中一般不会只有一个表
print("createTable")
return creatTableExecSQL(SQL_ARR: [creatUserTable])
}
//执行建表SQL语句
func creatTableExecSQL(SQL_ARR : [String]) -> Bool {
for item in SQL_ARR {
if execSQL(SQL: item) == false {
return false
}
}
print("creatTableExecSQL")
return true
}
//执行SQL语句
func execSQL(SQL : String) -> Bool{
// 1.将sql语句转成c语言字符串
let cSQL = SQL.cString(using: String.Encoding.utf8)
//错误信息
let errmsg : UnsafeMutablePointer<UnsafeMutablePointer<Int8>?>? = nil
if sqlite3_exec(db, cSQL, nil, nil, errmsg) == SQLITE_OK {
print("execSQL")
return true
}else{
print("SQL 语句执行出错 -> 错误信息: 一般是SQL语句写错了 \(errmsg)")
return false
}
}
关闭数据库
func closeDB()-> Void{
let result = sqlite3_close(db)
if result == SQLITE_OK {
db = nil
print("数据库关闭")
}else{
print("关闭失败")
}
}
插入数据
func insertData(_ user : CacheSeting) -> Bool{
_ = openDB()
var stmt : OpaquePointer? = nil
let sql = "INSERT INTO t_cache(equipmentID,sswitch1,sswitch2,svolume,mp3) VALUES ('\(user.equipmentID)','\(user.sswitch1)','\(user.sswitch2)','\(user.svolume)','\(user.mp3)')"
let cSql = sql.cString(using: .utf8)
let prepare_result = sqlite3_prepare_v2(db, cSql, -1, &stmt, nil)
if prepare_result != SQLITE_OK {
sqlite3_finalize(stmt)
if (sqlite3_errmsg(db)) != nil {
let msg = "SQLiteDB - failed to prepare SQL:\(sql)"
print(msg)
}
return false
}
print(sql)
sqlite3_bind_text(stmt, 1, user.equipmentID.cString(using: .utf8), -1, nil)
sqlite3_bind_text(stmt, 2, user.sswitch1.cString(using: .utf8), -1, nil)
sqlite3_bind_text(stmt, 3, user.sswitch2.cString(using: .utf8), -1, nil)
sqlite3_bind_text(stmt, 4, user.svolume.cString(using: .utf8), -1, nil)
sqlite3_bind_text(stmt, 5, user.mp3.cString(using: .utf8), -1, nil)
// sqlite3_bind_text(stmt, 6, user.disturb.cString(using: .utf8), -1, nil)
let step_result = sqlite3_step(stmt)
if step_result != SQLITE_OK && step_result != SQLITE_DONE {
sqlite3_finalize(stmt)
if (sqlite3_errmsg(db)) != nil {
let msg = "SQLiteDB - failed to execute SQL:\(sql)"
print(msg)
}
return false
}
sqlite3_finalize(stmt)
return true
}
查询
func selectToUserName(userID : String)-> CacheSeting{
_ = openDB()
let sql = "SELECT * FROM t_cache where equipmentID = '\(userID)'"
let user = CacheSeting()
var stmt:OpaquePointer? = nil
let cSql = sql.cString(using: .utf8)
let prepare_result = sqlite3_prepare_v2(db, cSql!, -1, &stmt, nil)
if prepare_result != SQLITE_OK{
sqlite3_finalize(stmt)
if (sqlite3_errmsg(db)) != nil {
let msg = "SQLiteDB - failed to prepare SQL:\(sql)"
print(msg)
}
}
while (sqlite3_step(stmt) == SQLITE_ROW) {
let myid = UnsafePointer(sqlite3_column_text(stmt, 0))
let switch1 = UnsafePointer(sqlite3_column_text(stmt, 1))
let switch2 = UnsafePointer(sqlite3_column_text(stmt, 2))
let myvloum = UnsafePointer(sqlite3_column_text(stmt, 3))
let myMP3 = UnsafePointer(sqlite3_column_text(stmt, 4))
// let myDisturb = UnsafePointer(sqlite3_column_text(stmt, 5))
user.equipmentID = String.init(cString: myid!)
user.sswitch1 = String.init(cString: switch1!)
user.sswitch2 = String.init(cString: switch2!)
user.svolume = String.init(cString: myvloum!)
user.mp3 = String.init(cString: myMP3!)
// user.disturb = String.init(cString: myDisturb!)
}
sqlite3_finalize(stmt)
return user
}
更新数据
func updataMP3(userID:String , toMP3:String)->Bool{
_ = openDB()
let sql = "update t_cache set mp3 = '\(toMP3)'where equipmentID = '\(userID)'";
//定义伴随指针
var stmt:OpaquePointer? = nil
//sql语句转换成cString类型
let cSql = sql.cString(using: .utf8)
//编译sql
let prepare_result = sqlite3_prepare_v2(db, cSql!, -1, &stmt, nil)
//判断如果失败，获取失败信息
if prepare_result != SQLITE_OK{
sqlite3_finalize(stmt)
if (sqlite3_errmsg(db)) != nil {
let msg = "SQLiteDB - failed to prepare SQL:\(sql)"
print(msg)
}
}
//step执行
let step_result = sqlite3_step(stmt)
//判断执行结果，如果失败，获取失败信息
if step_result != SQLITE_OK && step_result != SQLITE_DONE {
sqlite3_finalize(stmt)
if (sqlite3_errmsg(db)) != nil {
let msg = "SQLiteDB - failed to execute SQL:\(sql)"
print(msg)
}
return false
}
//释放跟随指针
sqlite3_finalize(stmt)
return true
}
删除数据
func deleteToUserId(userID : Int)-> Bool{
//删除sql语句
let sql = "delete from t_User where id = '\(userID)'"
//定义伴随指针
var stmt:OpaquePointer? = nil
//sql语句转换成cString类型
let cSql = sql.cString(using: .utf8)
//编译sql
let prepare_result = sqlite3_prepare_v2(db, cSql!, -1, &stmt, nil)
//判断如果失败，获取失败信息
if prepare_result != SQLITE_OK{
sqlite3_finalize(stmt)
if (sqlite3_errmsg(db)) != nil {
let msg = "SQLiteDB - failed to prepare SQL:\(sql)"
print(msg)
}
}
//step执行
let step_result = sqlite3_step(stmt)
//判断执行结果，如果失败，获取失败信息
if step_result != SQLITE_OK && step_result != SQLITE_DONE {
sqlite3_finalize(stmt)
if (sqlite3_errmsg(db)) != nil {
let msg = "SQLiteDB - failed to execute SQL:\(sql)"
print(msg)
}
return false
}
//释放跟随指针
sqlite3_finalize(stmt)
return true
}
以上就是数据库的简单操作了，SQLite里面还有很多的操作需要值得注意，比如说在多线程中执行的时候是有安全隐患的，可能会发生这里在执行插入、另外又在执行删除、更新或者其他的指令，所以多线程操作数据库一定要保证线程安全，这方面我还没着手去写，等有试过了再更新。
网友评论