架构设计
248
简介
SOCI是用C ++编写的数据库访问库，使人将SQL查询嵌入常规C ++代码中，而完全位于标准C ++中。
这个想法是为C ++程序员提供一种以最自然，最直观的方式访问SQL数据库的方法。如果您发现现有库太难满足您的需求或分散注意力，那么SOCI可能是一个不错的选择。
当前支持的后端：
一、连接测试
#include <iostream> #include <exception> #include <soci/soci.h> #include <soci/mysql/soci-mysql.h> void connectTest() { try { soci::session sql(soci::mysql, "host=127.0.0.1 dbname=mydb user=root password='123456'"); soci::rowset<soci::row> rs = (sql.prepare << "select Id, username,age FROM student"); for (auto it = rs.begin(); it != rs.end(); ++it) { const soci::row& row = *it; std::cout << "id:" << row.get<uint>("Id") << " username:" << row.get<std::string>("username") << " age:" << row.get<int>("age") << std::endl; } } catch(soci::soci_error & e) { std::cout << e.what() << std::endl; } }
二、查询
1、单行查询
soci::row itRet; (sqlSession << strSql),into(itRet); if (!sqlSession.got_data()） return; auto val = itRet.get<std::string>(0);
2、多行查询
soci::rowset<soci::row> rslt = (sqlSession.prepare << strSql); for (auto& itRet : rslt) { }
3、多行查询存放到列表
void queryTest() { try { soci::session sqlSession(soci::mysql, "host=127.0.0.1 dbname=mydb user=root password='123456'"); std::vector<std::string> vecStudent(100); std::vector<int> vecAge(100); sqlSession << "select username,age FROM student", soci::into(vecStudent), soci::into(vecAge); } catch(soci::soci_error & e) { std::cout << e.what() << std::endl; } }
4、多行全表数据查询
soci::row itRet; soci::statement st = ((sqlSession.prepare << strSQL),soci::into(itRet)); st.execute(); //获取字段信息 std::vector<std::string> vecFieldList; for (std::size_t i = 0; i != itRet.size(); ++i) { const soci::column_properties & props = itRet.get_properties(i); std::string strField = props.get_name(); vecFieldList.push_back(strField); } while (st.fetch()) { for (auto i = 0; i < itRet.size(); i++) { std::string strValue = GetFieldValue(itRet, i); } }
三、更新、插入、删除
1、简易操作
auto username = "admin"; auto age = 10; sqlSession << "insert into student(username, age) values(:username, :age)", use(username), use(age);
2、获取影响行数
statement st = (sqlSession.prepare << "delete from student where id=:id", use(id)); st.execute(true); int affected_rows = st.get_affected_rows();
四、多线程下连接池
int g_pool_size = 5; soci::connection_pool g_pool(g_pool_size); void init_pool() { for (int i = 0; i < g_pool_size; ++i) { session& sql = g_pool.at(i); sql.open(soci::mysql, "host=127.0.0.1 dbname=mydb user=root password='123456'"); } } soci::session sql(g_pool); sqlSession << "insert into student(username, age) values(:username, :age)", soci::use(username), soci::use(age);
五、泛型加载，统一转换成字符型
std::string GetFieldValue(const soci::row& itRet, int pos) { const soci::column_properties & props = itRet.get_properties(pos); if (itRet.get_indicator(pos) == soci::i_null) { return ""; } switch (props.get_data_type()) { case soci::dt_string: return itRet.get<std::string>(pos); break; case soci::dt_double: return std::to_string(itRet.get<double>(pos)); break; case soci::dt_integer: return std::to_string(itRet.get<int>(pos)); break; case soci::dt_long_long: return std::to_string(itRet.get<long long>(pos)); break; case soci::dt_unsigned_long_long: return std::to_string(itRet.get<unsigned long long>(pos)); break; case soci::dt_date: std::tm when = itRet.get<std::tm>(pos); return asctime(&when); break; } return std::string(); } soci::rowset<soci::row> rs = (sqlSession.prepare << "select Id, username,age FROM student"); for (auto& it : rs) { std::cout << "username:" << GetFieldValue(it, 0) << " age:" << GetFieldValue(it, 1) << std::endl; }
六、简化数据读取，防止空数据导致异常
template<typename T, typename V> void GetFieldValue(const soci::row& itRet, T at, V& val) { val = itRet.get<V>(at, val); } template<typename V> void GetFieldValue(const soci::row& itRet, int pos, V& val) { val = itRet.get<V>(pos, val); } template<typename V> void GetFieldValue(const soci::row& itRet, const char* field, V& val) { val = itRet.get<V>(field, val); } soci::rowset<soci::row> rs = (sqlSession.prepare << "select Id, username,age FROM student"); for (auto& it : rs) { std::stirng name; int age = -1; GetFieldValue(it, 0, name); GetFieldValue(it, 1, age); std::cout << "username:" << name << " age:" << age << std::endl; }
七、ORM
struct Person { int id; std::string firstName; std::string lastName; std::string gender; }; namespace soci { template<> struct type_conversion<Person> { typedef values base_type; static void from_base(values const & v, indicator /* ind */, Person & p) { p.id = v.get<int>("ID"); p.firstName = v.get<std::string>("FIRST_NAME"); p.lastName = v.get<std::string>("LAST_NAME"); // p.gender will be set to the default value "unknown" // when the column is null: p.gender = v.get<std::string>("GENDER", "unknown"); // alternatively, the indicator can be tested directly: // if (v.indicator("GENDER") == i_null) // { // p.gender = "unknown"; // } // else // { // p.gender = v.get<std::string>("GENDER"); // } } static void to_base(const Person & p, values & v, indicator & ind) { v.set("ID", p.id); v.set("FIRST_NAME", p.firstName); v.set("LAST_NAME", p.lastName); v.set("GENDER", p.gender, p.gender.empty() ? i_null : i_ok); ind = i_ok; } }; } session sql(oracle, "service=db1 user=scott password=tiger"); Person p; p.id = 1; p.lastName = "Smith"; p.firstName = "Pat"; sql << "insert into person(id, first_name, last_name) " "values(:ID, :FIRST_NAME, :LAST_NAME)", use(p); Person p1; sql << "select * from person", into(p1); assert(p1.id == 1); assert(p1.firstName + p.lastName == "PatSmith"); assert(p1.gender == "unknown"); p.firstName = "Patricia"; sql << "update person set first_name = :FIRST_NAME " "where id = :ID", use(p);
