*

* 根据SQL语句生成Sql对象,用于代码生成器的生成<br />

*

* 示例使用：

* <pre>

* Sql sql = new SqlFactory().parseSql("select * from user_info where username=#username# and password=#password#");

* </pre>

*

* @author badqiu

*

*/

   

    private List<SqlParameter> customParameters = new ArrayList<SqlParameter>();

   

    public SqlFactory() {

    }

   

  public SqlFactory(List<SqlParameter> customParameters) {

        this.customParameters = customParameters;

    }

    public Sql parseSql(String sourceSql) {

      if(StringHelper.isBlank(sourceSql)) throw new IllegalArgumentException("sourceSql must be not empty");

      String beforeProcessedSql = beforeParseSql(sourceSql);

     

      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 = DataSourceProvider.getConnection();

        try {

          if(!DatabaseMetaDataUtils.isHsqlDataBase(conn.getMetaData())){

            conn.setReadOnly(true);

          }

          conn.setAutoCommit(false);

          PreparedStatement ps = conn.prepareStatement(SqlParseHelper.removeOrders(executeSql));

          ResultSetMetaData resultSetMetaData = executeForResultSetMetaData(executeSql,ps);

            sql.setColumns(new SelectColumnsParser().convert2Columns(sql,resultSetMetaData));

          sql.setParams(new SqlParametersParser().parseForSqlParameters(parsedSql,sql));

         

          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 {

            conn.rollback();

            conn.close();

          }catch(Exception e) {

            throw new RuntimeException(e);

          }

        }

    }

    protected Sql afterProcessedSql(Sql sql) {

    return sql;

  }

  protected String beforeParseSql(String sourceSql) {

    return sourceSql;

  }

    private ResultSetMetaData executeForResultSetMetaData(String executeSql,PreparedStatement ps)throws SQLException {

    SqlParseHelper.setRandomParamsValueForPreparedStatement(SqlParseHelper.removeOrders(executeSql), ps);

    ps.setMaxRows(3);

        ps.setFetchSize(3);

        ps.setQueryTimeout(20);

        try {

      ResultSet rs = ps.executeQuery();

      return rs.getMetaData();

    } catch (SQLException e) {

      try {

        return ps.getMetaData();

      }catch(SQLException ee) {

        throw e;

      }

    }

  }

 

    public 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.getColumnNameOrLabel());

          if(column == null || column.getSqlType() != m.getColumnType()) {

              //可以再尝试解析sql得到 column以解决 password as pwd找不到column问题

              column = newColumn(table,m);

              GLogger.trace("not found column:"+m.getColumnNameOrLabel()+" on table:"+table.getSqlName()+" "+BeanHelper.describe(column));

              //isInSameTable以此种判断为错误

          }else {

            GLogger.trace("found column:"+m.getColumnNameOrLabel()+" 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.getColumnNameOrLabel(),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 TableFactory.getInstance().getTable(tableNameId);

      }catch(NotFoundTableException e) {

        Set<NameWithAlias> tableNames = SqlParseHelper.getTableNamesByQuery(sql.getExecuteSql());

        for(NameWithAlias tableName : tableNames) {

          if(tableName.getAlias().equalsIgnoreCase(tableNameId)) {

            return TableFactory.getInstance().getTable(tableName.getName());

          }

        }

      }

      return null;

    }

    }

  public class SqlParametersParser {

    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("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));

    }

    private LinkedHashSet<SqlParameter> parseForSqlParameters(ParsedSql parsedSql,Sql sql) throws Exception {

      LinkedHashSet<SqlParameter> result = new LinkedHashSet<SqlParameter>();

      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);

        }

        result.add(param);     

      }

      GLogger.perf("parseForSqlParameters() cost:"+(System.currentTimeMillis()- start));

      return result;

     

    }

    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 {

        for(SqlParameter customParam : customParameters) {

            if(customParam.getParamName().equals(paramName)) {

                return customParam;

            }

        }

      Column column = sql.getColumnByName(paramName);

      if(column == null) {

        //FIXME 还未处理 t.username = :username的t前缀问题

        column = findColumnByParseSql(parsedSql, SqlParseHelper.getColumnNameByRightCondition(parsedSql.toString(), paramName) );

      }

      if(column == null) {

        column = findColumnByParseSql(parsedSql, paramName);

      }

      return column;

    }

 

    private Column findColumnByParseSql(ParsedSql sql, String paramName) throws Exception {

      Collection<NameWithAlias> tableNames = SqlParseHelper.getTableNamesByQuery(sql.toString());

      for(NameWithAlias tableName : tableNames) {

        Table t = TableFactory.getInstance().getTable(tableName.getName());

        if(t != null) {

          Column column = t.getColumnByName(paramName);

          if(column != null) {

            return column;

          }

        }

      }

      return null;

    }

  }

   

    public static void main(String[] args) throws Exception {

      // ? parameters

//      SelectSqlMetaData t1 = new SqlQueryFactory().getByQuery("select * from user_info");

//      SelectSqlMetaData t2 = new SqlQueryFactory().getByQuery("select user_info.username,password pwd from user_info where username=? and password =?");

//      SelectSqlMetaData t3 = new SqlQueryFactory().getByQuery("select username,password,role.role_name,role_desc from user_info,role where user_info.user_id = role.user_id and username=? and password =?");

//      SelectSqlMetaData t4 = new SqlQueryFactory().getByQuery("select count(*) cnt from user_info,role where user_info.user_id = role.user_id and username=? and password =?");

//      SelectSqlMetaData t5 = new SqlQueryFactory().getByQuery("select sum(age) from user_info,role where user_info.user_id = role.user_id and username=? and password =?");

//      SelectSqlMetaData t6 = new SqlQueryFactory().getByQuery("select username,password,role_desc from user_info,role where user_info.user_id = role.user_id and username=? and password =? limit ?,?");

//      SelectSqlMetaData t7 = new SqlQueryFactory().getByQuery("select username,password,count(role_desc) role_desc_cnt from user_info,role where user_info.user_id = role.user_id group by username");

//   

      Sql n2 = new SqlFactory().parseSql("select user_info.username,password pwd from user_info where username=:username and password =:password");

      Sql n3 = new SqlFactory().parseSql("select username,password,role.role_name,role_desc from user_info,role where user_info.user_id = role.user_id and username=:username and password =:password");

      Sql n4 = new SqlFactory().parseSql("select count(*) cnt from user_info,role where user_info.user_id = role.user_id and username=:username and password =:password");

      Sql n5 = new SqlFactory().parseSql("select sum(age) from user_info,role where user_info.user_id = role.user_id and username=:username and password =:password");

      Sql n7 = new SqlFactory().parseSql("select username,password,count(role_desc) role_desc_cnt from user_info,role where user_info.user_id = role.user_id group by username");

      Sql n8 = new SqlFactory().parseSql("select username,password,count(role_desc) role_desc_cnt from user_info,role where user_info.user_id = :userId group by username");

      new SqlFactory().parseSql("select username,password,role_desc from user_info,role where user_info.user_id = role.user_id and username=:username and password =:password and birth_date between :birthDateBegin and :birthDateEnd");

      new SqlFactory().parseSql("select username,password,role_desc from user_info,role where user_info.user_id = role.user_id and username=:username and password =:password and birth_date between :birthDateBegin and :birthDateEnd limit :offset,:limit");

    }

   

}