BBP项目Sql语句拼接

 

最近一直在看BBP项目，感觉有很多要学习的东西，现在在这里总结一下sql语句的拼写：

   1. 类SQLCond：

   （1）首先是要创建一个类SQLCond，这个类用来存储sql条件的，它有3个属性，分别是name、value和operator。

   （2）因为operator有很多种类，有一种方法是：把它定义为枚举类型，然使用EnumMap类来映射枚举类型到真正的运算符号。静态EnumMap类型放在类SQLCond中。这样当我们存储一个sql条件时，operator使用的是枚举类型，而当我们拿这个sql条件类来拼接真正的sql语句时，在获取operator时，可以使用EnumMap来拿出相应的符号内容，即在getOperator方法中使用：

          "enumMap.get(some_enum_operator)"语句。

   （3）在这个SQLCond类中使用了枚举类型来表示运算符号，然后定义EnumMap类型来映射这个枚举运算符号到真正的运算符号。这样就会使一个变量能够处理多个运算符号，并且运算符号统一处理。

   2. 类SQLUtil：

   （1）类SQLUtil中放着很多的方法，用来拼接sql语句。其中主要是把List<SQLCond>中的sql条件给拿出来，拼成真正的sql语句。用了一个getCondition方法来处理，处理过程是循环每个List<SQLCond>，判断每个SQLCond的operator（使用switch)，来分别进行相应的处理。

