net.sf.jsqlparser.statement.Statement Java Examples

@Test
public void testWithNolock(){
    String sql = "SELECT * FROM A WITH(NOLOCK) INNER JOIN B WITH(NOLOCK) ON A.TypeId = B.Id";
    System.out.println(sql);
    sql = sql.replaceAll("((?i)\\s*(\\w?)\\s*with\\s*\\(nolock\\))", " $2_PAGEWITHNOLOCK");
    System.out.println(sql);
    //解析SQL
    Statement stmt = null;
    try {
        stmt = CCJSqlParserUtil.parse(sql);
    } catch (Throwable e) {
        e.printStackTrace();
        return;
    }
    Select select = (Select) stmt;
    SelectBody selectBody = select.getSelectBody();
    sql = selectBody.toString();

    sql = sql.replaceAll("\\s*(\\w*?)_PAGEWITHNOLOCK", " $1 WITH(NOLOCK)");

    System.out.println(sql);
}
 

@Override
public SqlStatementWrapper<Statement> transform(SqlStatementWrapper<Statement> statementWrapper, TransformConfig config) {
    if (Emptys.isEmpty(statementWrapper) || Emptys.isEmpty(config)) {
        return statementWrapper;
    }
    Statement statement = statementWrapper.get();
    List<WhereTransformConfig> expressionConfigs = config.getWhereInstrumentConfigs();
    if (Emptys.isEmpty(statement) || Emptys.isEmpty(expressionConfigs)) {
        return statementWrapper;
    }
    if (!JSqlParsers.isDML(statement)) {
        return statementWrapper;
    }

    if (Reflects.isSubClassOrEquals(Select.class, statement.getClass())) {
        transform((Select) statement, false, expressionConfigs);
    } else if (Reflects.isSubClassOrEquals(Update.class, statement.getClass())) {
        transform((Update) statement, expressionConfigs);
    } else if (Reflects.isSubClassOrEquals(Delete.class, statement.getClass())) {
        transform((Delete) statement, expressionConfigs);
    } else if (Reflects.isSubClassOrEquals(Insert.class, statement.getClass())) {
        transform((Insert) statement, config.getTenant());
    }

    return statementWrapper;
}
 

public static void parseRemove(String sql, Table table, Condition condition)
        throws JSQLParserException, FrontException {
    Statement statement = CCJSqlParserUtil.parse(sql);
    Delete delete = (Delete) statement;

    // parse table name
    net.sf.jsqlparser.schema.Table sqlTable = delete.getTable();
    table.setTableName(sqlTable.getName());

    // parse where clause
    Expression where = delete.getWhere();
    if (where != null) {
        BinaryExpression expr = (BinaryExpression) (where);
        handleExpression(condition, expr);
    }
    Limit limit = delete.getLimit();
    parseLimit(condition, limit);
}
 

@Override
public SqlStatementWrapper<Statement> transform(@NonNull SqlStatementWrapper<Statement> sw, @NonNull TransformConfig config) {
    Preconditions.checkNotNull(sw);
    Preconditions.checkNotNull(config);
    OrderBy orderBy = Preconditions.checkNotNull(config.getOrderBy());
    final Statement statement = sw.get();
    Preconditions.checkNotNull(statement, "statement is null");
    Preconditions.checkTrue(statement instanceof Select, new Supplier<Object[], String>() {
        @Override
        public String get(Object[] input) {
            return StringTemplates.formatWithPlaceholder("statement is not a select statement: {}", statement.toString());
        }
    });

    tranaform((Select) statement, orderBy);
    sw.setChanged(true);
    return sw;
}
 

public void instrmentTenant(String sql) throws Throwable {
    Statement statement = CCJSqlParserUtil.parse(sql);
    if (statement instanceof Select) {
        Select select = (Select) statement;
        System.out.println("print parsed sql statement:");
        System.out.println(select.toString());
        System.out.println("show tables:");
        TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
        List<String> tableNames = tablesNamesFinder.getTableList(select);
        for (String tableName : tableNames) {
            System.out.println(tableName);
        }

        SQLStatementInstrumentor instrumentor = new SQLStatementInstrumentor();
        /*
        String tenantSql = instrumentor.instrumentTenantSql(sql, AndTenantBuilder.DEFAULT
                .column("tenant")
                .value("1")
                .build());
        System.out.println("print instrumented sql:");
        System.out.println(tenantSql);
        */
        System.out.println("====================================");
    }
}
 

