/**
* Create the compatible Query object based on the SQL query.
*
* @param sql The SQL query.
* @return Query object.
*/
public static QueryAction create(Client client, String sql)
throws SqlParseException, SQLFeatureNotSupportedException {
sql = sql.replaceAll("\n", " ");
String firstWord = sql.substring(0, sql.indexOf(' '));
switch (firstWord.toUpperCase()) {
case "SELECT":
SQLQueryExpr sqlExpr = (SQLQueryExpr) toSqlExpr(sql);
if (isMulti(sqlExpr)) {
MultiQuerySelect multiSelect =
new SqlParser().parseMultiSelect((SQLUnionQuery) sqlExpr.getSubQuery().getQuery());
handleSubQueries(client, multiSelect.getFirstSelect());
handleSubQueries(client, multiSelect.getSecondSelect());
return new MultiQueryAction(client, multiSelect);
} else if (isJoin(sqlExpr, sql)) {
JoinSelect joinSelect = new SqlParser().parseJoinSelect(sqlExpr);
handleSubQueries(client, joinSelect.getFirstTable());
handleSubQueries(client, joinSelect.getSecondTable());
return ESJoinQueryActionFactory.createJoinAction(client, joinSelect);
} else {
Select select = new SqlParser().parseSelect(sqlExpr);
handleSubQueries(client, select);
return handleSelect(client, select);
}
case "DELETE":
SQLStatementParser parser = createSqlStatementParser(sql);
SQLDeleteStatement deleteStatement = parser.parseDeleteStatement();
Delete delete = new SqlParser().parseDelete(deleteStatement);
return new DeleteQueryAction(client, delete);
case "SHOW":
return new ShowQueryAction(client, sql);
default:
throw new SQLFeatureNotSupportedException(String.format("Unsupported query: %s", sql));
}
}
public void test_6() throws Exception {
String sql = "SELECT PASSWORD('badpwd')";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT PASSWORD('badpwd');", text);
}
public void test_0() throws Exception {
String sql = "LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;", text);
}
public void test_2() throws Exception {
String sql = "SELECT LENGTH(COMPRESS(REPEAT('a',16)))";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT LENGTH(COMPRESS(REPEAT('a', 16)));", text);
}
public void test_0() throws Exception {
String sql = "INSERT INTO t VALUES (1,AES_ENCRYPT('text','password'))";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("INSERT INTO t\nVALUES (1, AES_ENCRYPT('text', 'password'));", text);
}
public void test_0() throws Exception {
String sql = "SHOW DATABASES;";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SHOW DATABASES;", text);
}
public void test_3() throws Exception {
String sql =
"LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals(
"LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name COLUMNS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;",
text);
}
public void test_2() throws Exception {
String sql =
"LOAD DATA INFILE '/home/Order.txt' INTO TABLE Orders (Order_Number, Order_Date, Customer_ID);";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals(
"LOAD DATA INFILE '/home/Order.txt' INTO TABLE Orders (Order_Number, Order_Date, Customer_ID);",
text);
}
public void test_1() throws Exception {
String sql =
"LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test FIELDS TERMINATED BY ',' LINES STARTING BY 'xxx';";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals(
"LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test COLUMNS TERMINATED BY ',' LINES STARTING BY 'xxx';",
text);
}
public void test_demo_0() throws Exception {
String sql = "SELECT UUID();";
// parser得到AST
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList(); //
// 将AST通过visitor输出
StringBuilder out = new StringBuilder();
MySqlOutputVisitor visitor = new MySqlOutputVisitor(out);
for (SQLStatement stmt : stmtList) {
stmt.accept(visitor);
out.append(";");
}
System.out.println(out.toString());
}
public static String parameterize(String sql, String dbType) {
SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(sql, dbType);
List<SQLStatement> statementList = parser.parseStatementList();
if (statementList.size() == 0) {
return sql;
}
StringBuilder out = new StringBuilder();
ParameterizedVisitor visitor = createParameterizedOutputVisitor(out, dbType);
for (int i = 0; i < statementList.size(); i++) {
if (i > 0) {
out.append(";\n");
}
SQLStatement stmt = statementList.get(i);
stmt.accept(visitor);
}
if (visitor.getReplaceCount() == 0 && !parser.getLexer().isHasComment()) {
return sql;
}
return out.toString();
}