sqlite3数据库增删改查通用类swift

Sqlite 前端之家
前端之家收集整理的这篇文章主要介绍了sqlite3数据库增删改查通用类swift，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

不知道csdn博客怎么加附件，原帖有工程文件

http://www.hangge.com/blog/cache/detail_645.html

使用方法：

ViewController.swift代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
import UIKit
class ViewController : UIViewController {
var db: sqliteDB !
@IBOutlet txtUname: UITextField !
txtMobile: !
override func viewDidLoad() {
super .viewDidLoad()
// Do any additional setup after loading the view,typically from a nib.
//获取数据库实例
db = .sharedInstance()
//如果表还不存在则创建表
db.execute(
"create table if not exists t_user(uid integer primary key,uname varchar(20),mobile varchar(20))"
)
//如果有数据则加载
initUser()
}
didReceiveMemoryWarning() {
.didReceiveMemoryWarning()
// Dispose of any resources that can be recreated.
}
//点击保存
@IBAction saveClicked(sender: AnyObject ) {
saveUser()
}
//从sqlite加载数据
initUser() {
let data = db.query( "select * from t_user" )
if data.count > 0 {
//获取最后一行数据显示
user = data[data.count - 1] as sqlRow
txtUname.text = user[ "uname" ]?.asString()
txtMobile.text = user[ "mobile" ]?.asString()
}
}
//保存数据到sqlite
saveUser() {
uname = self .txtUname.text!
mobile = .txtMobile.text!
//插入数据库，这里用到了esc字符编码函数，其实是调用bridge.m实现的
sql = "insert into t_user(uname,mobile) values('\(uname)','\(mobile)')"
println ( "sql: \(sql)" )
//通过封装的方法执行sql
result = db.execute(sql)
}
} //

操作类sqliteDb.swift: 

//  TasksGalore
//
//  Created by Fahim Farook on 12/6/14.
//  Copyright (c) 2014 RookSoft Pte. Ltd. All rights reserved.
//

import Foundation
#if os(iOS)
import UIKit
#else
import AppKit
#endif

let sqlITE_DATE = sqlITE_NULL + 1

//private let sqlITE_STATIC = sqlite3_destructor_type(COpaquePointer(bitPattern:0))
//private let sqlITE_TRANSIENT = sqlite3_destructor_type(COpaquePointer(bitPattern:-1))

internal let sqlITE_STATIC = unsafeBitCast(0,sqlite3_destructor_type.self)
internal let sqlITE_TRANSIENT = unsafeBitCast(-1,sqlite3_destructor_type.self)

// MARK:- sqlColumn Class - Column Definition
class sqlColumn {
	var value:AnyObject? = nil
	var type:CInt = -1
	
	init(value:AnyObject,type:CInt) {
//		println("sqliteDB - Initialize column with type: \(type),value: \(value)")
		self.value = value
		self.type = type
	}
	
	// New conversion functions
	func asString()->String {
		switch (type) {
			case sqlITE_INTEGER,sqlITE_FLOAT:
				return "\(value!)"
				
			case sqlITE_TEXT:
				return value as! String
				
			case sqlITE_BLOB:
				if let str = NSString(data:value as! NSData,encoding:NSUTF8StringEncoding) {
					return str as String
				} else {
					return ""
				}
			
			case sqlITE_NULL:
				return ""
				
			case sqlITE_DATE:
				let fmt = NSDateFormatter()
				fmt.dateFormat = "yyyy-MM-dd HH:mm:ss"
				return fmt.stringFromDate(value as! NSDate)
				
			default:
				return ""
		}
	}
	
	func asInt()->Int {
		switch (type) {
			case sqlITE_INTEGER,sqlITE_FLOAT:
				return value as! Int
				
			case sqlITE_TEXT:
				let str = value as! NSString
				return str.integerValue
				
			case sqlITE_BLOB:
				if let str = NSString(data:value as! NSData,encoding:NSUTF8StringEncoding) {
					return str.integerValue
				} else {
					return 0
				}
				
			case sqlITE_NULL:
				return 0
				
			case sqlITE_DATE:
				return Int((value as! NSDate).timeIntervalSince1970)
				
			default:
				return 0
		}
	}
	
	func asDouble()->Double {
		switch (type) {
			case sqlITE_INTEGER,sqlITE_FLOAT:
				return value as! Double
			
			case sqlITE_TEXT:
				let str = value as! NSString
				return str.doubleValue
			
			case sqlITE_BLOB:
				if let str = NSString(data:value as! NSData,encoding:NSUTF8StringEncoding) {
					return str.doubleValue
				} else {
					return 0.0
				}
			
			case sqlITE_NULL:
				return 0.0
			
			case sqlITE_DATE:
				return (value as! NSDate).timeIntervalSince1970
			
			default:
				return 0.0
		}
	}
	
