做iOS开发以来,一直运用的第三方数据库的增删改查,项目多次出现读取数据出现问题,自己也不知道如何查起,后来经过查资料,自己写了几个方法来实现数据的增删改查功能:
首先在TARGETS---》》Build phases里面添加数据库所关联的库文件libsqlite3.tbd
添加完以后,在控制器上添加
#import <sqlite3.h> 且增加属性
{
sqlite3 *db;
}
在.m文件中实现增删改查的方法即可
#import "ViewController.h"
#define DBNAME @"personinfo.sqlite"
#define NAME @"name"
#define AGE @"age"
#define ADDRESS @"address"
#define TABLENAME @"PERSONINFO"
@interface ViewController ()
@end
@implementation ViewController
- (void)viewDidLoad {
[super viewDidLoad];
// Do any additional setup after loading the view, typically from a nib.
[self OpenDataBase];
[self createTable];
[self InsertData];
[self SelectData];
[self deleteData];
[self updateData];
}
#pragma mark-----------打开数据库
-(void)OpenDataBase
{
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(@"数据库打开失败");
}
}
#pragma mark----------创建数据表PERSONINFO的语句
-(void)createTable
{
NSString *sqlCreateTable = @"CREATE TABLE IF NOT EXISTS PERSONINFO (ID INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER, address TEXT)";
[self execSql:sqlCreateTable];
}
#pragma mark------------创建一个独立的执行sql语句的方法
-(void)execSql:(NSString *)sql
{
char *err;
if (sqlite3_exec(db, [sql UTF8String], NULL, NULL, &err) != SQLITE_OK) {
sqlite3_close(db);
NSLog(@"数据库操作数据失败!");
}
}
#pragma mark------------增加数据
-(void)InsertData
{
NSString *sql1 = [NSString stringWithFormat:
@"INSERT INTO '%@' ('%@', '%@', '%@') VALUES ('%@', '%@', '%@')",
TABLENAME, NAME, AGE, ADDRESS, @"张三", @"18", @"北京"];
NSString *sql2 = [NSString stringWithFormat:
@"INSERT INTO '%@' ('%@', '%@', '%@') VALUES ('%@', '%@', '%@')",
TABLENAME, NAME, AGE, ADDRESS, @"老四", @"19", @"深圳"];
[self execSql:sql1];
[self execSql:sql2];
}
#pragma mark----------------删除数据
-(void)deleteData
{
sqlite3_stmt *statement;
NSString *sqlStr = [NSString stringWithFormat:@"DELETE FROM PERSONINFO WHERE age = %d", 19];
// NSString *sqlStr = [NSString stringWithFormat:@"DELETE FROM PERSONINFO"];
int result = sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &statement, NULL);
if (result == SQLITE_OK) {
if (sqlite3_step(statement) == SQLITE_ROW) {//觉的应加一个判断, 若有这一行则删除
if (sqlite3_step(statement) == SQLITE_DONE) {
sqlite3_finalize(statement);
sqlite3_close(db);
}
}
}
sqlite3_finalize(statement);
sqlite3_close(db);
NSLog(@"-------删除后---------");
[self SelectData];
}
#pragma mark----------修改数据
-(void)updateData
{
sqlite3_stmt *statement;
NSString *sqlStr = [NSString stringWithFormat:@"update PERSONINFO set name = '%@' where age = %d", @"王五", 18];
int result = sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &statement, NULL);
if (result == SQLITE_OK) {
if (sqlite3_step(statement) == SQLITE_ROW) {//觉的应加一个判断, 若有这一行则修改
if (sqlite3_step(statement) == SQLITE_DONE) {
sqlite3_finalize(statement);
sqlite3_close(db);
}
}
}
sqlite3_finalize(statement);
sqlite3_close(db);
NSLog(@"------修改后----------");
[self SelectData];
}
#pragma mark-----------------查询数据
-(void)SelectData
{
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(@"name:%@ age:%d address:%@",nsNameStr,age, nsAddressStr);
}
}
sqlite3_close(db);
}
2016-07-12 13:55:54.130 SQLDemo[7313:144445] name:张三 age:18 address:北京
2016-07-12 13:55:54.130 SQLDemo[7313:144445] name:老四 age:19 address:深圳
2016-07-12 13:55:54.131 SQLDemo[7313:144445] -------删除后---------
2016-07-12 13:55:54.132 SQLDemo[7313:144445] name:张三 age:18 address:北京
2016-07-12 13:55:54.133 SQLDemo[7313:144445] ------修改后----------
2016-07-12 13:55:54.133 SQLDemo[7313:144445] name:王五 age:18 address:北京