mysql++ Learning (v)------Dedicated SQL structure

The dedicated SQL structure (SSQLS) feature allows you to easily define C + + structures that match SQL tables. The most common understanding: for each field in the SQL table, the SSQLS structure has a variable corresponding to it. However, other methods of SSQLS are also used within the mysql++, Operators to provide concise functionality.

To define ssqlses, you need to use a macro defined in Ssqls.h, which is also the only header file that mysql++ does not automatically join Mysql++.h.

Sql_create

If you have the following SQL table

CREATE TABLEStock (itemCHAR( -) not NULL, NumBIGINT  not NULL, WeightDOUBLE  not NULL, PriceDECIMAL(6,2) not NULL, Sdate DATE not NULL, Description MediumtextNULL)

You can define a corresponding C + + structure as follows

1 6 ,    Mysqlpp::sql_char, item,    mysqlpp::sql_bigint, num,    mysqlpp::sql_double, weight,    MYSQLPP:: Sql_decimal, Price,    mysqlpp::sql_date, sdate,    mysqlpp::null<mysqlpp::sql_mediumtext> Description

The stock structure declares that data members with the same name correspond to each column of SQL, and that the structure also contains some member functions, operators, and hidden data members.

sql_create_#In the call invocation, the field name precedes the C + + data type.

Sometimes you have to use the special data type of mysql++ to correspond to the data structure, consider the type of Description field, the sql_mediumtext type of mysql++ is an equivalent of std::string.

The usual structure is as follows:

sql_create_# (NAME, Compcount, SetCount, TYPE1, ITEM1, ...) type#, item#)

"#" is the number of variables, "name" is the structure name you want to create TYPEx , "" is the variable ITEMx type, "" is the variable name.

Ssqls comparison and initialization

Sql_create_# adds some member functions and actions to support comparisons between Ssqls objects.COMPCOUNT数量表示用来比较的字段的个数，上面例子中为1，意味着只比较item字段，将主键放到最前面并且指定比较个数，可以很好利用这一特性.sql_create_#创建的第二个参数是SETCOUNT,如果非0，它将增加一个构建函数和一个set()成员函数，该函数接受指定参数来设置结构第N个字段的值.

1 2 ,    Mysqlpp::sql_char, item,    mysqlpp::sql_bigint, num,             mysqlpp::sql_double, weight,      MYSQLPP:: Sql_decimal, Price,      mysqlpp::sql_date, Sdate,    mysqlpp::Null<mysqlpp::sql_ Mediumtext>, description)    ;

COMPCOUNTThe SETCOUNT values of the and are not equal, and if they are equal, the two build functions that produce the same argument list will be generated.

Querying data

Mysqlpp::query Query = conn.query ("Select item, description from stock"); Vector <stock> Res;query.storein (res);  for (Auto i = Res.begin (); I! = Res.end (); i++)    {'\ t'  ;     << i->description << Endl;}

After defining the SSQLS structure, the query structure can be placed directly into the corresponding container for various operations.

Add data

//Create a Stock objectStock New_row ("Hot Dog", -,1.5, the, Mysqlpp::sql_date ("2014-11-30"), MYSQLPP::NULL     );//Execute a queryMysqlpp::query Query =conn.query (); Query.insert (New_row); cout<<"Query:"<< Query <<Endl;query.execute ();

First, create a Ssqls object to insert, then create the query object, and finally execute the

Bulk Insert is also easy, just add a loop

vector<stock> lots_of_stuff;...populate the vector somehow...query.insert (Lots_of_stuff.begin (), lots_of_ Stuff.end ()). Execute ();

MySQL has a limit of SQL query length, the default maximum is 1M. Therefore, when you use Insert BULK INSERT, the query length in the container may exceed the limit.

In this case, use Query::insertfrom (), which is the insert andexecute()的组合.

Mysqlpp::query::maxpacketinsertpolicy<> Insert_policy (+); Query.insertfrom (Stock_ Vector.begin (), Stock_vector.end (), insert_policy);

It uses the maximum package policy, which specifies a maximum of 1000 for a query, which automatically detects whether the query exceeds the limit and then splits the execution query.

