Sql.java

package cn.org.rapid_framework.generator.provider.db.sql.model;

import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.LinkedHashSet;
import java.util.List;

import cn.org.rapid_framework.generator.GeneratorConstants;
import cn.org.rapid_framework.generator.GeneratorProperties;
import cn.org.rapid_framework.generator.provider.db.sql.SqlFactory;
import cn.org.rapid_framework.generator.provider.db.table.model.Column;
import cn.org.rapid_framework.generator.provider.db.table.model.ColumnSet;
import cn.org.rapid_framework.generator.provider.db.table.model.Table;
import cn.org.rapid_framework.generator.util.StringHelper;
import cn.org.rapid_framework.generator.util.sqlparse.SqlParseHelper;
import cn.org.rapid_framework.generator.util.sqlparse.SqlTypeChecker;
import cn.org.rapid_framework.generator.util.sqlparse.SqlParseHelper.NameWithAlias;
import cn.org.rapid_framework.generator.util.typemapping.JavaPrimitiveTypeMapping;

/**
 * 用于生成代码的Sql对象.对应数据库的sql语句
 * 使用SqlFactory.parseSql()生成 <br />
 * 
 * SQL参数同时支持以下几种语法
 * <pre>
 * hibernate: :username,
 * ibatis2: #username#,$usename$,
 * mybatis(or mybatis): #{username},${username}
 * </pre>
 * SQL对象创建示例：
 * <pre>
 * Sql sql = new SqlFactory().parseSql("select * from user_info where username=#username# and password=#password#");
 * </pre>
 * 
 * @see SqlFactory
 * @author badqiu
 *
 */
public class Sql {
	public static String MULTIPLICITY_ONE = "one"; // select查询回一条记录, selectOne()
	public static String MULTIPLICITY_MANY = "many"; // select查询回一个List, selectList()
	public static String MULTIPLICITY_PAGING = "paging"; // 分页查询
	
	public static String PARAMTYPE_PRIMITIVE = "primitive";
	public static String PARAMTYPE_OBJECT = "object";
	
	String operation = null;  //这段 sql相对应的操作名称.
	String resultClass; // select查询返回的结果集的class
	String parameterClass; //参数代表的parameterClass
	String remarks; //注释
	
	String                      multiplicity        = MULTIPLICITY_ONE;                 /* many or one or paging */
    boolean                     paging              = false;                            // 是否分页查询
    
    String                      sqlmap;                                                 /* for ibatis and mybatis */
    String                      resultMap           = null;                             /* for ibatis and mybatis */
	
    /** 代表一条select查询回来的结果列 */
	LinkedHashSet<Column> columns = new LinkedHashSet<Column>();
	/** 代表一条sql 查询有参数列表  */
	LinkedHashSet<SqlParameter> params = new LinkedHashSet<SqlParameter>();
	
	String sourceSql; // source sql
	String executeSql; //代表在数据库执行的sql
	private String              paramType           = PARAMTYPE_PRIMITIVE;                      /* primitive or object */
	
	/** 代表一段SQL include 其它的sql片段. 如ibatis中的 <include refid='User.Where'/> */
	private List<SqlSegment> sqlSegments = new ArrayList<SqlSegment>();
	
	public Sql() {
	}
	
	/** 判断select查询回来的列是否是同一张表的字段 */
	public boolean isColumnsInSameTable() {
		// FIXME 还要增加表的列数与columns是否相等,才可以为select 生成 include语句
		if(columns == null || columns.isEmpty()) return false;
		
		Collection<NameWithAlias> tableNames = SqlParseHelper.getTableNamesByQuery(executeSql);
		if(tableNames.size() > 1) {
		    return false;
		}
        Table t = SqlFactory.getTableFromCache(tableNames.iterator().next().getName());
        for(Column c : columns) {
            Column fromTableColumn = new ColumnSet(t.getColumns()).getBySqlName(c.getSqlName());
            if(fromTableColumn == null) {
                return false;
            }
        }
        
//		Column firstTable = columns.iterator().next();
//		if(columns.size() == 1) return true;
//		if(firstTable.getTable() == null) {
//			return false;
//		}
//		
//		String preTableName = firstTable.getTable().getSqlName();
//		for(Column c :columns) {
//			Table table = c.getTable();
//			if(table == null) {
//				return false;
//			}
//			if(preTableName.equalsIgnoreCase(table.getSqlName())) {
//				continue;
//			}else {
//			    return false;
//			}
//		}
		return true;
	}

	
    /**
     * 得到select查询返回的resultClass,可以通过setResultClass()自定义，如果没有自定义则为你自动生成<br />
     * resultClass可以为com.company.User的完全路径
     * 示例:
     * <pre>
     * select count(*) from user, 返回值为: Long
     * select * from user 返回值为: User
     * select count(*) cnt, sum(age) sum_age 返回值为: getOperation()+"Result";
     * </pre>
     * @return
     */
	public String getResultClass() {
		String resultClass = _getResultClass();
		if(isPaging() || MULTIPLICITY_MANY.equals(multiplicity)) {
		    return JavaPrimitiveTypeMapping.getWrapperType(resultClass);
		}else {
		    return resultClass;
		}
	}