	func asData()->NSData? {
		switch (type) {
			case sqlITE_INTEGER,sqlITE_FLOAT:
				let str = "\(value)" as NSString
				return str.dataUsingEncoding(NSUTF8StringEncoding)
			
			case sqlITE_TEXT:
				let str = value as! NSString
				return str.dataUsingEncoding(NSUTF8StringEncoding)
			
			case sqlITE_BLOB:
				return value as? NSData
			
			case sqlITE_NULL:
				return nil
			
			case sqlITE_DATE:
				let fmt = NSDateFormatter()
				fmt.dateFormat = "yyyy-MM-dd HH:mm:ss"
				let str = fmt.stringFromDate(value as! NSDate)
				return str.dataUsingEncoding(NSUTF8StringEncoding)
			
			default:
				return nil
		}
	}
	
	func asDate()->NSDate? {
		switch (type) {
			case sqlITE_INTEGER,sqlITE_FLOAT:
				let tm = value as! Double
				return NSDate(timeIntervalSince1970:tm)
			
			case sqlITE_TEXT:
				let fmt = NSDateFormatter()
				fmt.dateFormat = "yyyy-MM-dd HH:mm:ss"
				return fmt.dateFromString(value as! String)
			
			case sqlITE_BLOB:
				if let str = NSString(data:value as! NSData,encoding:NSUTF8StringEncoding) {
					let fmt = NSDateFormatter()
					fmt.dateFormat = "yyyy-MM-dd HH:mm:ss"
					return fmt.dateFromString(str as String)
				} else {
					return nil
				}
			
			case sqlITE_NULL:
				return nil
			
			case sqlITE_DATE:
				return value as? NSDate
			
			default:
				return nil
		}
	}
	
	func description()->String {
		return "Type: \(type),Value: \(value)"
	}
}

// MARK:- sqlRow Class - Row Definition
class sqlRow {
	var data = Dictionary<String,sqlColumn>()
	
	subscript(key: String) -> sqlColumn? {
		get {
			return data[key]
		}
		
		set(newVal) {
			data[key] = newVal
		}
	}
	
	func description()->String {
		return data.description
	}
}

// MARK:- sqliteDB Class - Does all the work
class sqliteDB {
	let DB_NAME = "data.db"
	let QUEUE_LABLE = "sqliteDB"
	private var db:COpaquePointer = nil
	private var queue:dispatch_queue_t
	private var fmt = NSDateFormatter()
	private var GROUP = ""
	
	struct Static {
		static var instance:sqliteDB? = nil
		static var token:dispatch_once_t = 0
	}
	
	class func sharedInstance() -> sqliteDB! {
		dispatch_once(&Static.token) {
			Static.instance = self.init(gid:"")
		}
		return Static.instance!
	}
	
	class func sharedInstance(gid:String) -> sqliteDB! {
		dispatch_once(&Static.token) {
			Static.instance = self.init(gid:gid)
		}
		return Static.instance!
	}
 
	required init(gid:String) {
		assert(Static.instance == nil,"Singleton already initialized!")
		GROUP = gid
		// Set queue
		queue = dispatch_queue_create(QUEUE_LABLE,nil)
		// Set up for file operations
		let fm = NSFileManager.defaultManager()
		let dbName:String = String.fromCString(DB_NAME)!
		var docDir = ""
		// Is this for an app group?
		if GROUP.isEmpty {
			// Get path to DB in Documents directory
			docDir = NSSearchPathForDirectoriesInDomains(.DocumentDirectory,.UserDomainMask,true)[0] 
		} else {
			// Get path to shared group folder
			if let url = fm.containerURLForSecurityApplicationGroupIdentifier(GROUP) {
				docDir = url.path!
			} else {
				assert(false,"Error getting container URL for group: \(GROUP)")
			}
		}
		let path = (docDir as NSString).stringByAppendingPathComponent(dbName)
		print("Database path: \(path)")
		// Check if copy of DB is there in Documents directory
		if !(fm.fileExistsAtPath(path)) {
			// The database does not exist,so copy to Documents directory
            let resourcePath = NSBundle.mainBundle().resourcePath
			if resourcePath != nil {
                let from = (resourcePath! as NSString).stringByAppendingPathComponent(dbName)
				var error:NSError?
				do {
					try fm.copyItemAtPath(from,toPath: path)
				} catch let error1 as NSError {
					error = error1
					print("sqliteDB - Failed to copy writable version of DB!")
					print("Error - \(error!.localizedDescription)")
					return
				}
			}
		}
		// Open the DB
		let cpath = path.cStringUsingEncoding(NSUTF8StringEncoding)
		let error = sqlite3_open(cpath!,&db)
		if error != sqlITE_OK {
			// Open Failed,close DB and fail
			print("sqliteDB - Failed to open DB!")
			sqlite3_close(db)
		}
		fmt.dateFormat = "YYYY-MM-dd HH:mm:ss"
	}
	
