soci连接mysql_使用soci操作数据库
基本操作
#include #include #include #include #include "soci.h"
// 使用soci访问sqlite3.
// 参考 http://blog.csdn.net/littlewhite1989/article/details/54691367
// soci官方文档test-sqlite3.cpp
// 创建表
static void CreateTable(soci::session& db) {
db << "CREATE TABLE IF NOT EXISTS STUDENTS ("
"ID INTEGER PRIMARY KEY AUTOINCREMENT,"
"NAME VARCHAR(50) NOT NULL,"
"AGE INTEGER NOT NULL"
")";
}
// 插入数据
static void TestInsert(soci::session& db, const std::string& name, int age) {
// 2.直接执行语句，相当于调用sqlite3_exec(),使用soci::use来传递参数。
try {
db << "INSERT INTO STUDENTS(NAME, AGE) VALUES(:name, :age)", soci::use(name), soci::use(age);
} catch (const soci::soci_error& e) {
std::cout << e.what() << std::endl;
return;
}
}
// 删除数据
static void TestDelete(soci::session& db, const std::string& name) {
// 3.先prepare编译SQL语句，然后在执行。
soci::statement stmt = (db.prepare << "DELETE FROM STUDENTS WHERE NAME = :name", soci::use(name));
stmt.execute(true);
long long affected_rows = stmt.get_affected_rows();
std::cout << "Affected rows is " << affected_rows << "." << std::endl;
}
// 修改数据
static void TestUpdate(soci::session& db, const std::string& name, int age) {
try {
db << "UPDATE STUDENTS SET AGE = :age WHERE NAME = :name", soci::use(age), soci::use(name);
} catch (const soci::soci_error& e) {
std::cout << e.what() << std::endl;
return;
}
}
// 查询数据
static void TestSelect(soci::session& db, int id) {
std::string name;
int age = -1;
try {
db << "SELECT NAME, AGE FROM STUDENTS WHERE ID = :id", soci::use(id), soci::into(name), soci::into(age);
} catch (const soci::soci_error& e) {
std::cout << e.what() << std::endl;
}
if (!db.got_data()) {
std::cout << "Select none!" << std::endl;
return;
}
std::cout << "Id: " << id << ", Name: " << name << ", Age: " << age << std::endl;
}
// 显式开启事务
static void TestTransaction(soci::session& db) {
soci::transaction transaction(db);
std::string name = "Zhe";
for (int age = 0; age < 10000; ++age) {
try {
db << "INSERT INTO STUDENTS(NAME, AGE) VALUES(:name, :age)", soci::use(name), soci::use(age);
} catch (const soci::soci_error& e) {
std::cout << e.what() << std::endl;
transaction.rollback();
return;
}
}
transaction.commit();
}
// TODO: 使用prepare执行方法，没有正确显式开启事务，执行速度非常慢。
static void FailedTestTransaction(soci::session& db) {
soci::transaction transaction(db);
soci::statement stmt = db.prepare << "INSERT INTO STUDENTS(NAME, AGE) VALUES(:name, :age)";
for (int i = 0; i < 100; ++i) {
std::string name = "Zhe";
stmt.exchange(soci::use(name, "name"));
stmt.exchange(soci::use(i, "age"));
stmt.define_and_bind();
stmt.execute(true);
}
transaction.commit();
}
// 统计有多少行数据
static long long TestCountAllRows(soci::session& db) {
long long count = 0;
try {
db << "SELECT COUNT(*) FROM STUDENTS", soci::into(count);
} catch (const soci::soci_error& e) {
std::cout << e.what() << std::endl;
return count;
}
std::cout << "The rows count is " << count << std::endl;
return count;
}
// 查询所有数据
static void TestSelectAll(soci::session& db) {
soci::rowsetrs = db.prepare << "SELECT * FROM STUDENTS";
for (const soci::row& r : rs) {
std::cout << "Id: " << r.get(0);
std::cout << ", Name: " << r.get(1);
std::cout << ", Age: " << r.get(2) << std::endl;
}
}
int main() {
// 1.打开数据库，如果数据库不存在，则创建文件数据库soci_test.db。
soci::session db("sqlite3", "soci_test.db");
CreateTable(db);
TestDelete(db, "Zhe");
TestInsert(db, "Zhe", 15);
TestUpdate(db, "Zhe", 123);
TestSelect(db, 1);
TestCountAllRows(db);
TestSelectAll(db);
TestTransaction(db);
FailedTestTransaction(db);
return 0;
}
ORM
#include #include #include #include #include "soci.h"
using namespace std;
// 参考 http://blog.csdn.net/littlewhite1989/article/details/54691367
// 1. 对象关系映射(Object Relational Mapping，简称ORM)模式是一种为了解决面向对象
// 与关系数据库存在的互不匹配的现象的技术。
static void CreateTable(soci::session& db) {
db << "CREATE TABLE IF NOT EXISTS TEACHERS ("
"ID INTEGER NOT NULL,"
"NAME VARCHAR(50) NOT NULL,"
"AGE INTEGER NOT NULL"
")";
}
struct TEACHER {
int id;
std::string name;
int age;
TEACHER()
: id(-1)
, age(-1) {
}
};
std::ostream& operator<
struct type_conversion{
typedef values base_type;
static void from_base(const values& v, indicator ind , TEACHER& teacher) {
teacher.id = v.get("ID");
teacher.name = v.get("NAME");
teacher.age = v.get("AGE");
}
static void to_base(const TEACHER& teacher, values& v, indicator& ind) {
v.set("ID", teacher.id);
v.set("NAME", teacher.name);
v.set("AGE", teacher.age);
ind = i_ok;
}
};
} // namespace soci
static void TestInsert(soci::session& db) {
TEACHER teacher;
teacher.id = 101;
teacher.name = "Zhe";
teacher.age = 10;
// soci::use可直接使用对象teacher，会根据占位符指定字段。
try {
db << "INSERT INTO TEACHERS VALUES(:ID, :NAME, :AGE)", soci::use(teacher);
} catch (const soci::soci_error& e) {
std::cout << e.what() << std::endl;
return;
}
}
static void TestSelect(soci::session& db, int id) {
TEACHER teacher;
try {
db << "SELECT * FROM TEACHERS WHERE ID = :id", soci::use(id), soci::into(teacher);
} catch (const soci::soci_error& e) {
std::cout << e.what() << std::endl;
return;
}
std::cout << teacher << std::endl;
}
static void TestUpdate(soci::session& db, const TEACHER& teacher) {
try {
db << "UPDATE TEACHERS SET NAME=:NAME, AGE=:AGE WHERE ID=:ID", soci::use(teacher);
} catch (const soci::soci_error& e) {
cout << "err:" << e.what() << endl;
return;
}
}
static void TestDelete(soci::session& db, const TEACHER& teacher) {
try {
db << "DELETE FROM TEACHERS WHERE NAME=:NAME", soci::use(teacher);
} catch (const soci::soci_error& e) {
cout << "err:" << e.what() << endl;
return;
}
}
static void TestSelectAll(soci::session& db) {
soci::rowsetteachers = db.prepare << "SELECT * FROM TEACHERS";
for (const TEACHER& teacher : teachers) {
std::cout << teacher << std::endl;
}
}
int main002() {
soci::session db("sqlite3", "soci_test.db");
CreateTable(db);
TestInsert(db);
TestSelectAll(db);
TestSelect(db, 101);
TEACHER teacher;
teacher.id = 101;
teacher.name = "ZZZ";
teacher.age = 111;
TestUpdate(db, teacher);
TestSelectAll(db);
TestDelete(db, teacher);
TestSelectAll(db);
return 0;
}