组织sql语句的类

    在进行数据库编程的时候，需要经常编写sql语句。在代码中直接写sql语句是不推荐的，我用c++封装了一个组织简单sql语句的方法。希望大家能够多多指教。如果有更加高效的方法，大家可以一起来讨论以下。下面附上我们源代码。我在资源里面也提供了下载，大家也可以去那边下载。

main.cpp

#include "SqlStatement.h"
using namespace std;
#include <iostream>


/**
 * 每次组装语句必须先调用SetTable，清理原来的数据，如果没有调用，会导致sql错乱的严重后果
 */
int main()
{
SqlStatement sqlSta;
/**简单查询语句**/
sqlSta.SetTable( "User" );
sqlSta.AddValue( "name" );
sqlSta.AddValue( "id" );
//select name,id from User;
cout << sqlSta.GetSelSqlStr() << endl;


sqlSta.SetTable( "User" );
//select * from User;
cout << sqlSta.GetSelSqlStr() << endl;


//select name,id from User where id=0;
sqlSta.SetTable( "User" );
sqlSta.AddValue( "name" );
sqlSta.AddValue( "id" );
sqlSta.AddWhere( "id", 0 );
sqlSta.AddWhere( "name", "a" );
//select name,id from User where id=0 and name='a';
cout << sqlSta.GetSelSqlStr() << endl;


sqlSta.SetTable( "User" );
sqlSta.AddValue( "name" );
sqlSta.AddValue( "id" );
sqlSta.AddWhere( "id", 0, SQL_CONTION_ENUM_OR, ">" );
sqlSta.AddWhere( "name", "a" );
//select name,id from User where id>0 or name='a';
cout << sqlSta.GetSelSqlStr() << endl;
/**简单查询语句**/




/**简单插入语句**/
sqlSta.SetTable( "User" );
sqlSta.AddValue( "id", 1 );
sqlSta.AddValue( "name", "abc" );
//insert into User (id,name) values(1,'abc');
cout << sqlSta.GetInsertSqlStr() << endl;
/**简单插入语句**/




/**简单插入语句,重复主键update**/
sqlSta.SetTable( "User" );
sqlSta.AddValue( "id", 1 );
sqlSta.AddValue( "name", "abc" );
sqlSta.AddValue( "money", 100 );
sqlSta.AddDup( "money", 100 );
//insert into User (id,name,money) values(1,'abc',100) ON DUPLICATE KEY UPDATE money=100;
cout << sqlSta.GetInsertDupSqlStr() << endl;
/**简单插入语句,重复主键update**/




/**简单修改**/
sqlSta.SetTable( "User" );
sqlSta.AddValue( "money", 100 );
//update User set money = 100;
cout << sqlSta.GetUpdateSqlStr() << endl;


sqlSta.SetTable( "User" );
sqlSta.AddValue( "money", 100 );
sqlSta.AddWhere( "id", 1 );
//update User set money = 100 where id=1;
cout << sqlSta.GetUpdateSqlStr() << endl;
/**简单修改**/




/**简单删除**/
sqlSta.SetTable( "User" );
sqlSta.AddWhere( "id", 1 );
//delete from User where id=1;
cout << sqlSta.GetDelSqlStr() << endl;
/**简单删除**/


return 0;
}

SqlStatement.h

#ifndef SQLSTATEMENT_H_
#define SQLSTATEMENT_H_


#include <string>
#include <map>
#include <vector>
using namespace std;
#include <algorithm>
#include <sstream>
static string NumToString( const int num )
{
std::stringstream ss;
ss << num;
return ss.str();
}


enum SqlSortType
{
SQL_SORT_TYPE_NULL,//没有排序
SQL_SORT_TYPE_ASC,//升序
SQL_SORT_TYPE_DESC,//降序
};


//sql条件
enum SqlContionEnum
{
SQL_CONTION_ENUM_ADN,
SQL_CONTION_ENUM_OR
};


