超完善注释SqlitDemo增删改查

首先导入libsqlite3.tbd

MVC模式

ClassRoom.h 继承 NSObject ClassRoom.m没有东西
@property(nonatomic,assign)NSInteger theIntId;

@property(nonatomic,strong)NSString *theNmae,*theAge,*theSex;

DataBase.h继承 NSObject

#import <sqlite3.h>

#import "ClassRoom.h"

{

sqlite3 *theSqliteDB;

}

//单例

+(instancetype)initDataBase;

//初始化数据库

-(void)initData;

//创建数据库表格

-(void)createTable;

//增加数据

-(void)addSqlite:(ClassRoom*)theData;

//删除数据

-(void)deleteData:(NSInteger )theId;

//修改数据

-(void)changeData:(ClassRoom*)theData;

//查询数据

-(NSMutableArray *)theDataArray;

//查询某一条数据

-(NSMutableDictionary *)showStudentOneMessage:(NSInteger)theId;

//关闭数据库

-(void)closeData;

DataBase.m

//创建单例变量

static DataBase *theDataBase = nil;

@implementation DataBase 下

//单例

+(instancetype)initDataBase{

if (!theDataBase) {

theDataBase = [[DataBase alloc]init];

}

return theDataBase;

}

//初始化数据库

-(void)initData{

//创建Document目录

NSString *theStrPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)objectAtIndex:0];

//拼接数据库表名的路径

NSString *theStr = [theStrPath stringByAppendingString:@"/1502H.db"];

int restus = sqlite3_open([theStr UTF8String], &theSqliteDB);

if (restus ==SQLITE_OK) {

NSLog(@"打开数据库");

//创建表格

[self createTable];

}

else

{

NSLog(@"数据库没有打开");

}

//创建数据库表格

-(void)createTable

{

//创建sql语句如果表格不存在则创建表格如果表格存在则忽略sql语句主键：id 格式：create table if not exists 表名（主键id integer primary key ,加上所有用到的数据类型）

const char *asql = "create table if not exists classroom(theIntId integer primary key, theNmae text,theAge text,theSex text)";

//预编译指针将sql语句执行的结果存入了stmt中

sqlite3_stmt *theStmt;

//预编译sql语句

//第一个参数数据库连接指针

//第二个参数 asql语句

//第三个参数 asql语句的长度－1自动匹配长度

//第四个参数预编译指针

//第五个参数未执行的sql语句部分

sqlite3_prepare_v2(theSqliteDB, asql, -1, &theStmt, nil);

//执行预编译指针

int restus = sqlite3_step(theStmt);

if (restus ==SQLITE_DONE) {

NSLog(@"表格创建成功");

}

else

NSLog(@"表格创建失败");

//销毁预编译

sqlite3_finalize(theStmt);

}

//增加数据

-(void)addSqlite:(ClassRoom*)theData{

//创建sql语句格式：insert into 表名 values (表格中的数据类型)

const char *sqlString = "insert into classroom values(null,?,?,?)";

//预编译指针

sqlite3_stmt *stmt;

//预编译sql语句

sqlite3_prepare_v2(theSqliteDB, sqlString, -1, &stmt, nil);

//绑定占位符

//第一个参数预编译指针

//第二个参数第几个问号

//第三个参数（绑定语句）取代当前问好的值（转为C字符串）

//第四个参数绑定语句长度－1 自动匹配

//第五个参数将字符串转换为sqlite识别的text类型

sqlite3 *db, 数据库句柄，跟文件句柄FILE很类似

sqlite3_stmt *stmt, 这个相当于ODBC的Command对象，用于保存编译好的SQL语句

sqlite3_open(), 打开数据库，没有数据库时创建。

sqlite3_exec(), 执行非查询的sql语句

Sqlite3_step(), 在调用sqlite3_prepare后，使用这个函数在记录集中移动。

Sqlite3_close(), 关闭数据库文件

还有一系列的函数，用于从记录集字段中获取数据，如

sqlite3_bind_text(), 取text类型的数据。

sqlite3_bind_blob（），取blob类型的数据

sqlite3_bind_int(), 取int类型的数据

// 邦定第二个字符串参数

sqlite3_bind_text(stmt, 1, [theData.theNmae UTF8String], -1, SQLITE_TRANSIENT);

// 邦定第三个字符串参数

sqlite3_bind_text(stmt, 2, [theData.theAge UTF8String], -1, SQLITE_TRANSIENT);

// 邦定第四个字符串参数

sqlite3_bind_text(stmt, 3, [theData.theSex UTF8String], -1, SQLITE_TRANSIENT);

//执行预编译指针

sqlite3_step(stmt);

//销毁预编译指针

sqlite3_finalize(stmt);

}