    private String _getResultClass() {
        if(StringHelper.isNotBlank(resultClass)) return resultClass;
		if(columns.size() == 1) {
			return columns.iterator().next().getSimpleJavaType();
		}
		if(isColumnsInSameTable()) {
		    Collection<NameWithAlias> tableNames = SqlParseHelper.getTableNamesByQuery(executeSql);
		    Table t = SqlFactory.getTableFromCache(tableNames.iterator().next().getName()); //FIXME 自定义的className将不会起作用,因为不是从同一个cache取的对象
		    return t.getClassName();
		}else {
			if(operation == null) return null;
			return StringHelper.makeAllWordFirstLetterUpperCase(StringHelper.toUnderscoreName(operation))+GeneratorProperties.getProperty(GeneratorConstants.GENERATOR_SQL_RESULTCLASS_SUFFIX);
		}
    }    
	
	public void setResultClass(String queryResultClass) {
		this.resultClass = queryResultClass;
	}
	
	public boolean isHasCustomResultClass() {
	    return StringHelper.isNotBlank(this.resultClass);
	}

	public boolean isHasResultMap() {
        return StringHelper.isNotBlank(this.resultMap);
    }
	
    /**
     * 返回getResultClass()的类名称 <br />
     * 示例: <br />
     * 如getResultClass()=com.company.User,将返回User
     */	
	public String getResultClassName() {
		int lastIndexOf = getResultClass().lastIndexOf(".");
		return lastIndexOf >= 0 ? getResultClass().substring(lastIndexOf+1) : getResultClass();
	}

    /**
     * SQL参数过多时用于封装为一个ParameterObject的class<br />
     * <pre>
     * 可以通过setParameterClass()自定义
     * 没有自定义则:
     * 如果是select查询,返回 operation+"Query"
     * 其它则返回operation+"Parameter"
     * <pre>
     */	
	public String getParameterClass() {
		if(StringHelper.isNotBlank(parameterClass)) return parameterClass;
		if(StringHelper.isBlank(operation)) return null;
		if(isSelectSql()) {
			return StringHelper.makeAllWordFirstLetterUpperCase(StringHelper.toUnderscoreName(operation))+"Query";
		}else {
			return StringHelper.makeAllWordFirstLetterUpperCase(StringHelper.toUnderscoreName(operation))+"Parameter";
		}
	}
	
	public void setParameterClass(String parameterClass) {
		this.parameterClass = parameterClass;
	}

    /**
     * 返回getParameterClass()的类名称 <br />
     * 示例: <br />
     * 如getParameterClass()=com.company.UserQuery,将返回UserQuery
     */		
	public String getParameterClassName() {
		int lastIndexOf = getParameterClass().lastIndexOf(".");
		return lastIndexOf >= 0 ? getParameterClass().substring(lastIndexOf+1) : getParameterClass();
	}
	
	// TODO columnsSize大于二并且不是在同一张表中,将创建一个QueryResultClassName类,同一张表中也要考虑创建类
	public int getColumnsCount() {
		return columns.size();
	}
	public void addColumn(Column c) {
		columns.add(c);
	}

    /**
     * 得到该sql方法相对应的操作名称,模板中的使用方式为: public List ${operation}(),示例值: findByUsername
     * @return
     */
	public String getOperation() {
		return operation;
	}
	public void setOperation(String operation) {
		this.operation = operation;
	}
	public String getOperationFirstUpper() {
		return StringHelper.capitalize(getOperation());
	}

    /**
     * 用于控制查询结果,固定值为:one,many
     * @return
     */
	public String getMultiplicity() {
		return multiplicity;
	}
	public void setMultiplicity(String multiplicity) {
		// TODO 是否要增加验证数据为 one,many
		this.multiplicity = multiplicity;
	}

    /**
     * 得到sqlect 查询的列对象(column),如果是insert,delete,update语句,则返回empty Set.<br />
     * 示例:
     * <pre>
     * SQL : select count(*) cnt, sum(age) sum_age from user_info
     * columns: cnt,sum_age
     * </pre>
     * @return
     */
	public LinkedHashSet<Column> getColumns() {
		return columns;
	}
	public void setColumns(LinkedHashSet<Column> columns) {
		this.columns = columns;
	}

