package cn.org.rapid_framework.generator.provider.db.sql.model;
import java.util.Iterator;
import java.util.LinkedHashSet;
import java.util.List;
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.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;
/**
* 用于生成代码的Sql对象.对应数据库的sql语句
* 使用SqlFactory.parseSql()生成 <br />
*
* SQL参数同时支持以下几种语法
* <pre>
* hibernate: :username,
* ibatis2: #username#,$usename$,
* mybatis(or ibatis3): #{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";
public static String MULTIPLICITY_MANY = "many";
public static String MULTIPLICITY_PAGING = "paging";
String tableSqlName = null; // 是否需要
String operation = null;
String resultClass;
String parameterClass;
String remarks;
String multiplicity = MULTIPLICITY_ONE; /* many or one or paging */
boolean paging = false; // 是否分页查询
String sqlmap; /* for ibatis and ibatis3 */
LinkedHashSet<Column> columns = new LinkedHashSet<Column>();
LinkedHashSet<SqlParameter> params = new LinkedHashSet<SqlParameter>();
String sourceSql; // source sql
String executeSql;
private String paramType = "primitive"; /* primitive or object */
public Sql() {
}
public Sql(Sql sql) {
this.tableSqlName = sql.tableSqlName;
this.operation = sql.operation;
this.parameterClass = sql.parameterClass;
this.resultClass = sql.resultClass;
this.multiplicity = sql.multiplicity;
this.columns = sql.columns;
this.params = sql.params;
this.sourceSql = sql.sourceSql;
this.executeSql = sql.executeSql;
this.remarks = sql.remarks;
}
public boolean isColumnsInSameTable() {
// FIXME 还要增加表的列数与columns是否相等,才可以为select 生成 include语句
if(columns == null || columns.isEmpty()) 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() {
if(StringHelper.isNotBlank(resultClass)) return resultClass;
if(columns.size() == 1) {
return columns.iterator().next().getSimpleJavaType();
}
if(isColumnsInSameTable()) {
return columns.iterator().next().getTable().getClassName();
}else {
if(operation == null) return null;
return StringHelper.makeAllWordFirstLetterUpperCase(StringHelper.toUnderscoreName(operation))+System.getProperty("generator.sql.resultClass.suffix","Result");
}
}
public void setResultClass(String queryResultClass) {
this.resultClass = queryResultClass;
}
/**
* 返回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 sqlmap;
}
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;
}
public String getSqlmap(List<String> params) {
if (params == null || params.size() == 0) {
return sqlmap;
}
String result = sqlmap;
if (params.size() == 1) {
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 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 getIbatis3CountSql() {
return toCountSqlForPaging(getIbatis3Sql());
}
public String getSqlmapCountSql() {
return toCountSqlForPaging(getSqlmap());
}
public String getSql() {
return replaceWildcardWithColumnsSqlName(sourceSql);
}
public String toCountSqlForPaging(String sql) {
if(sql == null) return null;
if(isSelectSql()) {
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 getIbatis3Sql() {
return StringHelper.isBlank(ibatis3Sql) ? SqlParseHelper.convert2NamedParametersSql(getSql(),"#{","}") : ibatis3Sql;
}
public void setIbatisSql(String ibatisSql) {
this.ibatisSql = ibatisSql;
}
public void setIbatis3Sql(String ibatis3Sql) {
this.ibatis3Sql = ibatis3Sql;
}
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;
}
}
/**
* 当前的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);
}
/**
* 得到表相对应的sqlName,主要用途为生成文件时的分组.
* @return
*/
public String getTableSqlName() {
return tableSqlName;
}
public void setTableSqlName(String tableName) {
this.tableSqlName = tableName;
}
/**
* 得到备注
* @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;
}
/**
* 根据tableSqlName和成相对应的tableClassName,主要用途路径变量引用.如${tableClassName}Dao.java
* @return
*/
public String getTableClassName() {
if(StringHelper.isBlank(tableSqlName)) return null;
String removedPrefixSqlName = Table.removeTableSqlNamePrefix(tableSqlName);
return StringHelper.makeAllWordFirstLetterUpperCase(StringHelper.toUnderscoreName(removedPrefixSqlName));
}
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 String toString() {
return "sourceSql:\n"+sourceSql+"\nsql:"+getSql();
}
private String ibatisSql;
private String ibatis3Sql;
}