SqlFactory.java

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

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashMap;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import cn.org.rapid_framework.generator.provider.db.DataSourceProvider;
import cn.org.rapid_framework.generator.provider.db.sql.model.Sql;
import cn.org.rapid_framework.generator.provider.db.sql.model.SqlParameter;
import cn.org.rapid_framework.generator.provider.db.table.TableFactory;
import cn.org.rapid_framework.generator.provider.db.table.TableFactory.NotFoundTableException;
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.BeanHelper;
import cn.org.rapid_framework.generator.util.DBHelper;
import cn.org.rapid_framework.generator.util.GLogger;
import cn.org.rapid_framework.generator.util.StringHelper;
import cn.org.rapid_framework.generator.util.sqlerrorcode.SQLErrorCodeSQLExceptionTranslator;
import cn.org.rapid_framework.generator.util.sqlparse.BasicSqlFormatter;
import cn.org.rapid_framework.generator.util.sqlparse.NamedParameterUtils;
import cn.org.rapid_framework.generator.util.sqlparse.ParsedSql;
import cn.org.rapid_framework.generator.util.sqlparse.ResultSetMetaDataHolder;
import cn.org.rapid_framework.generator.util.sqlparse.SqlParseHelper;
import cn.org.rapid_framework.generator.util.sqlparse.StatementCreatorUtils;
import cn.org.rapid_framework.generator.util.sqlparse.SqlParseHelper.NameWithAlias;
import cn.org.rapid_framework.generator.util.typemapping.JdbcType;
/**
 * 
 * 根据SQL语句生成Sql对象,用于代码生成器的生成<br />
 * 
 * 示例使用：
 * <pre>
 * Sql sql = new SqlFactory().parseSql("select * from user_info where username=#username# and password=#password#");
 * </pre>
 * 
 * @author badqiu
 *
 */
public class SqlFactory {
    
    public SqlFactory() {
    }
    
    public Sql parseSql(String sourceSql) {
    	if(StringHelper.isBlank(sourceSql)) throw new IllegalArgumentException("sourceSql must be not empty");
    	String beforeProcessedSql = beforeParseSql(sourceSql);
    	
//    	String unscapedSourceSql = StringHelper.unescapeXml(beforeProcessedSql);
    	String namedSql = SqlParseHelper.convert2NamedParametersSql(beforeProcessedSql,":","");
        ParsedSql parsedSql = NamedParameterUtils.parseSqlStatement(namedSql);
        String executeSql = new BasicSqlFormatter().format(NamedParameterUtils.substituteNamedParameters(parsedSql));
        
        Sql sql = new Sql();
        sql.setSourceSql(sourceSql);
        sql.setExecuteSql(executeSql);
        GLogger.debug("\n*******************************");
        GLogger.debug("sourceSql  :"+sql.getSourceSql());
        GLogger.debug("namedSql  :"+namedSql);
        GLogger.debug("executeSql :"+sql.getExecuteSql());
        GLogger.debug("*********************************");
        
        Connection conn = null;
        PreparedStatement ps = null;
        try {
        	conn = DataSourceProvider.getNewConnection();
        	conn.setAutoCommit(false);
//        	if(DatabaseMetaDataUtils.isMysqlDataBase(conn.getMetaData())){
//        		conn.setReadOnly(true);
//        	}
	        ps = conn.prepareStatement(SqlParseHelper.removeOrders(executeSql));
	        
	        SqlParametersParser sqlParametersParser = new SqlParametersParser();
            sqlParametersParser.execute(parsedSql,sql);
            
	        ResultSetMetaData resultSetMetaData = executeSqlForResultSetMetaData(executeSql,ps,sqlParametersParser.allParams);
            sql.setColumns(new SelectColumnsParser().convert2Columns(sql,resultSetMetaData));
            sql.setParams(sqlParametersParser.params);
	        
	        return afterProcessedSql(sql);
        }catch(SQLException e) {
        	throw new RuntimeException("execute sql occer error,\nexecutedSql:"+SqlParseHelper.removeOrders(executeSql),e);
        }catch(Exception e) {
        	throw new RuntimeException("sql parse error,\nexecutedSql:"+SqlParseHelper.removeOrders(executeSql),e);
        }finally {
        	try {
	        	DBHelper.rollback(conn);
        	}finally {
        		DBHelper.close(conn,ps,null);
        	}
        }
    }