@Override
public ResultSet executeQuery() throws SQLException {
  CustomDriverStatement custom = getCustomDriverStatement(sqlTokens);
  if (custom != null && custom.isQuery()) {
    return custom.executeQuery(sqlTokens);
  }
  Statement statement;
  try {
    statement = CCJSqlParserUtil.parse(sanitizeSQL(sql));
  } catch (JSQLParserException | TokenMgrException e) {
    throw new CloudSpannerSQLException(PARSE_ERROR + sql + ": " + e.getLocalizedMessage(),
        Code.INVALID_ARGUMENT, e);
  }
  if (statement instanceof Select) {
    determineForceSingleUseReadContext((Select) statement);
    com.google.cloud.spanner.Statement.Builder builder = createSelectBuilder(statement, sql);
    try (ReadContext context = getReadContext()) {
      com.google.cloud.spanner.ResultSet rs = context.executeQuery(builder.build());
      return new CloudSpannerResultSet(this, rs, sql);
    }
  }
  throw new CloudSpannerSQLException(
      "SQL statement not suitable for executeQuery. Expected SELECT-statement.",
      Code.INVALID_ARGUMENT);
}
 

private void setWhereParameters(Expression where,
    com.google.cloud.spanner.Statement.Builder builder) {
  if (where != null) {
    where.accept(new ExpressionVisitorAdapter() {
      private String currentCol = null;

      @Override
      public void visit(Column col) {
        currentCol = unquoteIdentifier(col.getFullyQualifiedName());
      }

      @Override
      public void visit(JdbcParameter parameter) {
        parameter.accept(new ValueBinderExpressionVisitorAdapter<>(getParameterStore(),
            builder.bind("p" + parameter.getIndex()), currentCol));
        currentCol = null;
      }

      @Override
      public void visit(SubSelect subSelect) {
        setSelectParameters(subSelect.getSelectBody(), builder);
      }

    });
  }
}
 

@Override
public CloudSpannerParameterMetaData getParameterMetaData() throws SQLException {
  // parse the SQL statement without executing it
  try {
    if (isDDLStatement()) {
      throw new CloudSpannerSQLException("Cannot get parameter meta data for DDL statement",
          Code.INVALID_ARGUMENT);
    }
    Statement statement = CCJSqlParserUtil.parse(sanitizeSQL(sql));
    if (statement instanceof Insert || statement instanceof Update
        || statement instanceof Delete) {
      // Create mutation, but don't do anything with it. This
      // initializes column names of the parameter store.
      createMutations(sql, false, true);
    } else if (statement instanceof Select) {
      // Create select builder, but don't do anything with it. This
      // initializes column names of the parameter store.
      createSelectBuilder(statement, sql);
    }
  } catch (JSQLParserException | TokenMgrException e) {
    throw new CloudSpannerSQLException(PARSE_ERROR + sql + ": " + e.getLocalizedMessage(),
        Code.INVALID_ARGUMENT, e);
  }
  return new CloudSpannerParameterMetaData(this);
}
 

/**
 * convert to order by sql
 *
 * @param sql
 * @param orderBy
 * @return
 */
public static String converToOrderBySql(String sql, String orderBy) {
    //解析SQL
    Statement stmt = null;
    try {
        stmt = CCJSqlParserUtil.parse(sql);
        Select select = (Select) stmt;
        SelectBody selectBody = select.getSelectBody();
        //处理body-去最外层order by
        List<OrderByElement> orderByElements = extraOrderBy(selectBody);
        String defaultOrderBy = PlainSelect.orderByToString(orderByElements);
        if (defaultOrderBy.indexOf('?') != -1) {
            throw new RuntimeException("原SQL[" + sql + "]中的order by包含参数，因此不能使用OrderBy插件进行修改!");
        }
        //新的sql
        sql = select.toString();
    } catch (Throwable e) {
        e.printStackTrace();
    }
    return sql + " order by " + orderBy;
}
 

