C++使用SQLite步骤及示例
开发环境:Windows 10+VS2013。
1、 下载sqlite文件。
下载网址:http://www.sqlite.org/download.html。
SQLite版本为SQLite 3.11.1,相关文件如下。
sqlite-dll-win32-x86-3110100.zip:包含sqlite3.def、sqlite3.dll文件。
sqlite-amalgamation-3110100.zip:包含sqlite3.h 文件。
sqlite-tools-win32-x86-3110100.zip:包含sqlite3.exe 文件。
2、 生成sqlite3.lib。
sqlite-dll-win32-x86-3110100.zip文件解压到D:\ sqlite。
运行Visual Studio 2013 lib命令行程序。
依次执行控制台命令。
- cd D:\sqlite\sqlite-dll-win32-x86-3110100
- D:
- E:\Microsoft Visual Studio 12.0\VC\bin\lib.exe /def:sqlite3.def /machine:ix86
即可生成sqlite3.lib文件。
3、 创建测试数据。
sqlite-tools-win32-x86-3110100.zip文件解压到D:\ sqlite。
启动命令行,进入D:\ sqlite目录。
命令依次为:
- cd D:\sqlite
- d:
创建test.db测试文件。
创建user表。
字段Code | 字段类型 | 字段描述 |
id | integer | 主键,自增 |
name | varchar(64) | 用户名 |
age | integer | 年龄 |
创建命令依次如下。
- D:\sqlite>sqlite3.exe test.db
- SQLite version 3.7.13 2012-06-11 02:05:22
- Enter ".help" for instructions
- Enter SQL statements terminated with a ";"
- sqlite> create table user
- ...> (
- ...> id integer primary key autoincrement,
- ...> name varchar(64),
- ...> age integer
- ...> );
- sqlite> .quit
4、 创建示例工程
创建win32控制台工程SQLiteTest。
sqlite3.h(在sqlite-amalgamation-3071300.zip压缩包中)添加到工程。
sqlite3.lib复制到工程文件夹下。
工程属性中添加sqlite3.lib库依赖。
Configuration Properties->Linker->Input->Additional Dependencies添加sqlite3.lib。
程序代码为:
- /*
- @brief 本程序测试sqlite数据库的增删改查
- @date 2012-09-03
- */
- // SQLiteTest.cpp : Defines the entry point for the console application.
- //
- #include "stdafx.h"
- #include "sqlite3.h"
- #include <iostream>
- using namespace std;
- sqlite3 * pDB = NULL;
- //增加用户
- bool AddUser(const string& sName, const string& sAge);
- //删除用户
- bool DeleteUser(const string& sName);
- //修改用户
- bool ModifyUser(const string& sName, const string& sAge);
- //查找用户
- bool SelectUser();
- int _tmain(int argc, _TCHAR* argv[])
- {
- //打开路径采用utf-8编码
- //如果路径中包含中文,需要进行编码转换
- int nRes = sqlite3_open("D:\\sqlite\\test.db", &pDB);
- if (nRes != SQLITE_OK)
- {
- cout<<"Open database fail: "<<sqlite3_errmsg(pDB);
- goto QUIT;
- }
- //添加“赵钱孙李”
- if ( !AddUser("zhao", "18")
- || !AddUser("qian", "19")
- || !AddUser("sun", "20")
- || !AddUser("li", "21"))
- {
- goto QUIT;
- }
- //删除“赵”
- if (!DeleteUser("zhao"))
- {
- goto QUIT;
- }
- //修改“孙”
- if (!ModifyUser("sun", "15"))
- {
- goto QUIT;
- }
- //查找用户
- if (!SelectUser())
- {
- goto QUIT;
- }
- QUIT:
- sqlite3_close(pDB);
- return 0;
- }
- bool AddUser(const string& sName, const string& sAge)
- {
- string strSql = "";
- strSql += "insert into user(name,age)";
- strSql += "values('";
- strSql += sName;
- strSql += "',";
- strSql += sAge;
- strSql += ");";
- char* cErrMsg;
- int nRes = sqlite3_exec(pDB , strSql.c_str() ,0 ,0, &cErrMsg);
- if (nRes != SQLITE_OK)
- {
- cout<<"add user fail: "<<cErrMsg<<endl;
- return false;
- }
- else
- {
- cout<<"add user success: "<<sName.c_str()<<"\t"<<sAge.c_str()<<endl;
- }
- return true;
- }
- bool DeleteUser(const string& sName)
- {
- string strSql = "";
- strSql += "delete from user where name='";
- strSql += sName;
- strSql += "';";
- char* cErrMsg;
- int nRes = sqlite3_exec(pDB , strSql.c_str() ,0 ,0, &cErrMsg);
- if (nRes != SQLITE_OK)
- {
- cout<<"delete user fail: "<<cErrMsg<<endl;
- return false;
- }
- else
- {
- cout<<"delete user success: "<<sName.c_str()<<endl;
- }
- return true;
- }
- bool ModifyUser(const string& sName, const string& sAge)
- {
- string strSql = "";
- strSql += "update user set age =";
- strSql += sAge;
- strSql += " where name='";
- strSql += sName;
- strSql += "';";
- char* cErrMsg;
- int nRes = sqlite3_exec(pDB , strSql.c_str() ,0 ,0, &cErrMsg);
- if (nRes != SQLITE_OK)
- {
- cout<<"modify user fail: "<<cErrMsg<<endl;
- return false;
- }
- else
- {
- cout<<"modify user success: "<<sName.c_str()<<"\t"<<sAge.c_str()<<endl;
- }
- return true;
- }
- static int UserResult(void *NotUsed, int argc, char **argv, char **azColName)
- {
- for(int i = 0 ; i < argc ; i++)
- {
- cout<<azColName[i]<<" = "<<(argv[i] ? argv[i] : "NULL")<<", ";
- }
- cout<<endl;
- return 0;
- }
- bool SelectUser()
- {
- char* cErrMsg;
- int res = sqlite3_exec(pDB, "select * from user;", UserResult , 0 , &cErrMsg);
- if (res != SQLITE_OK)
- {
- cout<<"select fail: "<<cErrMsg<<endl;
- return false;
- }
- return true;
- }
编译成功后,将sqlite3.dll复制到SQLiteTest.exe同一目录下,运行SQLiteTest.exe。
运行结果:
- add user success: zhao 18
- add user success: qian 19
- add user success: sun 20
- add user success: li 21
- delete user success: zhao
- modify user success: sun 15
- id = 2, name = qian, age = 19,
- id = 3, name = sun, age = 15,
- id = 4, name = li, age = 21,
5、 SQLite管理工具
可视化管理工具,推荐使用:SQLite Expert,见:http://www.sqliteexpert.com/。
1 #include "stdafx.h" 2 #include <stdio.h> 3 #include "sqlite3.h" 4 #include <process.h> 5 #pragma comment(lib,"sqlite3.lib") 6 7 static int callback(void *NotUsed, int argc, char **argv, char **azColName){ 8 int i; 9 for(i=0; i<argc; i++){ 10 printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL"); 11 } 12 printf("\n"); 13 return 0; 14 } 15 16 int _tmain(int argc, char* argv[]) 17 { 18 sqlite3 *db; 19 char *zErrMsg = 0; 20 int rc; 21 22 if( argc!=2 ){ 23 fprintf(stderr, "Usage: %s DATABASE\n", argv[0]); 24 system("pause"); 25 return(1); 26 } 27 rc = sqlite3_open(argv[1], &db); 28 if( rc ){ 29 fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db)); 30 sqlite3_close(db); 31 return(1); 32 } 33 34 //char* sqlstatement = "create table test(int id,varchar name);"; 35 //char* sqlstatement = "insert into test values(1,'hello');"; 36 char* sqlstatement = "select * from test;"; 37 rc = sqlite3_exec(db, sqlstatement, callback, 0, &zErrMsg); 38 if( rc!=SQLITE_OK ){ 39 printf("%s\n",argv[2]); 40 fprintf(stderr, "SQL error: %s\n", zErrMsg); 41 sqlite3_free(zErrMsg); 42 } 43 sqlite3_close(db); 44 45 system("pause"); 46 return 0; 47 }