//删除数据

-(void)deleteData:(NSInteger )theId{

//创建sql语句: 格式： delete from 表名 where 表格的主键名:id = ?

const char *deletesql = "delete from classroom where theIntId = ?";

//预编译指针

sqlite3_stmt *stmt;

//预编译sql语句

sqlite3_prepare_v2(theSqliteDB, deletesql, -1, &stmt, nil);

//绑定占位符

sqlite3_bind_int(stmt, 1, (int)theId);

//执行预编译指针

sqlite3_step(stmt);

//销毁预编译指针

sqlite3_finalize(stmt);

}

//修改数据

-(void)changeData:(ClassRoom*)theData{

//创建sql语句格式：update 表名 set 数据类型 where 主键id

const char *sqlString = "update classroom set theNmae = ?,theAge = ?, theSex = ? where theIntId = ?";

//预编译指针

sqlite3_stmt *stmt;

//预编译sql语句

sqlite3_prepare_v2(theSqliteDB, sqlString, -1, &stmt, nil);

//绑定占位符

sqlite3_bind_text(stmt, 1, [theData.theNmae UTF8String], -1, SQLITE_TRANSIENT);

sqlite3_bind_text(stmt, 2, [theData.theAge UTF8String], -1, SQLITE_TRANSIENT);

sqlite3_bind_text(stmt, 3, [theData.theSex UTF8String], -1, SQLITE_TRANSIENT);

sqlite3_bind_int(stmt, 4, (int)theData.theIntId);

NSLog(@"=======%s",sqlite3_errmsg(theSqliteDB));

//执行预编译指针

sqlite3_step(stmt);

//销毁预编译指针

sqlite3_finalize(stmt);

}

//查询数据

-(NSMutableArray *)theDataArray{

//创建sql语句格式：select * from 表名

const char *sqlSting = "select * from classroom";

//预编译指针

sqlite3_stmt *stmt;

//预编译sql语句

sqlite3_prepare_v2(theSqliteDB, sqlSting, -1, &stmt, nil);

NSMutableArray *arr = [NSMutableArray array];

//执行预编译指针 SQLITE_ROW 一行一行数据执行

while (sqlite3_step(stmt) == SQLITE_ROW) {

NSMutableDictionary *dic = [NSMutableDictionary dictionary];

//获取某行中的第一列数据下标从0开始

int stuendenId = sqlite3_column_int(stmt, 0);

[dic setObject:[NSNumber numberWithInt:stuendenId] forKey:@"theIntId"];

//获取某行中的第二列数据

const unsigned char *muName = sqlite3_column_text(stmt, 1);

[dic setObject:[NSString stringWithFormat:@"%s",muName] forKey:@"theNmae"];

//获取某行中的第三列数据

const unsigned char *stuendenAge = sqlite3_column_text(stmt, 2);

[dic setObject:[NSString stringWithFormat:@"%s",stuendenAge] forKey:@"theAge"];

//获取某行中的第四列数据

const unsigned char *stuendenSex = sqlite3_column_text(stmt, 3);

[dic setObject:[NSString stringWithFormat:@"%s",stuendenSex] forKey:@"theSex"];

[arr addObject:dic];

}

//销毁预编译指针

sqlite3_finalize(stmt);

return arr;

}

//关闭数据库

-(void)closeData{

sqlite3_close(theSqliteDB);

}

//查询某一条数据

-(NSMutableDictionary *)showStudentOneMessage:(NSInteger)theId{

//创建sql语句格式：select * from 表名 where 主键名id

const char *sqlSting = "select * from classroom where theIntId = ?";

//预编译指针

sqlite3_stmt *stmt;

//预编译sql语句

sqlite3_prepare_v2(theSqliteDB, sqlSting, -1, &stmt, nil);

//绑定占位符

sqlite3_bind_int(stmt, 1, (int)theId);

NSMutableDictionary *dic = [NSMutableDictionary dictionary];

//执行预编译指针 SQLITE_ROW 一行一行数据执行

while (sqlite3_step(stmt) == SQLITE_ROW) {

//获取某行中的第一列数据下标从0开始

int muid = sqlite3_column_int(stmt, 0);

[dic setObject:[NSNumber numberWithInt:muid] forKey:@"theIntId"];

//获取某行中的第二列数据

const unsigned char *theName = sqlite3_column_text(stmt, 1);

[dic setObject:[NSString stringWithFormat:@"%s",theName] forKey:@"theNmae"];

//获取某行中的第三列数据

const unsigned char *theAge = sqlite3_column_text(stmt, 2);

[dic setObject:[NSString stringWithFormat:@"%s",theAge] forKey:@"theAge"];

//获取某行中的第四列数据

const unsigned char *theSex = sqlite3_column_text(stmt, 3);

[dic setObject:[NSString stringWithFormat:@"%s",theSex] forKey:@"theSex"];

}

//销毁预编译指针

sqlite3_finalize(stmt);

return dic;

}

