sqlite3的基本使用
使用之前先倒入sqlite3
//
// ViewController.swift
// 数据库
//
// Created by zhang on 16/3/13.
// Copyright © 2016年 jin. All rights reserved.
//
import UIKit
class ViewController: UIViewController {
var db:COpaquePointer = COpaquePointer()
override func viewDidLoad() {
super.viewDidLoad()
self.openDataBase()
// Do any additional setup after loading the view, typically from a nib.
}
func openDataBase()
{
// 拼接文件名
let filePath = "\(NSSearchPathForDirectoriesInDomains(NSSearchPathDirectory.CachesDirectory, NSSearchPathDomainMask.UserDomainMask, true).last!)database.sqlite"
print(filePath)
// 打开数据库
if (sqlite3_open(filePath, &self.db) == SQLITE_OK) {
print("打开成功");
}else{
print("打开失败");
}
}
func execute(sql:String)->Bool
{
var flag:Bool
if (sqlite3_exec(self.db, sql, nil, nil, nil) == SQLITE_OK) {
flag = true;
}else{
flag = false;
}
return flag;
}
func query(sql:String)->[NSMutableArray]
{
// 1. 准备语句
var stmt: COpaquePointer = nil
/**
1. 数据库句柄
2. SQL 的 C 语言的字符串
3. SQL 的 C 语言的字符串长度 strlen,-1 会自动计算
4. stmt 的指针
5. 通常传入 nil
*/
var data:[NSMutableArray] = []
if sqlite3_prepare_v2(self.db, sql, -1, &stmt, nil) == SQLITE_OK {
// 单步获取SQL执行的结果 -> sqlite3_setup 对应一条记录
while sqlite3_step(stmt) == SQLITE_ROW {
// 获取每一条记录的数据
data.append(recordData(stmt))
}
}
return data
}
/// 获取每一条数据的记录
///
/// :param: stmt prepared_statement 对象
func recordData(stmt: COpaquePointer)->NSMutableArray
{
// 获取到记录
let array = NSMutableArray()
let count = sqlite3_column_count(stmt)
print("获取到记录,共有多少列 \(count)")
// 遍历每一列的数据
for i in 0..<count {
let type = sqlite3_column_type(stmt, i)
// 根据字段的类型,提取对应列的值
switch type {
case SQLITE_INTEGER:
print("整数 \(sqlite3_column_int64(stmt, i))")
array.addObject("\(sqlite3_column_int64(stmt, i))")
case SQLITE_FLOAT:
print("小树 \(sqlite3_column_double(stmt, i))")
array.addObject(sqlite3_column_double(stmt, i))
case SQLITE_NULL:
print("空 \(NSNull())")
case SQLITE_TEXT:
let chars = UnsafePointer<CChar>(sqlite3_column_text(stmt, i))
let str = String(CString: chars, encoding: NSUTF8StringEncoding)!
array.addObject(str)
print("字符串 \(str)")
case let type:
print("不支持的类型 \(type)")
}
}
return array
}
@IBAction func createDataBase(sender: AnyObject) {
}
@IBAction func createTable(sender: AnyObject) {
let sql = "create table if not exists t_user (id integer primary key autoincrement,name text,phone text);"
if self.execute(sql)
{
print("执行成功")
}
else
{
print("执行成功")
}
}
@IBAction func insert(sender: AnyObject) {
let sql = "insert into t_user (name,phone) values ('\(rand())','\(rand())')"
if self.execute(sql)
{
print("执行成功")
}
else
{
print("执行成功")
}
}
@IBAction func deleteTest(sender: AnyObject) {
let sql = "DELETE FROM t_user "
if self.execute(sql)
{
print("执行成功")
}
else
{
print("执行成功")
}
}
@IBAction func update(sender: AnyObject) {
let sql = "UPDATE t_user SET name = '2222222'"
if self.execute(sql)
{
print("执行成功")
}
else
{
print("执行成功")
}
}
@IBAction func selectTest(sender: AnyObject) {
let sql = "select * from t_user;"
print(self.query(sql))
}
}