	deinit {
		closeDatabase()
	}
 
	private func closeDatabase() {
		if db != nil {
			// Get launch count value
			let ud = NSUserDefaults.standardUserDefaults()
			var launchCount = ud.integerForKey("LaunchCount")
			launchCount--
			print("sqliteDB - Launch count \(launchCount)")
			var clean = false
			if launchCount < 0 {
				clean = true
				launchCount = 500
			}
			ud.setInteger(launchCount,forKey: "LaunchCount")
			ud.synchronize()
			// Do we clean DB?
			if !clean {
				sqlite3_close(db)
				return
			}
			// Clean DB
			print("sqliteDB - Optimize DB")
			let sql = "VACUUM; ANALYZE"
			if execute(sql) != sqlITE_OK {
				print("sqliteDB - Error cleaning DB")
			}
			sqlite3_close(db)
		}
	}
	
	// Execute sql with parameters and return result code
	func execute(sql:String,parameters:[AnyObject]?=nil)->CInt {
		var result:CInt = 0
		dispatch_sync(queue) {
			let stmt = self.prepare(sql,params:parameters)
			if stmt != nil {
				result = self.execute(stmt,sql:sql)
			}
		}
		return result
	}
	
	// Run sql query with parameters
	func query(sql:String,parameters:[AnyObject]?=nil)->[sqlRow] {
		var rows = [sqlRow]()
		dispatch_sync(queue) {
			let stmt = self.prepare(sql,params:parameters)
			if stmt != nil {
				rows = self.query(stmt,sql:sql)
			}
		}
		return rows
	}
	
	// Show alert with either supplied message or last error
	func alert(msg:String) {
		dispatch_async(dispatch_get_main_queue()) {
#if os(iOS)
			let alert = UIAlertView(title: "sqliteDB",message:msg,delegate: nil,cancelButtonTitle: "OK")
			alert.show()
#else
			let alert = NSAlert()
			alert.addButtonWithTitle("OK")
			alert.messageText = "sqliteDB"
			alert.informativeText = msg
			alert.alertStyle = NSAlertStyle.WarningAlertStyle
			alert.runModal()
#endif
		}
	}
	
	// Private method which prepares the sql
	private func prepare(sql:String,params:[AnyObject]?)->COpaquePointer {
		var stmt:COpaquePointer = nil
		let csql = sql.cStringUsingEncoding(NSUTF8StringEncoding)
		// Prepare
		let result = sqlite3_prepare_v2(self.db,csql!,-1,&stmt,nil)
		if result != sqlITE_OK {
			sqlite3_finalize(stmt)
			if let error = String.fromCString(sqlite3_errmsg(self.db)) {
				let msg = "sqliteDB - Failed to prepare sql: \(sql),Error: \(error)"
				print(msg)
			}
			return nil
		}
		// Bind parameters,if any
		if params != nil {
			// Validate parameters
			let cntParams = sqlite3_bind_parameter_count(stmt)
			let cnt = CInt(params!.count)
			if cntParams != cnt {
				let msg = "sqliteDB - Failed to bind parameters,counts did not match. sql: \(sql),Parameters: \(params)"
				print(msg)
				return nil
			}
			var flag:CInt = 0
			// Text & BLOB values passed to a C-API do not work correctly if they are not marked as transient.
			for ndx in 1...cnt {
//				println("Binding: \(params![ndx-1]) at Index: \(ndx)")
				// Check for data types
				if let txt = params![ndx-1] as? String {
					flag = sqlite3_bind_text(stmt,CInt(ndx),txt,sqlITE_TRANSIENT)
				} else if let data = params![ndx-1] as? NSData {
					flag = sqlite3_bind_blob(stmt,data.bytes,CInt(data.length),sqlITE_TRANSIENT)
				} else if let date = params![ndx-1] as? NSDate {
					let txt = fmt.stringFromDate(date)
					flag = sqlite3_bind_text(stmt,sqlITE_TRANSIENT)
				} else if let val = params![ndx-1] as? Double {
					flag = sqlite3_bind_double(stmt,CDouble(val))
				} else if let val = params![ndx-1] as? Int {
					flag = sqlite3_bind_int(stmt,CInt(val))
				} else {
					flag = sqlite3_bind_null(stmt,CInt(ndx))
				}
				// Check for errors
				if flag != sqlITE_OK {
					sqlite3_finalize(stmt)
					if let error = String.fromCString(sqlite3_errmsg(self.db)) {
						let msg = "sqliteDB - Failed to bind for sql: \(sql),Parameters: \(params),Index: \(ndx) Error: \(error)"
						print(msg)
					}
					return nil
				}
			}
		}
		return stmt
	}
	