//view文件夹

ClassView.h继承UIView

@property(nonatomic,strong)UITextField *theId,*theNameTF,*theAageTF,*theSexTF;

@property(nonatomic,strong)UIButton *theAddBtn,*theDeleteBtn,*theQuBtn,*theChangeBtn;

@property(nonatomic,strong)UITextView *theTextView;

//ClassView.m

-(instancetype)initWithFrame:(CGRect)frame

{

if (self = [super initWithFrame:frame])

{

[self addSubview:self.theId];

[self addSubview:self.theNameTF];

[self addSubview:self.theAageTF];

[self addSubview:self.theSexTF];

[self addSubview:self.theAddBtn];

[self addSubview:self.theDeleteBtn];

[self addSubview:self.theChangeBtn];

[self addSubview:self.theQuBtn];

[self addSubview:self.theTextView];

}

return self;

}

-(UITextField *)theId

{

if (!_theId) {

_theId = [[UITextField alloc]initWithFrame:CGRectMake(30, 60, 200, 40)];

_theId.borderStyle = UITextBorderStyleRoundedRect;

_theId.placeholder = @"请输入你的学号";

}

return _theId;

}

-(UITextField *)theNameTF

{

if (!_theNameTF) {

_theNameTF = [[UITextField alloc]initWithFrame:CGRectMake(30, 110, 200, 40)];

_theNameTF.borderStyle = UITextBorderStyleRoundedRect;

_theNameTF.placeholder = @"请输入你的姓名";

}

return _theNameTF;

}

-(UITextField *)theAageTF

{

if (!_theAageTF) {

_theAageTF = [[UITextField alloc]initWithFrame:CGRectMake(30, 160, 200, 40)];

_theAageTF.borderStyle = UITextBorderStyleRoundedRect;

_theAageTF.placeholder = @"请输入你的年龄";

}

return _theAageTF;

}

-(UITextField *)theSexTF

{

if (!_theSexTF) {

_theSexTF = [[UITextField alloc]initWithFrame:CGRectMake(30, 205, 200, 40)];

_theSexTF.borderStyle = UITextBorderStyleRoundedRect;

_theSexTF.placeholder = @"请输入你的性别";

}

return _theSexTF;

}

-(UIButton *)theAddBtn{

if (!_theAddBtn) {

_theAddBtn = [[UIButton alloc]initWithFrame:CGRectMake(30, 250, 60, 40)];

[_theAddBtn setTitle:@"添加" forState:UIControlStateNormal];

_theAddBtn.layer.cornerRadius = 10;

_theAddBtn.backgroundColor = [UIColor lightGrayColor];

}

return _theAddBtn;

}

-(UIButton *)theDeleteBtn{

if (!_theDeleteBtn) {

_theDeleteBtn = [[UIButton alloc]initWithFrame:CGRectMake(100, 250, 60, 40)];

[_theDeleteBtn setTitle:@"删除" forState:UIControlStateNormal];

_theDeleteBtn.layer.cornerRadius = 10;

_theDeleteBtn.backgroundColor = [UIColor greenColor];

}

return _theDeleteBtn;

}

-(UIButton *)theChangeBtn{

if (!_theChangeBtn) {

_theChangeBtn = [[UIButton alloc]initWithFrame:CGRectMake(170, 250, 60, 40)];

[_theChangeBtn setTitle:@"修改" forState:UIControlStateNormal];

_theChangeBtn.layer.cornerRadius = 10;

_theChangeBtn.backgroundColor = [UIColor redColor];

}

return _theChangeBtn;

}

-(UIButton *)theQuBtn{

if (!_theQuBtn) {

_theQuBtn = [[UIButton alloc]initWithFrame:CGRectMake(235, 250, 60, 40)];

_theQuBtn.layer.cornerRadius = 10;

[_theQuBtn setTitle:@"查询" forState:UIControlStateNormal];

_theQuBtn.backgroundColor = [UIColor blueColor];

}

return _theQuBtn;

}

