最近,一直在加强数据处理模块的功能,种种数据库中间件,感觉poco的还是比较好的,开发效率好,API接口清晰,下面,把最常用的基本步骤整理下来,供大家参考。
一 基本步骤
a.注册会话类型
b. 创建会话(session)
c. 从DB中读写数据(神奇的into, use)
d. 使用statements
e. 使用容器(Collection) (数据,集合...)
f. 使用limit限定
g. 如何使用复杂的数据类型(如何将一个C++对象映射到数据库的表
二 例程如下
例子以sqlite为主,其它数据库,只是建立会话参数不一样
2.1基本标准的数据库连接使用方法
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
|
#include "Poco/Data/Session.h"
#include "Poco/Data/SQLite/Connector.h"
#include <vector>
#include <iostream>
using
namespace
Poco::Data::Keywords;
using
Poco::Data::Session;
using
Poco::Data::Statement;
//保存查询结果
struct
Person
{
std::string name;
std::string address;
int
age;
};
int
main(
int
argc,
char
** argv)
{
//注册会话类型
Poco::Data::SQLite::Connector::registerConnector();
// 建立会话
Session session(
"SQLite"
,
"sample.db"
);
// 如果有名为Person的表存在,删除
session <<
"DROP TABLE IF EXISTS Person"
, now;
// 创建表格
session <<
"CREATE TABLE Person (Name VARCHAR(30), Address VARCHAR, Age INTEGER(3))"
, now;
// 建立一个结构体变量
Person person =
{
"Bart Simpson"
,
"Springfield"
,
12
};
//创建执行命令的语句执行器
Statement insert(session);
insert <<
"INSERT INTO Person VALUES(?, ?, ?)"
,
use(person.name),
use(person.address),
use(person.age);
//插入纪录
insert.execute();
//插入第二条纪录
person.name =
"Lisa Simpson"
;
person.address =
"Springfield"
;
person.age = 10;
insert.execute();
// 建立查询执行器
Statement select(session);
select <<
"SELECT Name, Address, Age FROM Person"
,
into(person.name),
into(person.address),
into(person.age),
range(0, 1);
// 每次放一条纪录
while
(!select.done())
{
select.execute();
std::cout << person.name <<
" "
<< person.address <<
" "
<< person.age << std::endl;
}
// 另一种,把查询结果放入到容器中
std::vector<std::string> names;
session <<
"SELECT Name FROM Person"
,
into(names),
now;
for
(std::vector<std::string>::const_iterator it = names.begin(); it != names.end(); ++it)
{
std::cout << *it << std::endl;
}
//退出会话注册器
Poco::Data::SQLite::Connector::unregisterConnector();
return
0;
}
|
2.2把查询结果信放入到纪录集中seo
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
|
#include "Poco/SharedPtr.h"
#include "Poco/DateTime.h"
#include "Poco/Data/SessionFactory.h"
#include "Poco/Data/Session.h"
#include "Poco/Data/RecordSet.h"
#include "Poco/Data/Column.h"
#include "Poco/Data/SQLite/Connector.h"
#include <iostream>
using
namespace
Poco::Data::Keywords;
using
Poco::DateTime;
using
Poco::Data::Session;
using
Poco::Data::Statement;
using
Poco::Data::RecordSet;
int
main(
int
argc,
char
** argv)
{
Poco::Data::SQLite::Connector::registerConnector();
// 建立会话
Session session(
"SQLite"
,
"sample.db"
);
// 如果这个表存在,刚删除
session <<
"DROP TABLE IF EXISTS Person"
, now;
// 创建表格
session <<
"CREATE TABLE Person (Name VARCHAR(30), Address VARCHAR, Age INTEGER(3), Birthday DATE)"
, now;
// 插入两条纪录
DateTime bd(1980, 4, 1);
DateTime ld(1982, 5, 9);
session <<
"INSERT INTO Person VALUES('Bart Simpson', 'Springfield', 12, ?)"
, use(bd), now;
session <<
"INSERT INTO Person VALUES('Lisa Simpson', 'Springfield', 10, ?)"
, use(ld), now;
// 建立一个查询执行器
Statement select(session);
select <<
"SELECT * FROM Person"
;
select.execute();
// 创建一个纪录集
RecordSet rs(select);
std::
size_t
cols = rs.columnCount();
// 输出所有字段名称
for
(std::
size_t
col = 0; col < cols; ++col)
{
std::cout << rs.columnName(col) << std::endl;
}
// 输出所有查询到内容
bool
more = rs.moveFirst();
while
(more)
{
for
(std::
size_t
col = 0; col < cols; ++col)
{
std::cout << rs[col].convert<std::string>() <<
" "
;
}
std::cout << std::endl;
more = rs.moveNext();
}
return
0;
}
|
2.3html表格的实现
具体不知道C++中实现这个,有什么用。
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
|
#include "Poco/SharedPtr.h"
#include "Poco/DateTime.h"
#include "Poco/Data/SessionFactory.h"
#include "Poco/Data/Session.h"
#include "Poco/Data/Statement.h"
#include "Poco/Data/RecordSet.h"
#include "Poco/Data/RowFormatter.h"
#include "Poco/Data/SQLite/Connector.h"
#include <iostream>
using
namespace
Poco::Data::Keywords;
using
Poco::DateTime;
using
Poco::Data::Session;
using
Poco::Data::Statement;
using
Poco::Data::RecordSet;
using
Poco::Data::RowFormatter;
class
HTMLTableFormatter :
public
RowFormatter
{
public
:
HTMLTableFormatter()
{
std::ostringstream os;
os <<
"<TABLE border=\"1\" cellspacing=\"0\">"
<< std::endl;
setPrefix(os.str());
os.str(
""
);
os <<
"</TABLE>"
<< std::endl;
setPostfix(os.str());
}
std::string& formatNames(
const
NameVecPtr pNames, std::string& formattedNames)
{
std::ostringstream str;
str <<
"\t<TR>"
<< std::endl;
NameVec::const_iterator it = pNames->begin();
NameVec::const_iterator end = pNames->end();
for
(; it != end; ++it) str <<
"\t\t<TH align=\"center\">"
<< *it <<
"</TH>"
<< std::endl;
str <<
"\t</TR>"
<< std::endl;
return
formattedNames = str.str();
}
std::string& formatValues(
const
ValueVec& vals, std::string& formattedValues)
{
std::ostringstream str;
str <<
"\t<TR>"
<< std::endl;
ValueVec::const_iterator it = vals.begin();
ValueVec::const_iterator end = vals.end();
for
(; it != end; ++it)
{
if
(it->isNumeric())
str <<
"\t\t<TD align=\"right\">"
;
else
str <<
"\t\t<TD align=\"left\">"
;
str << it->convert<std::string>() <<
"</TD>"
<< std::endl;
}
str <<
"\t</TR>"
<< std::endl;
return
formattedValues = str.str();
}
};
int
main(
int
argc,
char
** argv)
{
// register SQLite connector
Poco::Data::SQLite::Connector::registerConnector();
// create a session
Session session(
"SQLite"
,
"sample.db"
);
// drop sample table, if it exists
session <<
"DROP TABLE IF EXISTS Simpsons"
, now;
// (re)create table
session <<
"CREATE TABLE Simpsons (Name VARCHAR(30), Address VARCHAR, Age INTEGER(3), Birthday DATE)"
, now;
// insert some rows
DateTime hd(1956, 3, 1);
session <<
"INSERT INTO Simpsons VALUES('Homer Simpson', 'Springfield', 42, ?)"
, use(hd), now;
hd.assign(1954, 10, 1);
session <<
"INSERT INTO Simpsons VALUES('Marge Simpson', 'Springfield', 38, ?)"
, use(hd), now;
hd.assign(1980, 4, 1);
session <<
"INSERT INTO Simpsons VALUES('Bart Simpson', 'Springfield', 12, ?)"
, use(hd), now;
hd.assign(1982, 5, 9);
session <<
"INSERT INTO Simpsons VALUES('Lisa Simpson', 'Springfield', 10, ?)"
, use(hd), now;
// create a statement and print the column names and data as HTML table
HTMLTableFormatter tf;
Statement stmt = (session <<
"SELECT * FROM Simpsons"
, format(tf), now);
RecordSet rs(stmt);
std::cout << rs << std::endl;
// Note: The code above is divided into individual steps for clarity purpose.
// The four lines can be reduced to the following single line:
std::cout << RecordSet(session,
"SELECT * FROM Simpsons"
, HTMLTableFormatter());
// simple formatting example (uses the default SimpleRowFormatter provided by framework)
std::cout << std::endl <<
"Simple formatting:"
<< std::endl << std::endl;
std::cout << RecordSet(session,
"SELECT * FROM Simpsons"
);
return
0;
}
|
2.4把结果放入到数组中
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
|
#include "Poco/SharedPtr.h"
#include "Poco/Tuple.h"
#include "Poco/Data/SessionFactory.h"
#include "Poco/Data/Session.h"
#include "Poco/Data/SQLite/Connector.h"
#include <vector>
#include <iostream>
using
namespace
Poco::Data::Keywords;
using
Poco::Data::Session;
using
Poco::Data::Statement;
int
main(
int
argc,
char
** argv)
{
typedef
Poco::Tuple<std::string, std::string,
int
> Person;
typedef
std::vector<Person> People;
Poco::Data::SQLite::Connector::registerConnector();
// 创建会话
Session session(
"SQLite"
,
"sample.db"
);
// 如果表存在,删除
session <<
"DROP TABLE IF EXISTS Person"
, now;
// 创建表格
session <<
"CREATE TABLE Person (Name VARCHAR(30), Address VARCHAR, Age INTEGER(3))"
, now;
// 插入两行
People people;
people.push_back(Person(
"Bart Simpson"
,
"Springfield"
, 12));
people.push_back(Person(
"Lisa Simpson"
,
"Springfield"
, 10));
Statement insert(session);
insert <<
"INSERT INTO Person VALUES(:name, :address, :age)"
,
use(people), now;
people.clear();
// 建立查询执行器
Statement select(session);
select <<
"SELECT Name, Address, Age FROM Person"
,
into(people),
now;
for
(People::const_iterator it = people.begin(); it != people.end(); ++it)
{
std::cout <<
"Name: "
<< it->get<0>() <<
", Address: "
<< it->get<1>() <<
", Age: "
<< it->get<2>() <<std::endl;
}
return
0;
|