SqlLite是一款轻型的数据库,是遵守ACID的关系型数据库管理系统,它包含在一个相对小的C库中。它是D.RichardHipp建立的公有领域项目。它的设计目标是嵌入式的,而且目前已经在很多嵌入式产品中使用了它,它占用资源非常的低,在嵌入式设备中,可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix等等主流的操作系统,同时能够跟很多程序语言相结合,比如 Tcl、C#、PHP、Java等,还有ODBC接口,同样比起Mysql、PostgreSQL这两款开源的世界著名数据库管理系统来讲,它的处理速度比他们都快。SQLite第一个Alpha版本诞生于2000年5月。 至今已经有14个年头,SQLite也迎来了一个版本 SQLite 3已经发布。
下面利用SqlLite3 实现了简单的增、删、查、改,并将查询结果显示在MFC 的CListCtrl控件中:
1 //Sqlite.h
2 class_declspec (dllexport)Sqlite3 {4 public:5 Sqlite(){}6 ~Sqlite(){}7 void Open(stringname);8 voidClose();9 bool ExecDML(stringsql);10 int ExecScalar(stringsql);11 bool ExecQuery(string sql,vector &fieldName,vector &fieldValue);12 bool CreateTable(string tableName,vector &fieldName);13 bool DeleteTable(stringtableName);14 bool InsertRow(string tableName,vector &fieldName,vector &fieldValue);15 bool UpdateRow(string tableName,string key,string keyValue,vector &fieldName,vector &fieldValue);16 bool DeleteRow(string tableName,string key,stringkeyValue);17 private:18 CppSQLite3DB m_db;19 };
删表:
1 bool Sqlite::DeleteTable(stringtableName)2 {3 stringsql;4 sql=string("drop table")+tableName+";";5 //删除表table1 { "drop table table1"}
6 if(!m_db.tableExists(tableName.data()))return true;7 int ret&#61;m_db.execDML(sql.data());8 if(ret<0)return false;9 return true;10 }
建表&#xff1a;
1 bool Sqlite::CreateTable(string tableName,vector &fieldName)2 {3 stringsql;4 if(m_db.tableExists(tableName.data()))return true;5 sql&#61;string("create table")&#43;tableName&#43;"(";6 if(fieldName.size()<&#61;0)return false;7 vector ::iterator it;8 for(it&#61;fieldName.begin();it!&#61;fieldName.end();it&#43;&#43;)9 {10 sql&#43;&#61;(*it&#43;"string,");11 }12 sql[sql.size()-1]&#61;&#39;)&#39;;13 sql&#43;&#61;";";14 //sql&#61;" create table table1 (ID string,name string)"
15 int ret&#61;m_db.execDML(sql.data());16 if(ret<0)return false;17 return true;18 }
向量查询&#xff0c;取得表行数等 &#xff1a;
1 //sql&#61;"select count(*) from table1"
2 int Sqlite::ExecScalar(stringsql)3 {4 int ret&#61;-1;5 try{6 ret&#61;m_db.execScalar(sql.data());7 }catch(stringex) {8 return -1;9 }10 returnret;11 }
插入数据&#xff1a;
1 //sql&#61;"insert into table1 (&#39;ID&#39;,&#39;name&#39;) values(&#39;001&#39;,&#39;zhang&#39;);"
2 bool Sqlite::InsertRow(string tableName,vector &fieldName,vector &fieldValue)3 {4 //sql.Format("insert into UserTable (&#39;domain&#39;,&#39;ip&#39;,&#39;port&#39;,&#39;time&#39;) values(&#39;%s&#39;,&#39;%s&#39;,%d,&#39;%s&#39;);",domain,ip,port,t1);
5 stringsql;6 sql&#61;string("insert into")&#43;tableName&#43;"(";7 if(fieldName.size()<&#61;0)return false;8 vector ::iterator it;9 for(it&#61;fieldName.begin();it!&#61;fieldName.end();it&#43;&#43;)10 {11 sql&#43;&#61;("&#39;"&#43;*it&#43;"&#39;,");12 }13 sql[sql.size()-1]&#61;&#39;)&#39;;14 sql&#43;&#61;"values(";15
16 for(it&#61;fieldValue.begin();it!&#61;fieldValue.end();it&#43;&#43;)17 {18 sql&#43;&#61;("&#39;"&#43;*it&#43;"&#39;,");19 }20 sql[sql.size()-1]&#61;&#39;)&#39;;21 sql&#43;&#61;";";22 int ret&#61;m_db.execDML(sql.data());23 if(ret<0)return false;24 return true;25 }
更改数据&#xff1a;
1 //sql&#61;"update table1 set ID &#61; &#39;004&#39;,name &#61; &#39;zhao1&#39; where ID &#61; &#39;004&#39;;"
2 bool Sqlite::UpdateRow(string tableName,string key,string keyValue,vector &fieldName,vector &fieldValue)3 {4 stringsql;5 if(fieldName.size()<&#61;0)return false;6 if(fieldName.size()!&#61;fieldValue.size())return false;7 sql&#61;string("update")&#43;tableName&#43;"set";8 vector ::iterator it;9 vector ::iterator it1;10 it1&#61;fieldValue.begin();11 for(it&#61;fieldName.begin();it!&#61;fieldName.end();it&#43;&#43;)12 {13 sql&#43;&#61;(*it&#43;"&#61; &#39;"&#43;*it1&#43;"&#39;,");14 it1&#43;&#43;;15 }16 sql[sql.size()-1]&#61;&#39; &#39;;17 if(key.data()&&keyValue.data())18 {19 sql&#43;&#61;(string("where")&#43;key&#43;"&#61; &#39;"&#43;keyValue&#43;"&#39;");20 }21 sql&#43;&#61;";";22 int ret&#61;m_db.execDML(sql.data());23 if(ret<0)return false;24 return true;25 }
删除一行数据&#xff1a;
1 //sql&#61;"delete from table1 where ID &#61; &#39;004&#39;;";
2 bool Sqlite::DeleteRow(string tableName,string key,stringkeyValue)3 {4 stringsql;5 sql&#61;string("delete from")&#43;tableName&#43;" ";6 if(key.data()&&keyValue.data())7 {8 sql&#43;&#61;(string("where")&#43;key&#43;"&#61; &#39;"&#43;keyValue&#43;"&#39;");9 }10 sql&#43;&#61;";";11 int ret&#61;m_db.execDML(sql.data());12 if(ret<0)return false;13 return true;14 }
查询&#xff0c;后文有更详细的使用代码&#xff1a;
1 //sql&#61;"select * from table1";
2 bool Sqlite::ExecQuery(string sql,vector &fieldName,vector &fieldValue)3 {4 CppSQLite3Query q&#61;m_db.execQuery(sql.data());5 fieldName.clear();6 fieldValue.clear();7 for(int i&#61;0;i
demo code,how to use:
1 //插入了4条数据&#xff0c;删除一条&#xff0c;最后查询出所有数据
2 voidCmy_SqlLite_tDlg::OnBnClickedBnTest()3 {4
5 //TODO: 在此添加控件通知处理程序代码
6 int ret &#61; 0;7 Sqlite m;8 m.Open("test.db");9
10 ret &#61; m.DeleteTable("table1");11 TRACE("%d\n", ret);12 vectorfields;13 fields.push_back("ID");14 fields.push_back("name");15 ret &#61; m.CreateTable("table1", fields);16 TRACE("%d\n", ret);17 ret &#61; m.ExecScalar("select count(*) from table1");18 TRACE("%d\n", ret);19 vectorValues;20 Values.clear();21 Values.push_back("001");22 Values.push_back("zhang");23 ret &#61; m.InsertRow("table1", fields, Values);24 TRACE("%d\n", ret);25 ret &#61; m.ExecScalar("select count(*) from table1");26 TRACE("%d\n", ret);27 Values.clear();28 Values.push_back("002");29 Values.push_back("wang");30 ret &#61; m.InsertRow("table1", fields, Values);31 TRACE("%d\n", ret);32 ret &#61; m.ExecScalar("select count(*) from table1");33 TRACE("%d\n", ret);34 Values.clear();35 Values.push_back("003");36 Values.push_back("li");37 ret &#61; m.InsertRow("table1", fields, Values);38 TRACE("%d\n", ret);39 ret &#61; m.ExecScalar("select count(*) from table1");40 TRACE("%d\n", ret);41 Values.clear();42 Values.push_back("004");43 Values.push_back("zhao");44 ret &#61; m.InsertRow("table1", fields, Values);45 TRACE("%d\n", ret);46 ret &#61; m.ExecScalar("select count(*) from table1");47 TRACE("%d\n", ret);48 Values.clear();49 Values.push_back("004");50 Values.push_back("zhao1");51 ret &#61; m.UpdateRow("table1", (char *)fields[0].data(), "004", fields, Values);52 TRACE("%d\n", ret);53 ret &#61; m.DeleteRow("table1", (char *)fields[0].data(), "004");54 TRACE("%d\n", ret);55 ret &#61; m.ExecScalar("select count(*) from table1");56 TRACE("%d\n", ret);57
58
59 vectorfieldName;60 vectorfieldValue;61 ret &#61; m.ExecQuery("select * from table1", fieldName, fieldValue);62 vector ::iterator it;63 for (it &#61; fieldValue.begin(); it !&#61; fieldValue.end(); it&#43;&#43;)64 {65 MessageBox(*it);66
67 }68
69 m.Close();70
71 }
下面讨论下MFC CList控件显示查询结果&#xff1a;
先看看效果图&#xff1a;
代码&#xff1a;
1 voidCmy_SqlLite_tDlg::OnBnClickedBnselect()2 {3 //TODO: 在此添加控件通知处理程序代码4 //CStringArray fieldName;CStringArray rowValue;
5 CppSQLite3DB m_db;6 m_db.open("test.db");7
8
9 std::string sql &#61; "select * from table1;";10 CppSQLite3Query q &#61;m_db.execQuery(sql.data());11
12
13 //b14 //删除所用行和列&#xff0c;reset CListCtrl
15 while (m_LsData.DeleteColumn(0)) //因为你删除了第一列后&#xff0c;后面的列会依次向上移动。
16 m_LsData.DeleteAllItems();17
18
19 m_LsData.ModifyStyle(0L, LVS_REPORT);20 m_LsData.ModifyStyle(0L, LVS_SINGLESEL);21 m_LsData.ModifyStyle(0L, LVS_SHOWSELALWAYS);22 m_LsData.ModifyStyle(0L, LVS_NOSORTHEADER);23 m_LsData.SetExtendedStyle(LVS_EX_GRIDLINES);24 //e
25
26
27 for (int i &#61; 0; i
30 m_LsData.InsertColumn(i, q.fieldName(i), LVCFMT_LEFT);31 m_LsData.SetColumnWidth(i, strlen(q.fieldName(i)) &#43; 100);32 }33 int nRow &#61; 0;//数据太大分页&#xff0c;或做限制
34 while (!q.eof())35 {36 int nColumn &#61;q.numFields();37 m_LsData.InsertItem(nRow, "");38 for (int i &#61; 0; i
41 m_LsData.SetItemText(nRow, i, q.fieldValue(i));42
43 }44 nRow&#43;&#43;;45 q.nextRow();46 }47 m_db.close();48
49 }
下面是使用SQLite Expert Professional 3查看数据看”test.db“&#xff1a;(注&#xff1a;不免费&#xff0c;我下载的这个只有30天试用期)
整个工程&#xff0c;我分享在百度网盘&#xff0c;感谢支持&#xff0c;boyang987 ,all copyright reserved.