There are also two policy classes: Maxpacketinsertpolicy and Rowcountinsertpolicy, which provide a strategy method for querying length in a certain range of classes, and if not, you can also study the lib/insertpolicy.*, Then write your own policy class. Policy classes are essentially class templates.

modifying data

//Execute a queryMysqlpp::query Query = Conn.query ("SELECT * from stock"); query<<"Where item ="<< Mysqlpp::quote <<"ABC";//get query result in a storeresultMysqlpp::storequeryresult res =Query.store ();if(res) {stock row= res[0]; Stock Orig_row=Row; Row.Item="Hamburger";        Query.update (Orig_row, Row); cout<<"Query:"<< Query <<Endl; Query.execute ();}

First take the record that you want to modify, then modify the field, then replace the original field with the new field, and then perform the substitution operation.

Use associative containers to store SSQLS

//Execute a queryMysqlpp::query Query = Conn.query ("SELECT * from stock");//get query result in a storeresultSet<stock>Res;query.storein (res);if(Res.size ()) { for(Auto i = Res.begin (); I! = Res.end (); + +i) {cout<< i->item.c_str () <<'\ t'<< I->num <<'\ t'<< I->price <<Endl; }}

Change table name

By default, the database table and the SSQLS structure have the same table name. You can also modify the table names used in the query globally by using the following methods.

Stock::table("Mystockdata");

You can also modify the name of a table in each instance

Stock S;s.instance_table ("alternatetable");

Using SSQLS in multiple modules

// File my_ssqls.h: #if !defined (expand_my_ssqls_statics)#   define mysqlpp_ssqls_no_statics#endif   // the ssqls definition

// File foo.cpp, a mere user of the Ssqls: " My_ssqls.h "

// File My_ssqls.cpp, which owns the SSQLS: #define Expand_my_ssqls_statics"my_ssqls.h"

Using the internals of the SSQLS

Sql_create macros define useful methods for each ssqls, most of which are used inside the library, and the following pseudo-code shows some usage.

//Basic FormTemplate <classManip>stock_value_list<Manip> value_list (CChar *d =",", Manip m= Mysqlpp::quote)Const; Template<classManip>stock_field_list<Manip> field_list (CChar *d =",", Manip m= MYSQLPP::d o_nothing)Const; Template<classManip>stock_equal_list<Manip> equal_list (CChar *d =",", CChar*e =" = ", Manip m = mysqlpp::quote)Const; //Boolean argument formTemplate <classManip>stock_cus_value_list<Manip> value_list ([CChar *d, [Manip M,]]BOOLI1,BOOLI2 =false, ... ,BOOLi5 =false)Const; //List FormTemplate <classManip>stock_cus_value_list<Manip> value_list ([CChar *d, [Manip M,]] stock_enum i1, Stock_enum i2=Stock_null, Stock_enum i5= Stock_null)Const; //Vector FormTemplate <classManip>stock_cus_value_list<Manip> value_list ([CChar *d, [Manip m,]] vector<BOOL> *i)Const; ... Plus the obvious equivalents forField_list () and Equal_list ()

The following example shows a record of a query to build a query

Use different field names in C + + and SQL

1 5 ,       Mysqlpp::sql_char, M_sitem, "item",    Mysqlpp::sql_bigint, m_nnum, "num",    mysqlpp::sql_double, m_ Fweight, "Weight",    mysqlpp::sql_decimal, M_fprice, "Price",    mysqlpp::sql_date, M_date, "Sdate")

Inherit from a Ssqls

sql_create_2 (Base,1,2, Mysqlpp::sql_varchar, A, mysqlpp::sql_int, b);classDerived: Publicbase{ Public:  //default constructor[14]Derived (): Base () {}//For-comparison constructor[15]Derived (Constmysqlpp::sql_varchar&_a): Base (_a) {}//Full Creation ConstructorDerived (Constmysqlpp::sql_varchar& _a,Constmysqlpp::sql_int&_b): Base (_a, _b) {}//population constructor[16]Derived (Constmysqlpp::row&row): Base (Row) {}//functionality added to the SSQLS through inheritance  BOOLDo_something_interesting (intdata);};

mysql++ Learning (v)------Dedicated SQL structure