//创建数据库
static sqlite3 *sqlite=nil;
+(sqlite3*)openDB{
if (!sqlite) {
// DataBase *database=[[DataBase alloc]init];
// database;
NSString* fromPath=[[NSBundle mainBundle]pathForResource:@"ElevenStudents.sqlite" ofType:Nil];
NSString* toPath=[[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0] stringByAppendingPathComponent:@"ElevenStudents.sqlite"];
NSFileManager *file=[NSFileManager defaultManager];
if (![file fileExistsAtPath:toPath]) {
//拷贝
[file copyItemAtPath:fromPath toPath:toPath error:nil];
}
//打开数据库
int result=sqlite3_open([toPath UTF8String], &sqlite);
if (result==SQLITE_OK) {
//打开成功
return sqlite;
}
}
return sqlite;
}
+(sqlite3*)openDBCreate{
if (sqlite) {
return sqlite;
}
NSString* toPath=[[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0] stringByAppendingPathComponent:@"AllStu.sqlite"];
NSLog(@"%@",toPath);
NSFileManager *file=[NSFileManager defaultManager];
if (![file fileExistsAtPath:toPath]) {
//打开数据库,要是没有会自动创建一个
int result=sqlite3_open([toPath UTF8String], &sqlite);
if (result==SQLITE_OK) {
//创建表
NSString* sql=@"CREATE TABLE 'ALLStu' ('number' INTEGER PRIMARY KEY NOT NULL , 'name' VARCHAR NOT NULL , 'age' VARCHAR NOT NULL DEFAULT 18, 'sex' VARCHAR DEFAULT 未知, 'photo' BLOB);";
sqlite3_stmt *stmt=nil;
int check=sqlite3_prepare(sqlite, [sql UTF8String], -1, &stmt, nil);
if (check==SQLITE_OK) {
sqlite3_step(stmt);
}
sqlite3_finalize(stmt);
}
return sqlite;
}else{
//打开数据库
int result=sqlite3_open([toPath UTF8String], &sqlite);
if (result==SQLITE_OK) {
//打开成功
return sqlite;
}
}
return nil;
}
//关闭
+(void)closeDB{
sqlite3_close(sqlite);
sqlite=nil;
}
// 查询数据库
//
// DataHandle.m
// SQLLite
//
// Created by 0卜7r从l on 13-11-22.
// Copyright (c) 2013年 0卜7r从l. All rights reserved.
//
#import "DataHandle.h"
static DataHandle *dataHandle=nil;
@implementation DataHandle
+(DataHandle*)shareInstance{
if (!dataHandle) {
dataHandle=[[DataHandle alloc]init];
}
return dataHandle;
}
//从数据库选取所有学生
-(NSMutableArray*)selectAllStudent{
NSMutableArray *array=[[[NSMutableArray alloc]init]autorelease];
//第一步打开数据库
sqlite3 *db=[DataBase openDBCreate];
//第二部,生命stmt,跟屁虫
sqlite3_stmt *stmt=nil;
NSString* sql=@"select *from AllStu";
//验证sql语句
int check=sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, nil);
if (check==SQLITE_OK) {
//执行
while(sqlite3_step(stmt)==SQLITE_ROW){
//单步执行,如果找到符合sql语句的数据,row表示一条数据
Student* stu=[[Student alloc]init];
stu.number=sqlite3_column_int(stmt, 0);
stu.name=[[NSString alloc]initWithUTF8String:(const char*)sqlite3_column_text(stmt, 1)];
stu.age=sqlite3_column_int(stmt, 2);
stu.sex=[[NSString alloc]initWithUTF8String:(const char*)sqlite3_column_text(stmt, 3)];
[array addObject:stu];
[stu.name release];
[stu.sex release];
[stu release];
}
}
sqlite3_finalize(stmt);
return array;
}
//通过学号查询学生信息
-(Student*)selectStudentWithNumber:(int)number{
Student* stu=[[[Student alloc]init]autorelease];
sqlite3*db=[DataBase openDB] ;
sqlite3_stmt *stmt=Nil;
NSString* sql=[NSString stringWithFormat:@"select *from AllStudent where number=%d",number];
int check=sqlite3_prepare(db, [sql UTF8String], -1, &stmt, nil);
if (check==SQLITE_OK) {
if (sqlite3_step(stmt)==SQLITE_ROW) {
//单步执行,如果找到符合sql语句的数据,row表示一条数据
stu.number=sqlite3_column_int(stmt, 0);
stu.name=[[[NSString alloc]initWithUTF8String:(const char*)sqlite3_column_text(stmt, 1)]autorelease];
stu.age=sqlite3_column_int(stmt, 2);
stu.sex=[[[NSString alloc]initWithUTF8String:(const char*)sqlite3_column_text(stmt, 3)]autorelease];
}
}
sqlite3_finalize(stmt);
return stu;
}
-(void)addStudent:(Student*)stu{
sqlite3 *db=[DataBase openDBCreate];
sqlite3_stmt *stmt=nil;
// NSString* sql=[NSString stringWithFormat:@"insert into AllStudent (name,sex,age) values('%@','%@',%d)",stu.name,stu.sex,stu.age];
NSString* sql=@"insert into AllStu (name,sex,age) values(?,?,?)";
NSLog(@"ffffff%@",sql);
int check=sqlite3_prepare(db, [sql UTF8String], -1, &stmt, nil);
if (check==SQLITE_OK) {
//将sql语句中得?绑定相应的数据
sqlite3_bind_text(stmt, 1, [stu.name UTF8String], -1, nil);
sqlite3_bind_text(stmt, 2, [stu.sex UTF8String], -1, nil);
sqlite3_bind_int(stmt, 3, stu.age);
sqlite3_step(stmt);
}
sqlite3_finalize(stmt);
}
-(void)updateStudent:(Student*)stu{
sqlite3 *db=[DataBase openDB];
sqlite3_stmt *stmt=nil;
NSString* sql=@"update AllStudent set name='娘' where number=?";
NSLog(@"ffffff%@",sql);
int check=sqlite3_prepare(db, [sql UTF8String], -1, &stmt, nil);
if (check==SQLITE_OK) {
//将sql语句中得?绑定相应的数据
// sqlite3_bind_text(stmt, 1, [stu.name UTF8String], -1, nil);
sqlite3_bind_int(stmt, 1, stu.number);
sqlite3_step(stmt);
}
sqlite3_finalize(stmt);
}
-(void)deleteStudent:(Student*)stu{
sqlite3 *db=[DataBase openDB];
sqlite3_stmt *stmt=nil;
NSString* sql=@"delete from AllStudent where name=?";
NSLog(@"ffffff%@",sql);
int check=sqlite3_prepare(db, [sql UTF8String], -1, &stmt, nil);
if (check==SQLITE_OK) {
//将sql语句中得?绑定相应的数据
sqlite3_bind_text(stmt, 1, [stu.name UTF8String], -1, nil);
// sqlite3_bind_int(stmt, 1, stu.age);
sqlite3_step(stmt);
}
sqlite3_finalize(stmt);
}
@end