	// Private method which handles the actual execution of an sql statement
	private func execute(stmt:COpaquePointer,sql:String)->CInt {
		// Step
		var result = sqlite3_step(stmt)
		if result != sqlITE_OK && result != sqlITE_DONE {
			sqlite3_finalize(stmt)
			if let err = String.fromCString(sqlite3_errmsg(self.db)) {
				let msg = "sqliteDB - Failed to execute sql: \(sql),Error: \(err)"
				print(msg)
			}
			return 0
		}
		// Is this an insert
		let upp = sql.uppercaseString
		if upp.hasPrefix("INSERT ") {
			// Known limitations: http://www.sqlite.org/c3ref/last_insert_rowid.html
			let rid = sqlite3_last_insert_rowid(self.db)
			result = CInt(rid)
		} else if upp.hasPrefix("DELETE") || upp.hasPrefix("UPDATE") {
			var cnt = sqlite3_changes(self.db)
			if cnt == 0 {
				cnt++
			}
			result = CInt(cnt)
		} else {
			result = 1
		}
		// Finalize
		sqlite3_finalize(stmt)
		return result
	}
	
	// Private method which handles the actual execution of an sql query
	private func query(stmt:COpaquePointer,sql:String)->[sqlRow] {
		var rows = [sqlRow]()
		var fetchColumnInfo = true
		var columnCount:CInt = 0
		var columnNames = [String]()
		var columnTypes = [CInt]()
		var result = sqlite3_step(stmt)
		while result == sqlITE_ROW {
			// Should we get column info?
			if fetchColumnInfo {
				columnCount = sqlite3_column_count(stmt)
				for index in 0..<columnCount {
					// Get column name
					let name = sqlite3_column_name(stmt,index)
					columnNames.append(String.fromCString(name)!)
					// Get column type
					columnTypes.append(self.getColumnType(index,stmt:stmt))
				}
				fetchColumnInfo = false
			}
			// Get row data for each column
			let row = sqlRow()
			for index in 0..<columnCount {
				let key = columnNames[Int(index)]
				let type = columnTypes[Int(index)]
				if let val:AnyObject = self.getColumnValue(index,type:type,stmt:stmt) {
//						println("Column type:\(type) with value:\(val)")
					let col = sqlColumn(value: val,type: type)
					row[key] = col
				}
			}
			rows.append(row)
			// Next row
			result = sqlite3_step(stmt)
		}
		sqlite3_finalize(stmt)
		return rows
	}
	
	// Get column type
	private func getColumnType(index:CInt,stmt:COpaquePointer)->CInt {
		var type:CInt = 0
		// Column types - http://www.sqlite.org/datatype3.html (section 2.2 table column 1)
		let blobTypes = ["BINARY","BLOB","VARBINARY"]
		let charTypes = ["CHAR","CHARACTER","CLOB","NATIONAL VARYING CHARACTER","NATIVE CHARACTER","NCHAR","NVARCHAR","TEXT","VARCHAR","VARIANT","VARYING CHARACTER"]
		let dateTypes = ["DATE","DATETIME","TIME","TIMESTAMP"]
		let intTypes  = ["BIGINT","BIT","BOOL","BOOLEAN","INT","INT2","INT8","INTEGER","MEDIUMINT","SMALLINT","TINYINT"]
		let nullTypes = ["NULL"]
		let realTypes = ["DECIMAL","DOUBLE","DOUBLE PRECISION","FLOAT","NUMERIC","REAL"]
		// Determine type of column - http://www.sqlite.org/c3ref/c_blob.html
		let buf = sqlite3_column_decltype(stmt,index)
//		println("sqliteDB - Got column type: \(buf)")
		if buf != nil {
			var tmp = String.fromCString(buf)!.uppercaseString
			// Remove brackets
			let pos = tmp.positionOf("(")
			if pos > 0 {
				tmp = tmp.subStringTo(pos)
			}
			// Remove unsigned?
			// Remove spaces
			// Is the data type in any of the pre-set values?
//			println("sqliteDB - Cleaned up column type: \(tmp)")
			if intTypes.contains(tmp) {
				return sqlITE_INTEGER
			}
			if realTypes.contains(tmp) {
				return sqlITE_FLOAT
			}
			if charTypes.contains(tmp) {
				return sqlITE_TEXT
			}
			if blobTypes.contains(tmp) {
				return sqlITE_BLOB
			}
			if nullTypes.contains(tmp) {
				return sqlITE_NULL
			}
			if dateTypes.contains(tmp) {
				return sqlITE_DATE
			}
			return sqlITE_TEXT
		} else {
			// For expressions and sub-queries
			type = sqlite3_column_type(stmt,index)
		}
		return type
	}
	
