Java Code Examples for net.sf.jsqlparser.JSQLParserException

@Test
public void testSqlParser11() throws JSQLParserException {
    CountSqlParser countSqlParser = new CountSqlParser();
    System.out.println(countSqlParser.getSmartCountSql(
            "select so.id,so.address,so.area_code,so.area_id,so.del_flag,so.email," +
                    "so.fax,so.grade,so.icon,so.master, so.name,so.parent_id,so.parent_ids," +
                    "so.phone,so.remarks,so.type,so.zip_code " +
                    "from sys_organization so " +
                    "LEFT JOIN sys_user_organization suo ON (suo.org_id = so.id or FIND_IN_SET(suo.org_id,so.parent_ids)) " +
                    "where suo.user_id = ? group by so.id LIMIT ? "));

    System.out.println(countSqlParser.getSmartCountSql(
            "select so.id,so.address,so.area_code,so.area_id,so.del_flag,so.email," +
                    "so.fax,so.grade,so.icon,so.master, so.name,so.parent_id,so.parent_ids," +
                    "so.phone,so.remarks,so.type,so.zip_code " +
                    "from sys_organization so " +
                    "LEFT JOIN sys_user_organization suo ON (suo.org_id = so.id or FIND_IN_SET(suo.org_id,so.parent_ids)) " +
                    "where suo.user_id = ?"));
}
 

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 void visit(Table table) {
    String tableName = table.getName();
    //关键点：解析到需要进行数据权限控制的表时进行拼装，可以从当前线程获取表数据
    //需要进行的数据权限控制的表数据
    Map<String, IdsAndColumn> tables = DPHelper.getLocalDataPermissions().getTables();
    if (tables.containsKey(tableName)) {
        IdsAndColumn idsAndColumn = tables.get(tableName);
        List<String> ids = idsAndColumn.getIds();
        List<String> columns = idsAndColumn.getColumns();

        SubSelect subSelect = new SubSelect();
        String subSql = SqlSpliceUtils.spliceIdAndColumn(tableName, ids, columns);
        try {
            subSelect.setSelectBody(((Select) (CCJSqlParserUtil.parse(subSql))).getSelectBody());
        } catch (JSQLParserException e) {
            logger.error("数据权限sql解析异常");
        }
        //TODO:采用随机别名不能避免重名
        subSelect.setAlias(table.getAlias() != null ? table.getAlias() : new Alias("DP" + UUID.randomUUID()
                .toString().replace("-", "")));
        this.subSelect = subSelect;
    }
}
 

/**
 * The main method to parse the SQL statement into an NNA-understandable query.
 *
 * @param statement The SQL statement.
 * @throws JSQLParserException - if sql is unreadable
 */
public void parse(String statement) throws JSQLParserException {
  Statements statements = CCJSqlParserUtil.parseStatements(statement);
  INodeSqlStatementVisitor inodeVisitor = new INodeSqlStatementVisitor();
  statements.accept(inodeVisitor);
  set = inodeVisitor.set.toLowerCase();
  filters = String.join(",", inodeVisitor.filters);
  sum = inodeVisitor.sum;
  find = inodeVisitor.find;
  type = inodeVisitor.type;
  limit = inodeVisitor.limit;
  sortAscending = inodeVisitor.sortAscending;
  sortDescending = inodeVisitor.sortDescending;
  parentDirDepth = inodeVisitor.parentDirDepth;
  timeRange = inodeVisitor.timeRange;
}
 

private static Select createSelect(CloudSpannerConnection connection, Delete delete)
    throws SQLException {
  TableKeyMetaData table =
      connection.getTable(CloudSpannerDriver.unquoteIdentifier(delete.getTable().getName()));
  List<String> keyCols = table.getKeyColumns().stream()
      .map(x -> CloudSpannerDriver.quoteIdentifier(delete.getTable().getName()) + "."
          + CloudSpannerDriver.quoteIdentifier(x))
      .collect(Collectors.toList());
  StringBuilder sql = new StringBuilder();
  sql.append("SELECT ").append(String.join(", ", keyCols));
  sql.append("\nFROM ").append(CloudSpannerDriver.quoteIdentifier(delete.getTable().getName()));
  sql.append("\nWHERE ").append(delete.getWhere().toString());

  try {
    return (Select) CCJSqlParserUtil.parse(sql.toString());
  } catch (JSQLParserException e) {
    throw new CloudSpannerSQLException("Could not parse generated SELECT statement: " + sql,
        Code.INVALID_ARGUMENT);
  }
}
 

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

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

