对sql查询语句组合查询的通用实现算法（c++版，java版）-摩杜云开发者社区

现在我们做的大型项目大多要与关系型数据库进行交互，不知道大家有没有遇到这样的问题，组合查询，两个条件A,B，组合情况有A,B,AB，也就三种，写死SQL语句也不麻烦，不就是几个if，else吗！但是要是有三个条件组合呢？A,B,C，这样就会有A,B,C,AB,AC....这要n个if，else吧.

ok,下面我们写个通用算法解决以上问题

首先C++版

// 查询条件结构
struct SelCondition
{
string m_strValueFirst;
string m_strValueSecond;
enum ConditionType {CT_EQUAL = 1, CT_NOT_EQUAL, CT_ABOVE, CT_BELOW, CT_FUZZY,CT_AREA,CT_GROUP} m_conType;
//针对范围
SelCondition(string valueFirst,string valueSecond, SelCondition::ConditionType conType)
{
m_strValueFirst = valueFirst;
m_strValueSecond=valueSecond;
m_conType = conType;
}
//针对一个条件
SelCondition(string valueFirst, SelCondition::ConditionType conType)
{
m_strValueFirst = valueFirst;
m_conType = conType;
}
SelCondition(string valueFirst)
{
m_strValueFirst = valueFirst;
}
SelCondition(){};
};

// 返回不带 where 的条件语句，并且是以 and 开头，例如 and id>8 and name=aiht
string DbOpBase::AssembleCondition(const map<string, SelCondition> &mapConditions)
{
string strCondition="";
typedef map<string, SelCondition>::const_iterator CI;
for(CI p=mapConditions.begin(); p!=mapConditions.end(); ++p)
{
string strCmd;
string colName = p->first;
// TODO 判断列名是否是""
//进行查询类型的判定拼装相应条件

switch(p->second.m_conType)
{
case SelCondition::CT_ABOVE:
strCmd = _T(" AND ") + colName + _T(" > '") + p->second.m_strValueFirst + _T("'");
break;
case SelCondition::CT_BELOW:
strCmd = _T(" AND ") + colName + _T(" < '") + p->second.m_strValueFirst + _T("'");
break;
case SelCondition::CT_EQUAL:
strCmd = _T(" AND ") + colName + _T(" = '") + p->second.m_strValueFirst + _T("'");
break;
case SelCondition::CT_NOT_EQUAL:
strCmd = _T(" AND ") + colName + _T(" != '") + p->second.m_strValueFirst + _T("'");
break;
case SelCondition::CT_FUZZY:
strCmd =_T(" AND ") + colName + _T(" like '%") + p->second.m_strValueFirst +_T("%'");
break;
case SelCondition::CT_AREA:
strCmd =_T(" AND ") + colName + _T(" BETWEEN '") + p->second.m_strValueFirst +_T("' AND '")+p->second.m_strValueSecond+_T("'");
break;
case SelCondition::CT_GROUP:
strCmd=_T(" group by StartTime ");
break;
default:
;
}
strCondition += strCmd;
}
return strCondition;
}

来看看java版，其中带有自己所做项目的一些字段和逻辑，请见谅

public class HostSearchCondition
{
//构造数据库的查询条件（=，%，between）
public static final int CT_EQUAL = 0;
public static final int CT_LIKE = 1;
public static final int CT_AREA = 2;
// 界面向数据库操作类传值——结构
public static class SelCondition
{
String m_strValueFirst;
String m_strValueSecond;
int m_conType;
public SelCondition(String valueFirst,String valueSecond, int conType)
{
this.m_strValueFirst = valueFirst;
this.m_strValueSecond=valueSecond;
this.m_conType = conType;
}
public SelCondition(String valueFirst, int conType)
{
this.m_strValueFirst = valueFirst;
this.m_conType = conType;
}
public SelCondition(String valueFirst)
{
this.m_strValueFirst = valueFirst;
}
public SelCondition(){};
};
static HashMap<String, SelCondition> sqlmap = new HashMap<String, SelCondition>();
/**
*把组合查询条件传入map
* @param 接收的组合查询条件
*/
public static HashMap<String, SelCondition> Condition(HostBasicInfo condition)
{
sqlmap.clear();
SelCondition selcon;
if(condition.getSName().length()!= 0)
{
selcon = new SelCondition(condition.getSName(), CT_EQUAL);
sqlmap.put("sName", selcon);
}
if(condition.getSCharacterCode().length()!= 0)
{
selcon = new SelCondition(condition.getSName(), CT_LIKE);
sqlmap.put("sCharacterCode", selcon);
}
if(condition.getNHostType().length()!= 0)
{
selcon = new SelCondition(condition.getNHostType(), CT_EQUAL);
sqlmap.put("nHostType", selcon);
}
if(condition.getESecLevel().length()!= 0)
{
selcon = new SelCondition(condition.getESecLevel(), CT_EQUAL);
sqlmap.put("eSecLevel", selcon);
}
return sqlmap;
}
/**
*构造数据库where后的查询条件
* @param 组合查询条件map
*/
@SuppressWarnings("unchecked")
public static String AssembleCondition(Map<String, SelCondition> mapConditions)
{
String strCondition="";
Iterator<?> iter = mapConditions.entrySet().iterator();
while (iter.hasNext())
{
String strCmd = null;
Map.Entry entry = (Map.Entry) iter.next(); String key = (String)entry.getKey();
String colName = key;
SelCondition value = (SelCondition)entry.getValue();
switch(value.m_conType)
{
case CT_EQUAL:
strCmd = " AND Tbl_Host_BasicInfo."+ colName + " = '" + value.m_strValueFirst + "'";
break;
case CT_LIKE:
strCmd =" AND Tbl_Host_BasicInfo." + colName + " like '%" + value.m_strValueFirst +"%'";
break;
case CT_AREA:
strCmd =" " + colName + " BETWEEN '"+ value.m_strValueFirst +"' AND '"+ value.m_strValueSecond+"'";
break;
default:
;
}
strCondition = strCondition + strCmd;
}
return strCondition;
}
}