iPhone 数据库（sqlite3）的用法操作

首先你在用之前要在项目中加入libsqlite3.dylib

1、定义模型

01	#import <Foundation/Foundation.h>

02	#import "sqlite3.h"

03	@class NotePad;

04	@class NoteDb;

05	@interface NoteSqlite : NSObject{

06	sqlite3 *database;

07	sqlite3_stmt *statement;

08	char *errorMsg;

09

}

10

//打开数据库

11	-(BOOL)open;

12

//创建青

13	-(BOOL)create;

14

15	//增加、删除、修改、查询

16	-(BOOL)insert:(NotePad*)aNote;

17	-(BOOL)deleteALLNote;

18	-(BOOL)deleteaNote:(NotePad*)aNote;

19	-(BOOL)update:(NotePad*)aNote;

20

21	-(NoteDb*)selecteAll;

22	-(NoteDb*)selectNotes:(NotePad*)aNote;

23

24

@end

2、实现方法

查看源码

打印 ?

001	#import "NoteSqlite.h"

002	#import "NotePad.h"

003	#import "NoteDb.h"

004	@implementation NoteSqlite

005

006	-(id)init{

007	self=[super init];

008	return self;

009

}

010

//打开数据库

011	-(BOOL)open{

012	NSArray *paths= NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

013	NSString *documentsDirectory = [paths objectAtIndex:0];

014	NSString *path = [documentsDirectory stringByAppendingPathComponent:@"noteList.db"];

015	NSFileManager *fileManager = [NSFileManager defaultManager];

016	BOOL find = [fileManager fileExistsAtPath:path];

017	//判断文件是否存在

018	if (find) {

019	NSLog(@"数据库文件已经存在");

020	//打开数据库、返回操作是否正确

021	if(sqlite3_open([path UTF8String], &database) == SQLITE_OK) {

022	NSLog(@"打开成功数据库");

023

}

024	return YES;

025

}else{

026	if(sqlite3_open([path UTF8String], &database) == SQLITE_OK) {

027	//调用createMusicList创建数据库和表

028	[self create];

029	return YES;

030

} else {

031	sqlite3_close(database);

032	NSLog(@"Error: open database file.");

033	return NO;

034

}

035	return NO;

036

}

037

038

}

039

//创建表

040	-(BOOL)create{

041

//创建表语句

042	const char *createSql="create table if not exists note (id integer primary key autoincrement,theme text,information text,ndate text,priority integer)";

043

//创建表是否成功

044	if (sqlite3_exec(database, createSql, NULL, NULL, &errorMsg)==SQLITE_OK) {

045	NSLog(@"create ok.");

046	return YES;

047

}else{

048

//打印出错信息

049	NSLog(@"error: %s",errorMsg);

050	sqlite3_free(errorMsg);

051

}

052	return NO;

053

054

}

055

056	//增加、删除、修改、查询

