sqlite3使用

1. 
/*
2. @brief 本程序测试sqlite数据库的增删改查
3. @date 2012-09-03
4. */
5. // SQLiteTest.cpp : Defines the entry point for the console application.
6. //
7. 
8. //#include "stdafx.h"
9. #include "sqlite3.h"
10. #include <iostream>
11. using namespace std;
12. 
13. sqlite3 * pDB = NULL;
14. 
15. bool CreateTable();
16. 
17. //增加用户
18. bool AddUser(const string& sName, const string& sAge);
19. //删除用户
20. bool DeleteUser(const string& sName);
21. //修改用户
22. bool ModifyUser(const string& sName, const string& sAge);
23. //查找用户
24. bool SelectUser();
25. 
26. int main(void)
27. {
28. //打开路径采用utf-8编码
29. //如果路径中包含中文，需要进行编码转换
30. 
31.     CreateTable();
32. 
33.     int nRes = sqlite3_open("test.db", &pDB);
34. if (nRes != SQLITE_OK)
35.     {
36.         cout<<"Open database fail: "<<sqlite3_errmsg(pDB);
37.         goto QUIT;
38.     }
39. 
40. 
41. 
42. //添加“赵钱孙李”
43. if (    !AddUser("zhao", "18")
44.         || !AddUser("qian", "19")
45.         || !AddUser("sun", "20")
46.         || !AddUser("li", "21"))
47.     {
48.         goto QUIT;
49.     }
50. 
51. //删除“赵”
52. if (!DeleteUser("zhao"))
53.     {
54.         goto QUIT;
55.     }
56. 
57. //修改“孙”
58. if (!ModifyUser("sun", "15"))
59.     {
60.         goto QUIT;
61.     }
62. 
63.     cout << "find the user" << endl;
64. 
65. //查找用户
66. if (!SelectUser())
67.     {
68.         goto QUIT;
69.     }
70. 
71. QUIT:
72.     sqlite3_close(pDB);
73. 
74. return 0;
75. }
76. 
77. 
78. static int callback(void *NotUsed, int argc, char **argv, char **azColName)
79. {
80.     int i = 0;
81. for(i = 0; i < argc; i++)
82.     {
83.         printf("%s = %s\n",azColName[i], argv[i]?argv[i]:"NULL");
84.     }
85. 
86.     printf("\n");
87. return 0;
88. }
89. 
90. 
91. 
92. bool CreateTable()
93. {
94.     char *zErrMsg = 0;
95.     int rc;
96.     char *sql;
97. 
98.     rc = sqlite3_open("test.db", &pDB);  // 打开数据库
99. if(rc)
100.     {
101.         fprintf(stderr, "Can't open database:%s\n", sqlite3_errmsg(pDB));
102. return false;
103.     }
104. else
105.     {
106.         fprintf(stderr,"open database succeddfully\n");
107.     }
108. 
109. // sql = "create table company( "\
110.  //      "ID INT PRIMARY KEY NOT NULL,"\
111.  //      "NAME TEXT NOT NULL,"\
112.  //      "AGE TEXT NOT NULL);";
113. 
114.    sql = "CREATE TABLE user("  \
115. "NAME           TEXT    NOT NULL," \
116. "AGE            INT     NOT NULL," \
117. "ADDRESS        CHAR(50)," \
118. "SALARY         REAL );";
119. 
120.     rc = sqlite3_exec(pDB, sql, callback, 0, &zErrMsg); // 执行上面sql中的命令
121. if(SQLITE_OK != rc)
122.     {
123.         fprintf(stderr, "SQL error: %s\n", zErrMsg);
124.     }
125. else
126.     {
127.         fprintf(stdout, "create table successfully\n");
128.     }
129. 
130. }
131. 
132. 
133. bool AddUser(const string& sName, const string& sAge)
134. {
135. string strSql = "";
136.     strSql += "insert into user(name,age)";
137.     strSql += "values('";
138.     strSql += sName;
139.     strSql += "',";
140.     strSql += sAge;
141.     strSql += ");";
142. 
143.     char* cErrMsg;
144.     int nRes = sqlite3_exec(pDB , strSql.c_str() ,0 ,0, &cErrMsg);
145. if (nRes != SQLITE_OK)  
146.     {
147.         cout<<"add user fail: "<<cErrMsg<<endl;
148. return false;
149.     }
150. else
151.     {
152.         cout<<"add user success: "<<sName.c_str()<<"\t"<<sAge.c_str()<<endl;
153.     }
154. 
155. return true;
156. }
157. 
158. bool DeleteUser(const string& sName)
159. {
160. string strSql = "";
161.     strSql += "delete from user where name='";
162.     strSql += sName;
163.     strSql += "';";
164. 
165.     char* cErrMsg;
166.     int nRes = sqlite3_exec(pDB , strSql.c_str() ,0 ,0, &cErrMsg);
167. if (nRes != SQLITE_OK)  
168.     {
169.         cout<<"delete user fail: "<<cErrMsg<<endl;
170. return false;
171.     }
172. else
173.     {
174.         cout<<"delete user success: "<<sName.c_str()<<endl;
175.     }
176. 
177. return true;
178. }
179. 
180. bool ModifyUser(const string& sName, const string& sAge)
181. {
182. string strSql = "";
183.     strSql += "update user set age =";
184.     strSql += sAge;
185.     strSql += " where name='";
186.     strSql += sName;
187.     strSql += "';";
188. 
189.     char* cErrMsg;
190.     int nRes = sqlite3_exec(pDB , strSql.c_str() ,0 ,0, &cErrMsg);
191. if (nRes != SQLITE_OK)  
192.     {
193.         cout<<"modify user fail: "<<cErrMsg<<endl;
194. return false;
195.     }
196. else
197.     {
198.         cout<<"modify user success: "<<sName.c_str()<<"\t"<<sAge.c_str()<<endl;
199.     }
200. 
201. return true;
202. }
203. 
204. // static int UserResult(void *NotUsed, int argc, char **argv, char **azColName)
205. // {
206. //     for(int i = 0 ; i < argc ; i++)
207. //     {
208. //         cout<<azColName[i]<<" = "<<(argv[i] ? argv[i] : "NULL")<<", ";
209. //     }
210. //     cout<<endl;
211. 
212. //     return 0;
213. // }
214. 
215. 
216. // static int UserResult(void *data, int argc, char **argv, char **azColName){
217. //    int i;
218. //    fprintf(stderr, "%s: ", (const char*)data);
219. //    for(i=0; i<argc; i++){
220. //       printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
221. //    }
222. //    printf("\n");
223. //    return 0;
224. // }
225. 
226. bool SelectUser()
227. {
228.     char* cErrMsg;
229.     int res = sqlite3_exec(pDB, "select * from user;", callback , 0 , &cErrMsg);  
230. 
231. if (res != SQLITE_OK)
232.     {
233.         cout<<"select fail: "<<cErrMsg<<endl;
234. return false;
235.     }
236.     cout << "find the user end---" << endl;
237. 
238. return true;
239. }