1. Reasons for choosing soci
slightly
2. Characteristics of soci
① Enter sql statements in stream mode
② Pass and parse parameters through into and use syntax
③ Support connection pool and thread safety
- remarks:
It can be seen that it is only a lightweight package, so it also has greater flexibility. The back end supports oracle, mysql, etc. subsequent examples are based on MySQL
3.linux Installation Steps
① Project address: https://github.com/SOCI/soci
② Download and compile instructions
git clone https://github.com/SOCI/soci.git cd soci mkdir build cd build cmake .. -G "Unix Makefiles" -DCMAKE_INSTALL_PREFIX=/opt/third_party/soci make sudo make install
4. Query examples and query steps
1) Table structure
CREATE TABLE `Person` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `first_name` varchar(64) NOT NULL DEFAULT '', `second_name` varchar(64) NOT NULL DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2) Initialize session
using namespace soci;
session sql("mysql", "dbname=test user=your_name password=123456");
The first parameter is the back-end database type used, and the second parameter is the database connection parameter. The parameters that can be specified include host port dbname user passowrd, which are separated by spaces
3) Insert insert table
string first_name = "Steve";
string last_name = "Jobs";
sql << "insert into Person(first_name, last_name)"
" values(:first_name, :last_name)",
use(first_name), use(last_name);
long id;
sql.get_last_insert_id("Person", id)
- Supplement (matters needing attention):
① The sql statement is passed through the stream, and the parameters are passed through the use syntax
② Where Person(first_name, last_name) is the database table name and column name, and values (: first_name,: last_name) is the placeholder for parameters, which can be written here and get_ last_ insert_ The ID function can get the return value of the self growing field
③ It should be noted that the life cycle of the parameters in the use function. Remember that the return value of the function cannot be used as the parameters of the use function
4) select lookup table
int id = 1;
string first_name;
string last_name;
sql << "select first_name, last_name from Person where id=:id ",
use(id), into(first_name), into(last_name);
if (!sql.got_data())
{
cout << "no record" << endl;
}
- Remarks:
① Here, first is queried according to the id field_ Name and last_name two fields, and copy the data to the variable through the into function, got_ The data () method can determine whether there is data returned
② When the ID is an integer, the sql statement can also be written as sql < < select Balabala from person where id = "< < ID, but when the ID is a string, an error will be reported. Therefore, it is recommended to use the use function
③ If the query result is multi row data, you need to use rowset type and extract it yourself
rowset<row> rs = (sql.prepare << "select * from Person");
for (rowset<row>::iterator it = rs.begin(); it != rs.end(); ++it)
{
const row& row = *it;
cout << "id:" << row.get<long long>(0)
<< " first_name:" << row.get<string>(1)
<< " last_name:" << row.get<string>(2) << endl;
}
④ Here, the parameter type of the get template must correspond to the database type one by one, the varchar and text types correspond to string, and the integer type corresponds to the following relationship
5) Update update
int id = 1;
string first_name = "hello";
string last_name = "world";
sql << "update Person set first_name=:first_name, last_name=:last_name"
" where id=:id",
use(first_name), use(last_name), use(id);
6)delete
int id = 1; sql << "delete from Person where id=:id", use(id);
- Sometimes we need to pay attention to whether the delete operation really deletes the data. mysql itself will also return the number of rows affected by the operation, which can be obtained by the following methods
statement st = (sql.prepare << "delete from Person where id=:id", use(id)); st.execute(true); int affected_rows = st.get_affected_rows();
5. Connection pool
Using the connection pool can solve the problem of multithreading. When each thread operates the database, it first takes out a session from the connection pool. This session will be set to lock, and then change back to unlock. In this way, different threads use different sessions without affecting each other. Session objects can be constructed with connection pools. They are automatically locked during construction and unlocked during deconstruction
int g_pool_size = 3;
connection_pool g_pool(g_pool_size);
for (int i = 0; i < g_pool_size; ++i)
{
session& sql = g_pool.at(i);
sql.open("mysql", "dbname=test user=zhangmenghan password=123456");
}
session sql(g_pool);
sql << "select * from Person";
At this time, there is no timeout for the call of session sql(g_pool). If there is no available session, it will be blocked all the time. If you want to set the timeout, you can use connection_ Bottom interface of pool
session & at(std::size_t pos); bool try_lease(std::size_t & pos, int timeout); void give_back(std::size_t pos);
The calling method is as follows
size_t pos
if (!try_lease(pos, 3000)) // Lock the session and set the timeout to 3 seconds
{
return;
}
session& sql = g_pool.at(pos) // Get the session. At this time, the session corresponding to pos has been locked
/* sql Operation */
g_pool.give_back(pos); // Unlock the session corresponding to pos
It should be noted that if try_ Give was not called after the lease call was successful_ Back, the corresponding session will always be locked, so try_ Leave and give_back must be used in pairs
6. Services
The session object provides methods to manipulate transactions
void begin(); void commit(); void rollback();
It also provides encapsulated transaction objects, which can be used as follows
{
transaction tr(sql);
sql << "insert into ...";
sql << "more sql queries ...";
// ...
tr.commit();
}
If the commit is not executed, the transaction object will automatically call the rollback method of the session object when it is destructed
7.ORM
soci can directly use user objects in the use and into syntax by customizing the object conversion method. For example, for the Person form, we define the following structure and conversion function
struct Person
{
uint32_t id;
string first_name;
string last_name;
}
namespace soci {
template<>
struct type_conversion<Person>
{
typedef values base_type;
static void from_base(const values& v, indicator ind, Person& person)
{
person.id = v.get<long long>("id");
person.first_name = v.get<string>("first_name");
person.last_name = v.get<string>("last_name");
}
static void to_base(const Person& person, values& v, indicator& ind)
{
v.set("id", (long long)person.id);
v.set("first_name", person.first_name);
v.set("last_name", person.last_name);
}
};
}
It should be noted that the parameter type of the get template here must correspond to the database field. See the previous select example for the corresponding relationship. For integer types, it is best to add strong conversion during set and be consistent with get, otherwise the exception STD:: bad may be thrown_ cast. The first parameter of the get and set functions is a placeholder. The name of the placeholder does not have to be consistent with the database column name, but the placeholder in the values syntax in subsequent operations must be consistent with that specified here
Type defined_ After conversion, you can directly use the Person object when using the use and into syntax. At this time, soci will specify the field according to the placeholder operation
- insert
Person person;
person.first_name = "Steve";
person.last_name = "Jobs";
sql << "insert into Person(first_name, last_name)"
" values(:first_name, :last_name)", use(person);
- select
int id = 1;
Person person;
sql << "select * from Person where id=:id", use(id), into(person);
rowset<Person> rs = (sql.prepare << "select * from Person");
for (rowset<Person>::iterator it = rs.begin(); it != rs.end(); ++it)
{
const Person& person = *it;
// do something with person
}
- update
person.id = 1;
person.first_name = "hello";
person.last_name = "world";
sql << "update Person set first_name=:first_name, last_name=:last_name"
" where id=:id", use(person);
- delete
Person person; person.id = 1; sql << "delete from Person where id=:id", use(person);