一个类代替SQL语句

转自:http://junglesong.yculblog.com

在程序中写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);// 输出