class SqlStatement
{
public:
SqlStatement();
virtual ~SqlStatement();


void SetTable( const string& tableName );
void AddValue( const string& columnName );
void AddValue( const string& columnName, const int value );
void AddValue( const string& columnName, const string& value );


void AddWhere( const string& columnName, const int value, const SqlContionEnum condition = SQL_CONTION_ENUM_ADN );
void AddWhere( const string& columnName, const string& value, const SqlContionEnum condition = SQL_CONTION_ENUM_ADN );
void AddWhere( const string& columnName, const string& value, const SqlContionEnum condition, const string& compare = "=" );
void AddWhere( const string& columnName, const int value, const SqlContionEnum condition, const string& compare = "=" );


void AddDup( const string& columnName, const int value );
void AddDup( const string& columnName, const string& value );


void AddSort( const string& columnName, const SqlSortType sortType );


//获得简单查询的sql
const char* GetSelSql();
//获得简单查询的sql的的string形式
string GetSelSqlStr();


//获得简单插入的sql
const char* GetInsertSql();
//获得简单插入的sql的str形式
string GetInsertSqlStr();


//获得简单修改的sql语句的
const char* GetUpdateSql();
//获得简单修改的sql语句的str
string GetUpdateSqlStr();


//获得简单删除的sql语句的
const char* GetDelSql();
//获得简单删除的sql语句的的str
string GetDelSqlStr();


//获得插入简单插入on dupicate update的sql
const char* GetInsertDupSql();
//获得插入简单插入on dupicate update的sql的string形式
string GetInsertDupSqlStr();
int GetSqlLen();
private:
enum ColumnType
{
COLUMN_INT_TYPE = 1,//int类型
COLUMN_STR_TYPE = 2//str类型
};
struct SqlColumn
{
SqlColumn( const string& columnName, const int type, const int value )
: mColumnName( columnName ), mType( type ), mValue( value ), mCompare( "=" )
{
}


SqlColumn( const string& columnName, const int type, const int value, const SqlContionEnum condition )
: mColumnName( columnName ), mType( type ), mValue( value ), mCondition( condition ), mCompare( "=" )
{
}


SqlColumn( const string& columnName, const int type, const string& value, const SqlContionEnum condition )
: mColumnName( columnName ), mType( type ), mValueStr( value ), mCondition( condition ), mCompare( "=" )
{
}


SqlColumn( const string& columnName, const int type, const string& value, const SqlContionEnum condition, const string& compare )
: mColumnName( columnName ), mType( type ), mValueStr( value ), mCondition( condition ), mCompare( compare )
{


}


SqlColumn( const string& columnName, const int type, const int value, const SqlContionEnum condition, const string& compare )
: mColumnName( columnName ), mType( type ), mValue( value ), mCondition( condition ), mCompare( compare )
{
}


SqlColumn( const string& columnName, const int type, const string& mValueStr )
: mColumnName( columnName ), mType( type ), mValueStr( mValueStr ), mCompare( "=" )
{
}


SqlColumn( const string& columnName )
: mColumnName( columnName ), mType( 0 ), mValue( 0 ), mCompare( "=" )
{
}


string GetValueStr()
{
if( COLUMN_INT_TYPE == mType )
{
return NumToString(mValue);
}
else
{
return "'" + mValueStr + "'";
}
}


//获得sql条件的字符串形式
string GetSqlConditionStr()
{
switch( mCondition )
{
case SQL_CONTION_ENUM_ADN:
return "and";
case SQL_CONTION_ENUM_OR:
return "or";
}
return "and";
}
string mColumnName;//列名
int mType; //0代表默认类型, 1代表列名为int形，2代表列名位str类型
int mValue; //列值
string mValueStr;//字符串数据
SqlContionEnum mCondition;//条件
string mCompare;//比较(大于，等于，或者小于)
};
void ResetSql();
string mSqlSta;//sql语句的字符串形式


string mTableName;//表格名字


vector<SqlColumn> mColumnVec;//存放所有需要修改的value的列值
vector<SqlColumn> mWhereVec;//存放所有的条件判断语句的sql
vector<SqlColumn> mDupVec;//存放所有重复主键的vec
bool isNeedCout;                                       //用来控制是否需要cout


SqlSortType mSortType;//sql的排序类型
string mSortColumn;//排序的列明，目前只支持一个列明的排序，后续再加


//组装查询的sql语句
void PackSelSql();


//组装插入的sql语句
void PackInsertSql();


//组装插入，重复主键update的sql语句
void PackInsertDupSql();


//拼装where语句
void PackWhereSql();


//拼装Update的语句
void PackUpdateSql();


//拼装delete的sql语句
void PackDelSql();
};


#endif /* SQLSTATEMENT_H_ */

SqlStatement.cpp

#include <iostream>
#include "SqlStatement.h"


SqlStatement::SqlStatement() : isNeedCout( false )
{
// isNeedCout = false;
}


