iOS开发中的SQLite知识总结
更详细的数据库SQlite介绍: http://mobile.51cto.com/iphone-388476.htm
http://www.w3cschool.cc/sqlite/sqlite-group-by.html
![\](http://www.2cto.com/uploadfile/Collfiles/20140828/2014082808465296.png)
3.BaseDB.m
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
|
//
// BaseDB.m
// SqliteDemo
//
// Created by 赵超 on 14-8-26.
// Copyright (c) 2014年 赵超. All rights reserved.
//
#
import
"BaseDB.h"
@implementation
BaseDB
/*
* 获取沙盒目录
* name:追加的目录aa
*
*/
-(NSString*) DataBaseName:(NSString *) name {
NSString *fileName=[NSHomeDirectory() stringByAppendingFormat:@
"/Documents/%@"
,name];
return
fileName;
}
/**
* 选择数据
* sql:查询的SQL语句
* params:查询SQL语句中的参数
* dataName:查询数据库名称
*/
-(NSMutableArray*) selectSql:(NSString *)sql parmas:(NSArray *)params dataBaseName:(NSString *)dataName{
sqlite3 *sqlite=nil;
sqlite3_stmt *stmt=nil;
//打开数据库
NSString *fileName=[self DataBaseName:dataName];
int
result= sqlite3_open([fileName UTF8String], &sqlite);
if
(result!=SQLITE_OK) {
NSLog(@
"打开失败"
);
return
nil;
}
const
char
* sqlCh=[sql UTF8String];
//编译SQL语句
sqlite3_prepare_v2(sqlite, sqlCh, -
1
, &stmt, NULL);
//绑定参数
for
(
int
i=
0
; i<params.count; i++)=
""
{=
""
nsstring=
""
*param=
"[params"
objectatindex:i];=
""
sqlite3_bind_text(stmt,=
""
i+
1
,=
""
[param=
""
utf8string],=
""
-
1
,=
""
null
);=
""
}=
""
执行查询语句=
""
result=
"sqlite3_step(stmt);"
nsmutablearray=
""
*resultdata=
"[NSMutableArray"
array];=
""
遍历结果=
""
while
=
""
(result=
"=SQLITE_ROW)"
nsmutabledictionary=
""
*resultrow=
"[NSMutableDictionary"
dictionary];=
""
获取字段个数=
""
int
=
""
col_count=
"sqlite3_column_count(stmt);"
for
=
""
(
int
=
""
i=
"0;"
i<col_count;=
""
获取字段名称=
""
const
=
""
char
*columname=
"sqlite3_column_name(stmt,i);"
获取字段值=
""
char
*=
""
columvalue=
"(char*)"
sqlite3_column_text(stmt,=
""
i);=
""
*columkeystr=
"[NSString"
stringwithcstring:columname=
""
encoding:nsutf8stringencoding];=
""
*columvaluestr=
"[NSString"
stringwithcstring:columvalue=
""
[resultrow=
""
setobject:columvaluestr=
""
forkey:columkeystr];=
""
[resultdata=
""
addobject:resultrow];=
""
关闭数据库句柄=
""
sqlite3_finalize(stmt);=
""
关闭数据库=
""
sqlite3_close(sqlite);=
""
nslog(@
"查询完!"
);=
""
return
=
""
resultdata;=
""
**=
""
*=
""
执行sql语句,主要完成增加、修改、删除=
""
sql:执行的sql语句=
""
params:sql语句中的参数=
""
dataname:数据库名称=
""
-(bool)=
""
execsql:(nsstring=
""
*)sql=
""
parmas:(nsarray=
""
*)params=
""
databasename:(nsstring=
""
*)dataname{=
""
sqlite3=
""
*sqlite=
"nil;"
sqlite3_stmt=
""
*stmt=
"nil;"
打开数据库=
""
*filename=
"[self"
databasename:dataname];=
""
&sqlite);=
""
if
=
""
(result!=
"SQLITE_OK)"
nslog(@
"打开失败"
);=
""
no;=
""
sqlch=
"[sql"
utf8string];=
""
编译sql语句=
""
sqlite3_prepare_v2(sqlite,=
""
sqlch,=
""
&stmt,=
""
绑定参数=
""
i<params.count;=
""
*parm=
"[params"
[parm=
""
执行sql=
""
||=
""
nslog(@
"执行sql语句失败"
);=
""
nslog(@
"执行成功!"
);=
""
yes;=
""
创建一个表=
""
-(
void
)createtable:(nsstring=
""
}
else
{=
""
error;=
""
null
,=
""
&error);=
""
nslog(@
"创建失败"
);=
""
nslog(@
"%s"
,error);=
""
;=
""
nslog(@
"创建成功"
);=
""
@end
=
""
<=
""
pre=
""
><br>
<h3>
4
.调用格式</h3>
<pre
class
=
"brush:java;"
>
BaseDB *db=[[BaseDB alloc] init];
//创建表
NSString *dbCreate=@
"create table zhaochao( username text primary key,userPasswd test)"
;
NSString *dbName=@
"zhaochao.sqlite"
;
// [db createTable:dbCreate dataBaseName:dbName];
//添加数据
NSString *insertTable=@
"insert into zhaochao (username,userPasswd) values (?,?)"
;
NSArray *insertParmas=@[@
"acasdfaa"
,@
"bb"
];
// [db execSql:insertTable parmas:insertParmas dataBaseName:@"zhaochao.sqlite"];
//修改数据
NSString *updateTable=@
"update zhaochao set username=? where username=?"
;
NSArray *updateParams=@[@
"admin"
,@
"zhaochao"
];
// [db execSql:updateTable parmas:updateParams dataBaseName:@"zhaochao.sqlite"];
//删除数据
NSString *deleteTable=@
"delete from zhaochao where username=?"
;
NSArray *deleteParams=@[@
"aa"
];
// [db execSql:deleteTable parmas:deleteParams dataBaseName:@"zhaochao.sqlite"];
//查询数据
NSString *selectTable=@
"select username,userPasswd from zhaochao where userPasswd=?"
;
NSString *selectParam=@[@
"bb"
];
NSArray *result=[db selectSql:selectTable parmas:selectParam dataBaseName:@
"zhaochao.sqlite"
];
for
(
int
i=
0
; i<result.count; i++)=
""
{=
""
nsmutabledictionary=
""
*arr=
"[result"
objectatindex:i];=
""
nslog(@
"%@"
,arr);=
""
}=
""
<=
""
pre=
""
><br>
<br>
<br> </result.count;></pre></params.count;>
|