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