@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;
}
 

@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;
}
 

/**
 * 转换为分页语句
 *
 * @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;
}
 

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

@Test
public void testSql386() throws JSQLParserException {
    String originalSql = " select a.Guid,\n" +
        "               ProManager,\n" +
        "               WorkOrderType,\n" +
        "               a.Name,\n" +
        "               WorkNote,\n" +
        "               b.Name                             TeamName,\n" +
        "               ConstructionSite,\n" +
        "               iif(a.MaterialGuid is null, 0, 1) as IsMaterial,\n" +
        "               a.MaterialGuid,\n" +
        "               c.Code                          as MaterialCode,\n" +
        "               c.FullName                         MaterialName,\n" +
        "               d.FullName                         MatStdSortName\n" +
        "        from RMC_WorkOrder a\n" +
        "                 left join dbo.SYS_OrgFrame b on a.TeamGuid = b.Code and b.ParentGuid is null\n" +
        "                 left join dbo.BAS_Material c on a.MaterialGuid = c.Guid\n" +
        "                 left join BAS_MatStdSort d on a.MatStdSortGuid = d.Guid\n" +
        "        where a.ConfirmUser is null\n" +
        "          and b.Guid = 1\n" +
        "        order by a.ContractBillNO desc";
    System.out.println(sqlServer.convertToPageSql(originalSql, 1, 10));
}
 

@Test
public void testSql66() throws JSQLParserException {
    String originalSql = "SELECT *\n" +
        "FROM\n" +
        "forum_post_info a with(nolock)\n" +
        "LEFT JOIN forum_carcase_tags as b with(nolock) on a.id = b.carcase_id where b.tag_id = 127";
    ReplaceSql replaceSql = new RegexWithNolockReplaceSql();
    String replace = replaceSql.replace(originalSql);
    String pageSql = sqlServer.convertToPageSql(replace, 1, 10);


    CountSqlParser countSqlParser = new CountSqlParser();
    String smartCountSql = countSqlParser.getSmartCountSql(replace);
    smartCountSql = replaceSql.restore(smartCountSql);
    System.out.println(smartCountSql);


    String result = replaceSql.restore(pageSql);
    System.out.println(result);
}
 

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

public static void parseUpdate(String sql, Table table, Entry entry, Condition condition)
        throws JSQLParserException, FrontException {
    Statement statement = CCJSqlParserUtil.parse(sql);
    Update update = (Update) statement;

    // parse table name
    List<net.sf.jsqlparser.schema.Table> tables = update.getTables();
    String tableName = tables.get(0).getName();
    table.setTableName(tableName);

    // parse cloumns
    List<Column> columns = update.getColumns();
    List<Expression> expressions = update.getExpressions();
    int size = expressions.size();
    String[] values = new String[size];
    for (int i = 0; i < size; i++) {
        values[i] = expressions.get(i).toString();
    }
    for (int i = 0; i < columns.size(); i++) {
        entry.put(trimQuotes(columns.get(i).toString()), trimQuotes(values[i]));
    }

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

@Test
public void testSumFileSizeByUser() throws JSQLParserException {
  sqlParser.parse("select user,sum(fileSize) from files group by user");
  String inodeSet = sqlParser.getINodeSet();
  String filters = sqlParser.getFilters();
  String sum = sqlParser.getSum();
  String find = sqlParser.getFind();
  String type = sqlParser.getType();
  assertThat(inodeSet, is(equalTo("files")));
  assertThat(filters, is(""));
  assertThat(sum, is("fileSize"));
  assertThat(type, is("user"));
  assertThat(find, is(nullValue()));
}
 

@Test
public void testSumDiskspaceConsumedByAccessTime() throws JSQLParserException {
  sqlParser.parse("select accessTime,sum(diskspaceConsumed) from files group by accessTime");
  String inodeSet = sqlParser.getINodeSet();
  String filters = sqlParser.getFilters();
  String sum = sqlParser.getSum();
  String find = sqlParser.getFind();
  String type = sqlParser.getType();
  assertThat(inodeSet, is(equalTo("files")));
  assertThat(filters, is(""));
  assertThat(sum, is("diskspaceConsumed"));
  assertThat(type, is("accessTime"));
  assertThat(find, is(nullValue()));
}
 

@Test
public void testGetDoubleFileFilterAnd() throws JSQLParserException {
  sqlParser.parse("SELECT * FROM files WHERE fileSize = 0 AND user != pjeli");
  String inodeSet = sqlParser.getINodeSet();
  String filters = sqlParser.getFilters();
  assertThat(inodeSet, is(equalTo("files")));
  assertThat(filters, is("fileSize:eq:0,user:notEq:pjeli"));
}
 

@Test
public void testGetSumDoubleFileFilterAnd() throws JSQLParserException {
  sqlParser.parse(
      "SELECT SUM(diskspaceConsumed) FROM files WHERE fileSize = 0 AND user != pjeli");
  String inodeSet = sqlParser.getINodeSet();
  String filters = sqlParser.getFilters();
  String sum = sqlParser.getSum();
  assertThat(inodeSet, is(equalTo("files")));
  assertThat(filters, is("fileSize:eq:0,user:notEq:pjeli"));
  assertThat(sum, is(equalTo("diskspaceConsumed")));
}
 

@Test
public void testGetCountDoubleFileFilterAnd() throws JSQLParserException {
  sqlParser.parse("SELECT COUNT(*) FROM files WHERE fileSize = 0 AND user != pjeli");
  String inodeSet = sqlParser.getINodeSet();
  String filters = sqlParser.getFilters();
  String sum = sqlParser.getSum();
  assertThat(inodeSet, is(equalTo("files")));
  assertThat(filters, is("fileSize:eq:0,user:notEq:pjeli"));
  assertThat(sum, is(equalTo("count")));
}
 

@Test
public void testGetMaxDoubleFileFilterAnd() throws JSQLParserException {
  sqlParser.parse(
      "SELECT MAX(diskspaceConsumed) FROM files WHERE fileSize > 0 AND user != pjeli");
  String inodeSet = sqlParser.getINodeSet();
  String filters = sqlParser.getFilters();
  String sum = sqlParser.getSum();
  String find = sqlParser.getFind();
  assertThat(inodeSet, is(equalTo("files")));
  assertThat(filters, is("fileSize:gt:0,user:notEq:pjeli"));
  assertThat(sum, is(nullValue()));
  assertThat(find, is(equalTo("max:diskspaceConsumed")));
}
 

@Test
public void testGetMaxDoubleFileFilterPathStartsWithAnd() throws JSQLParserException {
  sqlParser.parse(
      "SELECT MAX(diskspaceConsumed) FROM files WHERE path LIKE '/tmp/%' AND user != pjeli");
  String inodeSet = sqlParser.getINodeSet();
  String filters = sqlParser.getFilters();
  String sum = sqlParser.getSum();
  String find = sqlParser.getFind();
  assertThat(inodeSet, is(equalTo("files")));
  assertThat(filters, is("path:startsWith:/tmp/,user:notEq:pjeli"));
  assertThat(sum, is(nullValue()));
  assertThat(find, is(equalTo("max:diskspaceConsumed")));
}
 

@Test
public void testGetSingleFileGroupByUser() throws JSQLParserException {
  sqlParser.parse("SELECT user,COUNT(*) FROM files WHERE fileSize = 0 GROUP BY user");
  String inodeSet = sqlParser.getINodeSet();
  String filters = sqlParser.getFilters();
  String type = sqlParser.getType();
  String sum = sqlParser.getSum();
  assertThat(inodeSet, is(equalTo("files")));
  assertThat(filters, is("fileSize:eq:0"));
  assertThat(type, is("user"));
  assertThat(sum, is(equalTo("count")));
}
 

@Test
public void testGetCountDoubleFileFilterAndLimit() throws JSQLParserException {
  sqlParser.parse("SELECT COUNT(*) FROM files WHERE fileSize = 0 AND user != pjeli LIMIT 1000");
  String inodeSet = sqlParser.getINodeSet();
  String filters = sqlParser.getFilters();
  String sum = sqlParser.getSum();
  Integer limit = sqlParser.getLimit();
  assertThat(inodeSet, is(equalTo("files")));
  assertThat(filters, is("fileSize:eq:0,user:notEq:pjeli"));
  assertThat(sum, is(equalTo("count")));
  assertThat(limit, is(1000));
}
 

/**
 * 查询替换为别名进行
 *
 * @param sql
 * @return
 * @throws JSQLParserException
 */