057	-(BOOL)insert:(NotePad*)aNote{

058

//向表中插入记录

059	//定义一个sql语句

060	NSString *insertStatementNS = [NSString stringWithFormat:

061	@"insert into \"note\"\

062	(theme, information, ndate,priority)\

063	values (\"%@\", \"%@\", \"%@\",%d)",

064	aNote.theme,aNote.information,[NSString stringWithFormat:@"%@",aNote.ndate],aNote.priority

065

];

066	//将定义的NSString的sql语句，转换成UTF8的c风格的字符串

067	const char *insertSql = [insertStatementNS UTF8String];

068

//执行插入语句

069	if (sqlite3_exec(database, insertSql, NULL, NULL, &errorMsg)==SQLITE_OK) {

070	NSLog(@"insert ok.");

071	return YES;

072

}else{

073	NSLog(@"error: %s",errorMsg);

074	sqlite3_free(errorMsg);

075

}

076	return NO;

077

078

}

079	-(BOOL)deleteALLNote{

080	//删除所有数据，条件为1>0永真

081	const char *deleteAllSql="delete from note where 1>0";

082

//执行删除语句

083	if(sqlite3_exec(database, deleteAllSql, NULL, NULL, &errorMsg)==SQLITE_OK){

084	NSLog(@"删除所有数据成功");

085

}

086	return YES;

087

}

088	-(BOOL)deleteaNote:(NotePad*)aNote{

089

//删除某条数据

090	NSString *deleteString=[NSString stringWithFormat:@"delete from note where id=%d",aNote.noteId];

091	//转成utf-8的c的风格

092	const char *deleteSql=[deleteString UTF8String];

093

//执行删除语句

094	if(sqlite3_exec(database, deleteSql, NULL, NULL, &errorMsg)==SQLITE_OK){

095	NSLog(@"删除成功");

096

}

097	return YES;

098

}

099	-(BOOL)update:(NotePad*)aNote{

100

//更新语句

101	NSString *updateString=[NSString stringWithFormat:@"update note set theme='%@', information='%@', ndate='%@',priority=%d where id=%d",aNote.theme,aNote.information,aNote.ndate,aNote.priority,aNote.noteId];

102	// NSLog(@"%@",aNote);

103	const char *updateSql=[updateString UTF8String];

104

//执行更新语句

105	if(sqlite3_exec(database, updateSql, NULL, NULL, &errorMsg)==SQLITE_OK){

106	NSLog(@"更新成功");

107

}

108	return YES;

109

}

110

111	-(NoteDb*)selecteAll{

112	NoteDb *noteDb=[[[NoteDb alloc]init]autorelease];

113

//查询所有语句

114	const char *selectAllSql="select * from note";

115

//执行查询

116	if (sqlite3_prepare_v2(database, selectAllSql, -1, &statement, nil)==SQLITE_OK) {

117	NSLog(@"select ok.");

118	//如果查询有语句就执行step来添加数据

119	while (sqlite3_step(statement)==SQLITE_ROW) {

120	NotePad *note=[[NotePad alloc]init];

121

122	int noteid=sqlite3_column_int(statement, 0);

123	NSMutableString *theme=[NSMutableString stringWithCString:(char*)sqlite3_column_text(statement, 1) encoding:NSUTF8StringEncoding];

124	NSMutableString *information=[NSMutableString stringWithCString:(char*)sqlite3_column_text(statement, 2) encoding:NSUTF8StringEncoding];

125	NSString *ndateString=[NSString stringWithCString:(char*)sqlite3_column_text(statement, 3) encoding:NSUTF8StringEncoding];

126	NSDateFormatter* formater = [[NSDateFormatter alloc] init];

127	[formater setDateFormat:@"yyyy-MM-dd HH:mm:ss"];

128

129	NSDate *ndate=[formater dateFromString:[ndateString substringToIndex:[ndateString length]-5]];

130	// NSLog(@"%@",[ndateString substringToIndex:[ndateString length]-5]);

131	[formater release];

132	int proriory=sqlite3_column_int(statement, 4);

133

134	note.noteId=noteid;

135	note.theme=theme;

136	note.information=information;

137	note.ndate=ndate;

138	note.priority=proriory;

139	[noteDb addNote:note];

140	[note release];

141

142

}

143	return noteDb;

144

}

145	return noteDb;

146

}

147	-(NoteDb*)selectNotes:(NotePad*)aNote{

148	NoteDb *noteDb=[[[NoteDb alloc]init]autorelease];

149	NSString *selectNSSql=[NSString stringWithFormat:@"select * from note where id=%i",aNote.noteId];

150

//查询所有语句

151	const char *selectSql=[selectNSSql UTF8String];

152

//执行查询

153	if (sqlite3_prepare_v2(database, selectSql, -1, &statement, nil)==SQLITE_OK) {

154	NSLog(@"select ok.");

155	//如果查询有语句就执行step来添加数据

156	while (sqlite3_step(statement)==SQLITE_ROW) {

157	NotePad *note=[[NotePad alloc]init];

158

159	int noteid=sqlite3_column_int(statement, 0);

160	NSMutableString *theme=[NSMutableString stringWithCString:(char*)sqlite3_column_text(statement, 1) encoding:NSUTF8StringEncoding];

161	NSMutableString *information=[NSMutableString stringWithCString:(char*)sqlite3_column_text(statement, 2) encoding:NSUTF8StringEncoding];

162	NSString *ndateString=[NSString stringWithCString:(char*)sqlite3_column_text(statement, 3) encoding:NSUTF8StringEncoding];

163	NSDateFormatter* formater = [[NSDateFormatter alloc] init];

164	[formater setDateFormat:@"yyyy-MM-dd HH:mm:ss"];

165

166	NSDate *ndate=[formater dateFromString:[ndateString substringToIndex:[ndateString length]-5]];

167	// NSLog(@"%@",[ndateString substringToIndex:[ndateString length]-5]);

168	[formater release];

169	int proriory=sqlite3_column_int(statement, 4);

170

171	note.noteId=noteid;

172	note.theme=theme;

173	note.information=information;

174	note.ndate=ndate;

175	note.priority=proriory;

176	[noteDb addNote:note];

177	[note release];

178

179

}

180	return noteDb;

181

}

182	return noteDb;

183

}

184

@end

转自：http://blog.csdn.net/rhljiayou/article/details/7615743