贴篇旧文章:一种用类来代替SQL语句的方法。

按:这个是我一年前写的,受关注少,今天贴上来请大家指正指正.
在我以前的某个项目中,查询很多且多变,查询条件个数不固定,相互间还存在制约.这些代码就是为它而写,以规范化和类化这个过程.数据库主要针对Oracle.
原帖地址在:http://junglesong.yculblog.com/post.1245345.html,如果看不到图请说明.

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



本文系原创文章，转载请注明出处。

refreshCondition这个方法名改成input如何

public void refreshCondition(String key,Object value){   
    condiions.remove(key);   
    condiions.put(key,value);   
  } 

这个函数有更新的意思在里头,不是光input.

这种东西最难的处理地方应该是通用性比如 你说的select .....这个部分不应该让用户自己去拼,应该提高通用的接口用户往里仍字段和类型 程序自动根据字段和类型 生成select....部分 字符形式的数字形式的DATA形式的等等

再比如是否支持所有的数据库等等

To ASDF1982:
   对于Sql语句,最复杂的部分应该是Where这一块,变化多,情况复杂,其它相对固定而简单.
   在我以前的某个项目中,查询很多且多变,查询条件个数不固定,相互间存在制约.这些代码就是为它而写,以规范化和类化这个过程.
   数据库主要是Oracle,但我想拓展到其它也不是很难的事.

觉得不好！

说出来讨论一下?

这样确实使得在使用sql的时候方便了许多，但是是不是能够与项目中使用的持久层框架很好的融合可能还是个问题！
最好还是能够支持多类型数据库！

当时项目就是使用的JDBC访问Oracle,没有特殊的地方,所以也没有考虑兼容的问题.
当时主要考虑的是把不OO的Sql给OO化,以及把组合Sql的过程结构化,其它的就掠过了.