	// Get column value
	private func getColumnValue(index:CInt,type:CInt,stmt:COpaquePointer)->AnyObject? {
		// Integer
		if type == sqlITE_INTEGER {
			let val = sqlite3_column_int(stmt,index)
			return Int(val)
		}
		// Float
		if type == sqlITE_FLOAT {
			let val = sqlite3_column_double(stmt,index)
			return Double(val)
		}
		// Text - handled by default handler at end
		// Blob
		if type == sqlITE_BLOB {
			let data = sqlite3_column_blob(stmt,index)
			let size = sqlite3_column_bytes(stmt,index)
			let val = NSData(bytes:data,length: Int(size))
			return val
		}
		// Null
		if type == sqlITE_NULL {
			return nil
		}
		// Date
		if type == sqlITE_DATE {
			// Is this a text date
			let txt = UnsafePointer<Int8>(sqlite3_column_text(stmt,index))
			if txt != nil {
				if let buf = NSString(CString:txt,encoding:NSUTF8StringEncoding) {
					let set = NSCharacterSet(charactersInString: "-:")
					let range = buf.rangeOfCharacterFromSet(set)
					if range.location != NSNotFound {
						// Convert to time
						var time:tm = tm(tm_sec: 0,tm_min: 0,tm_hour: 0,tm_mday: 0,tm_mon: 0,tm_year: 0,tm_wday: 0,tm_yday: 0,tm_isdst: 0,tm_gmtoff: 0,tm_zone:nil)
						strptime(txt,"%Y-%m-%d %H:%M:%S",&time)
						time.tm_isdst = -1
						let diff = NSTimeZone.localTimeZone().secondsFromGMT
						let t = mktime(&time) + diff
						let ti = NSTimeInterval(t)
						let val = NSDate(timeIntervalSince1970:ti)
						return val
					}
				}
			}
			// If not a text date,then it's a time interval
			let val = sqlite3_column_double(stmt,index)
			let dt = NSDate(timeIntervalSince1970: val)
			return dt
		}
		// If nothing works,return a string representation
		let buf = UnsafePointer<Int8>(sqlite3_column_text(stmt,index))
		let val = String.fromCString(buf)
//		println("sqliteDB - Got value: \(val)")
		return val
	}
}
原文链接：https://www.f2er.com/sqlite/199308.html

猜你在找的Sqlite相关文章

SQLite学习笔记
安装 在Windows上安装SQLite。 访问官网下载下Precompliled Binaries for Windows的两个压...
windows上如何安装Sqlite
一、安装 下载地址：http://www.sqlite.org/download.html 将Precompiled Binaries for Wi...
【Android数据存储】SQLite使用实例(附源码)
实例: 会员信息管理 功能：1.查看数据库 2.清空数据库 3.增加会员 4.删除会员 5.更新会员 ...
SQLite数据库管理
SQLite3常用命令
加密你的SQLite
关于SQLite SQLite是一个轻量的、跨平台的、开源的数据库引擎，它的在读写效率、消耗总量、...
SQLite这么娇小可爱，不多了解点都不行啊
在我眼里，MySQL和Oracle是这样的 而SQLite在是这样的 所以这么萌的数据库，我真的应该多了...
SQLite这么娇小可爱，不多了解点都不行啊
在我眼里，MySQL和Oracle是这样的 而SQLite在是这样的 所以这么萌的数据库，我真的应该多了...
SQLite 这么娇小可爱，不多了解点都不行啊
（点击上方蓝字，快速关注我们) 作者：空之境界 网址：http://supermao.cn/sqlitezhe-yao-...
SQLITE3 使用总结
SQLITE3 使用总结 投稿：hebedich 字体：[ 增加  减小] 类型：转载 时间：2014-08-19 Sql...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问