SqlData.h
#import#import#import "ClassMessage.h"
#import <>
@interface SqlData : NSObject
{
sqlite3 *db;
}
//单例方法
+(instancetype)initData;
//初始化数据库
-(void)initSql;
//初始化数据库表格
-(void)initTable;
//添加数据
-(void)AddData:(ClassMessage *)data;
//修改数据
-(void)upData:(ClassMessage *)data;
//删除数据
-(void)deletaData:(NSInteger)theId;
//查询数据
-(NSMutableArray *)showData;
//关闭数据
-(void)closeSql;
@end
SqlData.m
#import "SqlData.h"
//创建静态变量
static SqlData *sqlData;
@implementation SqlData
//单例方法
+(instancetype)initData
{
if (!sqlData) {
sqlData = [[SqlData alloc]init];
}
return sqlData;
}
//初始化数据库
-(void)initSql
{
//Documents 目录 （路径）
NSString *str = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)objectAtIndex:0];
//拼接
NSString *strName = [str stringByAppendingString:@"/1511E.db"];
//打开数据库
if (sqlite3_open([strName UTF8String], &db) == SQLITE_OK) {
NSLog(@"数据库打开成功");
[self initTable];
}else
{
NSLog(@"数据库打开失败");
}
}
//初始化数据库表格
-(void)initTable
{
//sql 语句
//初始化数据库表格的格式：create table if not exists 表名（主键id integer primary key，所有的数据类型);
// const char *sql = "create table if not exists ClassMessage(classid integer primary key,name text,age text,sex text,heigt text,weitht text)";
//*name,*age,*sex,*heigt,*weitht;
const char *sql = "create table if not exists ClassMessage(classid integer primary key,name text,age text,sex text,heigt text,weitht text)";
//预编译数据库的指针
sqlite3_stmt *stmt;
//绑定数据库指针的一个接口 -1 自动匹配长度
sqlite3_prepare_v2(db, sql, -1, &stmt, nil);
//执行预编译接口
//一行一行的去判断是否执行完成
if (sqlite3_step(stmt) == SQLITE_DONE) {
NSLog(@"数据库表格创建成功");
}else
{
NSLog(@"数据框表格创建失败");
}
//销毁接口
sqlite3_finalize(stmt);
}
//添加数据
-(void)AddData:(ClassMessage *)data
{
//添加数据的sql语句: insert into 表明 values(null,?,?,?,?,?);
// const char *sql = "insert into ClassMessage values(null,?,?,?,?,?)";
const char *sql = "insert into ClassMessage values(null,?,?,?,?,?)";
//预编译数据库的指针
sqlite3_stmt *stmt;
//绑定数据库指针的一个接口 -1 自动匹配长度
sqlite3_prepare_v2(db, sql, -1, &stmt, nil);
//添加数据库的接口
//绑定数据库接口
sqlite3_bind_text(stmt, 1, [data.name UTF8String], -1 , SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 2, [data.age UTF8String], -1 , SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 3, [data.sex UTF8String], -1 , SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 4, [data.heigt UTF8String], -1 , SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 5, [data.weitht UTF8String], -1 , SQLITE_TRANSIENT);
//执行预编译接口 sqlite3_step(stmt);
sqlite3_step(stmt);
//销毁接口
sqlite3_finalize(stmt);
}
//修改数据
-(void)upData:(ClassMessage *)data
{
//sql 语句的格式：update 表名 set 所有数据 where 主键 = ？
const char *sql = "update ClassMessage set name = ?,age = ?,sex = ?,heigt = ?,weitht = ? where classid = ?";
//预编译指针 (链接到数据库)
sqlite3_stmt *stmt;
//绑定数据库指针的接口
sqlite3_prepare_v2(db, sql, -1, &stmt, nil);
//添加数据库的接口
//绑定数据库接口
sqlite3_bind_text(stmt, 1, [data.name UTF8String], -1 , SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 2, [data.age UTF8String], -1 , SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 3, [data.sex UTF8String], -1 , SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 4, [data.heigt UTF8String], -1 , SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 5, [data.weitht UTF8String], -1 , SQLITE_TRANSIENT);
//绑定主键 id
sqlite3_bind_int(stmt, 6,(int)(data.classid));
//执行预编译接口 sqlite3_step(stmt);
sqlite3_step(stmt);
//销毁接口
sqlite3_finalize(stmt);
}
//删除数据
-(void)deletaData:(NSInteger)theId
{
//sql 语句： delete from 表名 where 表明的主键 id = ?
const char *sql = "delete from ClassMessage where classid = ?";
//预编译指针 (链接到数据库)
sqlite3_stmt *stmt;
//绑定数据库指针的接口
sqlite3_prepare_v2(db, sql, -1, &stmt, nil);
//删除绑定主键 id
sqlite3_bind_int(stmt, 1, (int)theId);
//执行预编译接口 sqlite3_step(stmt);
sqlite3_step(stmt);
//销毁接口
sqlite3_finalize(stmt);
}
//查询数据
-(NSMutableArray *)showData
{
//sql 语句格式 ：select *from 表名
// const char *sql = "select *from ClassMessage";
const char *sql = "select *from ClassMessage";
//预编译指针 (链接到数据库)
sqlite3_stmt *stmt;
//绑定数据库指针的接口
sqlite3_prepare_v2(db, sql, -1, &stmt, nil);
NSMutableArray *arr = [NSMutableArray array];
//执行数据库中的预编译接口
//SQLITE_ROW一行一行的去查询数据库中的数据
while (sqlite3_step(stmt) == SQLITE_ROW) {
ClassMessage *classData = [[ClassMessage alloc]init];
//找到表格中的主键
//sqlite3_column_xxx 标识返回当前的行（指的是列的数据）
classData.classid = sqlite3_column_int(stmt, 0);
classData.name = [NSString stringWithUTF8String:(const char *) sqlite3_column_text(stmt, 1)];
classData.age = [NSString stringWithUTF8String:(const char *) sqlite3_column_text(stmt, 2)];
classData.sex = [NSString stringWithUTF8String:(const char *) sqlite3_column_text(stmt, 3)];
classData.heigt = [NSString stringWithUTF8String:(const char *) sqlite3_column_text(stmt, 4)];
classData.weitht = [NSString stringWithUTF8String:(const char *) sqlite3_column_text(stmt, 5)];
[arr addObject:classData];
}
//销毁接口
sqlite3_finalize(stmt);
return arr;
}
//关闭数据
-(void)closeSql
{
sqlite3_close(db);
}
@end
ClassMessage.h
#import@interface ClassMessage : NSObject
//唯一标识 数据库必须得有一个主键
@property(nonatomic,assign)NSInteger classid;
@property(nonatomic,strong) NSString *name,*age,*sex,*heigt,*weitht;
@end
ClassView.h
#import@interface ClassView : UIView
@property(nonatomic,strong) UITextField *nameTf,*ageTf,*sexTf,*heightTf,*weightTf;
@end
ClassView.m
#import "ClassView.h"
@implementation ClassView
-(instancetype)initWithFrame:(CGRect)frame
{
if (self = [super initWithFrame:frame]) {
[self addSubview:self.nameTf];
[self addSubview:self.ageTf];
[self addSubview:self.sexTf];
[self addSubview:self.heightTf];
[self addSubview:self.weightTf];
}
return self;
}
//懒加载
-(UITextField *)nameTf
{
if (!_nameTf) {
_nameTf = [[UITextField alloc]initWithFrame:CGRectMake(0, 80, self.frame.size.width, 50)];
_nameTf.borderStyle = UITextBorderStyleRoundedRect;
_nameTf.placeholder = @"姓名";
_nameTf.textColor = [UIColor lightGrayColor];
_nameTf.textAlignment = NSTextAlignmentCenter;
}
return _nameTf;
}
-(UITextField *)ageTf
{
if (!_ageTf) {
_ageTf = [[UITextField alloc]initWithFrame:CGRectMake(0, 140, self.frame.size.width, 50)];
_ageTf.borderStyle = UITextBorderStyleRoundedRect;
_ageTf.placeholder = @"年龄";
_ageTf.textColor = [UIColor lightGrayColor];
_ageTf.textAlignment = NSTextAlignmentCenter;
}
return _ageTf;
}
-(UITextField *)sexTf
{
if (!_sexTf) {
_sexTf = [[UITextField alloc]initWithFrame:CGRectMake(0, 200, self.frame.size.width, 50)];
_sexTf.borderStyle = UITextBorderStyleRoundedRect;
_sexTf.placeholder = @"性别";
_sexTf.textColor = [UIColor lightGrayColor];
_sexTf.textAlignment = NSTextAlignmentCenter;
}
return _sexTf;
}
-(UITextField *)heightTf
{
if (!_heightTf) {
_heightTf = [[UITextField alloc]initWithFrame:CGRectMake(0, 260, self.frame.size.width, 50)];
_heightTf.borderStyle = UITextBorderStyleRoundedRect;
_heightTf.placeholder = @"身高";
_heightTf.textColor = [UIColor lightGrayColor];
_heightTf.textAlignment = NSTextAlignmentCenter;
}
return _heightTf;
}
-(UITextField *)weightTf
{
if (!_weightTf) {
_weightTf = [[UITextField alloc]initWithFrame:CGRectMake(0, 320, self.frame.size.width, 50)];
_weightTf.borderStyle = UITextBorderStyleRoundedRect;
_weightTf.placeholder = @"体重";
_weightTf.textColor = [UIColor lightGrayColor];
_weightTf.textAlignment = NSTextAlignmentCenter;
}
return _weightTf;
}
@end
ViewController.m
#import "ViewController.h"
#import "SqlData.h"
#import "ClassMessage.h"
#import "sectionViewController.h"
@interface ViewController ()
{
NSMutableArray *array;
}
@end
@implementation ViewController
- (void)viewDidLoad {
[super viewDidLoad];
// //1.先调用类方法 2.通过类方法 3.调用实例方法
// [[SqlData initData]initSql];
//标题
self.title = @"数据库";
//初始化
array = [NSMutableArray array];
self.tableView.rowHeight = 150;
//导航右按钮
self.navigationItem.rightBarButtonItem = [[UIBarButtonItem alloc]initWithBarButtonSystemItem:UIBarButtonSystemItemAdd target:self action:@selector(click)];
}
//按钮点击事件跳转
-(void)click
{
sectionViewController *section = [[sectionViewController alloc]init];
[self.navigationController pushViewController:section animated:YES];
}
//视图将要显示
-(void)viewWillAppear:(BOOL)animated
{
//调用数据库单例方法
[[SqlData initData]initSql];
//调用数据库查询方法
array = [[SqlData initData]showData];
//关闭数据方法
[[SqlData initData]closeSql];
//刷新表格
[self.tableView reloadData];
}
#pragma mark -
#pragma mark UITableViewDataSource
//表格行数
-(NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section
{
return array.count;
}
//表格内容
-(UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath
{
UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:@""];
if (!cell) {
cell = [[UITableViewCell alloc]initWithStyle:UITableViewCellStyleDefault reuseIdentifier:@""];
}
ClassMessage *classmsg = array[indexPath.row];
cell.textLabel.text = [NSString stringWithFormat:@"%ld\n%@\n%@\n%@\n%@\n%@",classmsg.classid,classmsg.name,classmsg.age,classmsg.sex,classmsg.heigt,classmsg.weitht];
cell.textLabel.numberOfLines = 0;
return cell;
}
-(void)tableView:(UITableView *)tableView commitEditingStyle:(UITableViewCellEditingStyle)editingStyle forRowAtIndexPath:(NSIndexPath *)indexPath
{
//调用数据库
[[SqlData initData]initSql];
//是删除主键ID 获取数据库中的数据
[[SqlData initData]deletaData:[array[indexPath.row]classid]];
//关闭数据库
[[SqlData initData]closeSql];
//删除数据
[array removeObject:array[indexPath.row]];
//刷新表格
[self.tableView reloadData];
}
//代理方法
-(void)tableView:(UITableView *)tableView didSelectRowAtIndexPath:(NSIndexPath *)indexPath
{
sectionViewController *secVc = [sectionViewController new];
//属性传值
secVc.msg = array[indexPath.row];
[self.navigationController pushViewController:secVc animated:YES];
}
@end
sectionViewController.h
#import#import "ClassMessage.h"
@interface sectionViewController : UIViewController
@property(nonatomic,strong)ClassMessage *msg;
@end
sectionViewController.m
#import "sectionViewController.h"
#import "ClassView.h"
#import "ClassMessage.h"
#import "SqlData.h"
@interface sectionViewController ()
{
ClassView *theview;
}
@end
@implementation sectionViewController
- (void)viewDidLoad {
[super viewDidLoad];
theview = [[ClassView alloc]initWithFrame:self.view.frame];
theview.backgroundColor = [UIColor blackColor];
self.view = theview;
theview.nameTf.text = self.msg.name;
theview.ageTf.text = self.msg.age;
theview.sexTf.text = self.msg.sex;
theview.heightTf.text = self.msg.heigt;
theview.weightTf.text = self.msg.weitht;
//标题
if (theview.nameTf.text.length <=0) {
self.title = @"添加数据";
self.navigationItem.rightBarButtonItem = [[UIBarButtonItem alloc]initWithBarButtonSystemItem:UIBarButtonSystemItemSave target:self action:@selector(save)];
}else
{
self.title = @"修改数据";
self.navigationItem.rightBarButtonItem = [[UIBarButtonItem alloc]initWithBarButtonSystemItem:UIBarButtonSystemItemEdit target:self action:@selector(Edit)];
}
}
-(void)save
{
ClassMessage *message = [ClassMessage new];
message.name = theview.nameTf.text;
message.age = theview.ageTf.text;
message.sex = theview.sexTf.text;
message.heigt = theview.heightTf.text;
message.weitht = theview.weightTf.text;
//调用数据库方法
[[SqlData initData]initSql];
//调用添加数据库方法
[[SqlData initData]AddData:message];
//调用关闭数据库方法
[[SqlData initData]closeSql];
//跳转到上一试图
[self.navigationController popViewControllerAnimated:YES];
}
-(void)Edit
{
self.msg.name = theview.nameTf.text;
self.msg.age = theview.ageTf.text;
self.msg.sex = theview.sexTf.text;
self.msg.heigt = theview.heightTf.text;
self.msg.weitht = theview.weightTf.text;
//调用数据库方法
[[SqlData initData]initSql];
[[SqlData initData]upData:self.msg];
[[SqlData initData]closeSql];
//跳转到上一试图
[self.navigationController popViewControllerAnimated:YES];
}
@end
网友评论