#import "sqlite3.h"
#define DBNAME @"personinfo.sqlite"
#define NAME @"name"
#define AGE @"age"
#define ADDRESS @"address"
#define TABLENAME @"PERSONINFO"
-(void)execSql:(NSString *)sql
{
char *err;
if (sqlite3_exec(db, [sql UTF8String], NULL, NULL, &err) != SQLITE_OK) {
sqlite3_close(db);
NSLog(@"数据库操作数据失败!");
}
}
-(void)createTbDB{
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documents = [paths objectAtIndex:0];
NSString *database_path = [documents stringByAppendingPathComponent:DBNAME];
if (sqlite3_open([database_path UTF8String], &db) != SQLITE_OK) {
sqlite3_close(db);
NSLog(@"数据库打开失败");
}
NSString *sqlCreateTable = @"CREATE TABLE IF NOT EXISTS PERSONINFO (ID INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER, address TEXT)";
[self execSql:sqlCreateTable];
NSLog(@"c创建表成功!");
sqlite3_close(db);
}
-(void)insertIntoTabDb{
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documents = [paths objectAtIndex:0];
NSString *database_path = [documents stringByAppendingPathComponent:DBNAME];
if (sqlite3_open([database_path UTF8String], &db) != SQLITE_OK) {
sqlite3_close(db);
NSLog(@"数据库打开失败");
}
NSString *sql1 = [NSString stringWithFormat:
@"INSERT INTO '%@' ('%@', '%@', '%@') VALUES ('%@', '%@', '%@')",
TABLENAME, NAME, AGE, ADDRESS, @"张三", @"23", @"西城区"];
NSString *sql2 = [NSString stringWithFormat:
@"INSERT INTO '%@' ('%@', '%@', '%@') VALUES ('%@', '%@', '%@')",
TABLENAME, NAME, AGE, ADDRESS, @"李四", @"20", @"东城区"];
[self execSql:sql1];
[self execSql:sql2];
NSLog(@"插入数据成功!");
}
-(void)selectTbMe{
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documents = [paths objectAtIndex:0];
NSString *database_path = [documents stringByAppendingPathComponent:DBNAME];
if (sqlite3_open([database_path UTF8String], &db) != SQLITE_OK) {
sqlite3_close(db);
NSLog(@"数据库打开失败");
}
NSString *sqlQuery = @"SELECT * FROM PERSONINFO";
sqlite3_stmt * statement;
if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK) {
while (sqlite3_step(statement) == SQLITE_ROW) {
char *name = (char*)sqlite3_column_text(statement, 1);
NSString *nsNameStr = [[NSString alloc]initWithUTF8String:name];
int age = sqlite3_column_int(statement, 2);
char *address = (char*)sqlite3_column_text(statement, 3);
NSString *nsAddressStr = [[NSString alloc]initWithUTF8String:address];
NSLog(@"查询数据成功!");
NSLog(@"name:%@ age:%d address:%@",nsNameStr,age, nsAddressStr);
}
}
}