cpp easy sql简介

• 将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接口

安装依赖

安装mysql

yum install -y mariadb-server mariadb

安装mysql-connector-c++

wget 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

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表为例：

CREATETABLE `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;

1. 定义xml

<?xml version="1.0" encoding="UTF-8"?>
<mappernamespace="get_user">
<!-- 传入参数用于拼接sql --><!-- name应和 sql 表中的字段同名 -->
<GetUserInfoParam>
<fieldname="name"type="string" ></field>
<fieldname="passwd"type="string" ></field>
<fieldname="state_id"type="int32" ></field>
</GetUserInfoParam>
<!-- 返回的结果对象 --><!-- 如果使用index获取结果 field定义的顺序需要和select的顺序保持一致 -->
<GetUserInfoEntity>
<fieldname="user_id"type="uint64"></field>
<fieldname="name"type="string"></field>
<fieldname="passwd"type="string" ></field>
<fieldname="state_id"type="int32" ></field>
</GetUserInfoEntity>
<!-- id:方法名 --><!-- param_type:参数类 --><!-- result_type:结果类 -->
<selectid="GetUserInfo"param_type="GetUserInfoParam"result_type="GetUserInfoEntity">
select `user_id`,`name`,`passwd`,`state_id`
from `user_info`
where 1 = 1
<iftest="!#{name}.empty()">
and `name` = #{name}
</if>
<iftest="!#{passwd}.empty()">
and `passwd` = #{passwd}
</if>
<iftest="#{state_id} > 0">
and `state_id` = #{state_id}
</if>
</select>
</mapper>

将其保存为get_user.xml

2. 转cpp代码

python cpp_xml.py get_user.xml #生成相应cpp代码

生成的相应cpp代码中包含以下内容：

• 作为查询条件出入的参数类

structGetUserInfoParam {
std::string name;
std::string passwd;
int32_t state_id;
};

• 获取一行结果的类

structGetUserInfoEntity {
uint64_t user_id;
std::string name;
std::string passwd;
int32_t state_id;
};

• 根据查询条件类拼sql的函数

std::string GetUserInfoSql (CommonSqlHandler *sql_handler, const GetUserInfoParam &param) {
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 &param) {
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;
}