现在我们做的大型项目大多要与关系型数据库进行交互，不知道大家有没有遇到这样的问题，组合查询，两个条件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;
- }
- }