    protected Sql afterProcessedSql(Sql sql) {
		return sql;
	}

	protected String beforeParseSql(String sourceSql) {
		return sourceSql;
	}

    private ResultSetMetaData executeSqlForResultSetMetaData(String sql,PreparedStatement ps,List<SqlParameter> params)throws SQLException {
//		SqlParseHelper.setRandomParamsValueForPreparedStatement(SqlParseHelper.removeOrders(executeSql), ps);
		StatementCreatorUtils.setRandomParamsValueForPreparedStatement(sql, ps, params);
		
		try {
			ps.setMaxRows(3);
	        ps.setFetchSize(3);
	        ps.setQueryTimeout(20);
	        ResultSet rs = null;
			if(ps.execute()) {
				rs = ps.getResultSet();
				return rs.getMetaData();
			}
			return null;
		}catch(SQLException e) {
			if(isDataIntegrityViolationException(e)) {
			    GLogger.warn("ignore executeSqlForResultSetMetaData() SQLException,errorCode:"+e.getErrorCode()+" sqlState:"+e.getSQLState()+" message:"+e.getMessage()+ "\n executedSql:"+sql);
				return null;
			}
			String message = "errorCode:"+e.getErrorCode()+" SQLState:"+e.getSQLState()+" errorCodeTranslatorDataBaaseName:"+getErrorCodeTranslatorDataBaaseName()+" "+ e.getMessage();
			throw new SQLException(message,e.getSQLState(),e.getErrorCode());
		}
	}

    private String getErrorCodeTranslatorDataBaaseName() {
        SQLErrorCodeSQLExceptionTranslator transaltor = SQLErrorCodeSQLExceptionTranslator.getSQLErrorCodeSQLExceptionTranslator(DataSourceProvider.getDataSource());
        if(transaltor.getSqlErrorCodes() == null) return "null";
        return Arrays.toString(transaltor.getSqlErrorCodes().getDatabaseProductNames());
    }

    /** 判断是否是外键,完整性约束等异常 引发的异常 */
    protected boolean isDataIntegrityViolationException(SQLException sqlEx) {
        SQLErrorCodeSQLExceptionTranslator transaltor = SQLErrorCodeSQLExceptionTranslator.getSQLErrorCodeSQLExceptionTranslator(DataSourceProvider.getDataSource());
        return transaltor.isDataIntegrityViolation(sqlEx);
	}

	public static Map<String,Table> cache = new HashMap<String,Table>();
    public static Table getTableFromCache(String tableSqlName) {
        if(tableSqlName == null) throw new IllegalArgumentException("tableSqlName must be not null");
        
        Table table = cache.get(tableSqlName.toLowerCase());
        if(table == null) {
            table = TableFactory.getInstance().getTable(tableSqlName);
            cache.put(tableSqlName.toLowerCase(), table);
        }
        return table;
    }
	
    public static class SelectColumnsParser {
    
		private LinkedHashSet<Column> convert2Columns(Sql sql,ResultSetMetaData metadata) throws SQLException, Exception {
			if(metadata == null) return new LinkedHashSet();
			LinkedHashSet<Column> columns = new LinkedHashSet();
	        for(int i = 1; i <= metadata.getColumnCount(); i++) {
	        	Column c = convert2Column(sql,metadata, i);
	        	if(c == null) throw new IllegalStateException("column must be not null");
				columns.add(c);
	        }
			return columns;
		}
	
        private Column convert2Column(Sql sql,ResultSetMetaData metadata, int i) throws SQLException, Exception {
			ResultSetMetaDataHolder m = new ResultSetMetaDataHolder(metadata, i);
			if(StringHelper.isNotBlank(m.getTableName())) {
				//FIXME 如果表有别名,将会找不到表,如 inner join user_info t1, tableName将为t1,应该转换为user_info
				Table table = foundTableByTableNameOrTableAlias(sql, m.getTableName());
				if(table == null) {
					return newColumn(null,m);
				}
			    Column column = table.getColumnBySqlName(m.getColumnLabelOrName());
			    if(column == null || column.getSqlType() != m.getColumnType()) {
			        //可以再尝试解析sql得到 column以解决 password as pwd找不到column问题
			        column = newColumn(table,m);
			        GLogger.trace("not found column:"+m.getColumnLabelOrName()+" on table:"+table.getSqlName()+" "+BeanHelper.describe(column));
			        //isInSameTable以此种判断为错误
			    }else {
			    	GLogger.trace("found column:"+m.getColumnLabelOrName()+" on table:"+table.getSqlName()+" "+BeanHelper.describe(column));
			    }
			    return column;
			}else {
			    return newColumn(null,m);
			}
		}

