Tutorials for using C + + to encapsulate the MySQL API

In fact, I believe that every programmer and MySQL have dealt with the programmers should try to encapsulate a set of MySQL interface, this time the package has not been able to remember the first few times, but every time I want to do better than last time, easier to use.

First of all, the package is followed by several principles, some of which are borrowed from Python:

1. Simple

Simplicity means that the interface is not complicated for the sake of minor efficiency gains. Because the bottleneck of database storage efficiency is not the one or two memory copy, the design based on this can be seen everywhere in the code.
2. Low learning Costs

The use of a new set of libraries usually means the cost of learning, and this time the package does not implement a complete set of model systems like Django, and does not do SOCI as a parser, I choose the most straightforward way: To do SQL statement splicing, so accustomed to use the native MySQL API friends, Learning costs are low
3. Modular

The code actually consists of two modules, one is the MySQL client side of the package, and the other is a SQL splicing, the two modules are completely independent, the caller can be any combination or independent use.
4. Use STL and templates as much as possible to simplify code writing

The biggest feature is the massive use of StringStream for type conversion, which reduces the amount of repetitive code.

OK, based on the above simple introduction, let's look first
A. mysql client-side encapsulation:

Class cmysqlwrapper{/** * @brief get error message * * @return error message */char* geterrmsg ();/** * @brief connection to MySQL, Automatic reconnection mode is supported, i.e. Mys QL server close link will automatically re-connect * * @param IP IP * @param user username * @param pwd password (no null) * @param DB Library (no null) * * @return 0 SUCC * Else fail */int Open (const char* ip,const char* user,const char* pwd,const char* strDB); /** * @brief close link and release result */void close (); /** * @brief Execute SQL statement * * @param strsql EXECUTE statement * @param result Execution Results * * @return 0 SUCC * Else fail */int Quer Y (const char* strSQL); /** * @brief Query related to read (select), can support blob * * @param strsql SQL statement * @param vecdata rows * * @return 0 su CC * ELSE fail */int Query (const char* strSQL, Vector
 
  
 > &vecdata); /** * @brief for Write (insert,update,delete)-related query * * @param strsql SQL statement * @param number of rows affected by Affectrowscount * @r Eturn 0 SUCC * Else fail */int Query (const char* strSQL, int& affectrowscount); /** * Get Data @brief Select, remember to refactor manually, or execute results with stmysqlres * * @param result * * @return 0 SUCC * Else fail */int Re Sult (Mysql_res *&result); /** * @brief Returns the number of rows affected * * @return >0 succ * 0 Not updated * <0 fail */int affectedrows (); /** * @brief mainly converts blobs into strings * * @param src blob source * @param len Length * * @return Converted String */String escstr (const char * src,uint32_t Len); /** * @brief convert some characters in a string (such as ') * * @param src String * * @return Converted String */String escstr (const char* src);};  Class cmysqlwrapper{/** * @brief get error message * * @return error message */char* geterrmsg (); /** * @brief connection to MySQL, the automatic reconnection mode is supported, that is, the MySQL server close link will automatically reconnect * * @param IP IP * @param user username * @param pwd password (n ULL) * @param DB Library (Null is not passed) * * @retUrn 0 SUCC * else fail */int Open (const char* ip,const char* user,const char* pwd,const char* strDB);  /** * @brief close link and release result */void close (); /** * @brief Execute SQL statement * * @param strsql EXECUTE statement * @param result Execution Results * * @return 0 SUCC * Else fail */int Quer  Y (const char* strSQL); /** * @brief Query related to read (select), can support blob * * @param strsql SQL statement * @param vecdata rows * * @return 0 su CC * ELSE fail */int Query (const char* strSQL, Vector
  
   <>
   
    > &vecdata); /** * @brief for Write (insert,update,delete)-related query * * @param strsql SQL statement * @param number of rows affected by Affectrowscount * @r   Eturn 0 SUCC * Else fail */int Query (const char* strSQL, int& affectrowscount); /** * Get Data @brief Select, remember to refactor manually, or execute results with stmysqlres * * @param result * * @return 0 SUCC * Else fail */int Re  Sult (Mysql_res *&result);  /** * @brief Returns the number of rows affected * * @return >0 succ * 0 Not updated * <0 fail */int affectedrows (); /** * @brief mainly converts blobs into strings * * @param src blob source * @param len Length * * @return Converted String */String escstr (const char  * src,uint32_t Len);
/** * @brief convert some characters in a string (such as ') * * @param src String * * @return Converted String */String escstr (const char* src);}; 
    
   
  
 

