Java CCJSqlParserUtil类代码示例

import net.sf.jsqlparser.parser.CCJSqlParserUtil; //导入依赖的package包/类
private void genSql(Invocation invocation, SplitRequest splitRequest) {
    try {
        genParams(invocation, splitRequest);

        BoundSql boundSql = ((MappedStatement)invocation.getArgs()[0]).getBoundSql(invocation.getArgs()[1]);
        Statement statement = CCJSqlParserUtil.parse(boundSql.getSql());
        String sql = parseSql(statement);

        FieldUtil.setValue("sql", BoundSql.class, boundSql, sql);
        MappedStatement mappedStatement = copyMappedStatement((MappedStatement)invocation.getArgs()[0], boundSql);
        invocation.getArgs()[0] = mappedStatement;

    } catch (OgnlException ognl) {
        logger.error("ognl exception", ognl);
    } catch (JSQLParserException e) {
        logger.error("jSqlParser exception", e);
    } catch (Exception e1) {
        logger.error("error happend", e1);
    }
}
 

import net.sf.jsqlparser.parser.CCJSqlParserUtil; //导入依赖的package包/类
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);
	}
}
 

import net.sf.jsqlparser.parser.CCJSqlParserUtil; //导入依赖的package包/类
private static void testCreateTableStatement(String sql) throws SQLException
{
	boolean isDDL = isDDLStatement(sql);
	Assert.assertTrue(isDDL);
	Statement statement = null;
	try
	{
		statement = CCJSqlParserUtil.parse(sql);
	}
	catch (JSQLParserException e)
	{
		throw new CloudSpannerSQLException("Could not parse SQL statement", Code.INVALID_ARGUMENT, e);
	}
	Assert.assertNotNull(statement);
	Assert.assertEquals(CreateTable.class, statement.getClass());
}
 

import net.sf.jsqlparser.parser.CCJSqlParserUtil; //导入依赖的package包/类
/**
 * 转换为分页语句
 *
 * @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;
}
 

import net.sf.jsqlparser.parser.CCJSqlParserUtil; //导入依赖的package包/类
@Test
public void test() throws JSQLParserException {
    String sql = "select max(id) from (\n" +
            "            select distinct(t.id) as id\n" +
            "            from (select i.sale_order_item_id as id,o.order_id as orderId,o.marketplace as marketplace,\n" +
            "            replace(json_extract(o.others,'$.warehouse_upload'),'\"','') as swname\n" +
            "            from fantasia.sale_order_item i\n" +
            "            left join fantasia.sale_order o on o.order_id=i.order_id\n" +
            "            where o.type in ('7','8') and o.state in ('3','4') and o.upload_date>DATE_SUB(CURDATE(), INTERVAL 3 MONTH) and i.sale_order_item_id not in (\n" +
            "            select order_item_id from fantasia.customs_declaration_order_item di\n" +
            "            left join fantasia.customs_declaration_form f on di.form_id=f.id where f.removed=0)) t\n" +
            "            left join fantasia.`storage_warehouse` sw on t.swname = sw.name\n" +
            "            left join fantasia.`company` c on replace(json_extract(sw.others,'$.company'),'\"','') = c.id where c.`inland` = '1') as a";

    ReplaceTablesNamesFinder tablesNamesFinder = new ReplaceTablesNamesFinder();
    Statement statement = CCJSqlParserUtil.parse(sql);
    List<String> tables = tablesNamesFinder.getTableList((Select) statement);
    Assert.assertTrue(tables.size() == 6);
}
 

import net.sf.jsqlparser.parser.CCJSqlParserUtil; //导入依赖的package包/类
@Test
public void test2() throws JSQLParserException {
    String sql = "select max(id) from (\n" +
            "            select distinct(t.id) as id\n" +
            "            from (select i.sale_order_item_id as id,o.order_id as orderId,o.marketplace as marketplace,\n" +
            "            replace(json_extract(o.others,'$.warehouse_upload'),'\"','') as swname\n" +
            "            from fantasia.sale_order_item i\n" +
            "            left join fantasia.sale_order o on o.order_id=i.order_id\n" +
            "            where o.type in ('7','8') and o.state in ('3','4') and o.upload_date>DATE_SUB(CURDATE(), INTERVAL 3 MONTH) and i.sale_order_item_id not in (\n" +
            "            select order_item_id from fantasia.customs_declaration_order_item di\n" +
            "            left join fantasia.customs_declaration_form f on di.form_id=f.id where f.removed=0)) t\n" +
            "            left join fantasia.`storage_warehouse` sw on t.swname = sw.name\n" +
            "            left join fantasia.`company` c on replace(json_extract(sw.others,'$.company'),'\"','') = c.id where c.`inland` = '1') as a";

    ReplaceTablesNamesFinder tablesNamesFinder = new ReplaceTablesNamesFinder();
    Statement statement = CCJSqlParserUtil.parse(sql);
    tablesNamesFinder.getTableList((Select) statement);
    System.out.println(statement.toString());
}
 

import net.sf.jsqlparser.parser.CCJSqlParserUtil; //导入依赖的package包/类
/**
 * 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;
}
 

import net.sf.jsqlparser.parser.CCJSqlParserUtil; //导入依赖的package包/类
public String parse(String sql) {
    if (CACHE.get(sql) != null) {
        return CACHE.get(sql);
    }
    Statement stmt = null;
    try {
        stmt = CCJSqlParserUtil.parse(sql);
    } catch (JSQLParserException e) {
        //无法解析的用一般方法返回count语句
        String countSql = simpleParser.getCountSql(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;
}
 

import net.sf.jsqlparser.parser.CCJSqlParserUtil; //导入依赖的package包/类
private Generator tableGenerator(String sql, DataSource ds) {
    try {
        CreateTable stmt = (CreateTable) CCJSqlParserUtil.parse("CREATE TABLE " + sql);
        return new Generator()
                .writing("migration", g -> g.task(
                        new FlywayTask("src/main/java", stmt.getTable().getName(), "CREATE TABLE " + sql)));
    } catch (JSQLParserException e) {
        throw new IllegalArgumentException("Statement generating a table is wrong syntax.", e);
    }
}
 

import net.sf.jsqlparser.parser.CCJSqlParserUtil; //导入依赖的package包/类
/**
 * Get the used tables for a query
 * @param c The connection
 * @param query The query
 * @return The names
 */
