android sqllite git,C++使用SQLite步骤及示例

C++使用SQLite步骤及示例

开发环境：Windows XP+VS2005。

开发语言：C++。

1、 下载sqlite文件。

下载网址：http://www.sqlite.org/download.html。

SQLite版本为SQLite 3.7.13，相关文件如下。

sqlite-dll-win32-x86-3071300.zip：包含sqlite3.def、sqlite3.dll文件。

sqlite-amalgamation-3071300.zip：包含sqlite3.h 文件。

sqlite-shell-win32-x86-3071300.zip：包含sqlite3.exe 文件。

2、 生成sqlite3.lib。

 sqlite-dll-win32-x86-3071300.zip文件解压到D:\ sqlite。

 运行Visual Studio 2005 Command Prompt命令行程序。

启动位置：开始程序->Microsoft Visual Studio

2005->Visual Studio Tools->Visual

Studio 2005 Command Prompt。

 依次执行控制台命令。

cd

D:\sqlite\sqlite-dll-win32-x86-3071300

D:

lib /def:sqlite3.def

/machine:ix86

cd D:\sqlite\sqlite-dll-win32-x86-3071300

D:

lib /def:sqlite3.def /machine:ix86

即可生成sqlite3.lib文件。

3、 创建测试数据。

 sqlite-shell-win32-x86-3071300.zip文件解压到D:\ sqlite。

 启动命令行，进入D:\ sqlite目录。

命令依次为：

cd D:\sqlite

d:

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

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。

 程序代码为：

// SQLiteTest.cpp :

Defines the entry point for the console application.

//

#include "stdafx.h"

#include

"sqlite3.h"

#include

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<

"<

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<

"<

return

false;

}

else

{

cout<

user success:

"<

}

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<

"<

return

false;

}

else

{

cout<

"<

}

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<

"<

return

false;

}

else

{

cout<

"<

}

return true;

}

static

int

UserResult(void

*NotUsed, int

argc, char

**argv, char

**azColName)

{

for(int i = 0 ; i < argc ;

i++)

{

cout<

= "<

: "NULL")<

";

}

cout<

return

0;

}

bool

SelectUser()

{

char*

cErrMsg;

int res

= sqlite3_exec(pDB, "select * from

user;", UserResult , 0 ,

&cErrMsg);

if (res !=

SQLITE_OK)

{

cout<

"<

return false;

}

return

true;

}

// SQLiteTest.cpp : Defines the entry point for the console application.

//

#include "stdafx.h"

#include "sqlite3.h"

#include

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<

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<

return false;

}

else

{

cout<

}

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<

return false;

}

else

{

cout<

}

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<

return false;

}

else

{

cout<

}

return true;

}

static int UserResult(void *NotUsed, int argc, char **argv, char **azColName)

{

for(int i = 0 ; i < argc ; i++)

{

cout<

}

cout<

return 0;

}

bool SelectUser()

{

char* cErrMsg;

int res = sqlite3_exec(pDB, "select * from user;", UserResult , 0 , &cErrMsg);

if (res != SQLITE_OK)

{

cout<

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,

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/。