The comments in the code have been described very clearly, the language description is not clear, we directly look at the Gtest code:

Select:string g_name = "good"; int g_sex = 1;string g_name_up = "Update"; int g_sex_up = 2; TEST (Mysql_wrapper_easy, select) {vector
 
  > vecdata; String sql = "SELECT * from tb_test WHERE name = '" +g_name_up+ "'"; int ret = g_client. Query (Sql.c_str (), vecdata); ASSERT_EQ (ret, 0) << g_client. Geterrmsg ();   foreach (Vecdata, It_vec) {foreach (*it_vec, it_map) {cout << it_map->first << ","; if (It_map->first = = "Sex") {cout << it_map->second.as
  
   ();   } else {cout << it_map->second.data ();  } cout << "," << it_map->second.size () << Endl; }}}int Main (int argc, char **argv) {int ret = g_client. Open ("localhost", "Dantezhu", NULL, "Soci"); int ret = g_client. Open ("127.0.0.1", "Dantezhu", NULL, "Soci"); if (ret) {cout << ret << "," << g_client.  Geterrmsg () << Endl; return-1; }:: Testing::initgoogletest (&AMP;ARGC, argv); return run_all_tests ();} String g_name = "good"; int g_sex = 1; String g_name_up = "Update"; int g_sex_up = 2; TEST (Mysql_wrapper_easy, select) {vector
   
    > vecdata; String sql = "SELECT * from tb_test WHERE name = '" +g_name_up+ "'"; int ret = g_client. Query (Sql.c_str (), vecdata); ASSERT_EQ (ret, 0) << g_client.  Geterrmsg ();   foreach (Vecdata, It_vec) {foreach (*it_vec, it_map) {cout << it_map->first << ","; if (It_map->first = = "Sex") {cout << it_map->second.as
    
     
();   } else {cout << it_map->second.data ();  } cout << "," << it_map->second.size () << Endl; }}}int Main (int argc, char **argv) {int ret = g_client. Open ("localhost", "Dantezhu", NULL, "Soci"); int ret = g_client. Open ("127.0.0.1", "Dantezhu", NULL, "Soci"); if (ret) {cout << ret << "," << g_client.  Geterrmsg () << Endl; return-1; }:: Testing::initgoogletest (&AMP;ARGC, argv); return run_all_tests ();} Insert:test (Mysql_wrapper_easy, insert) {clear_data (); StringStream SS; SS << "INSERT into Tb_test (name,sex) value S (' "<< g_client. Escstr (G_name.c_str ()) << "'," << g_sex << "); int affectrowsnum; int ret = g_client. Query (Ss.str (). C_STR (), affectrowsnum); ASSERT_EQ (ret, 0) << g_client. Geterrmsg (); Expect_ge (affectrowsnum,0) << g_client. Geterrmsg ();} TEST (Mysql_wrapper_easy, insert) {clear_data (); StringStream SS; SS << "INSERT into Tb_test (name,sex) VALUES ('" & lt;< G_client.  Escstr (G_name.c_str ()) << "'," << g_sex << "); int affectrowsnum; int ret = g_client. Query (Ss.str (). C_STR (), affectrowsnum); ASSERT_EQ (ret, 0) << g_client.  Geterrmsg (); Expect_ge (affectrowsnum,0) << g_client. Geterrmsg ();}
    
   
  
 

As you can see, the transceiver for MySQL is very concise, but the SQL statement is very cumbersome to assemble. So this time the SQL statement Builder-sqljoin!
Two. SQL Statement Builder-sqljoin