    /**
     * 得到SQL的参数对象<br />
     * 示例:
     * <pre>
     * SQL : select * from user_info where username=:user and password=:pwd limit :offset,:limit
     * params: user,pwd,offset,limit
     * </pre>
     * @return
     */
	public LinkedHashSet<SqlParameter> getParams() {
		return params;
	}
	public void setParams(LinkedHashSet<SqlParameter> params) {
		this.params = params;
	}
	public SqlParameter getParam(String paramName) {
		for(SqlParameter p : getParams()) {
			if(p.getParamName().equals(paramName)) {
				return p;
			}
		}
		return null;
	}

    /**
     * 得到SQL原始语句
     * @return
     */
	public String getSourceSql() {
		return sourceSql;
	}
	public void setSourceSql(String sourceSql) {
		this.sourceSql = sourceSql;
	}
	
	public String getSqlmap() {
		return getSqlmap(getParamNames());
	}
	
	public void setSqlmap(String sqlmap) {
	    if(StringHelper.isNotBlank(sqlmap)) {
	        sqlmap = StringHelper.replace(sqlmap, "${cdata-start}", "<![CDATA[");
	        sqlmap = StringHelper.replace(sqlmap, "${cdata-end}", "]]>");
	    }
	    this.sqlmap = sqlmap;
	}

    private List<String> getParamNames() {
        List<String> paramNames = new ArrayList<String>();
        for(SqlParameter p : params) {
            paramNames.add(p.getParamName());
        }
        return paramNames;
    }
	   
    private String getSqlmap(List<String> params) {
        if (params == null || params.size() == 0) {
            return sqlmap;
        }

        String result = sqlmap;

        if (params.size() == 1) {
        	//FIXME: 与dalgen相比,修正是否将 ${param1} 的替换值是: value
            return StringHelper.replace(result, "${param1}", "value");
        } else {
            for (int i = 0; i < params.size(); i++) {
                result = StringHelper.replace(result, "${param" + (i + 1) + "}", params.get(i));
            }
        }

        return result;
    }
	
	public boolean isHasSqlMap() {
		return StringHelper.isNotBlank(sqlmap);
	}
	
	public String getResultMap() {
        return resultMap;
    }

    public void setResultMap(String resultMap) {
        this.resultMap = resultMap;
    }

    //	public String replaceParamsWith(String prefix,String suffix) {
//		String sql = sourceSql;
//		List<SqlParameter> sortedParams = new ArrayList(params);
//		Collections.sort(sortedParams,new Comparator<SqlParameter>() {
//			public int compare(SqlParameter o1, SqlParameter o2) {
//				return o2.paramName.length() - o1.paramName.length();
//			}
//		});
    // for(SqlParameter s : sortedParams){ //FIXME 现在只实现了:username参数替换
//			sql = StringHelper.replace(sql,":"+s.getParamName(),prefix+s.getParamName()+suffix);
//		}
//		return sql;
//	}
    /**
     * sourceSql转换为在数据库实际执行的SQL,
     * 示例:
     * <pre>
     * sourceSql: select * from user where username=:username and password=:password
     * executeSql: select * from user where username=? and password=?
     * </pre>
     * @return
     */
	public String getExecuteSql() {
		return executeSql;
	}
	
	public void setExecuteSql(String executeSql) {
		this.executeSql = executeSql;
	}
	
    public String getCountHql() {
        return toCountSqlForPaging(getHql());
    }
	   
	public String getCountSql() {
	    return toCountSqlForPaging(getSql());
	}

    public String getIbatisCountSql() {
        return toCountSqlForPaging(getIbatisSql());
    }
    
    public String getMybatisCountSql() {
        return toCountSqlForPaging(getMybatisSql());
    }

    public String getSqlmapCountSql() {
        return toCountSqlForPaging(getSqlmap());
    }
    
	public String getSql() {
		return replaceWildcardWithColumnsSqlName(sourceSql);
	}
	
	public static String toCountSqlForPaging(String sql) {
	    if(sql == null) return null;
	    if(SqlTypeChecker.isSelectSql(sql)) {
            return SqlParseHelper.toCountSqlForPaging(sql, "select count(*) ");
	    }
	    return sql;
	}
	
	public String getSpringJdbcSql() {
		return SqlParseHelper.convert2NamedParametersSql(getSql(),":","");
	}
	
	public String getHql() {
		return SqlParseHelper.convert2NamedParametersSql(getSql(),":","");
	}
	
