Java JSQLParserException类代码示例

import net.sf.jsqlparser.JSQLParserException; //导入依赖的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.JSQLParserException; //导入依赖的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.JSQLParserException; //导入依赖的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.JSQLParserException; //导入依赖的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.JSQLParserException; //导入依赖的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.JSQLParserException; //导入依赖的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.JSQLParserException; //导入依赖的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.JSQLParserException; //导入依赖的package包/类
public static void main(String[] args) throws JSQLParserException {
//        Statement statement = CCJSqlParserUtil.parse("select a,b,c from test where test.id = ? and oid in (?,?,?) " +
//                "and  user.age = ? and user.create_at between ? and ? and label like ?");
//        Select select = (Select) statement;
//        PlainSelect plain = (PlainSelect) select.getSelectBody();
//        Expression where = plain.getWhere();
        JSqlParser parser = new JSqlParser();
        parser.parse("select a,b,c from test where id = $id and oid in ($pid,$oid,$mid) " +
                "and age = ? limit ?,?");
//        parser.parse("update t_user set clientId = $clientId , username = ? , password = ? where age > ?");
        for (String param:parser.getSqlParamNames()){
            System.out.println(param);
        }
        System.out.println("preparedSql:"+parser.getPreparedSql());
//        Map<Integer,String> map = new HashMap<>();
//        map.put(2,"2");
//        map.put(3,"3");
//        map.put(1,"1");
//        System.out.println(map.get(map.size()));
//        System.out.println(parser.getCurrentIndex(map));
    }
 

import net.sf.jsqlparser.JSQLParserException; //导入依赖的package包/类
public List<Map<String, Object>> listForMapList(String sql, int nowPage, int pageSize, Object... args) {
    sql = initSql(sql);
    String limitSql = null;
    try {
        limitSql = SelectLimitUtils.getLimitSql(sql, nowPage, pageSize);
    } catch (JSQLParserException e) {
        System.out.println(sql + ",格式不正确,请检查后再试.");
        return null;
    }

    Page<Map<String, Object>> page = new Page<Map<String, Object>>();
    List<Object> objs = new ArrayList<Object>();
    if (args != null && args.length > 0) {
        for (Object obj : args) {
            objs.add(obj);
        }
    }

    return getJdbcTemplate().query(limitSql, objs.toArray(), new RowMapper<Map<String, Object>>() {
        public Map<String, Object> mapRow(ResultSet rs, int i) throws SQLException {
            return toMap(rs);
        }
    });
}
 

import net.sf.jsqlparser.JSQLParserException; //导入依赖的package包/类
public <T> List<T> listForBeanList(String sql, int nowPage, int pageSize, final Map<String, String> columnToPropertyOverrides, Object... args) {
    sql = initSql(sql);
    String limitSql = null;
    try {
        limitSql = SelectLimitUtils.getLimitSql(sql, nowPage, pageSize);
    } catch (JSQLParserException e) {
        System.out.println(sql + ",格式不正确,请检查后再试.");
        return null;
    }
    List<Object> objs = new ArrayList<Object>();
    if (args != null && args.length > 0) {
        for (Object obj : args) {
            objs.add(obj);
        }
    }
    return getJdbcTemplate().query(limitSql, objs.toArray(), new RowMapper<T>() {
        public T mapRow(ResultSet rs, int i) throws SQLException {
            return (T) toBean(rs, columnToPropertyOverrides);
        }
    });

}
 

import net.sf.jsqlparser.JSQLParserException; //导入依赖的package包/类
/**
 * configurationData函数，辅助填充Database的hashmap函数
 * @param statement 对应的文件中的一条创建数据库请求
 * @throws JSQLParserException
 */
public void configurationData(String statement) throws JSQLParserException {
    CreateTable createTable = (CreateTable) parserManager.parse(new StringReader(statement));
    MTable mtable = new MTable();
    ArrayList<MField> mFields = new ArrayList<MField>(); // field信息用ArrayList进行构造

    for(int i = 0;i < createTable.getColumnDefinitions().size();i++){ // 填充该mtable的field信息
        MField mField = new MField();
        mField.setField(((ColumnDefinition) createTable.getColumnDefinitions().get(i)).getColumnName().split("`")[1], ((ColumnDefinition) createTable.getColumnDefinitions().get(i)).getColumnSpecStrings().get(1),((ColumnDefinition)createTable.getColumnDefinitions().get(i)).getColDataType().toString().split(" ")[0]);
        mFields.add(mField);
    }
    // 填充mtable
    mtable.set_mTable(createTable.getTable().getName().split("`")[1],"JData", createTable.getColumnDefinitions().size(),mFields);
    // 填充hashmap
    this.DataBase.put(createTable.getTable().getName().split("`")[1], mtable);

}
 

import net.sf.jsqlparser.JSQLParserException; //导入依赖的package包/类
/**
 * setConfigurationdata函数，填充数据库表信息的函数
 * @param file_path 创建数据库信息的文件的路径
 * @throws IOException
 * @throws JSQLParserException
 */
public void setConfigurationdata(String file_path) throws IOException, JSQLParserException{
    File input_file = new File(file_path);
    String CreateTableQuery = ""; // 截取文件中的create table的query
    InputStreamReader read = new InputStreamReader(new FileInputStream(input_file));
    BufferedReader bufferedReader = new BufferedReader(read);

    String line = null;
    while((line = bufferedReader.readLine())!=null) { // 每次读取一行文本知道文件被读完
        CreateTableQuery = CreateTableQuery + line;
        if (line.endsWith(";")) { // 遇到分号表示获取了文件中的一个完整创建数据库请求
            configurationData(CreateTableQuery); // 针对这个请求填充hashmap
            CreateTableQuery = ""; // 清空请求，准备重新构造请求
        }
    }

}
 

import net.sf.jsqlparser.JSQLParserException; //导入依赖的package包/类
public static void loadingDataStructure(String dsPath) throws IOException,JSQLParserException{
    Global configurationFileWrite = new Global();
    configurationFileWrite.setConfigurationdata(dsPath);

    try {

        Iterator it = configurationFileWrite.DataBase.keySet().iterator();
        while (it.hasNext()){
            String key = (String)it.next();
            System.out.println(key);
            MTable m = configurationFileWrite.DataBase.get(key);
            System.out.println(m.getName()[0] + " " + m.getName()[1]);
            System.out.println(m.table_attr_Size());
            ArrayList<MField> mm = m.getField();
            for(int i = 0;i < mm.size(); i++){
                System.out.println(mm.get(i).getField()[0] + " " +  mm.get(i).getField()[1] + " " + mm.get(i).getField()[2]);
            }

        }
    }catch (Exception e){
        System.out.println("读取文件出错");
    }

}
 

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