sqlite 操作与封装 与生成.a静态库相匹配

如果想更深的理解SQLITE的封请学习FMDB，FMDB是对SQLITE的线程访问封装。

以下是本人以个人习惯简单的封装。

符合D开发的操作者习惯。

ocsqlite.h

1. //
2. //OCSqlite.m
3. //sqlite
4. //
5. //Createdbyfengshon12-12-3.
6. //Copyright(c)2012年sqlite_Lib.Allrightsreserved.
7. //
8. /*
9. 对ＳＱＬＩＴＥ的封装，主要设计是操作上的习惯。对于轻量级数据量可以。
10. 对于在数据量就需要注意内存的开销了。
11. */
13. #import<Foundation/Foundation.h>
14. #import<sqlite3.h>
16. enumfieldtype
17. {
18. ftInt,ftFloat,ftDouble,ftString,ftBlob,ftBool,ftDate,ftTime,ftDateTime,ftBinary
19. };
21. /*
22. 字段类
23. 作用：主要用于与数据库中的字段属性进行对应
24. 字段名，字段类型，字段值，字段索引号
25. */
26. @interfaceOCField:NSObject
27. {
28. NSString*fieldName;
29. idfieldValue;
30. enumfieldtypemtype;
31. intseq_column;
32. }
34. -(NSString*)toString;
35. -(NSInteger)toInteger;
36. -(NSDate*)toDate;
37. -(NSString*)toDateString;
38. -(NSString*)toTimeString;
39. -(NSString*)toDateTimeString;
40. -(NSNumber*)toNumber;
42. -(enumfieldtype)getFieldType;
44. @property(nonatomic)intseq_column;
46. @end
48. /*
49. 数据集类
50. 作用：
51. 类似于数据源的集合，带游标，可访问数据源中的数据
52. */
53. @interfaceOCDataset:NSObject
54. {
55. NSMutableArray*records;
56. NSIntegercursor;
57. }
59. -(void)clear;
60. -(NSInteger)count;
61. -(BOOL)next;
62. -(BOOL)first;
63. -(BOOL)move:(NSInteger)index;
64. -(OCField*)fieldbyname:(NSString*)fieldname;
65. -(OCField*)indexOffield:(NSInteger)index;
67. @end
69. /*
70. 简单的数据定义语言操作及数据库查询的封装
71. 未支持参数绑定，因此在处理blob上还需要扩展代码。
72. 后续完善
73. */
74. @interfaceOCSqlite:NSObject
75. {
76. sqlite3*db;
77. OCDataset*dataset;
78. }
80. -(id)init;
82. -(BOOL)ConnectToDB:(NSString*)dbfilepath;
83. -(void)DisconnectDB;
85. -(BOOL)startTranslation;
86. -(BOOL)commitTranslation;
87. -(BOOL)rollbackTranslation;
89. -(BOOL)excesql:(NSString*)ddlsql;
90. -(BOOL)query:(NSString*)qysql;
92. @property(nonatomic,readonly)OCDataset*dataset;
94. @end