	public String getIbatisSql() {
	    return StringHelper.isBlank(ibatisSql) ? SqlParseHelper.convert2NamedParametersSql(getSql(),"#","#") : ibatisSql;
	}
	
	public String getMybatisSql() {
	    return StringHelper.isBlank(mybatisSql) ? SqlParseHelper.convert2NamedParametersSql(getSql(),"#{","}") : mybatisSql;
	}

	public void setIbatisSql(String ibatisSql) {
        this.ibatisSql = ibatisSql;
    }

    public void setMybatisSql(String mybatisSql) {
        this.mybatisSql = mybatisSql;
    }

    private String joinColumnsSqlName() {
        // TODO 未解决 a.*,b.*问题
		StringBuffer sb = new StringBuffer();
		for(Iterator<Column> it = columns.iterator();it.hasNext();) {
			Column c = it.next();
			sb.append(c.getSqlName());
			if(it.hasNext()) sb.append(",");
		}
		return sb.toString();
	}
	
	public String replaceWildcardWithColumnsSqlName(String sql) {
		if(SqlTypeChecker.isSelectSql(sql) && SqlParseHelper.getSelect(SqlParseHelper.removeSqlComments(sql)).indexOf("*") >= 0 && SqlParseHelper.getSelect(SqlParseHelper.removeSqlComments(sql)).indexOf("count(") < 0) {
			return SqlParseHelper.getPrettySql("select " + joinColumnsSqlName() + " " + SqlParseHelper.removeSelect(sql));
		}else {
			return sql;
		}
	}

	public List<SqlSegment> getSqlSegments() {
		return sqlSegments;
	}

	public void setSqlSegments(List<SqlSegment> includeSqls) {
		this.sqlSegments = includeSqls;
	}
	
	public SqlSegment getSqlSegment(String id) {
		for(SqlSegment seg : sqlSegments) {
			if(seg.getId().equals(id)) {
				return seg;
			}
		}
		return null;
	}
	
	public List<SqlParameter> getFilterdWithSqlSegmentParams() {
		List<SqlParameter> result = new ArrayList<SqlParameter>();
		for(SqlParameter p : getParams()) {
			if(isSqlSegementContainsParam(p.getParamName())) {
				continue;
			}
			result.add(p);
		}
		return result;
	}
	
    private boolean isSqlSegementContainsParam(String paramName) {
		for(SqlSegment seg : getSqlSegments()) {
			//TODO 增加如果参数数是1,则不生成  SqlSegemnt,此处也要修改对1的特殊控制
			if(seg.getParamNames().contains(paramName)) {
				return true;
			}
		}
		return false;
	}

	/**
     * 当前的sourceSql是否是select语句
     * @return
     */
	public boolean isSelectSql() {
		return SqlTypeChecker.isSelectSql(sourceSql);
	}

    /**
     * 当前的sourceSql是否是update语句
     * @return
     */
	public boolean isUpdateSql() {
		return SqlTypeChecker.isUpdateSql(sourceSql);
	}

    /**
     * 当前的sourceSql是否是delete语句
     * @return
     */
	public boolean isDeleteSql() {
		return SqlTypeChecker.isDeleteSql(sourceSql);
	}

    /**
     * 当前的sourceSql是否是insert语句
     * @return
     */
	public boolean isInsertSql() {
		return SqlTypeChecker.isInsertSql(sourceSql);
	}

    /**
     * 得到备注
     * @return
     */
	public String getRemarks() {
		return remarks;
	}
	
	public String getParamType() {
		return paramType;
	}

	public void setParamType(String paramType) {
		this.paramType = paramType;
	}

	public void setRemarks(String comments) {
		this.remarks = comments;
	}
	
	public boolean isPaging() {
		if(MULTIPLICITY_PAGING.equalsIgnoreCase(multiplicity)) {
			return true;
		}
        return paging;
    }

    public void setPaging(boolean paging) {
        this.paging = paging;
    }

	public Column getColumnBySqlName(String sqlName) {
		for(Column c : getColumns()) {
			if(c.getSqlName().equalsIgnoreCase(sqlName)) {
				return c;
			}
		}
		return null;
	}
	
	public Column getColumnByName(String name) {
	    Column c = getColumnBySqlName(name);
	    if(c == null) {
	    	c = getColumnBySqlName(StringHelper.toUnderscoreName(name));
	    }
	    return c;
	}
	
	public void afterPropertiesSet() {
		for(SqlSegment seg : sqlSegments) {
			seg.setParams(seg.getParams(this));
		}
	}
	
	public String toString() {
		return "sourceSql:\n"+sourceSql+"\nsql:"+getSql();
	}
	
	private String ibatisSql;
	private String mybatisSql;
}