-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathmain.cpp
More file actions
144 lines (125 loc) · 4.63 KB
/
main.cpp
File metadata and controls
144 lines (125 loc) · 4.63 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
/*
注： 实际项目中，只需要将 sqlite3.h sqlite3.c sqlite3ext.h 加入到项目中来。就可以使用sqlite。
另一种方案是使用vs 生成lib，然后在项目中引入lib跟dll， 不过对比加入lib 跟 dll。直接引入源文件编译到exe文件麻烦少点。
*/
#include <iostream>
#include "sqlite/sqlite3.h"
#include <windows.h>
using namespace std;
string translateUtf82GBK(string utf8){ //utf8转 GBK
int length1 = MultiByteToWideChar(CP_UTF8, 0, utf8.c_str(), -1, NULL, 0);
WCHAR * wGBK = new WCHAR[length1];
MultiByteToWideChar(CP_UTF8, 0, utf8.c_str(), -1, wGBK, length1);
int length2 = WideCharToMultiByte(CP_ACP, 0, wGBK, -1, NULL, 0, NULL, NULL);
char * gbk = new char[length2];
WideCharToMultiByte(CP_ACP, 0, wGBK, -1, gbk, length2, NULL, NULL);
utf8 = gbk;
delete wGBK;
delete gbk;
return utf8;
}
int printRow(void *NotUsed, int argc, char **argv, char **azColName)
{
for(int i = 0 ; i < argc; i++)
{
cout<<translateUtf82GBK(azColName[i])<<" = "<<translateUtf82GBK((argv[i] ? argv[i] : "NULL"))<< (i != argc - 1 ? ", " : "\n");
}
return 0;
}
int mySqliteExec(sqlite3 * pSqlite, string sql) throw(std::string){
char * errMsg = NULL;
int toReturn;
if((toReturn = sqlite3_exec(pSqlite, sql.c_str(), NULL, NULL, &errMsg)) != SQLITE_OK){
string toThrow = "数据库操作失败,错误信息如下:\n";
toThrow += string(errMsg);
sqlite3_free(errMsg);
throw toThrow;
}
return toReturn;
}
void execSqlInTransaction(sqlite3 * pSqlite, int argc, ...) throw(std::string){
va_list args;
va_start(args, argc);
try{
mySqliteExec(pSqlite, "begin");
char * sql = NULL;
for(int i = 0; i < argc; ++i){
sql = va_arg(args,char*);
mySqliteExec(pSqlite, sql);
}
mySqliteExec(pSqlite, "commit");
}catch(std::string & e){
mySqliteExec(pSqlite, "rollback");
throw e;
}
}
int main()
{
sqlite3 * pDb = NULL;
if(sqlite3_open("./db/test.db", &pDb) != SQLITE_OK){
cout << translateUtf82GBK("数据库打开失败") << endl;
return -1;
}
string sql = "select * from test";
//查询
//*
if(sqlite3_exec(pDb, sql.c_str(), printRow, NULL, NULL) != SQLITE_OK){
cout << translateUtf82GBK("查询失败") << endl;
} else {
cout << translateUtf82GBK("查询成功") << endl;
}
//*/
sql = "insert into test(name,age) values('test',1);";
//插入
/*
if(sqlite3_exec(pDb, sql.c_str(), NULL, NULL, NULL) != SQLITE_OK){
cout << translateUtf82GBK("插入数据失败") << endl;
} else {
cout << translateUtf82GBK("插入数据成功") << endl;
}
*/
//更新
//删除
//更新和删除， 跟插入的代码基本一样，这里不再重复测试。
//*
//使用预处理
sqlite3_stmt * stmt = NULL;
sql = "select * from test where `id`<? and `age`<:age;";
const char * zTail = NULL; //用来存贮查询多出来的结果（遇到终止之后或者取到了给定的长度内容之后剩下的）
//预处理
int res = sqlite3_prepare_v2(pDb, sql.c_str(), -1, &stmt, &zTail); //第三个参数如果是负数，就一直取到终止
if(res != SQLITE_OK){
cout << "程序运行出错" << endl;
return -1;
}
//绑定
res = sqlite3_bind_int(stmt, 1, 4); // 注意，绑定的时候占位符计数是从 1 开始
if(res != SQLITE_OK){
cout << "程序运行出错" << endl;
return -1;
}
///cout << "index: " << sqlite3_bind_parameter_index(stmt,":age") << endl; //这个函数获取的索引从 1 开始，配合bind函数使用，因为没有直接用绑定变量名称的bind函数
res = sqlite3_bind_int(stmt,sqlite3_bind_parameter_index(stmt,":age"),10);
if(res != SQLITE_OK){
cout << "程序运行出错" << endl;
return -1;
}
while(sqlite3_step(stmt) == SQLITE_ROW){ //处理一行的数据
int id = sqlite3_column_int(stmt, 0);
string name = string((const char *)sqlite3_column_text(stmt, 1));
int age = sqlite3_column_int(stmt, 2);
cout << "{ id=" << id << ",name=" << translateUtf82GBK(name) << ",age=" << age << " }" << endl;
}
sqlite3_finalize(stmt); // 释放
//*/
//事务处理
string sql1 = "update test set name='sql1修改' where id=1", sql2 = "delete from test where id=100";
try{
execSqlInTransaction(pDb, 2, sql1.c_str(), sql2.c_str());
}catch(string & e){
cout << translateUtf82GBK(e) << endl;
}
//关闭数据库
sqlite3_close(pDb);
return 0;
}