sqlite操作与封装的示例分析

小编给大家分享一下sqlite操作与封装的示例分析，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

ocsqlite.h

[plain]
view plaincopy

1. //

2. // OCSqlite.m

3. // sqlite

4. //

5. // Created by fengsh on 12-12-3.

6. // Copyright (c) 2012年 sqlite_Lib. All rights reserved.

7. //

8. /*

9. 对ＳＱＬＩＴＥ的封装，主要设计是操作上的习惯。对于轻量级数据量可以。

10. 对于在数据量就需要注意内存的开销了。

11. */

12. 
    

13. #import

14. #import

15. 
    

16. enum fieldtype

17. {

18. ftInt,ftFloat,ftDouble,ftString,ftBlob,ftBool,ftDate,ftTime,ftDateTime,ftBinary

19. };

20. 
    

21. /*

22. 字段类

23. 作用：主要用于与数据库中的字段属性进行对应

24. 字段名，字段类型，字段值，字段索引号

25. */

26. @interface OCField : NSObject

27. {

28. NSString* fieldName;

29. id fieldValue;

30. enum fieldtype mtype;

31. int seq_column;

32. }

33. 
    

34. -(NSString*)toString;

35. -(NSInteger)toInteger;

36. -(NSDate*)toDate;

37. -(NSString*)toDateString;

38. -(NSString*)toTimeString;

39. -(NSString*)toDateTimeString;

40. -(NSNumber*)toNumber;

41. 
    

42. -(enum fieldtype)getFieldType;

43. 
    

44. @property (nonatomic) int seq_column;

45. 
    

46. @end

47. 
    

48. /*

49. 数据集类

50. 作用：

51. 类似于数据源的集合，带游标，可访问数据源中的数据

52. */

53. @interface OCDataset : NSObject

54. {

55. NSMutableArray* records;

56. NSInteger cursor;

57. }

58. 
    

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;

66. 
    

67. @end

68. 
    

69. /*

70. 简单的数据定义语言操作及数据库查询的封装

71. 未支持参数绑定，因此在处理blob上还需要扩展代码。

72. 后续完善

73. */

74. @interface OCSqlite : NSObject

75. {

76. sqlite3* db;

77. OCDataset* dataset;

78. }

79. 
    

80. -(id)init;

81. 
    

82. -(BOOL)ConnectToDB:(NSString*) dbfilepath;

83. -(void)DisconnectDB;

84. 
    

85. -(BOOL)startTranslation;

86. -(BOOL)commitTranslation;

87. -(BOOL)rollbackTranslation;

88. 
    

89. -(BOOL)excesql:(NSString*) ddlsql;

90. -(BOOL)query:(NSString*) qysql;

91. 
    

92. @property (nonatomic,readonly) OCDataset* dataset;

93. 
    

94. @end

ocsqlite.m

[plain]
view plaincopy

1. //

2. // OCSqlite.m

3. // sqlite

4. //

5. // Created by fengsh on 12-12-3.

6. // Copyright (c) 2012年 sqlite_Lib. All rights reserved.

7. //

8. 
   

9. #import "OCSqlite.h"

10. 
    

11. 
    

12. @implementation OCField

13. 
    

14. @synthesize seq_column;

15. 
    

16. -(id)init

17. {

18. self = [super init];

19. if (self) {

20. fieldValue = NULL;

21. return self;

22. }

23. return nil;

24. }

25. 
    

26. -(void)setfield:(NSString*) name withvalue:(sqlite3_value*) value withtype:(NSString*) tp

27. {

28. fieldName = name;

29. 
    

30. NSString* result = @"";

31. 
    

32. if ([tp isEqualToString:@"integer"]||[tp isEqualToString:@"smallint"])

33. {

34. mtype = ftInt;

35. fieldValue = (id)sqlite3_value_int(value);

36. return;

37. }

38. else if ([tp isEqualToString:@"boolean"])

39. {

40. mtype = ftBool;

41. }

42. else if ([tp isEqualToString:@"float"])

43. {

44. mtype = ftFloat;

45. 
    

46. }

47. else if ([tp isEqualToString:@"double"]||[tp isEqualToString:@"real"])

48. {

49. mtype = ftDouble;

50. }

51. else if ([tp isEqualToString:@"text"]||[tp isEqualToString:@"varchar"])

52. {

53. mtype = ftString;

54. }

55. else if ([tp isEqualToString:@"blob"])

56. {

57. mtype = ftBlob;

58. return;

59. }

60. else if ([tp isEqualToString:@"date"])

61. {

62. mtype = ftDate;

63. }

64. else if ([tp isEqualToString:@"time"])

65. {

66. mtype = ftTime;

67. 
    

68. }

69. else if ([tp isEqualToString:@"timestamp"])

70. {

71. mtype = ftDateTime;

72. 
    

73. }

74. else if ([tp isEqualToString:@"binary"])

75. {

76. mtype = ftBinary;

77. return;

78. }

79. 
    

80. char* floatstr = (char*)sqlite3_value_text(value);

81. if (floatstr) {

82. result = [NSString stringWithCString:floatstr encoding:NSUTF8StringEncoding];

83. }

84. fieldValue = result;

85. }

