使用soci操作数据库

基本操作

#include <iostream>#include <string>#include <list>#include <cassert>#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::rowset<soci::row> rs = db.prepare << "SELECT * FROM STUDENTS";  for (const soci::row& r : rs) {    std::cout << "Id: " << r.get<int>(0);    std::cout << ", Name: " << r.get<std::string>(1);    std::cout << ", Age: " << r.get<int>(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 <iostream>#include <string>#include <vector>#include <list>#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<<(std::ostream& out, const TEACHER& teacher) {  out << "Id: " << teacher.id << ", Name: " << teacher.name << ", Age: " << teacher.age;  return out;}namespace soci {template<>struct type_conversion<TEACHER> {  typedef values base_type;  static void from_base(const values& v, indicator ind , TEACHER& teacher) {    teacher.id = v.get<int>("ID");    teacher.name = v.get<std::string>("NAME");    teacher.age = v.get<int>("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 socistatic 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::rowset<TEACHER> teachers = 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;}