Class Sqljoin{public:/** * @brief with stream processing, add a column name * * @param key Column name * * @return 0 */sqljoin& operator &LT;&LT ; (const string& key); /** * @brief Add a Sqlpair object with stream processing * * @param pair_data Sqlpair Object * * @return 0 */sqljoin& operator << ( Const sqlpair& Pair_data); /** * @brief Output all column names (such as name, sex, age) * * @return All column names */string keys (); /** * @brief output all column values (such as ' Dante ', 1, *) * @return All column values */String value (); /** * @brief Enter all column names-column values, split with the specified delimiter (such as Name= ' Dante ', Sex=1, age=25) * @param split_str delimiter, by default ', ' or with and, or, and so on * @ret Urn All column names-column values */string pairs (const string& SPLIT_STR = ","); /** * @brief Clear all data */void Clear ();}; Class Sqljoin{public:/** * @brief with stream processing, add a column name * * @param key Column name * * @return 0 */sqljoin& operator &LT;&LT ;  (const string& key); /** * @brief Add a Sqlpair object with stream processing * * @param pair_data Sqlpair Object * * @return 0 */sqljoin& operator << (  Const sqlpair& Pair_data); /** * @brieF Output All column names (such as name, sex, age) * * @return All column names */string keys ();  /** * @brief output all column values (such as ' Dante ', 1, *) * @return All column values */String value (); /** * @brief Enter all column names-column values, split with the specified delimiter (such as Name= ' Dante ', Sex=1, age=25) * @param split_str delimiter, by default ', ' or with and, or, and so on * @ret  Urn All column names-column values */string pairs (const string& SPLIT_STR = ","); /** * @brief Clear all data */void Clear ();};

See how the code after we used the Sqljoin should:

TEST (Mysql_wrapper_join, insert) {clear_data (); Sqljoin Sql_join; Sql_join << Sqlpair ("name", G_client. Escaperealstring (G_name.c_str ())) << Sqlpair ("Sex", g_sex); StringStream SS;  SS << "INSERT INTO Tb_test (" << Sql_join.keys () << ") VALUES (" << sql_join.values () << ")"; int affectrowsnum; int ret = g_client. Executewrite (Ss.str (). C_STR (), affectrowsnum); ASSERT_EQ (ret, 0) << g_client. Geterrmsg (); Expect_ge (affectrowsnum,0) << g_client. Geterrmsg ();} TEST (mysql_wrapper_join, update) {Sqljoin sql_join; Sql_join << sqlpair ("name", g_name_up) << sqlpair ("Sex" , g_sex_up); StringStream SS; SS << "Update tb_test Set" << sql_join.pairs () << "where Name= '" << g_name << "';"; int affectrowsnum; int ret = g_client. Executewrite (Ss.str (). C_STR (), affectrowsnum); ASSERT_EQ (ret, 0) << g_client. Geterrmsg (); Expect_ge (affectrowsnum,0) << g_client. Geterrmsg ();} TEST (Mysql_wrapper_join, insert) {clear_data (); Sqljoin Sql_join; Sql_join << Sqlpair ("name", G_client.  Escaperealstring (G_name.c_str ())) << Sqlpair ("Sex", g_sex); StringStream SS;  SS << "INSERT INTO Tb_test (" << Sql_join.keys () << ") VALUES (" << sql_join.values () <<  ")"; int affectrowsnum; int ret = g_client. Executewrite (Ss.str (). C_STR (), affectrowsnum); ASSERT_EQ (ret, 0) << g_client.  Geterrmsg (); Expect_ge (affectrowsnum,0) << g_client. Geterrmsg ();} TEST (mysql_wrapper_join, update) {Sqljoin sql_join; Sql_join << sqlpair ("name", g_name_up) << sqlpair ("Sex"  , g_sex_up); StringStream SS; SS << "Update tb_test Set" << sql_join.pairs () << "where Name= '" << g_name << "';"; int affectrowsnum; int ret = g_client. Executewrite (Ss.str (). C_STR (), affectrowsnum); ASSERT_EQ (ret, 0) << g_client.  Geterrmsg (); Expect_ge (affectrowsnum,0) << g_client. Geterrmsg ();}

As you can see from the code above, the maintainability and robustness of the code has been greatly improved.

OK, Simple introduction is this, said the relatively brief, we are interested can directly see the code, but also welcome to give me advice and suggestions. The code download path is as follows:
Mysql_wrapper

Tomorrow this code will formally enter the production environment as the database access layer code, so there is any bug I will update here in time.