@Override
public String onBeforeAnyExecute(StatementInformation statementInformation) throws SQLException {
    String sql = statementInformation.getSqlWithValues();

    // 当前业务链接
    DTXLocalContext.cur().setResource(statementInformation.getStatement().getConnection());

    // 拦截处理
    try {
        Statement statement = CCJSqlParserUtil.parse(sql);
        log.debug("statement > {}", statement);
        statementInformation.setAttachment(statement);
        if (statement instanceof Update) {
            sqlExecuteInterceptor.preUpdate((Update) statement);
        } else if (statement instanceof Delete) {
            sqlExecuteInterceptor.preDelete((Delete) statement);
        } else if (statement instanceof Insert) {
            sqlExecuteInterceptor.preInsert((Insert) statement);
        } else if (statement instanceof Select) {
            sqlExecuteInterceptor.preSelect(new LockableSelect((Select) statement));
        }
    } catch (JSQLParserException e) {
        throw new SQLException(e);
    }
    return sql;
}
 

@Override
public Map<String, TableSchema> getTablesFromQuery(String pathToBeTested) {
    Map<String, TableSchema> tableSchemas = new HashMap<>();

    // Get a list of table names from the query
    Statement stmt;
    try {
        stmt = CCJSqlParserUtil.parse(pathToBeTested);
    } catch (JSQLParserException e) {
        e.printStackTrace();
        return null;
    }

    if (!(stmt instanceof Select)) {
        return null;
    }
    List<String> tableList = new TablesNamesFinder().getTableList(stmt);

    for (String tableName : tableList) {
        tableName = tableName.replaceAll("^\"|\"$", ""); // Remove quotes around tablenames
        tableSchemas.put(tableName, this.extract(tableName));
    }

    return tableSchemas;
}
 

public void instrmentOrderBy(String sql) throws Throwable {
    Statement statement = CCJSqlParserUtil.parse(sql);
    if (statement instanceof Select) {
        Select select = (Select) statement;
        System.out.println("print parsed sql statement:");
        System.out.println(select.toString());
        System.out.println("show tables:");
        TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
        List<String> tableNames = tablesNamesFinder.getTableList(select);
        for (String tableName : tableNames) {
            System.out.println(tableName);
        }

        SQLStatementInstrumentor instrumentor = new SQLStatementInstrumentor();
        String orderBySql = instrumentor.instrumentOrderBySql(sql, SqlStyleOrderByBuilder.DEFAULT.build("name asc, age desc"));

        System.out.println("print instrumented sql:");
        System.out.println(orderBySql);

        System.out.println("====================================");
    }
}
 

/**
 * convert to order by sql
 *
 * @param sql
 * @param orderBy
 * @return
 */
public static String converToOrderBySql(String sql, String orderBy) {
    //解析SQL
    Statement stmt = null;
    try {
        stmt = CCJSqlParserUtil.parse(sql);
        Select select = (Select) stmt;
        SelectBody selectBody = select.getSelectBody();
        //处理body-去最外层order by
        List<OrderByElement> orderByElements = extraOrderBy(selectBody);
        String defaultOrderBy = PlainSelect.orderByToString(orderByElements);
        if (defaultOrderBy.indexOf('?') != -1) {
            throw new PageException("原SQL[" + sql + "]中的order by包含参数，因此不能使用OrderBy插件进行修改!");
        }
        //新的sql
        sql = select.toString();
    } catch (Throwable e) {
        log.warn("处理排序失败: " + e + "，降级为直接拼接 order by 参数");
    }
    return sql + " order by " + orderBy;
}
 

/**
 * 转换为分页语句
 *
 * @param sql
 * @param offset
 * @param limit
 * @return
 */
public String convertToPageSql(String sql, Integer offset, Integer limit) {
    //解析SQL
    Statement stmt;
    try {
        stmt = CCJSqlParserUtil.parse(sql);
    } catch (Throwable e) {
        throw new PageException("不支持该SQL转换为分页查询!", e);
    }
    if (!(stmt instanceof Select)) {
        throw new PageException("分页语句必须是Select查询!");
    }
    //获取分页查询的select
    Select pageSelect = getPageSelect((Select) stmt);
    String pageSql = pageSelect.toString();
    //缓存移到外面了，所以不替换参数
    if (offset != null) {
        pageSql = pageSql.replace(START_ROW, String.valueOf(offset));
    }
    if (limit != null) {
        pageSql = pageSql.replace(PAGE_SIZE, String.valueOf(limit));
    }
    return pageSql;
}
 

