在进行sql查询的时候，有时候要进行很多条件限制，自己来拼写SQLwhere条件容易出错，而且判断条件复杂，后期维护困难，基于这个原因我在一个小项目中写了一套生成sql条件的类。总共包括一个Condition类，与两个枚举型类型（LogicOper，CompareOper）
代码如下：
public class Condition
{
static string [] logicOpers = new string[]{\"and\",\"or\"};
static string [] compareOpers = new string[]{\">\",\"<\",\"<=\",\">=\",\"=\",\"<>\",\"like\",\"not like\",\"in\"};
string compareOper=null;
string name=null;
string templateName = null;
string valType=null;
object val=null;
public Condition(CompareOper co,string valType,string name,object val)
{
this.compareOper = compareOpers[(int)co];
this.name = name;
templateName = name;
this.valType = valType;
this.val = val;
}
public Condition(CompareOper co,string valType,string name,object val,string templateName)
{
this.compareOper = compareOpers[(int)co];
this.name = name;
this.templateName = templateName;
this.valType = valType;
this.val = val;
}
public Condition(){}
public string toSqlString() [Page]
{
string [] arr1 = (string[])operaters.ToArray(\"\".GetType());
Condition [] arr2 = (Condition[])conditions.ToArray((new Condition()).GetType());
StringBuilder outStr = new StringBuilder();
int count=0;
if(name!=null&&val!=null)
{
outStr.Append(name);
outStr.Append(\" \");
outStr.Append(compareOper);
outStr.Append(\" \");
if(valType.ToLower()==\"int\"
||valType.ToLower()==\"float\"
||valType.ToLower()==\"double\"
||valType.ToLower()==\"bool\"
||valType.ToLower()==\"number\"
)
{
outStr.Append(val);
} [Page]
else if(valType.ToLower()==\"string\")
{
string tmp = (string)val;
outStr.Append(\"’\"+tmp.Replace(\"’\",\"’’\")+\"’\");
}
else if(valType.ToLower()==\"date\")
{
DateTime dt = (DateTime)val;
outStr.Append(\"’\"+dt.ToString(\"yyyy-MM-dd\")+\"’\");
}
else if(valType.ToLower()==\"datetime\")
{
DateTime dt = (DateTime)val;
outStr.Append(\"’\"+dt.ToString(\"yyyy-MM-dd hh:mm:ss.fff\")+\"’\");
}
else
{
string tmp = val.ToString();
outStr.Append(\"’\"+tmp.Replace(\"’\",\"’’\")+\"’\"); [Page]
}
count++;
}
if(arr1.Length>0)
{
for(int i=0;i<arr1.Length;i++)
{
if(arr2[i].toSqlTempletString()==\"\")
continue;
count++;
if((name!=null&&val!=null)||count>1)
{
outStr.Append(\" \");
outStr.Append(arr1[i]);
outStr.Append(\" \");
}
outStr.Append(arr2[i].toSqlString());
}
}
if(count>1)
{ [Page]
outStr.Insert(0,’(’);
outStr.Append(’)’);
}
return outStr.ToString();
}
public string toSqlTempletString()
{
string [] arr1 = (string[])operaters.ToArray(\"\".GetType());
Condition [] arr2 = (Condition[])conditions.ToArray((new Condition()).GetType());
StringBuilder outStr = new StringBuilder();
int count=0;
if(name!=null&&val!=null)
{
outStr.Append(name);
outStr.Append(\" \");
outStr.Append(compareOper);
outStr.Append(\" @\");
outStr.Append(templateName);
count++;
}
if(arr1.Length>0)
{
for(int i=0;i<arr1.Length;i++) [Page]
{
if(arr2[i].toSqlTempletString()==\"\")
continue;
count++;
if((name!=null&&val!=null)||count>1)
{
outStr.Append(\" \");
outStr.Append(arr1[i]);
outStr.Append(\" \");
}
outStr.Append(arr2[i].toSqlTempletString());
}
}
if(count>1)
{
outStr.Insert(0,’(’);
outStr.Append(’)’);
}
return outStr.ToString();
}
public SqlParameter [] getSqlParameters() [Page]
{
ArrayList tmp = new ArrayList();
if(name!=null&&val!=null)
{
tmp.Add(new SqlParameter(\"@\"+templateName,val));
}
Condition [] arr = (Condition[])conditions.ToArray((new Condition()).GetType());
for(int i=0;i<arr.Length;i++)
{
SqlParameter [] sps = arr[i].getSqlParameters();
for(int j=0;j<sps.Length;j++)
{
tmp.Add(sps[j]);
}
}
return (SqlParameter [])tmp.ToArray(new SqlParameter(\"\",\"\").GetType());
}
ArrayList operaters = new ArrayList();
ArrayList conditions = new ArrayList();
public void addCondition(LogicOper lo,Condition c)
{
operaters.Add(logicOpers[(int)lo]);
conditions.Add(c); [Page]
}
}
public enum LogicOper: int
{
and=0,or=1
}
public enum CompareOper: int
{
moreThan=0,lessThan=1,notMoreThan=2,notLessThan=3,equal=4,notEqual=5,like=6,notLike=7,IN=8
}
/*----------------------------------------------------------------------------------------------------------------------------
使用如下:
Condition condition = new Condition(CompareOper.equal,\"string\",\"name\",\"%kkp%\");
Condition condition2 = new Condition(CompareOper.equal,\"int\",\"id\",1024);
Condition condition3 = new Condition(CompareOper.like,\"string\",\"nickName\",\"%’kkp’%\");
Condition condition4 = new Condition(CompareOper.equal,\"date\",\"age\",DateTime.Now);
Condition condition5 = new Condition(CompareOper.equal,\"datetime\",\"signTime\",DateTime.Now);
Condition condition6 = new Condition();
condition.addCondition(LogicOper.or,condition2);
condition.addCondition(LogicOper.or,condition3);
condition6.addCondition(LogicOper.or,condition4);
condition6.addCondition(LogicOper.or,condition5);
condition6.addCondition(LogicOper.and,condition);
condition6.toSqlString();
condition6.toSqlTempletString(); [Page]
condition6.getSqlParameters();
通过Condition类的addCondition方法可以实现任意复杂的条件组合。toSqlString()方法返回sql条件，可以用于sql拼接方式使用。而toSqlTempletString()方式生成的是以参数形式的 sql条件，配合getSqlParameters()方法可以实现以参数传递的条件（相当于java中的prepareStatement实现）。
condition6.toSqlString();
condition6.toSqlTempletString();
的结果分别是：
(age = ’2007-07-16’ or signTime = ’2007-07-16 02:06:02.667’ and (name = ’%kkp%’ or id = 1024 or nickName like ’%’’kkp’’%’))
(age = @age or signTime = @signTime and (name = @name or id = @id or nickName like @nickName))
