SQLLite是一个轻量级关系型数据库,设计的目标是嵌入式的,很多桌面应用程序(如火狐浏览器,QQ,skype)以及手机应用经常用它来作为数据存储,毕竟纯文本或者xml格式的读取不方便。C语言操作SQLite3的方式很简单,下面的程序大致演示了数据表创建,插入数据,查询数据几个常用的数据操作。
#include<iostream>
extern "C"
{
#include"sqlite3.h"
}
using namespace std;
//callback function
int select_cb_function(void *data,int n_cols,char **colvals,char **colnames)
{
int i;
static bool title_shown = false;
if(title_shown == false)
{
//print col name
for(i = 0;i < n_cols;i ++)
{
cout << colnames[i] << "\t";
}
cout << endl;
title_shown = true;
}
//print select results
for(i = 0;i < n_cols;i ++)
{
cout << colvals[i] << "\t";
}
cout << endl;
return 0;
}
int main()
{
sqlite3 *sdb = NULL;
char *err = NULL;
//open a database
int rc = sqlite3_open("temp.db",&sdb);
if(!rc){
//create a table
rc = sqlite3_exec(sdb,"create table user(name varchar(20),age int)",0,0,&err);
if(rc != SQLITE_OK){
cout << "error occured " << err << endl;
sqlite3_close(sdb);
exit(-1);
}
//insert a record
for(int i = 0; i < 2;i ++){
rc = sqlite3_exec(sdb,"insert into user values(\"ciaos\",25)",0,0,&err);
if(rc != SQLITE_OK){
cout << "error occured " << err << endl;
sqlite3_close(sdb);
exit(-1);
}
}
//select records
rc = sqlite3_exec(sdb,"select * from user",select_cb_function,0,&err);
if(rc != SQLITE_OK){
cout << "error occured " << err << endl;
sqlite3_close(sdb);
exit(-1);
}
sqlite3_close(sdb);
}
return 0;
}
当然需要引用sqlite3.dll,sqlite3.lib可以自行下载源码编译或者使用sqlite3.def文件编译时指定生成lib文件。下面是我封装的一个操作类
sqliteproxy.h头文件内容
#ifndef __SQLITEPROXY_H
#define __SQLITEPROXY_H
#include<map>
extern "C"
{
#include"sqlite3.h"
}
using std::map;
typedef struct _DBConn
{
sqlite3 *dbc;
}DBConn;
typedef struct _DBResult
{
bool status;
char **result;
int nRow;
int nColumn;
}DBResult;
class SQLiteProxy{
private:
std::map<char *,DBConn>pxs;
DBConn OpenSQLite(char *dbname){
map<char *,DBConn>::iterator it = pxs.find(dbname);
if(it == pxs.end()){
DBConn dbp;
dbp.dbc = NULL;
int res = sqlite3_open(dbname,&dbp.dbc);
if(res != SQLITE_OK){
exit(-1);
}
pxs.insert(map<char *,DBConn>::value_type(dbname,dbp));
return dbp;
}
else
{
return it->second;
}
}
public:
SQLiteProxy(){
}
~SQLiteProxy(){
int status;
for(map<char *,DBConn>::iterator it = pxs.begin(), ite = pxs.end();it != ite;it ++){
status = sqlite3_close(it->second.dbc);
if(status != SQLITE_OK){
exit(-1);
}
}
pxs.clear();
}
//create|insert|update|delete ...
bool Exec(char *dbname,char *sql){
int res;
char *err = NULL;
DBConn db = OpenSQLite(dbname);
res = sqlite3_exec(db.dbc,sql,0,0,&err);
if(res != SQLITE_OK){
return false;
}
return true;
}
//select
DBResult Query(char *dbname,char *sql){
int res;
char *err = NULL;
DBConn db = OpenSQLite(dbname);
DBResult dbr;
res = sqlite3_get_table(db.dbc,sql,&dbr.result,&dbr.nRow,&dbr.nColumn,&err);
if(res == SQLITE_OK)
{
dbr.status = true;
return dbr;
}
dbr.status = false;
return dbr;
}
};
#endif
select查询操作调用Query函数,其他操作调用Exec函数,使用示例如下:
#include<iostream>
#include"sqliteproxy.h"
using namespace std;
int main()
{
//
// new
//
SQLiteProxy *sp = new SQLiteProxy();
bool res;
res = sp->Exec("1.db","create table user(name varchar(20),age int)");
cout << res << endl;
res = sp->Exec("1.db","insert into user values('ciaos',25)");
cout << res << endl;
res = sp->Exec("1.db","delete from user");
cout << res << endl;
res = sp->Exec("1.db","insert into user values('ciaos',25)");
cout << res << endl;
DBResult dbr = sp->Query("1.db","select * from user");
if(dbr.status){
int index = dbr.nColumn;
for(int i =1;i<=dbr.nRow;i++){
for(int j =0;j<dbr.nColumn;j++){
cout << dbr.result[j] << " " << dbr.result[index] << " ";
index ++;
}
cout << endl;
}
}
//
// delete
//
delete sp;
//
return 0;
}