private String replaceTableName(String sql) throws JSQLParserException {
    Statement statement = CCJSqlParserUtil.parse(sql);
    Select select = (Select) statement;

    StringBuilder buffer = new StringBuilder();
    ExpressionDeParser expressionDeParser = new ExpressionDeParser();

    TableNameParser tableNameParser = new TableNameParser(expressionDeParser, buffer);

    expressionDeParser.setSelectVisitor(tableNameParser);
    expressionDeParser.setBuffer(buffer);
    select.getSelectBody().accept(tableNameParser);
    return select.toString();
}
 

private AbstractTablePartWorker createWorker(String sql, ParameterStore parameters)
    throws JSQLParserException {
  CloudSpannerConnection connection = mock(CloudSpannerConnection.class);
  Select select = (Select) CCJSqlParserUtil.parse(sql);
  DMLOperation operation = DMLOperation.INSERT;
  AbstractTablePartWorker worker = mock(AbstractTablePartWorker.class,
      withSettings().useConstructor(connection, select, parameters, true, operation)
          .defaultAnswer(CALLS_REAL_METHODS));
  return worker;
}
 

@Test
public void testGetEstimatedRecordCount() throws JSQLParserException {
  assertEquals("SELECT COUNT(*) AS C FROM ((SELECT * FROM FOO) LIMIT 1000) Q",
      getTestCount("SELECT * FROM FOO", 1000L));
  assertEquals("SELECT COUNT(*) AS C FROM ((SELECT * FROM FOO LIMIT 500) LIMIT 1000) Q",
      getTestCount("SELECT * FROM FOO LIMIT 500", 1000L));
  assertEquals("SELECT COUNT(*) AS C FROM ((SELECT BAR, `TEST`, bla FROM FOO) LIMIT 1000) Q",
      getTestCount("SELECT BAR, `TEST`, bla FROM FOO", 1000L));
  assertEquals(
      "SELECT COUNT(*) AS C FROM ((SELECT * FROM FOO UNION ALL SELECT * FROM bar) LIMIT 1000) Q",
      getTestCount("SELECT * FROM FOO union all select * from bar", 1000L));
  assertEquals(
      "SELECT COUNT(*) AS C FROM ((SELECT * FROM FOO UNION ALL SELECT * FROM bar LIMIT 500) LIMIT 1000) Q",
      getTestCount("SELECT * FROM FOO union all select * from bar limit 500", 1000L));
}
 