		private Column newColumn(Table table,ResultSetMetaDataHolder m) {
			//Table table, int sqlType, String sqlTypeName,String sqlName, int size, int decimalDigits, boolean isPk,boolean isNullable, boolean isIndexed, boolean isUnique,String defaultValue,String remarks
			Column column = new Column(null,m.getColumnType(),m.getColumnTypeName(),m.getColumnLabelOrName(),m.getColumnDisplaySize(),m.getScale(),false,false,false,false,null,null);
			GLogger.trace("not found on table by table emtpty:"+BeanHelper.describe(column));
			return column;
		}

		private Table foundTableByTableNameOrTableAlias(Sql sql,String tableNameId) throws Exception {
			try {
				return getTableFromCache(tableNameId);
			}catch(NotFoundTableException e) {
				Set<NameWithAlias> tableNames = SqlParseHelper.getTableNamesByQuery(sql.getExecuteSql());
				for(NameWithAlias tableName : tableNames) {
					if(tableName.getAlias().equalsIgnoreCase(tableNameId)) {
						return getTableFromCache(tableName.getName());
					}
				}
			}
			return null;
		}
    }

	public static class SqlParametersParser {
		private static Map<String,Column> specialParametersMapping = new HashMap<String,Column>();
		{
			specialParametersMapping.put("offset", new Column(null,JdbcType.INTEGER.TYPE_CODE,"INTEGER","offset",0,0,false,false,false,false,null,null));
			specialParametersMapping.put("limit", new Column(null,JdbcType.INTEGER.TYPE_CODE,"INTEGER","limit",0,0,false,false,false,false,null,null));
            specialParametersMapping.put("pageSize", new Column(null,JdbcType.INTEGER.TYPE_CODE,"INTEGER","pageSize",0,0,false,false,false,false,null,null));
            specialParametersMapping.put("pageNo", new Column(null,JdbcType.INTEGER.TYPE_CODE,"INTEGER","pageNo",0,0,false,false,false,false,null,null));
            specialParametersMapping.put("pageNumber", new Column(null,JdbcType.INTEGER.TYPE_CODE,"INTEGER","pageNumber",0,0,false,false,false,false,null,null));
            specialParametersMapping.put("pageNum", new Column(null,JdbcType.INTEGER.TYPE_CODE,"INTEGER","pageNumber",0,0,false,false,false,false,null,null));
            specialParametersMapping.put("page", new Column(null,JdbcType.INTEGER.TYPE_CODE,"INTEGER","page",0,0,false,false,false,false,null,null));
			
			specialParametersMapping.put("beginRow", new Column(null,JdbcType.INTEGER.TYPE_CODE,"INTEGER","beginRow",0,0,false,false,false,false,null,null));
			specialParametersMapping.put("beginRows", new Column(null,JdbcType.INTEGER.TYPE_CODE,"INTEGER","beginRows",0,0,false,false,false,false,null,null));
			specialParametersMapping.put("startRow", new Column(null,JdbcType.INTEGER.TYPE_CODE,"INTEGER","startRow",0,0,false,false,false,false,null,null));
			specialParametersMapping.put("startRows", new Column(null,JdbcType.INTEGER.TYPE_CODE,"INTEGER","startRows",0,0,false,false,false,false,null,null));
			specialParametersMapping.put("endRow", new Column(null,JdbcType.INTEGER.TYPE_CODE,"INTEGER","endRow",0,0,false,false,false,false,null,null));
			specialParametersMapping.put("endRows", new Column(null,JdbcType.INTEGER.TYPE_CODE,"INTEGER","endRows",0,0,false,false,false,false,null,null));
			specialParametersMapping.put("lastRow", new Column(null,JdbcType.INTEGER.TYPE_CODE,"INTEGER","lastRow",0,0,false,false,false,false,null,null));
			specialParametersMapping.put("lastRows", new Column(null,JdbcType.INTEGER.TYPE_CODE,"INTEGER","lastRows",0,0,false,false,false,false,null,null));
			
			specialParametersMapping.put("orderBy", new Column(null,JdbcType.VARCHAR.TYPE_CODE,"VARCHAR","orderBy",0,0,false,false,false,false,null,null));
			specialParametersMapping.put("orderby", new Column(null,JdbcType.VARCHAR.TYPE_CODE,"VARCHAR","orderby",0,0,false,false,false,false,null,null));
			specialParametersMapping.put("sortColumns", new Column(null,JdbcType.VARCHAR.TYPE_CODE,"VARCHAR","sortColumns",0,0,false,false,false,false,null,null));
		}
		