需要注意的是：拼接的sql语句是预处理的sql语句，当我们在是放字段和“？”的时候，我们也同时放置了相应的值到一个list中，这样就可以保证“？”与值对应了。

   （2）operator的分开处理主要有：

              is (not) null/(not) in/between/其他。

   3. 以下是SQLCond类和SQLUtil类中的获取select语句的方法：

   （1）SQLCond类源码：

   public class SQLCond {

 

   private String name = "";

   private Object value;

   private Operator operator = Operator.EQUAL;

  

   public static enum Operator{

      ISNULL,

      ISNOTNULL,

      LIKE,

      EQUAL,

      NOTEQUAL,

      LESS_OR_EQUAL,

      GREATER_OR_EQUAL,

      LESS,

      GREATER,

      ORDER,

      IN,

      NOT_IN,

      BETWEEN

   }

  

   private static EnumMap<SQLCond.Operator, String> operateMap =new EnumMap<Operator, String>(Operator.class);

  

   static{

      operateMap.put(Operator.EQUAL, " = ");

      operateMap.put(Operator.GREATER, " > ");

      operateMap.put(Operator.GREATER_OR_EQUAL, " >= ");

      operateMap.put(Operator.ISNOTNULL, " IS NOT NULL");

      operateMap.put(Operator.ISNULL, " IS NULL");

      operateMap.put(Operator.LESS, "  < ");

      operateMap.put(Operator.LESS_OR_EQUAL, " <= ");

      operateMap.put(Operator.LIKE, " LIKE ");

      operateMap.put(Operator.NOTEQUAL, " <> ");

      operateMap.put(Operator.ORDER, " ORDER BY ");

      operateMap.put(Operator.IN, " IN ");

      operateMap.put(Operator.NOT_IN, " NOT IN ");

      operateMap.put(Operator.BETWEEN, " BETWEEN ? AND ? ");

      //operateMap.put(Operator.OR, " OR ");

   }

     

   public SQLCond(String name, Object value, Operator operator) {

      this.setName(name);

      this.setValue(value);

      this.setOperator(operator);

   }

  

   public SQLCond(String name, Object value) {

      this.setName(name);

      this.setValue(value);

   }

  

   public String getName() {

      return name;

   }

   public void setName(String name) {

      this.name = name;

   }

 

   public String getOperateString() {

      return operateMap.get(operator);

   }

   public Operator getOperator() {

      return this.operator;

   }

   public void setOperator(Operator operator) {

      this.operator = operator;

   }

 

   public Object getValue() {

      return value;

   }

 

   public void setValue(Object value) {

      this.value = value;

   }

}

 

   （2）SQLUtil类中的获取select语句的方法源码：

   public String getSelectSql(String tablename, List<String>

        fieldlist, List<SQLCond> condlist, List paramlist){

      return getSearchSql(tablename, fieldlist, condlist,                      paramlist, false);

   }

     

   public String getSearchSql(String tablename,               List<String> fieldlist, List<SQLCond> condlist,

      List paramlist, boolean distinct){

      StringBuffer sqlBuffer = new StringBuffer("SELECT ");

      Iterator<String> iteratorField;

     

      if(distinct){

        sqlBuffer.append("DISTINCT ");

      }

     

      if (fieldlist != null && fieldlist.size() > 0) {

        iteratorField = fieldlist.iterator();

        while (iteratorField.hasNext()) {

           //sqlBuffer.append("[");

           sqlBuffer.append(iteratorField.next());

           //sqlBuffer.append("]");

           if (iteratorField.hasNext())

              sqlBuffer.append(", ");

           else

              break;

        }

      }else{

        sqlBuffer.append("*");

      }

     

      sqlBuffer.append(" FROM ");

      sqlBuffer.append(tablename);

     

      if(condlist == null || condlist.size() <= 0)

        return sqlBuffer.toString();

     

      if(condlist != null && paramlist == null)

        throw new NullPointerException();

     

      getCondition(condlist, paramlist, sqlBuffer);

     

      return sqlBuffer.toString();

   }

  

   private void getCondition(List<SQLCond> condlist, List paramlist, StringBuffer sqlBuffer){

      Iterator<SQLCond> iteratorCond;

     

      iteratorCond = condlist.iterator();

      sqlBuffer.append(" WHERE ");

      StringBuffer orderby = new StringBuffer();

      //Class objType = obj.getClass();

     

      while(iteratorCond.hasNext()){

        SQLCond sqlcond = iteratorCond.next();

        if(sqlcond.getOperator() == null)

           throw new NullPointerException("NullOperatorException");

        switch(sqlcond.getOperator()){

           case ISNOTNULL:

           case ISNULL:

              //sqlBuffer.append("[");

              sqlBuffer.append(sqlcond.getName());

              //sqlBuffer.append("]");

              sqlBuffer.append(sqlcond.getOperateString());

              break;

           case ORDER:

              int location = sqlBuffer.lastIndexOf(" AND ");

              if(location != -1){

                 sqlBuffer.delete(location, location + 5);

              }

              orderby.append(sqlcond.getOperateString());

              //orderby.append("[");

              orderby.append(sqlcond.getName());

              //orderby.append("] ");

              orderby.append(" ");

              orderby.append(sqlcond.getValue());

              break;

           case NOT_IN:

           case IN:

              Object[] objects = (Object[])sqlcond.getValue();

              sqlBuffer.append(sqlcond.getName());

              sqlBuffer.append(sqlcond.getOperateString());

              sqlBuffer.append("(");

              for(int i = 0; i < objects.length; i++){

                 sqlBuffer.append("?");

                 paramlist.add(objects[i]);

                 if(i+1 < objects.length)

                    sqlBuffer.append(", ");

              }

              sqlBuffer.append(")");

              break;          

           case BETWEEN:

              Object[] objs = (Object[])sqlcond.getValue();

              sqlBuffer.append(sqlcond.getName());

              sqlBuffer.append(sqlcond.getOperateString());

              for(int i = 0; i < objs.length; i++){               

                 paramlist.add(objs[i]);               

              }

              break;

           default:

              if(sqlcond.getValue() == null)

                 break;

              //sqlBuffer.append("[");

              sqlBuffer.append(sqlcond.getName());

              //sqlBuffer.append("]");

              sqlBuffer.append(sqlcond.getOperateString());

              sqlBuffer.append("?");

              paramlist.add(sqlcond.getValue());

              break;

        }

        if(iteratorCond.hasNext()){

           sqlBuffer.append(" AND ");

           continue;

        }

      }

      sqlBuffer.append(orderby);

   }

 

 

   4.举例：

具体示例可参考BBP项目中

com.innov8tion.dao.jdbc. CategoryDAOImpl类中的save方法。

5.注意StringBuffer类的使用：

  StringBuffer用于sql语句的连接。