/**
 * @brief Visit the statement. This is the function that should be called
 * in the first place.
 * @param statement A SQL statement.
 */
public void visit(Statement statement) throws Exception {
    if (statement instanceof Select) {
        Select select = (Select) statement;
        if (select.getWithItemsList() != null) {
            for (WithItem withItem : ((Select) statement).getWithItemsList()) {
                withItem.accept(this);
            }
        }
        if (select.getSelectBody() != null) {
            select.getSelectBody().accept(this);
        }
    } else if (statement instanceof DescribeTable) {
        ((DescribeTable)statement).accept(this);
    }
    // TODO: Add more type support here.
}
 

@Test
public void BatchTestArithOps() {
    String[] arithOps = {"+", "-", "*", "/", "%", "&", "|", "^"};
    String sqlcmd = "select ddf://adatao/a.id %s ddf://adatao/a.id2 from ddf://adatao/a";
    TableNameReplacer tableNameReplacer = new TableNameReplacer(this.manager);
    for (String arithOp : arithOps) {
        String newSqlCmd = String.format(sqlcmd, arithOp);
        Statement statement = null;
        try {
            statement = this.testFullURISingle(newSqlCmd);
            // System.out.println(statement);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
 

public static String modifyTableNames(String sql,TableRenamer tableRenamer) 
{

	if(sql == null)
	{
		throw new IllegalArgumentException("sql is null");
	}
	
	Statement statement = null;
	try
	{
		statement = CCJSqlParserUtil.parse(sql);
	} 
	catch (JSQLParserException e) 
	{
		throw new IllegalArgumentException("Error when parsing sql:[" + sql+"]",e);
	}
	
	TableRenameVisitor tableRenameVisitor=new TableRenameVisitor(tableRenamer);
	statement.accept(tableRenameVisitor);
	return statement.toString();
}
 

/**
 * 转换为分页语句
 *
 * @param sql
 * @param offset
 * @param limit
 * @param orderBy
 * @return
 */
public String convertToPageSql(String sql, int offset, int limit, String orderBy) {
    StringBuilder key = new StringBuilder(sql.length() + 40);
    key.append(sql);
    key.append(orderBy);
    String pageSql = CACHE.get(key.toString());
    if (pageSql == null) {
        //解析SQL
        Statement stmt;
        try {
            stmt = CCJSqlParserUtil.parse(sql);
        } catch (JSQLParserException e) {
            throw new RuntimeException("不支持该SQL转换为分页查询!");
        }
        if (!(stmt instanceof Select)) {
            throw new RuntimeException("分页语句必须是Select查询!");
        }
        //获取分页查询的select
        Select pageSelect = getPageSelect((Select) stmt, orderBy);
        pageSql = pageSelect.toString();
        CACHE.put(key.toString(), pageSql);
    }
    pageSql = pageSql.replace(START_ROW, String.valueOf(offset));
    pageSql = pageSql.replace(PAGE_SIZE, String.valueOf(limit));
    return pageSql;
}
 

@Override
public Map<String, TableSchema> getTablesFromQuery(String pathToBeTested) {
    Map<String, TableSchema> tableSchemas = new HashMap<String, TableSchema>();

    // Get a list of table names from the query
    Statement stmt;
    try {
        stmt = CCJSqlParserUtil.parse(pathToBeTested);
    } catch (JSQLParserException e) {
        e.printStackTrace();
        return null;
    }

    if (!(stmt instanceof Select)) {
        return null;
    }
    List<String> tableList = new TablesNamesFinder().getTableList(stmt);

    for (String tableName : tableList) {
        tableName = tableName.replaceAll("^\"|\"$", ""); // Remove quotes around tablenames
        tableSchemas.put(tableName,	this.extract(tableName));
    }

    return tableSchemas;
}
 

/**
 * convert to order by sql
 *
 * @param sql
 * @param orderBy
 * @return
 */
public static String converToOrderBySql(String sql, String orderBy) {
    //解析SQL
    Statement stmt = null;
    try {
        stmt = CCJSqlParserUtil.parse(sql);
        Select select = (Select) stmt;
        SelectBody selectBody = select.getSelectBody();
        //处理body-去最外层order by
        List<OrderByElement> orderByElements = extraOrderBy(selectBody);
        String defaultOrderBy = PlainSelect.orderByToString(orderByElements);
        if (defaultOrderBy.indexOf('?') != -1) {
            throw new RuntimeException("原SQL[" + sql + "]中的order by包含参数，因此不能使用OrderBy插件进行修改!");
        }
        //新的sql
        sql = select.toString();
    } catch (Throwable e) {
        e.printStackTrace();
    }
    return sql + " order by " + orderBy;
}
 

@Override
public JSqlParserStatementWrapper parse(String sql) throws SQLParseException {
    try {
        Statement statement = CCJSqlParserUtil.parse(sql);
        JSqlParserStatementWrapper result = new JSqlParserStatementWrapper(statement);
        result.setOriginalSql(sql);
        return result;
    } catch (JSQLParserException ex) {
        throw new SQLParseException(ex);
    }
}
 

@Override
public ResultSet executeQuery(String sql) throws SQLException {
  String[] sqlTokens = getTokens(sql);
  CustomDriverStatement custom = getCustomDriverStatement(sqlTokens);
  if (custom != null && custom.isQuery()) {
    return custom.executeQuery(sqlTokens);
  }
  try (ReadContext context = getReadContext()) {
    com.google.cloud.spanner.ResultSet rs =
        context.executeQuery(com.google.cloud.spanner.Statement.of(sql));
    return new CloudSpannerResultSet(this, rs, sql);
  }
}
 

public Builder setStatement(Statement statement) throws com.github.vincentrussell.query.mongodb.sql.converter.ParseException, ParseException {
    
    if (Select.class.isAssignableFrom(statement.getClass())) {
        sqlCommandType = SQLCommandType.SELECT;
        SelectBody selectBody = ((Select) statement).getSelectBody();

        if (SetOperationList.class.isInstance(selectBody)) {
            SetOperationList setOperationList = (SetOperationList)selectBody;
            if (setOperationList.getSelects() != null
                    && setOperationList.getSelects().size() == 1
                    && PlainSelect.class.isInstance(setOperationList.getSelects().get(0))) {
                return setPlainSelect((PlainSelect) setOperationList.getSelects().get(0));
            }
        } else if (PlainSelect.class.isInstance(selectBody)) {
            return setPlainSelect((PlainSelect) selectBody);
        }

        throw new ParseException("No supported sentence");


    } else if (Delete.class.isAssignableFrom(statement.getClass())) {
        sqlCommandType = SQLCommandType.DELETE;
        Delete delete = (Delete)statement;
        return setDelete(delete); 
    } else {
    	throw new ParseException("No supported sentence");
    }
}
 

/**
 * 获取智能的countSql
 *
 * @param sql
 * @return
 */
public String getSmartCountSql(String sql) {
    //校验是否支持该sql
    isSupportedSql(sql);
    if (CACHE.get(sql) != null) {
        return CACHE.get(sql);
    }
    //解析SQL
    Statement stmt = null;
    try {
        stmt = CCJSqlParserUtil.parse(sql);
    } catch (JSQLParserException e) {
        //无法解析的用一般方法返回count语句
        String countSql = getSimpleCountSql(sql);
        CACHE.put(sql, countSql);
        return countSql;
    }
    Select select = (Select) stmt;
    SelectBody selectBody = select.getSelectBody();
    //处理body
    processSelectBody(selectBody);
    //处理with
    processWithItemsList(select.getWithItemsList());
    //处理为count查询
    sqlToCount(select);
    String result = select.toString();
    CACHE.put(sql, result);
    return result;
}
 

/**
 * 往where sql里面插入AND关系的表达式。
 * 
 * 例如：whereSql为 where a!=3 or a!=2 limit 1
 *      condExpress为 deleted=0
 * 那么返回：where (deleted=0 and (a!=3 or a!=2)) limit 1
 * 
 * @param whereSql 从where起的sql子句，如果有where必须带上where关键字。
 * @param condExpression 例如a=?  不带where或and关键字。
 * @return 注意返回字符串前面没有空格
 * @throws JSQLParserException 
 */
public static String insertWhereAndExpression(String whereSql, String condExpression) 
		throws JSQLParserException {
	
	if(condExpression == null || condExpression.trim().isEmpty()) {
		return whereSql == null ? "" : whereSql;
	}
	if(whereSql == null || whereSql.trim().isEmpty()) {
		return "WHERE " + condExpression;
	}
	
	whereSql = whereSql.trim();
	if(!whereSql.toUpperCase().startsWith("WHERE ")) {
		return "WHERE " + condExpression + " " + whereSql;
	}
	
	// 为解决JSqlParse对复杂的condExpression不支持的问题，这里用替换的形式来达到目的
    String magic = "A" + UUID.randomUUID().toString().replace("-", "");
	
	String selectSql = "select * from dual "; // 辅助where sql解析用
	Statement statement = CCJSqlParserUtil.parse(selectSql + whereSql);
	Select selectStatement = (Select) statement;
	PlainSelect plainSelect = (PlainSelect)selectStatement.getSelectBody();
	
	Expression ce = CCJSqlParserUtil.parseCondExpression(magic);
	Expression oldWhere = plainSelect.getWhere();
	Expression newWhere = new FixedAndExpression(oldWhere, ce);
	plainSelect.setWhere(newWhere);
	
	String result = plainSelect.toString().substring(selectSql.length());
	return result.replace(magic, condExpression);
}
 

@Test
public void testUnion() {
    TableNameReplacer tableNameReplacer = new TableNameReplacer(manager);
    String sqlcmd = "select * from ddf://adatao/a union select * from " +
            "ddf://adatao/b";
    try {
        Statement statement = parser.parse(new StringReader(sqlcmd));
        // System.out.println(statement.toString());
        int a = 2;
    } catch (JSQLParserException e) {
        e.printStackTrace();
    }
}
 

@Override
protected Statement doConvert(Statement statement, String tableSuffix, Pattern includePattern) {
    if (!(statement instanceof Delete)) {
        throw new IllegalArgumentException("The argument statement must is instance of Delete.");
    }
    Delete delete = (Delete) statement;

    String name = delete.getTable().getName();

    if (includePattern.matcher(name).find()) {
        delete.getTable().setName(this.convertTableName(name, tableSuffix));
    }

    return delete;
}
 

@Override
protected Statement doConvert(Statement statement, String suffix, Pattern includePattern) {
    if (!(statement instanceof Insert)) {
        throw new IllegalArgumentException("The argument statement must is instance of Insert.");
    }
    Insert insert = (Insert) statement;

    String name = insert.getTable().getName();

    if (includePattern.matcher(name).find()) {
        insert.getTable().setName(this.convertTableName(name, suffix));
    }

    return insert;
}
 

/**
     * 修改sql语句
     * 
     * @param sql
     * @param suffix
     * @return 修改后的sql
     */
    public String convert(String sql, String suffix, String includePattern) {

        Statement statement = null;
        try {
            statement = pm.parse(new StringReader(sql));
        } catch (JSQLParserException e) {
            log.error(e.getMessage(), e);
            Throwables.propagate(e);
        }

        SqlConverter converter = this.converterMap.get(statement.getClass().getName());

        if (converter != null) {
            return converter.convert(statement, suffix, includePattern);
        }
        return sql;
        
//        try {
//
//            SQLStatement statement = SQLParserDelegate.parse(sql);
//
//            statement.accept(new TableReplaceVisitor(suffix, includePattern));
//
//            MySQLOutputASTVisitor sqlGen = new MySQLOutputASTVisitor(new StringBuilder());
//            statement.accept(sqlGen);
//
//            return sqlGen.getSql();
//
//        } catch (SQLSyntaxErrorException e) {
//            Throwables.propagate(e);
//        }
//
//        return sql;
    }
 

public static Select select(String sql) {
    Statement stmt = null;
    try {
        stmt = CCJSqlParserUtil.parse(sql);
    } catch (JSQLParserException e) {
        throw new RuntimeException(e);
    }
    if (stmt instanceof Select) {
        return (Select) stmt;
    }
    throw new RuntimeException("仅支持Select查询");
}