在程序中写SQL语句是我们通常用于访问数据库的方法，但这种方式和OO是不兼容的，有很多弊端，但O/R Mapping有时又有点庞大，本人制作了几个类，希望和大家探讨一下一种新方法的可行性。讲述在下面，请大家保持耐心。
先把类代码贴上来：
// SqlMaker：这个类构成SQL语句的主体。
import java.util.HashMap;
import java.util.Iterator;
public class SqlMaker{
protected String selectPart="";
protected String orderPart="";
protected HashMap condiions=new HashMap();
public void addCondition(String key,Condition value){
condiions.put(key,value);
}
public void refreshCondition(String key,Object value){
condiions.remove(key);
condiions.put(key,value);
}
public String getAllSql(){
String allSql="";
allSql+=selectPart;
allSql+=" where ";
Iterator it = condiions.values().iterator();
while (it.hasNext()) {
Object value = it.next();
if(value instanceof SqlMaker){
allSql+="("+((SqlMaker)value).getAllSql()+")";
}
else{
allSql+=((Condition)value).getSql();
}
}
allSql+=" 1=1 ";
allSql+=orderPart;
return allSql;
}
public HashMap getCondiions() {
return condiions;
}
public void setCondiions(HashMap condiions) {
this.condiions = condiions;
}
public String getOrderPart() {
return orderPart;
}
public void setOrderPart(String orderPart) {
this.orderPart = orderPart;
}
public String getSelectPart() {
return selectPart;
}
public void setSelectPart(String selectPart) {
this.selectPart = selectPart;
}
}
// Condition类：这个类构成每个小条件
public class Condition{
private String field="";
private String operator="";
private Object value="";
public static final String OPRT_Photocopy=" =";
public static final String OPRT_Equal="=";
public static final String OPRT_BigThan=">=";
public static final String OPRT_LessThan="<=";
public static final String OPRT_Like="like";
public static final String OPRT_In="in";
public Condition(String field,String operator,Object value){
this.field=field;
this.operator=operator;
this.value=value;
}
public String getSql(){
String retval="";
if(value instanceof SqlMaker){
retval+=field+" ";
retval+=operator+" ";
retval+="("+((SqlMaker)value).getAllSql()+") and ";
}
else{
retval+=field+" ";
if(operator.equals(OPRT_Photocopy)){
retval+=operator+" ";
retval+=value+" and ";
}
else{
retval+=operator+" '";
retval+=value+"' and ";
}
if(((String)value).length()<1) retval="";
}
return retval;
}
public String getField() {
return field;
}
public void setField(String field) {
this.field = field;
}
public String getOperator() {
return operator;
}
public void setOperator(String operator) {
this.operator = operator;
}
}
这里是三个表的原数据：
调用方法如下：
// Eg.01 ：这一段是一个Select加一个where
SqlMaker sqlBook=new SqlMaker();
// Setup Sql Logic
sqlBook.setSelectPart("select NAME, TYPE, AUTHOR, BRIEF from BOOK");
sqlBook.addCondition("NAME",new Condition("NAME",Condition.OPRT_Equal,""));
sqlBook.addCondition("AUTHOR",new Condition("AUTHOR",Condition.OPRT_Like,""));
// Add Condition
sqlBook.refreshCondition("NAME",new Condition("NAME",Condition.OPRT_Equal,"d"));
sqlBook.refreshCondition("AUTHOR",new Condition("AUTHOR",Condition.OPRT_Like,"d%"));
// Get Sql
String bookSql=sqlBook.getAllSql();
System.out.println("bookSql="+bookSql);
输出：bookSql=select NAME, TYPE, AUTHOR, BRIEF from BOOK where AUTHOR like 'd%' and NAME = 'd' and 1=1
// Eg.02：这一段是一个Select加两个where
SqlMaker sqlTank=new SqlMaker();
// Setup Sql Logic
sqlTank.setSelectPart("select NAME, NATION, WEIGHT, PRODUCTTIME from TANKS");
sqlTank.addCondition("WEIGHTFROM",new Condition("WEIGHT",Condition.OPRT_BigThan,""));
sqlTank.addCondition("WEIGHTTO",new Condition("WEIGHT",Condition.OPRT_LessThan,""));
sqlBook.addCondition("BRIEF",new Condition("BRIEF",Condition.OPRT_Equal,""));
// Add Condition
sqlTank.refreshCondition("WEIGHTFROM",new Condition("WEIGHT",Condition.OPRT_BigThan,"30"));
sqlTank.refreshCondition("WEIGHTTO",new Condition("WEIGHT",Condition.OPRT_LessThan,"60"));
// Get Sql
System.out.println("TankSql="+sqlTank.getAllSql());
输出：TankSql=select NAME, NATION, WEIGHT, PRODUCTTIME from TANKS where WEIGHT >= '30' and WEIGHT <= '60' and 1=1
// Eg.03：复合查询例子
SqlMaker sqlTank2=new SqlMaker();
// Setup Sql Logic
sqlTank2.setSelectPart("select NAME, NATIONS.NATION, WEIGHT, PRODUCTTIME from TANKS,NATIONS");
sqlTank2.addCondition("NATIONOUUTERLINK",new Condition("TANKS.NATION",Condition.OPRT_Photocopy,"NATIONS.ID(+)"));
sqlTank2.addCondition("WEIGHTFROM",new Condition("WEIGHT",Condition.OPRT_BigThan,""));
sqlTank2.addCondition("WEIGHTTO",new Condition("WEIGHT",Condition.OPRT_LessThan,""));
// Add Condition
sqlTank2.refreshCondition("NATION",new Condition("NATIONS.NATION",Condition.OPRT_Equal,"Germany"));
sqlTank2.refreshCondition("WEIGHTFROM",new Condition("WEIGHT",Condition.OPRT_BigThan,"30"));
sqlTank2.refreshCondition("WEIGHTTO",new Condition("WEIGHT",Condition.OPRT_LessThan,"60"));
// Get Sql
System.out.println("TankSql2="+sqlTank2.getAllSql());
输出：TankSql2=select NAME, NATIONS.NATION, WEIGHT, PRODUCTTIME from TANKS,NATIONS where WEIGHT >= '30' and TANKS.NATION = NATIONS.ID(+) and NATIONS.NATION = 'Germany' and WEIGHT <= '60' and 1=1
// Eg.04：子查询例子
SqlMaker sqlTank3=new SqlMaker();
// Setup Sql Logic
sqlTank3.setSelectPart("select NATION from TANKS ");
SqlMaker subSql=new SqlMaker();
subSql.setSelectPart("select distinct id from nations");
sqlTank3.addCondition("Nation",new Condition("NATION",Condition.OPRT_In,subSql));
// Get Sql
System.out.println("TankSql3="+sqlTank3.getAllSql());
输出：TankSql3=select NATION from TANKS where NATION in (select distinct id from nations where 1=1 ) and 1=1
这个设计的基本思想是将SQl语句中的“select .....where......Order by...”三部分分开，select 和Order 部分一般比较简单，这里用String来存储，也保持了原SQL的简单性，where部分比较复杂，但经分析大多是 key operator value的形式，因此用Condition类来描述它，多个Condition保存在SqlMaker类中，用遍历来生成整个Where部分,同时也可以消除了许多复杂的分支。用户最后调用SqlMaker.getAllSql（）可以得到完整的SQL语句.
基本调用过程详述
// 新建一个SqlMaker 类的实例sqlBook,用于Book表的查询
SqlMaker sqlBook=new SqlMaker();
// 这里是初始化部分,一般按最长的SQL语句书写
sqlBook.setSelectPart("select NAME, TYPE, AUTHOR, BRIEF from BOOK");// 建立Sql语句的select部分
sqlBook.addCondition("NAME",new Condition("NAME",Condition.OPRT_Equal,""));// 添加条件一
sqlBook.addCondition("AUTHOR",new Condition("AUTHOR",Condition.OPRT_Like,""));// 添加条件二
// 这里是临时赋值部分,根据View的传值进行变化
sqlBook.refreshCondition("NAME",new Condition("NAME",Condition.OPRT_Equal,"d"));// 为条件一赋值
sqlBook.refreshCondition("AUTHOR",new Condition("AUTHOR",Condition.OPRT_Like,"d%"));// 为条件二赋值
// 得到最终的SQL语句
String bookSql=sqlBook.getAllSql();// 得到最终的SQL语句
System.out.println("bookSql="+bookSql);// 输出
本文系原创文章，转载请注明出处。
