//? 定義一個靜態(tài)區(qū)指針 連接數(shù)據(jù)庫 讓數(shù)據(jù)庫再程序結(jié)束后自動釋放
staticsqlite3*db?=nil;
-?(sqlite3*)openDB
{
//??說明已經(jīng)打開數(shù)據(jù)庫
if(db?!=nil)?{
returndb;
}
//??創(chuàng)建數(shù)據(jù)庫
//??先搞路徑
NSString*dbPath?=?[[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,?NSUserDomainMask,YES)lastObject]stringByAppendingPathComponent:@"Student.sqlite"];
NSLog(@"%@",dbPath);
//??如果沒有Student.sqlite文件?會創(chuàng)建一個
intresult?=?sqlite3_open(dbPath.UTF8String,?&db);
if(result?==?SQLITE_OK)?{
NSLog(@"打開數(shù)據(jù)庫成功");
}else{
NSLog(@"打開數(shù)據(jù)庫失敗");
}
returndb;
}
-?(void)closeDB
{
intresult?=?sqlite3_close(db);
if(result?==?SQLITE_OK)?{
//??把指針?重置為空?方便下次打開
db?=nil;
NSLog(@"關(guān)閉數(shù)據(jù)庫成功");
}else{
NSLog(@"關(guān)閉數(shù)據(jù)庫失敗");
}
}
//??創(chuàng)建表
-?(void)createTable
{
//?1.打開數(shù)據(jù)庫
db?=?[self openDB];
//??2.寫sql語句?根據(jù)model?創(chuàng)建表
//??create?table?Student?(Student?是表名)
//??number?integer?primary?key?not?NULL?主鍵值?如果不操作?自增
NSString*sql?=@"create?table?IF?NOT?EXISTS?lanOuStudent(number?integer?primary?key?not?NULL,?name?text?not?NULL,?gender?text?not?NULL,?age?integer?not?NULL)";
//??3.執(zhí)行sql語句?創(chuàng)建表
/**
*??第1個參數(shù)不再說了，是前面open函數(shù)得到的指針竣贪。說了是關(guān)鍵數(shù)據(jù)結(jié)構(gòu)唆涝。
第2個參數(shù)constchar*sql是一條sql?語句溉愁，以\0結(jié)尾。
第3個參數(shù)sqlite3_callback?是回調(diào)抒和，當(dāng)這條語句執(zhí)行之后，sqlite3會去調(diào)用你提供的這個函數(shù)。
第4個參數(shù)void*是你所提供的指針亏镰，你可以傳遞任何一個指針參數(shù)到這里，這個參數(shù)最終會傳到回調(diào)函數(shù)里面拯爽，如果不需要傳遞指針給回調(diào)函數(shù)索抓，可以填NULL。等下我們再看回調(diào)函數(shù)的寫
法毯炮，以及這個參數(shù)的使用逼肯。
第5個參數(shù)char**?errmsg?是錯誤信息。
*
*/
intresult?=?sqlite3_exec(db,?sql.UTF8String,NULL,NULL,NULL);
if(result?==?SQLITE_OK)?{
NSLog(@"創(chuàng)建表成功");
}else{
NSLog(@"創(chuàng)建表失敗");
}
//??4.關(guān)閉數(shù)據(jù)庫
[selfcloseDB];
}
//??插入數(shù)據(jù)
-?(void)insertWithStudent:(LanOuStudent*)student
{
//??1.打開數(shù)據(jù)庫
db?=?[selfopenDB];
//??2.寫sql語句
NSString*sql?=?[NSStringstringWithFormat:@"insert?into?lanOuStudent(name?,gender?,age?,?number)?values('%@'?,'%@'?,?'%ld'?,?'%ld')",student.name,student.gender,student.age,student.number];
//??3.執(zhí)行語句
intresult?=?sqlite3_exec(db,?sql.UTF8String,NULL,NULL,NULL);
if(result?==?SQLITE_OK)?{
NSLog(@"插入成功");
}else{
NSLog(@"插入失敗");
}
//??4.關(guān)閉數(shù)據(jù)庫
[selfcloseDB];
}
//??刪除數(shù)據(jù)
-?(void)deleteWithAge:(NSInteger)age
{
//??1.打開數(shù)據(jù)庫
db?=?[selfopenDB];
//??2.寫sql語句
NSString*sql?=?[NSStringstringWithFormat:@"delete?from?lanOuStudent?where?age?>?'%ld'",age];
//??3.執(zhí)行語句
intresult?=?sqlite3_exec(db,?sql.UTF8String,NULL,NULL,NULL);
if(result?==?SQLITE_OK)?{
NSLog(@"刪除成功");
}else{
NSLog(@"刪除失敗");
}
//??4.關(guān)閉數(shù)據(jù)庫
[selfcloseDB];
}
//??更改數(shù)據(jù)??根據(jù)名字?更改學(xué)號
-?(void)updateName:(NSString*)namebyAge:(NSInteger)age
{
//??1.打開數(shù)據(jù)庫
db?=?[selfopenDB];
//??2.寫sql語句??where?后面是根據(jù)什么
NSString*sql?=?[NSStringstringWithFormat:@"update?lanOuStudent?set?age?=?'%ld'?where?name?=?'%@'",age,name];
//??3.執(zhí)行語句
intresult?=?sqlite3_exec(db,?sql.UTF8String,NULL,NULL,NULL);
if(result?==?SQLITE_OK)?{
NSLog(@"更改成功");
}else{
NSLog(@"更改失敗");
}
//??4.關(guān)閉數(shù)據(jù)庫
[selfcloseDB];
}
//??查詢學(xué)生??根據(jù)名字和學(xué)號
-?(LanOuStudent*)queryStudentWithName:(NSString*)nameage:(NSInteger)age
{
//??1.打開數(shù)據(jù)庫
db?=?[selfopenDB];
//??2.寫sql語句??*表示?查詢所有字段
NSString*sql?=?[NSStringstringWithFormat:@"select?*?from?lanOuStudent?where?name?=?'%@'?and?age?=?'%ld'",name,age];
//??3.創(chuàng)建跟隨指針?保存sql語句
sqlite3_stmt*stmt?=nil;
//??4.執(zhí)行語句
//??-1?指sql語句長度?可以無限長
//??&stmt?跟隨指針?地址
//??第四個參數(shù)?截取sql語句未使用部分
intresult?=?sqlite3_prepare_v2(db,?sql.UTF8String,?-1,?&stmt,NULL);
//??5.判斷語句是否正確
if(result?==?SQLITE_OK)?{
NSLog(@"查詢語句正確");
//??6.進(jìn)行查詢值的綁定?也就是問號??的綁定
//??第二個參數(shù)?指第幾個問號?從1開始
sqlite3_bind_text(stmt,1,?name.UTF8String,?-1,NULL);
sqlite3_bind_int(stmt,2,?(int)age);//??注意轉(zhuǎn)換類型
//??7.進(jìn)行查詢
//??如果?查詢結(jié)果?返回?SQLITE_ROW?那么查詢正確?執(zhí)行循環(huán)
while(sqlite3_step(stmt)?==?SQLITE_ROW)?{
//??8.滿足條件?讀取數(shù)據(jù)
//??第二個參數(shù)?指的是?表中的列數(shù)?從0開始
charchar*name?=?(charchar*)sqlite3_column_text(stmt,1);
charchar*gender?=?(charchar*)sqlite3_column_text(stmt,2);
intage?=?sqlite3_column_int(stmt,3);
intnumber?=?sqlite3_column_int(stmt,0);
//??9.賦值學(xué)生對象?字符串需要轉(zhuǎn)換格式
LanOuStudent*student?=?[[LanOuStudentalloc]init];
student.name=?[NSStringstringWithUTF8String:name];
student.gender=?[NSStringstringWithUTF8String:gender];
student.age=?age;
student.number=?number;
//??10.釋放跟隨指針
sqlite3_finalize(stmt);
returnstudent;
}
}else{
NSLog(@"查詢語句錯誤");
}
//??查詢語句錯誤?也要釋放跟隨指針
sqlite3_finalize(stmt);
//??11.關(guān)閉數(shù)據(jù)庫
[selfcloseDB];
returnnil;
}
//??查詢?nèi)繉W(xué)生
-?(NSArray*)queryAllStudent
{
//??1.打開數(shù)據(jù)庫
db?=?[selfopenDB];
//??2.寫sql語句
NSString*sql?=@"select?*?from?lanOuStudent";
//??3.創(chuàng)建跟隨指針
sqlite3_stmt*stmt?=nil;
//??4.執(zhí)行語句
intresult?=?sqlite3_prepare_v2(db,?sql.UTF8String,?-1,?&stmt,NULL);
//??5.判斷語句是否正確
if(result?==?SQLITE_OK)?{
NSLog(@"查詢語句正確");
//??6.創(chuàng)建數(shù)組
NSMutableArray*array?=?[NSMutableArrayarray];
//??7.執(zhí)行查詢
while(sqlite3_step(stmt)?==?SQLITE_ROW)?{
//??8.滿足條件?讀取數(shù)據(jù)
charchar*name?=?(charchar*)sqlite3_column_text(stmt,1);
charchar*gender?=?(charchar*)sqlite3_column_text(stmt,2);
intage?=?sqlite3_column_int(stmt,3);
intnumber?=?sqlite3_column_int(stmt,0);
//??9.賦值學(xué)生對象?字符串需要轉(zhuǎn)換格式
LanOuStudent*student?=?[[LanOuStudentalloc]init];
student.name=?[NSStringstringWithUTF8String:name];
student.gender=?[NSStringstringWithUTF8String:gender];
student.age=?age;
student.number=?number;
//??10.裝進(jìn)數(shù)組
[arrayaddObject:student];
//??11.釋放學(xué)生對象
[studentrelease];
}
//??12.釋放跟隨指針
sqlite3_finalize(stmt);
returnarray;
}else{
NSLog(@"查詢語句錯誤");
}
//??查詢語句錯誤?也要釋放跟隨指針
sqlite3_finalize(stmt);
//??11.關(guān)閉數(shù)據(jù)庫
[selfcloseDB];
returnnil;
}
