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表为例:
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;
}