简介
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);