void SqlStatement::ResetSql()
{
mColumnVec.clear();
mWhereVec.clear();
mDupVec.clear();
mSortType = SQL_SORT_TYPE_NULL;
}


SqlStatement::~SqlStatement()
{
}


void SqlStatement::SetTable( const string& tableName )
{
ResetSql();
mTableName = tableName;
}


void SqlStatement::AddValue( const string& columnName )
{
SqlColumn s(columnName);
mColumnVec.push_back( s );
}


void SqlStatement::AddValue( const string& columnName, const int value )
{
mColumnVec.push_back( SqlColumn( columnName, COLUMN_INT_TYPE, value ) );
}


void SqlStatement::AddValue( const string& columnName, const string& value )
{
mColumnVec.push_back( SqlColumn( columnName, COLUMN_STR_TYPE, value ) );
}


void SqlStatement::AddWhere( const string& columnName, const int value, const SqlContionEnum condition )
{
mWhereVec.push_back( SqlColumn( columnName, COLUMN_INT_TYPE, value, condition ) );
}


void SqlStatement::AddWhere( const string& columnName, const string& value, const SqlContionEnum condition )
{
mWhereVec.push_back( SqlColumn( columnName, COLUMN_STR_TYPE, value, condition ) );
}


void SqlStatement::AddWhere( const string& columnName, const string& value, const SqlContionEnum condition, const string& compare )
{
mWhereVec.push_back( SqlColumn( columnName, COLUMN_STR_TYPE, value, condition, compare ) );
}


void SqlStatement::AddWhere( const string& columnName, const int value, const SqlContionEnum condition, const string& compare )
{
mWhereVec.push_back( SqlColumn( columnName, COLUMN_INT_TYPE, value, condition, compare ) );
}


void SqlStatement::AddDup( const string& columnName, const int value )
{
mDupVec.push_back( SqlColumn( columnName, COLUMN_INT_TYPE, value ) );
}




void SqlStatement::AddDup( const string& columnName, const string& value )
{
mDupVec.push_back( SqlColumn( columnName, COLUMN_STR_TYPE, value ) );
}


void SqlStatement::AddSort( const string& columnName, const SqlSortType sortType )
{
mSortType = sortType;
mSortColumn = columnName;
}


const char* SqlStatement::GetSelSql()
{
PackSelSql();
if( isNeedCout )
{
cout << "SqlStatement::GetSelSql() mSqlSta = " << mSqlSta << endl;
}
return mSqlSta.c_str();
}


string SqlStatement::GetSelSqlStr()
{
PackSelSql();
if( isNeedCout )
{
cout << "SqlStatement::GetSelSql() mSqlSta = " << mSqlSta << endl;
}
return mSqlSta;
}


const char* SqlStatement::GetInsertSql()
{
PackInsertSql();
mSqlSta += ";";
if( isNeedCout )
{
cout << "SqlStatement::GetSelSql() mSqlSta = " << mSqlSta << endl;
}
return mSqlSta.c_str();
}


string SqlStatement::GetInsertSqlStr()
{
PackInsertSql();
mSqlSta += ";";
if( isNeedCout )
{
cout << "SqlStatement::GetSelSql() mSqlSta = " << mSqlSta << endl;
}
return mSqlSta;
}


//获得简单修改的sql语句的
const char* SqlStatement::GetUpdateSql()
{
PackUpdateSql();
mSqlSta += ";";
if( isNeedCout )
{
cout << "SqlStatement::GetSelSql() mSqlSta = " << mSqlSta << endl;
}
return mSqlSta.c_str();
}


//获得简单修改的sql语句
string SqlStatement::GetUpdateSqlStr()
{
PackUpdateSql();
mSqlSta += ";";
if( isNeedCout )
{
cout << "SqlStatement::GetSelSql() mSqlSta = " << mSqlSta << endl;
}
return mSqlSta;
}


//获得简单删除的sql语句的
const char* SqlStatement::GetDelSql()
{
PackDelSql();
mSqlSta += ";";
if( isNeedCout )
{
cout << "SqlStatement::GetSelSql() mSqlSta = " << mSqlSta << endl;
}
return mSqlSta.c_str();
}


//获得简单删除的sql语句的的st r
string SqlStatement::GetDelSqlStr()
{
PackDelSql();
mSqlSta += ";";
if( isNeedCout )
{
cout << "SqlStatement::GetSelSql() mSqlSta = " << mSqlSta << endl;
}
return mSqlSta;
}