private static Set<String> getUsedTables(Connection c, String query) throws Exception
{
   Set<String> result = new HashSet<>();

   net.sf.jsqlparser.statement.Statement s = CCJSqlParserUtil.parse(query);
   List<String> tableNames = new TablesNamesFinder().getTableList(s);

   for (String t : tableNames)
      result.add(t.toLowerCase());
   
   for (String tableName : result)
   {
      if (!tables.containsKey(tableName))
      {
         initTable(c, tableName);
      }
   }

   return result;
}
 

import net.sf.jsqlparser.parser.CCJSqlParserUtil; //导入依赖的package包/类
public JSQLParserAdapter(String sql, ShardRouter shardRouter, boolean enableLimitCheck) {
    this.sql = sql;
    this.shardRouter = shardRouter;
    this.enableLimitCheck = enableLimitCheck;
    try {
        this.statement = CCJSqlParserUtil.parse(sql);
    } catch (Throwable e) {
        throw new SQLSyntaxErrorException("sql is [" + sql + "]", e);
    }
    if (statement instanceof Select //
        || statement instanceof Update//
        || statement instanceof Insert//
        || statement instanceof Delete) {
        // ok
    } else {
        throw new UnsupportedSQLExpressionException(
                                                    "Sql ["
                                                            + sql
                                                            + "] is not supported in shard sql. Only support 'select' 'insert' 'update' and 'delete' sql statement");
    }
}
 

import net.sf.jsqlparser.parser.CCJSqlParserUtil; //导入依赖的package包/类
@SuppressWarnings({"rawtypes", "unchecked"})
private String convertToPreparedStatement(String where, Collection args) {
    if (StringUtils.isBlank(where)) {
        return where;
    }
    try {
        Expression expression = CCJSqlParserUtil.parseCondExpression(where);
        PreparedExpressionConverter expressionConverter = getSelectConverter().getExpressionConverter();
        expression.accept(expressionConverter);

        // TODO pass in context to check entitlements
        // checkEntitlements(expressionConverter.getContext());
        args.addAll(expressionConverter.getValues());

        return expressionConverter.getBuffer().toString();
    } catch (JSQLParserException e) {
        throw RaptureExceptionFactory.create("Failed to parse where clause (" + where + ")", e);
    }
}
 

import net.sf.jsqlparser.parser.CCJSqlParserUtil; //导入依赖的package包/类
/**
 * 获取select的count语句
 *
 * @param originalSql
 *            selectSQL
 * @return
 */
public static String sqlCountOptimize(String originalSql) {
    Assert.hasLength(originalSql);
    String sqlCount;
    try {
        Select selectStatement = (Select) CCJSqlParserUtil.parse(originalSql);
        PlainSelect plainSelect = (PlainSelect) selectStatement.getSelectBody();
        Distinct distinct = plainSelect.getDistinct();
        List<Expression> groupBy = plainSelect.getGroupByColumnReferences();
        // 优化Order by
        List<OrderByElement> orderBy = plainSelect.getOrderByElements();
        // 添加包含groupby 不去除orderby
        if (CollectionUtils.isEmpty(groupBy) && CollectionUtils.isNotEmpty(orderBy)) {
            plainSelect.setOrderByElements(null);
        }
        if (distinct != null || CollectionUtils.isNotEmpty(groupBy)) {
            return String.format(SQL_BASE_COUNT, selectStatement.toString());
        }
        List<SelectItem> selectCount = countSelectItem();
        plainSelect.setSelectItems(selectCount);
        sqlCount = selectStatement.toString();
    } catch (Exception e) {
        sqlCount = String.format(SQL_BASE_COUNT, originalSql);
    }
    return sqlCount;
}
 

import net.sf.jsqlparser.parser.CCJSqlParserUtil; //导入依赖的package包/类
/**
 * Use the {@link android.database.sqlite.SQLiteDatabase#rawQuery(String, String[]) rawQuery} method.
 * This method uses {@link net.sf.jsqlparser.parser.CCJSqlParserUtil#parse(String) parser} method to parse the SQL query.
 * Unlike the rawQuery of the SQLiteDatabase, there is no need to call the moveToFirst method of the returned Cursor to apply SQL query.
 * @param tag The tag to be mapped to the restoring query.
 * @throws IllegalArgumentException if the tag is null.
 */
public Cursor rawQuery(String sql, String[] selectionArgs, String tag)
        throws JSQLParserException, ClassCastException {
    if (tag == null)
        throw new IllegalArgumentException("The tag must not be null.");

    Statement statement = CCJSqlParserUtil.parse(sql);

    generateRawUpdateDeleteQuery(statement, sql, selectionArgs, tag);

    Cursor cursor =  mSQLiteDatabase.rawQuery(sql, selectionArgs);
    cursor.moveToFirst();

    if (sql.toLowerCase(Locale.getDefault()).contains("insert into")) {
        Insert insertStatement = (Insert) statement;
        String table = insertStatement.getTable().getName();
        generateInsertRawQuery(table, tag);
    }

    return cursor;
}
 

import net.sf.jsqlparser.parser.CCJSqlParserUtil; //导入依赖的package包/类
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();
}