@Test
public void testCreateInsertSelectOnDuplicateKeyUpdateStatement()
    throws JSQLParserException, SQLException {
  String sql = "UPDATE FOO SET BAR=2 WHERE VALUE=1";
  Update update = (Update) CCJSqlParserUtil.parse(sql);
  String insert = subject.createInsertSelectOnDuplicateKeyUpdateStatement(update);
  assertEquals("INSERT INTO `FOO`\n" + "(`ID`, `BAR`)\n" + "SELECT `FOO`.`ID`, 2\n"
      + "FROM `FOO`\n" + "WHERE VALUE = 1\n" + "ON DUPLICATE KEY UPDATE", insert);
}
 

@Test
public void testCreateInsertSelectOnDuplicateKeyUpdateStatementWithParameters()
    throws JSQLParserException, SQLException {
  String sql = "UPDATE FOO SET BAR=? WHERE ID=? AND VALUE=?";
  Update update = (Update) CCJSqlParserUtil.parse(sql);
  String insert = subject.createInsertSelectOnDuplicateKeyUpdateStatement(update);
  assertEquals("INSERT INTO `FOO`\n" + "(`ID`, `BAR`)\n" + "SELECT `FOO`.`ID`, ?\n"
      + "FROM `FOO`\n" + "WHERE ID = ? AND VALUE = ?\n" + "ON DUPLICATE KEY UPDATE", insert);
}