超完善注释SqlitDemo增删改查
首先导入libsqlite3.tbd
首先导入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"
<UITextFieldDelegate>
{
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];
}
谢谢 !!!
