content: package com.taobao.yugong.common.db.sql;
  
  import java.util.List;
  
  import com.taobao.yugong.common.db.meta.ColumnMeta;
  
  /**
   * sql构造
   *
   * @author agapple 2013-9-10 下午6:10:10
   * @since 1.0.0
   */
  public class SqlTemplate {
  
      private static final String DOT = ".";
  
      /**
       * 根据字段的列表顺序，拼写以 col1,col2,col3,....
       */
      public String makeColumn(List<ColumnMeta> columns) {
          StringBuilder str = new StringBuilder();
          int size = columns.size();
          for (int i = 0; i < size; i++) {
              str.append(getColumnName(columns.get(i)));
              if (i < (size - 1)) {
                  str.append(",");
              }
          }
          return str.toString();
      }
  
      /**
       * 根据字段的列表顺序，拼写以 ?,?,?,....
       */
      public String makeInsert(List<ColumnMeta> columns) {
          StringBuilder str = new StringBuilder();
          int size = columns.size();
          for (int i = 0; i < size; i++) {
              str.append('?');
              if (i < (size - 1)) {
                  str.append(",");
              }
          }
          return str.toString();
      }
  
      /**
       * 根据字段的列表顺序，拼写 column1=?,column2=?,column3=?,...
       */
      public String makeUpdate(List<ColumnMeta> columns) {
          StringBuilder str = new StringBuilder();
          int size = columns.size();
          for (int i = 0; i < size; i++) {
              str.append(getColumnName(columns.get(i)));
              str.append("=?");
              if (i < (size - 1)) {
                  str.append(",");
              }
          }
          return str.toString();
      }
  
      /**
       * 根据字段列表，拼写column=? and column=? and ...字符串
       */
      public String makeWhere(List<ColumnMeta> columns) {
          StringBuilder sb = new StringBuilder("");
          int size = columns.size();
          for (int i = 0; i < size; i++) {
              sb.append(getColumnName(columns.get(i)));
              sb.append("=?");
              if (i != (size - 1)) {
                  sb.append(" and ");
              }
          }
          return sb.toString();
      }
  
      /**
       * 根据字段列表，拼写column >= ? and column < ?
       */
      public String makeRange(ColumnMeta column) {
          return makeRange(column.getName());
      }
  
      /**
       * 根据字段列表，拼写 column >= ? and column < ?
       */
      public String makeRange(String columnName) {
          StringBuilder sb = new StringBuilder("");
          sb.append(getColumnName(columnName));
          sb.append(" >= ? and ");
          sb.append(getColumnName(columnName));
          sb.append(" <= ?");
          return sb.toString();
      }
  
      /**
       * 根据字段名和参数个数，拼写 column in (?,?,...) 字符串
       */
      public String makeIn(ColumnMeta column, int size) {
          return makeIn(column.getName(), size);
      }
  
      /**
       * 根据字段名和参数个数，拼写 column in (?,?,...) 字符串
       */
      public String makeIn(String columnName, int size) {
          StringBuilder sb = new StringBuilder("");
          sb.append(getColumnName(columnName));
          sb.append(" in (");
          for (int i = 0; i < size; i++) {
              sb.append("?");
              if (i != (size - 1)) {
                  sb.append(",");
              }
          }
          sb.append(")");
          return sb.toString();
      }
  
      public String getSelectSql(String schemaName, String tableName, String[] pkNames, String[] colNames) {
          StringBuilder sql = new StringBuilder();
          sql.append("select ");
          String[] allColumns = buildAllColumns(pkNames, colNames);
          int size = allColumns.length;
          for (int i = 0; i < size; i++) {
              sql.append(getColumnName(allColumns[i])).append(splitCommea(size, i));
          }
  
          sql.append(" from ").append(makeFullName(schemaName, tableName)).append(" where ( ");
          if (pkNames.length > 0) { // 可能没有主键
              makeColumnEquals(sql, pkNames, "and");
          } else {
              makeColumnEquals(sql, colNames, "and");
          }
          sql.append(" ) ");
          return sql.toString().intern();
      }
  
      public String getSelectInSql(String schemaName, String tableName, String[] pkNames, String[] columnNames, int inSize) {
          StringBuilder sql = new StringBuilder("select ");
          String[] allColumns = buildAllColumns(pkNames, columnNames);
          int size = allColumns.length;
          for (int i = 0; i < size; i++) {
              sql.append(getColumnName(allColumns[i])).append(splitCommea(size, i));
          }
  
          sql.append(" from ").append(makeFullName(schemaName, tableName)).append(" where ( ");
          size = pkNames.length;
          if (size == 0) {
              size = columnNames.length;
          }
  
          for (int i = 0; i < size; i++) {
              if (pkNames.length > 0) {
                  sql.append(getColumnName(pkNames[i])).append(splitCommea(size, i));
              } else {
                  sql.append(getColumnName(columnNames[i])).append(splitCommea(size, i));
              }
          }
          sql.append(") in (");
  
          size = pkNames.length;
          if (size == 0) {
              size = columnNames.length;
          }
          for (int i = 0; i < inSize; i++) {
              sql.append('(');
              for (int j = 0; j < size; j++) {
                  sql.append('?').append((j + 1 < size) ? " , " : "");
              }
              sql.append(')').append((i + 1 < inSize) ? " , " : "");
          }
          sql.append(")");
          return sql.toString();
      }
  
      public String getInsertSql(String schemaName, String tableName, String[] pkNames, String[] columnNames) {
          StringBuilder sql = new StringBuilder();
          sql.append("insert into ").append(makeFullName(schemaName, tableName)).append("(");
          String[] allColumns = buildAllColumns(pkNames, columnNames);
          int size = allColumns.length;
          for (int i = 0; i < size; i++) {
              sql.append(getColumnName(allColumns[i])).append(splitCommea(size, i));
          }
  
          sql.append(") values (");
          makeColumnQuestions(sql, allColumns);
          sql.append(")");
          return sql.toString().intern();
      }
  
      public String getUpdateSql(String schemaName, String tableName, String[] pkNames, String[] columnNames) {
          StringBuilder sql = new StringBuilder();
          sql.append("update ").append(makeFullName(schemaName, tableName)).append(" set ");
          makeColumnEquals(sql, columnNames, ",");
          sql.append(" where (");
          makeColumnEquals(sql, pkNames, "and");
          sql.append(")");
          return sql.toString().intern();
      }
  
      public String getDeleteSql(String schemaName, String tableName, String[] pkNames) {
          StringBuilder sql = new StringBuilder();
          sql.append("delete from ").append(makeFullName(schemaName, tableName)).append(" where ");
          makeColumnEquals(sql, pkNames, "and");
          // intern优化，避免出现大量相同的字符串
          return sql.toString().intern();
      }
  
      protected String makeFullName(String schemaName, String tableName) {
          String full = schemaName + DOT + tableName;
          return full.intern();
      }
  
      protected void makeColumnEquals(StringBuilder sql, String[] columns, String separator) {
          int size = columns.length;
          for (int i = 0; i < size; i++) {
              sql.append(" ").append(getColumnName(columns[i])).append(" = ").append("? ");
              if (i != size - 1) {
                  sql.append(separator);
              }
          }
      }
  
      protected void makeColumnQuestions(StringBuilder sql, String[] columns) {
          int size = columns.length;
          for (int i = 0; i < size; i++) {
              sql.append("?").append(splitCommea(size, i));
          }
      }
  
      protected String getColumnName(String columName) {
          return columName;
      }
  
      protected String getColumnName(ColumnMeta column) {
          return column.getName();
      }
  
      protected String splitCommea(int size, int i) {
          return (i + 1 < size) ? " , " : "";
      }
  
      protected String[] buildAllColumns(String[] pkNames, String[] colNames) {
          String[] allColumns = new String[pkNames.length + colNames.length];
          System.arraycopy(colNames, 0, allColumns, 0, colNames.length);
          System.arraycopy(pkNames, 0, allColumns, colNames.length, pkNames.length);
          return allColumns;
      }
  
  }
  