@implementation CHViewController
//打开数据库
-(void)openDB
{
NSArray *paths=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory=[paths objectAtIndex:0];
NSString *dbFileName=@"sample.db";
NSString *dataFilePath=[documentsDirectory stringByAppendingPathComponent:dbFileName];
if(sqlite3_open([dataFilePath UTF8String],&database)!=SQLITE_OK)
{
sqlite3_close(database);
NSLog(@"打开数据库失败!");
}
}
//创建表格
-(void)createTable
{
char *errorMessage;
NSString *sql=@"create table if not exists user(user_id integer primary key,username text,password text);";
if(sqlite3_exec(database, [sql UTF8String], NULL, NULL, &errorMessage)!=SQLITE_OK)
{
sqlite3_close(database);
NSLog(@"创建表失败!"); }
}
//插入数据
-(void)insertData:(NSString *)username andPassword:(NSString *)password
{
char *updateSql="insert or replace into user(username,password)values(?,?)";
sqlite3_stmt *statement;
if(sqlite3_prepare_v2(database, updateSql, -1, &statement, nil)==SQLITE_OK)
{
sqlite3_bind_text(statement, 1, [username UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(statement, 2, [password UTF8String], -1, SQLITE_TRANSIENT);
}
if(sqlite3_step(statement)!=SQLITE_DONE)
{
NSLog(@"数据库插入数据失败");
}
else
{
NSLog(@"数据库插入数据ok");
}
sqlite3_finalize(statement);
sqlite3_close(database);
}
//插入数据
- (IBAction)insertDataAction:(id)sender {
[self openDB];
[self createTable];
[self insertData:@"testname4" andPassword:@"testpassword4"];
}
//查询数据
- (IBAction)queryDataAction:(id)sender {
[self openDB];
[self createTable];
sqlite3_stmt *statement=nil;
char *sql="select * from user";
if(sqlite3_prepare_v2(database, sql, -1, &statement, NULL)==SQLITE_OK)
{
NSLog(@"faile query 1");
}
while(sqlite3_step(statement)==SQLITE_ROW)
{
char *tempName=(char *)sqlite3_column_text(statement,1);
char *tempPassword=(char *)sqlite3_column_text(statement,2);
NSString *userName=[NSString stringWithUTF8String:tempName];
NSString *userPassword=[NSString stringWithUTF8String:tempPassword];
NSLog(@"%@,%@",userName,userPassword);
}
sqlite3_finalize(statement);
sqlite3_close(database);
}