-(UITextView *)theTextView{

if (!_theTextView) {

_theTextView = [[UITextView alloc]initWithFrame:CGRectMake(30, 300, 300, 300)];

_theTextView.textColor = [UIColor redColor];

}

return _theTextView;

}

//Controller文件夹

ViewController.h继承UIViewController

#import "ClassView.h"

#import "DataBase.h"

#import "ClassRoom.h"

{

ClassView *theClassView;

}

//===============================

theClassView = [[ClassView alloc]initWithFrame:self.view.frame];

self.view = theClassView;

theClassView.theId.delegate = self;

//添加数据

[theClassView.theAddBtn addTarget:self action:@selector(addMessage:) forControlEvents:UIControlEventTouchUpInside];

//删除数据

[theClassView.theDeleteBtn addTarget:self action:@selector(deleteMessage) forControlEvents:UIControlEventTouchUpInside];

//修改数据

[theClassView.theChangeBtn addTarget:self action:@selector(changeClick) forControlEvents:UIControlEventTouchUpInside];

//查询数据

[theClassView.theQuBtn addTarget:self action:@selector(showMessage:) forControlEvents:UIControlEventTouchUpInside];

//======================================

//添加数据

-(void)addMessage:(id)sender{

//打开数据库

[[DataBase initDataBase]initData];

//添加数据

ClassRoom *theClass = [[ClassRoom alloc]init];

theClass.theNmae = theClassView.theNameTF.text;

theClass.theAge = theClassView.theAageTF.text;

theClass.theSex = theClassView.theSexTF.text;

[[DataBase initDataBase]addSqlite:theClass];

//关闭数据库

[[DataBase initDataBase]closeData];

}

//删除数据

-(void)deleteMessage{

//打开数据库

[[DataBase initDataBase]initData];

//删除数据

[[DataBase initDataBase]deleteData:[theClassView.theId.text intValue]];

//关闭数据库

[[DataBase initDataBase] closeData];

}

//修改数据

-(void)changeClick{

//打开数据库

[[DataBase initDataBase]initData];

//修改数据

ClassRoom *theClass = [[ClassRoom alloc]init];

theClass.theIntId = [theClassView.theId.text integerValue];

theClass.theNmae = theClassView.theNameTF.text;

theClass.theAge = theClassView.theAageTF.text;

theClass.theSex = theClassView.theSexTF.text;

[[DataBase initDataBase]changeData:theClass];

//关闭数据库

[[DataBase initDataBase]closeData];

}

//展示数据

-(void)showMessage:(id)sender{

//打开数据库

[[DataBase initDataBase]initData];

//查询数据

NSMutableArray *arr = [[DataBase initDataBase] theDataArray];

[self showStudentsMessage:arr];

//关闭数据库

[[DataBase initDataBase] closeData];

}

-(void)showStudentsMessage:(NSMutableArray *)theArray

{

NSMutableString *str = [[NSMutableString alloc]initWithFormat:@"学生ID 学生姓名学生年龄学生性别\n"];

for (NSMutableDictionary *dic in theArray)

{

[str appendFormat:@" %@ %@ %@ %@\n",[dic objectForKey:@"theIntId"],[dic objectForKey:@"theNmae"],[dic objectForKey:@"theAge"],[dic objectForKey:@"theSex"]];

}

theClassView.theTextView.text = str;

}

- (BOOL)textField:(UITextField *)textField shouldChangeCharactersInRange:(NSRange)range replacementString:(NSString *)string{

//打开数据库

[[DataBase initDataBase]initData];

//查询某一条数据

NSMutableDictionary *dicc = [[DataBase initDataBase] showStudentOneMessage:[[textField.text stringByAppendingString:string] integerValue]];

theClassView.theNameTF.text = [dicc objectForKey:@"theNmae"];

theClassView.theAageTF.text = [dicc objectForKey:@"theAge"];

theClassView.theSexTF.text = [dicc objectForKey:@"theSex"];

//关闭数据库

[[DataBase initDataBase] closeData];

return YES;

}

-(void)touchesBegan:(NSSet*)touches withEvent:(UIEvent *)event{

[self.view endEditing:YES];

}

谢谢 !!!

最后编辑于：2017.12.10 03:40:46

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 203,456评论 5赞 477
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,370评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 150,337评论 0赞 337
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,583评论 1赞 273
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,596评论 5赞 365
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,572评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,936评论 3赞 395
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,595评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,850评论 1赞 297
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,601评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,685评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,371评论 4赞 318
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,951评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,934评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,167评论 1赞 259
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 43,636评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,411评论 2赞 342

超完善注释SqlitDemo增删改查

推荐阅读更多精彩内容