86. 
    

87. -(NSString*)toString

88. {

89. return (NSString*)fieldValue;

90. }

91. 
    

92. -(NSInteger)toInteger

93. {

94. return (int)fieldValue;

95. }

96. 
    

97. -(NSNumber*)toNumber

98. {

99. switch (mtype) {

100. case ftFloat:

101. return [NSNumber numberWithFloat:[(NSString*)fieldValue floatValue]];

102. break;

103. 
     

104. case ftDouble:

105. return [NSNumber numberWithDouble:[(NSString*)fieldValue doubleValue]];

106. break;

107. 
     

108. case ftBool:

109. return [NSNumber numberWithBool:[(NSString*)fieldValue boolValue]];

110. break;

111. default:

112. return [NSNumber numberWithInt:[(NSString*)fieldValue intValue]];

113. break;

114. }

115. 
     

116. }

117. 
     

118. -(NSString*)toDateString

119. {

120. NSDateFormatter* fmt = [[[NSDateFormatter alloc]init]autorelease];

121. 
     

122. [fmt setDateFormat:@"yyyy-mm-dd"];

123. NSDate* date = [fmt dateFromString:fieldValue];

124. 
     

125. NSString* datestr = [fmt stringFromDate:date];

126. 
     

127. return (datestr==nil) ? @"":datestr;

128. }

129. 
     

130. -(NSString*)toTimeString

131. {

132. NSDateFormatter* fmt = [[[NSDateFormatter alloc]init]autorelease];

133. 
     

134. [fmt setDateFormat:@"HH:mm:ss"];//H为0－23，h为1-12

135. 
     

136. NSDate* time = [fmt dateFromString:fieldValue];

137. 
     

138. NSString* timestr = [fmt stringFromDate:time];

139. 
     

140. return (timestr==nil) ? @"":timestr;

141. }

142. 
     

143. -(NSString*)toDateTimeString;

144. {

145. NSDateFormatter* fmt = [[[NSDateFormatter alloc]init]autorelease];

146. 
     

147. [fmt setDateFormat:@"yyyy-MM-dd HH:mm:ss"];//H为0－23，h为1-12

148. 
     

149. NSDate* datetime = [fmt dateFromString:fieldValue];

150. 
     

151. NSString* dtimestr = [fmt stringFromDate:datetime];

152. 
     

153. return (dtimestr==nil) ? @"":dtimestr;

154. }

155. 
     

156. -(NSDate*)toDate

157. {

158. 
     

159. NSDateFormatter* fmt = [[NSDateFormatter alloc]init];

160. 
     

161. [fmt setDateFormat:@"yyyy-MM-dd HH:mm:ss"];

162. 
     

163. NSDate* date = [fmt dateFromString:fieldValue];

164. 
     

165. return date;

166. 
     

167. }

168. 
     

169. 
     

170. -(enum fieldtype)getFieldType

171. {

172. return mtype;

173. }

174. 
     

175. @end

176. 
     

177. @implementation OCDataset

178. 
     

179. -(id)init

180. {

181. self = [super init];

182. if (self) {

183. cursor = -1;

184. records = [[NSMutableArray alloc]init];

185. return self;

186. }

187. return nil;

188. }

189. 
     

190. -(void)dealloc

191. {

192. [records release];

193. [super dealloc];

194. }

195. 
     

196. -(void)reset

197. {

198. cursor = 0;

199. }

200. 
     

201. -(void)fillData:(sqlite3_stmt*) cmd

202. {

203. NSInteger colcount = sqlite3_column_count(cmd);

204. 
     

205. NSMutableDictionary* fields = [[[NSMutableDictionary alloc]init]autorelease];

206. for (NSInteger i = 0; i < colcount; i++) {

207. char* fieldname = (char*)sqlite3_column_name(cmd, i);

208. NSString* strfieldname = [NSString stringWithCString:fieldname encoding:NSUTF8StringEncoding];

209. sqlite3_value* mvalue = sqlite3_column_value(cmd, i);

210. 
     

211. char* ity = (char*)sqlite3_column_decltype(cmd, i);

212. 
     

213. NSString* stype = [NSString stringWithCString:ity encoding:NSUTF8StringEncoding];

214. //int type = sqlite3_column_type(cmd, i);

215. 
     

216. OCField* field = [[OCField alloc]init];

217. 
     

218. [field setfield:strfieldname withvalue:mvalue withtype:stype];

219. field.seq_column = i;

220. 
     

221. 
     

222. [fields setObject:field forKey:strfieldname];

223. }

224. [records addObject:fields];

225. }

