#import "ViewController.h"
#import <sqlite3.h>
#define kFilename @"Homeworkdb.rdb"
@interface ViewController ()
@end
@implementation ViewController
- (void)viewDidLoad
{
[super viewDidLoad];
}
- (void)didReceiveMemoryWarning
{
[super didReceiveMemoryWarning];
// Dispose of any resources that can be recreated.
}
-(NSString *)dataFilePath
{
NSArray *path = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString * document = [path objectAtIndex:0];
return [document stringByAppendingPathComponent:kFilename];
}
//增加
- (IBAction)addClick:(id)sender {
//创建
sqlite3 * database;
if(sqlite3_open([[self dataFilePath] UTF8String], &database)!=SQLITE_OK)
{
sqlite3_close(database);
NSAssert(0, @"Failed to open database");
}
NSString * creataSQL = @"CREATE TABLE IF NOT EXISTS FIELDS" "(id INTEGER PRIMARY KEY,name TEXT);";
char * errorMsg;
if(sqlite3_exec(database, [creataSQL UTF8String], NULL, NULL, &errorMsg)!=SQLITE_OK)
{
sqlite3_close(database);
NSAssert(0, @"Error creating table:%s",errorMsg);
}
char * update = "insert or replace into FIELDS (id, name)" "values (?,?);";
sqlite3_stmt * stmt;
if(sqlite3_prepare_v2(database, update, -1, &stmt, nil) == SQLITE_OK)
{
sqlite3_bind_int(stmt, 1, [self.idText.text intValue]); //1表示第一个问号
sqlite3_bind_text(stmt, 2, [self.nameText.text UTF8String], -1, NULL);//2表示第二个问号
}
if(sqlite3_step(stmt)!=SQLITE_DONE)
{
NSAssert(0, @"Error updating table:%s",errorMsg);
}
sqlite3_finalize(stmt); //这句话不能漏
sqlite3_close(database); //关闭数据库
UIAlertView *a = [[UIAlertView alloc] initWithTitle:@"友情提示" message:@"添加成功" delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil];
[a show];
}
//删除
- (IBAction)deleteClick:(id)sender {
sqlite3 * database;
if(sqlite3_open([[self dataFilePath] UTF8String], &database)!=SQLITE_OK)
{
sqlite3_close(database);
NSAssert(0, @"Failed to open database");
}
NSString * creataSQL = @"CREATE TABLE IF NOT EXISTS FIELDS" "(id INTEGER PRIMARY KEY,name TEXT);";
char * errorMsg;
if(sqlite3_exec(database, [creataSQL UTF8String], NULL, NULL, &errorMsg)!=SQLITE_OK)
{
sqlite3_close(database);
NSAssert(0, @"Error creating table:%s",errorMsg);
}
char * del = "delete from FIELDS where id = ?;";
sqlite3_stmt * stmt;
if(sqlite3_prepare_v2(database, del, -1, &stmt, nil) == SQLITE_OK)
{
sqlite3_bind_int(stmt, 1, [self.idText.text intValue]); //1表示第一个问号
}
if(sqlite3_step(stmt)!=SQLITE_DONE)
{
NSAssert(0, @"Error delete table:%s",errorMsg);
}
sqlite3_finalize(stmt);
sqlite3_close(database);
UIAlertView *a = [[UIAlertView alloc] initWithTitle:@"友情提示" message:@"删除成功" delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil];
[a show];
}
//修改
- (IBAction)updateClick:(id)sender {
sqlite3 * database;
if(sqlite3_open([[self dataFilePath] UTF8String], &database)!=SQLITE_OK)
{
sqlite3_close(database);
NSAssert(0, @"Failed to open database");
}
NSString * creataSQL = @"CREATE TABLE IF NOT EXISTS FIELDS" "(id INTEGER PRIMARY KEY,name TEXT);";
char * errorMsg;
if(sqlite3_exec(database, [creataSQL UTF8String], NULL, NULL, &errorMsg)!=SQLITE_OK)
{
sqlite3_close(database);
NSAssert(0, @"Error creating table:%s",errorMsg);
}
char * update = "update FIELDS set id = ?,name = ? where id = ?";
sqlite3_stmt * stmt;
if(sqlite3_prepare_v2(database, update, -1, &stmt, nil) == SQLITE_OK)
{
sqlite3_bind_int(stmt, 1, [self.idText.text intValue]); //1表示第一个问号
sqlite3_bind_text(stmt, 2, [self.nameText.text UTF8String], -1, NULL);//2表示第二个问号
sqlite3_bind_int(stmt, 3, [self.idText.text intValue]);
}
if(sqlite3_step(stmt)!=SQLITE_DONE)
{
NSAssert(0, @"Error updating table:%s",errorMsg);
}
sqlite3_finalize(stmt); //这句话不能漏
sqlite3_close(database); //关闭数据库
UIAlertView *a = [[UIAlertView alloc] initWithTitle:@"友情提示" message:@"修改成功" delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil];
[a show];
}
//查询
- (IBAction)selectClick:(id)sender {
//创建数据库
sqlite3 * database;
if(sqlite3_open([[self dataFilePath] UTF8String], &database)!=SQLITE_OK)
{
sqlite3_close(database);
NSAssert(0, @"Failed to open database");
}
NSString * creataSQL = @"CREATE TABLE IF NOT EXISTS FIELDS" "(id INTEGER PRIMARY KEY,name TEXT);";
char * errorMsg;
if(sqlite3_exec(database, [creataSQL UTF8String], NULL, NULL, &errorMsg)!=SQLITE_OK)
{
sqlite3_close(database);
NSAssert(0, @"Error creating table:%s",errorMsg);
}
NSString * query = @"select * from FIELDS where id = ?";
sqlite3_stmt * statement;
if(sqlite3_prepare_v2(database, [query UTF8String], -1, &statement, nil) == SQLITE_OK)
{
sqlite3_bind_int(statement, 1, [self.txtSearchBar.text intValue]);
//sqlite3_bind_text(statement, 2, [self.nameText.text UTF8String], -1, NULL);
while(sqlite3_step(statement) == SQLITE_ROW)
{
int id = sqlite3_column_int(statement, 0); //0表示第1列
char * name = (char *)sqlite3_column_text(statement, 1); //1表示第2列
NSString * sid = [[NSString alloc] initWithFormat:@"%d",id];
NSString * sname = [[NSString alloc] initWithUTF8String:name];
self.nameText.text = sname;
self.idText.text = sid;
UIAlertView *a = [[UIAlertView alloc] initWithTitle:@"友情提示" message:@"查询成功" delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil];
[a show];
}
sqlite3_finalize(statement);
}
else
{
NSLog(@"error");
}
sqlite3_close(database);
}
- (void)dealloc {
[_idText release];
[_nameText release];
[_txtSearchBar release];
[super dealloc];
}
- (IBAction)viewClick:(id)sender {
[self.idText resignFirstResponder];
[self.nameText resignFirstResponder];
}
@end