const char* SqlStatement::GetInsertDupSql()
{
PackInsertDupSql();
mSqlSta += ";";
if( isNeedCout )
{
cout << "SqlStatement::GetSelSql() mSqlSta = " << mSqlSta << endl;
}
return mSqlSta.c_str();
}


//获得插入简单插入on dupicate update的sql的string形式
string SqlStatement::GetInsertDupSqlStr()
{
PackInsertDupSql();
mSqlSta += ";";
if( isNeedCout )
{
cout << "SqlStatement::GetSelSql() mSqlSta = " << mSqlSta << endl;
}
return mSqlSta;
}


int SqlStatement::GetSqlLen()
{
return mSqlSta.length();
}


//组装查询的sql语句
void SqlStatement::PackSelSql()
{
if( 0 == mColumnVec.size() )
{
mSqlSta = "select * from " + mTableName;
}
else
{
mSqlSta = "select ";
int size = 0;
for( vector<SqlColumn>::iterator it = mColumnVec.begin(); it != mColumnVec.end(); ++it )
{
++size;
mSqlSta += (*it).mColumnName;
if( size != (int)mColumnVec.size() )
{
mSqlSta += ",";
}
}
mSqlSta += " from " + mTableName;
}


if( mWhereVec.size() > 0 )
{
PackWhereSql();
}


if( mSortType != SQL_SORT_TYPE_NULL )
{
mSqlSta = " order by " + mSortColumn + " ";
if( SQL_SORT_TYPE_ASC == mSortType )
{
mSqlSta += "asc";
}
else
{
mSqlSta += "desc";
}
}
mSqlSta += ";";
}


//组装插入的sql语句
void SqlStatement::PackInsertSql()
{
mSqlSta = "insert into " + mTableName + " (";
int size = 0;
for( vector<SqlColumn>::iterator it = mColumnVec.begin(); it != mColumnVec.end(); ++it )
{
++size;
mSqlSta += (*it).mColumnName;
if( size != (int)mColumnVec.size() )
{
mSqlSta += ",";
}
else
{
mSqlSta += ")";
}
}
mSqlSta += " values(";
size = 0;
for( vector<SqlColumn>::iterator it = mColumnVec.begin(); it != mColumnVec.end(); ++it )
{
++size;
mSqlSta += (*it).GetValueStr();
if( size != (int)mColumnVec.size() )
{
mSqlSta += ",";
}
else
{
mSqlSta += ")";
}
}
}


//组装插入，重复主键update的sql语句
void SqlStatement::PackInsertDupSql()
{
PackInsertSql();
if( mDupVec.size() > 0 )
{
int size = 0;
mSqlSta += " ON DUPLICATE KEY UPDATE ";
for( vector<SqlColumn>::iterator it = mDupVec.begin(); it != mDupVec.end(); ++it )
{
++size;
mSqlSta += (*it).mColumnName + "=" + (*it).GetValueStr();
if( size != (int)mDupVec.size() )
{
mSqlSta += ",";;
}


}
}
}


//拼装where语句
void SqlStatement::PackWhereSql()
{
int size = 0;
mSqlSta += " where ";
for( vector<SqlColumn>::iterator it = mWhereVec.begin(); it != mWhereVec.end(); ++it )
{
++size;
mSqlSta += (*it).mColumnName + (*it).mCompare + (*it).GetValueStr();;
if( size != (int)mWhereVec.size() )
{
mSqlSta +=  " " + (*it).GetSqlConditionStr() + " ";
}
}
}


//拼装Update的语句
void SqlStatement::PackUpdateSql()
{
mSqlSta = "update " + mTableName + " set";


int size = 0;
for( vector<SqlColumn>::iterator it = mColumnVec.begin(); it != mColumnVec.end(); ++it )
{
++size;
mSqlSta += " " + (*it).mColumnName + " = " + (*it).GetValueStr();
if( size != (int)mColumnVec.size() )
{
mSqlSta += ",";
}
}


if( mWhereVec.size() > 0 )
{
PackWhereSql();
}
}


//拼装delete的sql语句
void SqlStatement::PackDelSql()
{
//为了安全，不处理没有where 语句的查询，防止误操作，删除表内所有的数据
if( mWhereVec.size() <= 0 )
{
mSqlSta = "";
return;
}
mSqlSta = "delete from " + mTableName;
PackWhereSql();
}