226. 
     

227. -(void)clear

228. {

229. [records removeAllObjects];

230. cursor = -1;

231. }

232. 
     

233. -(NSInteger)count

234. {

235. return [records count];

236. }

237. 
     

238. -(OCField*)fieldbyname:(NSString*) fieldname

239. {

240. NSMutableDictionary* dic = [records objectAtIndex:cursor];

241. 
     

242. return (OCField*)[dic objectForKey:fieldname];

243. }

244. 
     

245. -(BOOL)next

246. {

247. ++cursor;

248. int rcount = [records count];

249. if (cursor <= rcount) {

250. return YES;

251. }

252. else

253. {

254. cursor = rcount+1;

255. return NO;

256. }

257. }

258. 
     

259. -(BOOL)first

260. {

261. if ([records count]>0) {

262. cursor = 0;

263. return YES;

264. }

265. else

266. {

267. cursor = -1;

268. return NO;

269. }

270. }

271. 
     

272. -(BOOL)move:(NSInteger) index

273. {

274. int idx = index -1;

275. if (-1 < idx < [records count]) {

276. cursor = idx;

277. return YES;

278. }

279. return NO;

280. }

281. 
     

282. -(OCField*)indexOffield:(NSInteger) index

283. {

284. OCField* ret = nil;

285. int ct = 0;

286. NSMutableDictionary* dic = [records objectAtIndex:cursor];

287. for (NSString* key in dic)

288. {

289. ret = [dic objectForKey:key];

290. 
     

291. if (index == ct)

292. break;

293. else

294. ct++;

295. 
     

296. }

297. return ret;

298. }

299. 
     

300. @end

301. 
     

302. 
     

303. 
     

304. @implementation OCSqlite

305. 
     

306. @synthesize dataset;

307. 
     

308. -(id)init

309. {

310. self = [super init];

311. if (self) {

312. 
     

313. dataset = [[OCDataset alloc]init];

314. return self;

315. }

316. return nil;

317. }

318. 
     

319. -(void)dealloc

320. {

321. [dataset release];

322. sqlite3_close(db);

323. [super dealloc];

324. }

325. 
     

326. -(BOOL)ConnectToDB:(NSString*) dbfilepath

327. {

328. BOOL successful;

329. successful = sqlite3_open([dbfilepath UTF8String], &db)==SQLITE_OK;

330. 
     

331. if (!successful) {

332. sqlite3_close(db);

333. return NO;

334. }

335. 
     

336. return YES;

337. }

338. 
     

339. -(void)DisconnectDB

340. {

341. sqlite3_close(db);

342. }

343. 
     

344. -(BOOL)excesql:(NSString*) ddlsql

345. {

346. char* err;

347. if (sqlite3_exec(db, [ddlsql UTF8String], NULL, NULL, &err)!=SQLITE_OK)

348. {

349. return NO;

350. }

351. return YES;

352. }

353. 
     

354. -(BOOL)query:(NSString*) qysql

355. {

356. sqlite3_stmt* cmd;

357. 
     

358. if (sqlite3_prepare_v2(db, [qysql UTF8String], -1, &cmd, nil)!=SQLITE_OK)

359. {

360. return NO;

361. }

362. 
     

363. [dataset clear];

364. 
     

365. while(sqlite3_step(cmd)==SQLITE_ROW)

366. {

367. [dataset fillData:cmd];

368. }

369. 
     

370. [dataset reset];

371. 
     

372. sqlite3_finalize(cmd);

373. 
     

374. return YES;

375. }

376. 
     

377. 
     

378. -(BOOL)startTranslation

379. {

380. char* err;

381. if (sqlite3_exec(db, "begin transaction",NULL, NULL, &err)!=SQLITE_OK)

382. {

383. return NO;

384. }

385. return YES;

386. }

387. 
     

388. -(BOOL)commitTranslation

389. {

390. char* err;

391. if (sqlite3_exec(db, "commit transaction",NULL, NULL, &err)!=SQLITE_OK)

392. {

393. return NO;

394. }

395. return YES;

396. }

397. 
     

398. -(BOOL)rollbackTranslation

399. {

400. char* err;

401. if (sqlite3_exec(db, "rollback transaction",NULL, NULL, &err)!=SQLITE_OK)

402. {

403. return NO;

404. }

405. return YES;

406. }

407. 
     

408. 
     

409. @end
     

以上是"sqlite操作与封装的示例分析"这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注行业资讯频道！