		public LinkedHashSet<SqlParameter> params = new LinkedHashSet<SqlParameter>();
		public List<SqlParameter> allParams = new ArrayList<SqlParameter>();
		
		private void execute(ParsedSql parsedSql,Sql sql) throws Exception {
			long start = System.currentTimeMillis();
			for(int i = 0; i < parsedSql.getParameterNames().size(); i++) {
				String paramName = parsedSql.getParameterNames().get(i);
				Column column = findColumnByParamName(parsedSql, sql, paramName);
				if(column == null) {
					column = specialParametersMapping.get(paramName);
					if(column == null) {
						//FIXME 不能猜测的column类型
						column = new Column(null,JdbcType.UNDEFINED.TYPE_CODE,"UNDEFINED",paramName,0,0,false,false,false,false,null,null);
					}
				}
				SqlParameter param = new SqlParameter(column);
				
				param.setParamName(paramName);
				if(isMatchListParam(sql.getSourceSql(), paramName)) { //FIXME 只考虑(:username)未考虑(#inUsernames#) and (#{inPassword}),并且可以使用 #inUsername[]#
					param.setListParam(true);
				}
				params.add(param);
				allParams.add(param);
			}
			GLogger.perf("parseForSqlParameters() cost:"+(System.currentTimeMillis()- start));
		}

		public boolean isMatchListParam(String sql, String paramName) {
			return 
			    sql.matches("(?s).*\\sin\\s*\\([:#\\$&]\\{?"+paramName+"\\}?[$#}]?\\).*") // match in (:username) ,not in (#username#)
			    || sql.matches("(?s).*[#$]"+paramName+"\\[]\\.?\\w*[#$].*") //match #user[]# $user[]$ #user[].age# for ibatis
			   || sql.matches("(?s).*[#$]\\{"+paramName+"\\[[$\\{\\}\\w]+]\\}*.*"); //match #{user[index]}# ${user[${index}]}  for mybatis
		}
	
		private Column findColumnByParamName(ParsedSql parsedSql,Sql sql, String paramName) throws Exception {
			Column column = sql.getColumnByName(paramName);
			if(column == null) {
				//FIXME 还未处理 t.username = :username的t前缀问题,应该直接根据 t.确定属于那一张表,不需要再猜测
				String leftColumn = SqlParseHelper.getColumnNameByRightCondition(parsedSql.toString(), paramName);
				if(leftColumn != null) {
					column = findColumnByParseSql(parsedSql, leftColumn );
				}
			}
			if(column == null) {
				column = findColumnByParseSql(parsedSql, paramName);
			}
			return column;
		}
	
		private Column findColumnByParseSql(ParsedSql sql, String paramName) throws Exception {
			if(paramName == null) throw new NullPointerException("'paramName' must be not null");
			try {
    			Collection<NameWithAlias> tableNames = SqlParseHelper.getTableNamesByQuery(sql.toString());
    			for(NameWithAlias tableName : tableNames) {
    				Table t = getTableFromCache(tableName.getName());
    				if(t != null) {
    					Column column = t.getColumnByName(paramName);
    					if(column != null) {
    						return column;
    					}
    				}
    			}
			}catch(NotFoundTableException e) {
			    throw new IllegalArgumentException("get tableNamesByQuery occer error:"+sql.toString(),e);
			}
			return null;
		}
	}
    
}