1. //
2. //OCSqlite.m
3. //sqlite
4. //
5. //Createdbyfengshon12-12-3.
6. //Copyright(c)2012年sqlite_Lib.Allrightsreserved.
7. //
9. #import"OCSqlite.h"
12. @implementationOCField
14. @synthesizeseq_column;
16. -(id)init
17. {
18. self=[superinit];
19. if(self){
20. fieldValue=NULL;
21. returnself;
22. }
23. returnnil;
24. }
26. -(void)setfield:(NSString*)namewithvalue:(sqlite3_value*)valuewithtype:(NSString*)tp
27. {
28. fieldName=name;
30. NSString*result=@"";
32. if([tpisEqualToString:@"integer"]||[tpisEqualToString:@"smallint"])
33. {
34. mtype=ftInt;
35. fieldValue=(id)sqlite3_value_int(value);
36. return;
37. }
38. elseif([tpisEqualToString:@"boolean"])
39. {
40. mtype=ftBool;
41. }
42. elseif([tpisEqualToString:@"float"])
43. {
44. mtype=ftFloat;
46. }
47. elseif([tpisEqualToString:@"double"]||[tpisEqualToString:@"real"])
48. {
49. mtype=ftDouble;
50. }
51. elseif([tpisEqualToString:@"text"]||[tpisEqualToString:@"varchar"])
52. {
53. mtype=ftString;
54. }
55. elseif([tpisEqualToString:@"blob"])
56. {
57. mtype=ftBlob;
58. return;
59. }
60. elseif([tpisEqualToString:@"date"])
61. {
62. mtype=ftDate;
63. }
64. elseif([tpisEqualToString:@"time"])
65. {
66. mtype=ftTime;
68. }
69. elseif([tpisEqualToString:@"timestamp"])
70. {
71. mtype=ftDateTime;
73. }
74. elseif([tpisEqualToString:@"binary"])
75. {
76. mtype=ftBinary;
77. return;
78. }
80. char*floatstr=(char*)sqlite3_value_text(value);
81. if(floatstr){
82. result=[NSStringstringWithCString:floatstrencoding:NSUTF8StringEncoding];
83. }
84. fieldValue=result;
85. }
87. -(NSString*)toString
88. {
89. return(NSString*)fieldValue;
90. }
92. -(NSInteger)toInteger
93. {
94. return(int)fieldValue;
95. }
97. -(NSNumber*)toNumber
98. {
99. switch(mtype){
100. caseftFloat:
101. return[NSNumbernumberWithFloat:[(NSString*)fieldValuefloatValue]];
102. break;
104. caseftDouble:
105. return[NSNumbernumberWithDouble:[(NSString*)fieldValuedoubleValue]];
106. break;
108. caseftBool:
109. return[NSNumbernumberWithBool:[(NSString*)fieldValueboolValue]];
110. break;
111. default:
112. return[NSNumbernumberWithInt:[(NSString*)fieldValueintValue]];
113. break;
114. }
116. }
118. -(NSString*)toDateString
119. {
120. NSDateFormatter*fmt=[[[NSDateFormatteralloc]init]autorelease];
122. [fmtsetDateFormat:@"yyyy-mm-dd"];
123. NSDate*date=[fmtdateFromString:fieldValue];
125. NSString*datestr=[fmtstringFromDate:date];
127. return(datestr==nil)?@"":datestr;
128. }
130. -(NSString*)toTimeString
131. {
132. NSDateFormatter*fmt=[[[NSDateFormatteralloc]init]autorelease];
134. [fmtsetDateFormat:@"HH:mm:ss"];//H为0－23，h为1-12
136. NSDate*time=[fmtdateFromString:fieldValue];
138. NSString*timestr=[fmtstringFromDate:time];
140. return(timestr==nil)?@"":timestr;
141. }
143. -(NSString*)toDateTimeString;
144. {
145. NSDateFormatter*fmt=[[[NSDateFormatteralloc]init]autorelease];
147. [fmtsetDateFormat:@"yyyy-MM-ddHH:mm:ss"];//H为0－23，h为1-12
149. NSDate*datetime=[fmtdateFromString:fieldValue];
151. NSString*dtimestr=[fmtstringFromDate:datetime];
153. return(dtimestr==nil)?@"":dtimestr;
154. }
156. -(NSDate*)toDate
157. {
159. NSDateFormatter*fmt=[[NSDateFormatteralloc]init];
161. [fmtsetDateFormat:@"yyyy-MM-ddHH:mm:ss"];
163. NSDate*date=[fmtdateFromString:fieldValue];
165. returndate;
167. }
170. -(enumfieldtype)getFieldType
171. {
172. returnmtype;
173. }
175. @end
177. @implementationOCDataset
179. -(id)init
180. {
181. self=[superinit];
182. if(self){
183. cursor=-1;
184. records=[[NSMutableArrayalloc]init];
185. returnself;
186. }
187. returnnil;
188. }
190. -(void)dealloc
191. {
192. [recordsrelease];
193. [superdealloc];
194. }
196. -(void)reset
197. {
198. cursor=0;
199. }
201. -(void)fillData:(sqlite3_stmt*)cmd
202. {
203. NSIntegercolcount=sqlite3_column_count(cmd);
205. NSMutableDictionary*fields=[[[NSMutableDictionaryalloc]init]autorelease];
206. for(NSIntegeri=0;i<colcount;i++){
207. char*fieldname=(char*)sqlite3_column_name(cmd,i);
208. NSString*strfieldname=[NSStringstringWithCString:fieldnameencoding:NSUTF8StringEncoding];
209. sqlite3_value*mvalue=sqlite3_column_value(cmd,i);
211. char*ity=(char*)sqlite3_column_decltype(cmd,i);
213. NSString*stype=[NSStringstringWithCString:ityencoding:NSUTF8StringEncoding];
214. //inttype=sqlite3_column_type(cmd,i);
216. OCField*field=[[OCFieldalloc]init];
218. [fieldsetfield:strfieldnamewithvalue:mvaluewithtype:stype];
219. field.seq_column=i;
222. [fieldssetObject:fieldforKey:strfieldname];
223. }
224. [recordsaddObject:fields];
225. }
227. -(void)clear
228. {
229. [recordsremoveAllObjects];
230. cursor=-1;
231. }
233. -(NSInteger)count
234. {
235. return[recordscount];
236. }
238. -(OCField*)fieldbyname:(NSString*)fieldname
239. {
240. NSMutableDictionary*dic=[recordsobjectAtIndex:cursor];
242. return(OCField*)[dicobjectForKey:fieldname];
243. }
245. -(BOOL)next
246. {
247. ++cursor;
248. intrcount=[recordscount];
249. if(cursor<=rcount){
250. returnYES;
251. }
252. else
253. {
254. cursor=rcount+1;
255. returnNO;
256. }
257. }
259. -(BOOL)first
260. {
261. if([recordscount]>0){
262. cursor=0;
263. returnYES;
264. }
265. else
266. {
267. cursor=-1;
268. returnNO;
269. }
270. }
272. -(BOOL)move:(NSInteger)index
273. {
274. intidx=index-1;
275. if(-1<idx<[recordscount]){
276. cursor=idx;
277. returnYES;
278. }
279. returnNO;
280. }
282. -(OCField*)indexOffield:(NSInteger)index
283. {
284. OCField*ret=nil;
285. intct=0;
286. NSMutableDictionary*dic=[recordsobjectAtIndex:cursor];
287. for(NSString*keyindic)
288. {
289. ret=[dicobjectForKey:key];
291. if(index==ct)
292. break;
293. else
294. ct++;
296. }
297. returnret;
298. }
300. @end
304. @implementationOCSqlite
306. @synthesizedataset;
308. -(id)init
309. {
310. self=[superinit];
311. if(self){
313. dataset=[[OCDatasetalloc]init];
314. returnself;
315. }
316. returnnil;
317. }
319. -(void)dealloc
320. {
321. [datasetrelease];
322. sqlite3_close(db);
323. [superdealloc];
324. }
326. -(BOOL)ConnectToDB:(NSString*)dbfilepath
327. {
328. BOOLsuccessful;
329. successful=sqlite3_open([dbfilepathUTF8String],&db)==SQLITE_OK;
331. if(!successful){
332. sqlite3_close(db);
333. returnNO;
334. }
336. returnYES;
337. }
339. -(void)DisconnectDB
340. {
341. sqlite3_close(db);
342. }
344. -(BOOL)excesql:(NSString*)ddlsql
345. {
346. char*err;
347. if(sqlite3_exec(db,[ddlsqlUTF8String],NULL,&err)!=SQLITE_OK)
348. {
349. returnNO;
350. }
351. returnYES;
352. }
354. -(BOOL)query:(NSString*)qysql
355. {
356. sqlite3_stmt*cmd;
358. if(sqlite3_prepare_v2(db,[qysqlUTF8String],-1,&cmd,nil)!=SQLITE_OK)
359. {
360. returnNO;
361. }
363. [datasetclear];
365. while(sqlite3_step(cmd)==SQLITE_ROW)
366. {
367. [datasetfillData:cmd];
368. }
370. [datasetreset];
372. sqlite3_finalize(cmd);
374. returnYES;
375. }
378. -(BOOL)startTranslation
379. {
380. char*err;
381. if(sqlite3_exec(db,"begintransaction",&err)!=SQLITE_OK)
382. {
383. returnNO;
384. }
385. returnYES;
386. }
388. -(BOOL)commitTranslation
389. {
390. char*err;
391. if(sqlite3_exec(db,"committransaction",&err)!=SQLITE_OK)
392. {
393. returnNO;
394. }
395. returnYES;
396. }
398. -(BOOL)rollbackTranslation
399. {
400. char*err;
401. if(sqlite3_exec(db,"rollbacktransaction",&err)!=SQLITE_OK)
402. {
403. returnNO;
404. }
405. returnYES;
406. }
409. @end

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!