swift SQLite自封装
1.数据库封装类MySQLiteManager
import Foundation
import SQLite3
//数据库封装类
class MySQLiteManager {
private var db: OpaquePointer!
//重写构造函数
init(dbPath: String) {
let isSuccess = openDB(dbPath: dbPath)
if isSuccess {
print("打开数据库成功")
}else {
print("打开数据库失败")
}
}
//打开数据库
func openDB(dbPath: String) -> Bool {
let result = sqlite3_open(dbPath, &db)
if result != SQLITE_OK { return false }
return true
}
//关闭数据库
func closeDB() {
sqlite3_close(db)
}
deinit {
sqlite3_close(db)
}
}
extension MySQLiteManager {
func createTable(sql: String) -> Bool {
guard sql.contains("create") else { return false }
return execSQL(sql: sql)
}
func dropTable(sql: String) -> Bool {
guard sql.contains("drop") else { return false }
return execSQL(sql: sql)
}
}
extension MySQLiteManager {
//MARK: - 插入
func insert(sql: String) -> Bool {
guard sql.contains("insert") else { return false }
return execSQL(sql: sql)
}
//MARK: - 删除
func delete(sql: String) -> Bool {
guard sql.contains("delete") else { return false }
return execSQL(sql: sql)
}
//MARK: - 修改
func update(sql: String) -> Bool {
guard sql.contains("update") else { return false }
return execSQL(sql: sql)
}
func execSQL(sql: String) -> Bool {
var errMsg: UnsafeMutablePointer<Int8>? = nil
let cSql = sql.cString(using: String.Encoding.utf8)!
if sqlite3_exec(db, cSql, nil, nil, &errMsg) == SQLITE_OK {
return true
}
let msg = String.init(cString: errMsg!)
print(msg)
return false
}
//MARK: - 查询
func query(sql: String) -> [[String: Any]]? {
guard sql.contains("select") else {
print("sql语句有误")
return nil
}
let cSql = sql.cString(using: String.Encoding.utf8)!
var statement: OpaquePointer? = nil
if sqlite3_prepare_v2(db, cSql, -1, &statement, nil) != SQLITE_OK {
sqlite3_finalize(statement)
print("执行\(sql)错误\n")
let errmsg = sqlite3_errmsg(db)
if errmsg != nil {
print(errmsg!)
}
return nil
}
var rows = [[String: Any]]()
while sqlite3_step(statement) == SQLITE_ROW {
rows.append(record(statement: statement!))
}
sqlite3_finalize(statement)
return rows
}
private func record(statement: OpaquePointer) -> [String: Any] {
var row = [String: Any]()
for col in 0 ..< sqlite3_column_count(statement) {
let cName = sqlite3_column_name(statement, col)
let name = String(cString: cName!, encoding: String.Encoding.utf8)
var value: Any?
switch (sqlite3_column_type(statement, col))
{
case SQLITE_FLOAT:
value = sqlite3_column_double(statement, col) as Any
case SQLITE_INTEGER:
value = Int(sqlite3_column_int(statement, col)) as Any
case SQLITE_TEXT:
let cText = sqlite3_column_text(statement, col)
value = String.init(cString: cText!) as Any
case SQLITE_NULL:
value = nil
default:
print("")
}
row[name!] = value
}
return row
}
//存储Blob数据 Blob（Binary Large Object）表示二进制类型的大对象。在数据库管理系统中，将二进制数据存储为一个单一个体的集合。
func execSaveBlob(sql: String, blob: NSData) {
let csql = sql.cString(using: .utf8)!
var statement:OpaquePointer? = nil
if sqlite3_prepare_v2(db, csql, -1, &statement, nil) != SQLITE_OK {
sqlite3_finalize(statement)
print("Prepare error:\(sql)")
return
}
let paramsCnt = sqlite3_bind_parameter_count(statement)
if paramsCnt != 1 {
print("need only 1 parameter:\(sql)")
sqlite3_finalize(statement)
return
}
if sqlite3_bind_blob(statement, 1, blob.bytes, Int32(blob.length), nil) != SQLITE_OK {
print("bind blob error:\(sql)")
sqlite3_finalize(statement)
return
}
let rslt = sqlite3_step(statement)
if rslt != SQLITE_OK && rslt != SQLITE_DONE {
print("extue blob error:\(sql)")
sqlite3_finalize(statement)
return
}
sqlite3_finalize(statement)
return
}
//读取Blob数据
func execLoadBlob(sql:String) -> Data? {
let csql = sql.cString(using: String.Encoding.utf8)!
var statement:OpaquePointer? = nil
if sqlite3_prepare_v2(db, csql, -1, &statement, nil) != SQLITE_OK {
sqlite3_finalize(statement)
print("执行\(sql)错误\n")
if let errmsg = sqlite3_errmsg(db) {
print(errmsg)
}
return nil
}
while sqlite3_step(statement) == SQLITE_ROW {
if let dataBlob = sqlite3_column_blob(statement, 0) {
let dataBlobLength = sqlite3_column_bytes(statement, 0)
let data = Data(bytes: dataBlob, count: Int(dataBlobLength))
sqlite3_finalize(statement)
return data
}
}
sqlite3_finalize(statement)
return nil
}
}
//MARK: - sql语句
/**
1.DDL
1.创建表
create table if not exists t_student(id integer auto_increment primary key ,name
varchar(3) , age int);
删除表
drop table if exists t_student;
修改表结构
alter table t_student add address varchar(4);
alter table t_student modify address varchar(6) not null;
tip：
简单约束：
name varchar(3) not null
age int default 66
主键约束：
id integer auto_increment primary key
2. DML
1.插入数据
insert into t_student(name,age,address) values ('xiaoM',12,'guangzhou');
2.修改数据
update t_student set name='xiaoHuang' where id=1;
3.删除数据
delete from t_student where id= 1 ;
tip：
条件约束
delete from t_student where id is 1 or name = 'xiaoM';
3. DQL
查询语句
1.查询所有字段信息
select * from t_student;
2.查询部分字段信息
select name,age from t_student;
3.查询符合条件下的部分字段信息
select name,age from t_student where id=2;
4.查询数据个数
select count(*) from t_student;
5.查询age不为空值的个数
select count(age) from t_student;
6.查询age平均值
select avg(age) from t_student;
7.查询age总和
select sum(age) from t_student;
8.查询age最大值
select max(age) from t_student;
9.查询age最小值
select min(age) from t_student;
10.查询按分数升序，按年龄降序
select *from t_student order by score asc, age desc;
*/2.针对表test写的类TestTableManager
import Foundation
class TestTableManager {
private var sqliteManager: MySQLiteManager!
static let shared = TestTableManager()
init() {
let dbPath = NSHomeDirectory() + "/Documents/testDB.sqlite"
sqliteManager = MySQLiteManager(dbPath: dbPath)
createTable()
}
private func createTable() {
let sql = "create table if not exists test (id integer auto_increment primary key ,name char(50) not null default '');"
if sqliteManager.createTable(sql: sql) {
print("表创建成功")
}else {
print("表创建失败")
}
}
func dropTable() {
let sql = "drop table if exists test;"
if sqliteManager.dropTable(sql: sql) {
print("表删除成功")
}else {
print("表删除失败")
}
}
func insert(name: String) {
let sql = "insert into test(name) values ('\(name)');"
let isSuccess = sqliteManager.insert(sql: sql)
if isSuccess {
print("插入成功")
}else {
print("插入失败")
}
}
func quary() {
let sql = "select * from test;"
let data = sqliteManager.query(sql: sql)
print(data!)
}
}3.测试代码
import UIKit
class ViewController: UIViewController {
override func viewDidLoad() {
super.viewDidLoad()
//MARK: - sqlite自封装
TestTableManager.shared.insert(name: "aaaaa")
TestTableManager.shared.insert(name: "bbbbb")
TestTableManager.shared.quary()
TestTableManager.shared.dropTable()
}
override func viewDidAppear(_ animated: Bool) {
super.viewDidAppear(animated)
}
}原文地址：https://blog.csdn.net/liu_yun_long/article/details/136477254
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！