- 将sql语句与代码分离解耦，存放于xml配置文件中
- 用逻辑标签控制SQL的拼接
- 查询的结果集与C++对象自动映射
- cpp_xml.py 将xml定义的sql语句转成c++代码
- sql_handler.h 定义了两组执行sql、获取结果的接口。分别是ColumnIndexSqlHandler通过index获取结果、ColumnLabelSqlHandler通过字段名获取结果。
- cpp_sql.h 利用mysql-connection-c++实现了ColumnLabelSqlHandler接口
- c_sql.h 利用mysql-connection-c 实现了ColumnIndexSqlHandler接口
yum install -y mariadb-server mariadbwget https://dev.mysql.com/get/Downloads/Connector-C++/mysql-connector-c++-1.1.9-linux-el7-x86-64bit.rpm rpm -Uvh mysql-connector-c++-1.1.9-linux-el7-x86-64bit.rpm<select id="方法名" param_type="传入参数对象" result_type="返回结果列表对象类型"></select> <update id="方法名" param_type="传入参数对象"> </update> <insert id="方法名" param_type="传入参数对象"> </insert> <delete id="方法名" param_type="传入参数对象"> </delete> // #{} 形式的变量将会替换 <if test="!#{passwd}.empty()"> and `passwd` = #{passwd} </if> foreach标签支持4个属性
<foreach collection="#{name}" open="(" close =")" separator=","></foreach> 例：
<select id="GetUserInfoByName" param_type="GetUserInfoByNameParam" result_type="GetUserInfoByNameResult"> <if test="#{name}.size() > 0"> select `user_id`,`name`,`passwd`,`state_id` from `user_info` where `name` in <foreach collection="#{name}" open="(" close =")" separator=","></foreach> </if> </select> 以查询下面这个user_info表为例：
CREATE TABLE `user_info` ( `user_id` int(11) AUTO_INCREMENT PRIMARY KEY, `name` varchar(50) NOT NULL, `passwd` varchar(50) NOT NULL, `state_id` int(4) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;- 定义xml
<?xml version="1.0" encoding="UTF-8"?> <mapper namespace="get_user"> <!-- 传入参数用于拼接sql --> <!-- name应和 sql 表中的字段同名 --> <GetUserInfoParam> <field name="name" type="string" ></field> <field name="passwd" type="string" ></field> <field name="state_id" type="int32" ></field> </GetUserInfoParam> <!-- 返回的结果对象 --> <!-- 如果使用index获取结果 field定义的顺序需要和select的顺序保持一致 --> <GetUserInfoEntity> <field name="user_id" type="uint64"></field> <field name="name" type="string"></field> <field name="passwd" type="string" ></field> <field name="state_id" type="int32" ></field> </GetUserInfoEntity> <!-- id:方法名 --> <!-- param_type:参数类 --> <!-- result_type:结果类 --> <select id="GetUserInfo" param_type="GetUserInfoParam" result_type="GetUserInfoEntity"> select `user_id`,`name`,`passwd`,`state_id` from `user_info` where 1 = 1 <if test="!#{name}.empty()"> and `name` = #{name} </if> <if test="!#{passwd}.empty()"> and `passwd` = #{passwd} </if> <if test="#{state_id} > 0"> and `state_id` = #{state_id} </if> </select> </mapper> 将其保存为get_user.xml
- 转cpp代码
python cpp_xml.py get_user.xml #生成相应cpp代码生成的相应cpp代码中包含以下内容：
- 作为查询条件出入的参数类
struct GetUserInfoParam { std::string name; std::string passwd; int32_t state_id; };- 获取一行结果的类
struct GetUserInfoEntity { uint64_t user_id; std::string name; std::string passwd; int32_t state_id; };- 根据查询条件类拼sql的函数
std::string GetUserInfoSql (CommonSqlHandler *sql_handler, const GetUserInfoParam ¶m) { ostringstream ss; ss << " select `user_id`,`name`,`passwd`,`state_id`"; ss << " from `user_info`"; ss << " where 1 = 1"; if (!param.name.empty()) { ss << " and `name` = '" + sql_handler->EscapeString(param.name) + "'"; } if (!param.passwd.empty()) { ss << " and `passwd` = '" + sql_handler->EscapeString(param.passwd) + "'"; } if (param.state_id > 0) { ss << " and `state_id` = '" + to_string(param.state_id) + "'"; } return ss.str(); } - 执行sql并获取结果集
std::vector<GetUserInfoEntity> GetUserInfo (ColumnLabelSqlHandler *sql_handler, const GetUserInfoParam ¶m) { std::vector<GetUserInfoEntity> ret; auto sql = GetUserInfoSql(sql_handler, param); sql_handler->ExecuteQuery(sql); while(sql_handler->Next()) { ret.emplace_back(); ret.back().user_id = sql_handler->GetUInt64("user_id"); ret.back().name = sql_handler->GetString("name"); ret.back().passwd = sql_handler->GetString("passwd"); ret.back().state_id = sql_handler->GetInt("state_id"); } return ret; }