Java Statement类代码示例

import net.sf.jsqlparser.statement.Statement; //导入依赖的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.statement.Statement; //导入依赖的package包/类
private void moveToNextResult(int current) throws SQLException
{
	if (current != java.sql.Statement.KEEP_CURRENT_RESULT && lastResultSet != null)
		lastResultSet.close();
	lastResultSet = null;
	lastUpdateCount = -1;
}
 

import net.sf.jsqlparser.statement.Statement; //导入依赖的package包/类
private void setSelectParameters(SelectBody body, com.google.cloud.spanner.Statement.Builder builder)
{
	if (body instanceof PlainSelect)
	{
		setPlainSelectParameters((PlainSelect) body, builder);
	}
	else
	{
		body.accept(new SelectVisitorAdapter()
		{
			@Override
			public void visit(PlainSelect plainSelect)
			{
				setPlainSelectParameters(plainSelect, builder);
			}
		});
	}
}
 

import net.sf.jsqlparser.statement.Statement; //导入依赖的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.statement.Statement; //导入依赖的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.statement.Statement; //导入依赖的package包/类
/**
 * 找到所有绑定变量
 *
 * @param sql
 * @return
 */
protected static Set<Expression> findBindArgsExpressions(String sql) {
    if (sql == null || sql.startsWith("PRAGMA") || !sql.contains("?")) {
        return new LinkedHashSet<>();
    }

    KbSqlParserManager pm = new KbSqlParserManager();

    try {
        Statement statement = pm.parse(sql);

        Set<Expression> expressionSet = findBindArgsExpressions(statement);

        return expressionSet;
    } catch (JSQLParserException e) {
        e.printStackTrace();
    }
    return new LinkedHashSet<>();
}
 

import net.sf.jsqlparser.statement.Statement; //导入依赖的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.statement.Statement; //导入依赖的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.statement.Statement; //导入依赖的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.statement.Statement; //导入依赖的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.statement.Statement; //导入依赖的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.statement.Statement; //导入依赖的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();
}
 

import net.sf.jsqlparser.statement.Statement; //导入依赖的package包/类
public void testParse() throws Exception {
	CCJSqlParserManager parserManager = new CCJSqlParserManager();
	BufferedReader in = new BufferedReader(new FileReader("testfiles" + File.separator + "simple_parsing.txt"));
	String statement = "";
	while (true) {
		try {
			statement = CCJSqlParserManagerTest.getStatement(in);
			if (statement == null)
				break;
				
			Statement parsedStm = parserManager.parse(new StringReader(statement));
			//System.out.println(statement);
		} catch (JSQLParserException e) {
			throw new TestException("impossible to parse statement: " + statement, e);
		}
	}
}
 

import net.sf.jsqlparser.statement.Statement; //导入依赖的package包/类
public void testExists() throws JSQLParserException {
	String statement = "SELECT * FROM tab1 WHERE";
	String where = " EXISTS (SELECT * FROM tab2)";
	statement += where;
	Statement parsed = parserManager.parse(new StringReader(statement));
	assertEquals(statement, ""+parsed);

	PlainSelect plainSelect =	(PlainSelect) ((Select) parsed).getSelectBody();
	ExpressionDeParser expressionDeParser = new ExpressionDeParser();
	StringBuffer stringBuffer = new StringBuffer();
	expressionDeParser.setBuffer(stringBuffer);
	SelectDeParser deParser = new SelectDeParser(expressionDeParser, stringBuffer);
	expressionDeParser.setSelectVisitor(deParser);
	plainSelect.getWhere().accept(expressionDeParser);
	assertEquals(where, stringBuffer.toString());

}
 

import net.sf.jsqlparser.statement.Statement; //导入依赖的package包/类
/**
 * 转换为分页语句
 *
 * @param sql
 * @param offset
 * @param limit
 * @return
 */
public static String convertToPageSql(String sql, long offset, long limit) {
    String pageSql = CACHE.get(sql);
    if (pageSql == null) {
        //解析SQL
        Statement stmt;
        try {
            stmt = CCJSqlParserUtil.parse(sql);
        } catch (Throwable e) {
            throw new RuntimeException("不支持该SQL转换为分页查询!");
        }
        if (!(stmt instanceof Select)) {
            throw new RuntimeException("分页语句必须是Select查询!");
        }
        //获取分页查询的select
        Select pageSelect = getPageSelect((Select) stmt);
        pageSql = pageSelect.toString();
        CACHE.put(sql, pageSql);
    }
    pageSql = pageSql.replace(START_ROW, String.valueOf(offset));
    pageSql = pageSql.replace(PAGE_SIZE, String.valueOf(limit));
    return pageSql;
}