關(guān)于JSqlparser使用攻略(高效的SQL解析工具)
JSqlparser github地址
JSqlParser is a SQL statement parser. It translates SQLs in a traversable hierarchy of Java classes. JSqlParser is not limited to one database but provides support for a lot of specials of Oracle, SqlServer, MySQL, PostgreSQL … To name some, it has support for Oracles join syntax using (+), PostgreSQLs cast syntax using ::, relational operators like != and so on.
JSqlParser 是SQL語句分析的插件,他使用Java語言去解析SQL。
sqlparser提供很多的數(shù)據(jù)庫語法解析支持其中支持很多oracle的特殊語法。
可以結(jié)合mybatis的攔截修改SQL來實現(xiàn)多租戶、SQL拼接甚至聯(lián)表的功能。
可以參考我之前寫的MyBatis通過攔截修改SQL
Maven 引用
遠(yuǎn)程倉庫
<repositories> <repository> <id>jsqlparser-snapshots</id> <snapshots> <enabled>true</enabled> </snapshots> <url>https://oss.sonatype.org/content/groups/public/</url> </repository> </repositories>
依賴包
<dependency> <groupId>com.github.jsqlparser</groupId> <artifactId>jsqlparser</artifactId> <version>4.4</version> </dependency>
SQL解析
獲取SQL中的信息
public class TestSqlparser { public static void main(String[] args) throws JSQLParserException { // 根據(jù)sql創(chuàng)建select Select stmt = (Select) CCJSqlParserUtil.parse("SELECT col1 AS a, col2 AS b, col3 AS c FROM table T WHERE col1 = 10 AND col2 = 20 AND col3 = 30"); Map<String, Expression> map = new HashMap<>(); Map<String, String> mapTable = new HashMap<>(); ((PlainSelect) stmt.getSelectBody()).getFromItem().accept(new FromItemVisitorAdapter() { @Override public void visit(Table table) { // 獲取別名 => 表名 mapTable.put(table.getAlias().getName(), table.getName()); } }); ((PlainSelect) stmt.getSelectBody()).getWhere().accept(new ExpressionVisitorAdapter() { @Override public void visit(AndExpression expr) { // 獲取where表達(dá)式 System.out.println(expr); } }); for (SelectItem selectItem : ((PlainSelect)stmt.getSelectBody()).getSelectItems()) { selectItem.accept(new SelectItemVisitorAdapter() { @Override public void visit(SelectExpressionItem item) { // 獲取字段別名 => 字段名 map.put(item.getAlias().getName(), item.getExpression()); } }); } System.out.println("map " + map); System.out.println("mapTables" + mapTable); } }
創(chuàng)建Select的方式
創(chuàng)建Select(非SQL String 創(chuàng)建)
@Test public void testCreateSelect1 () throws JSQLParserException { Select select1 = SelectUtils.buildSelectFromTable(new Table("mytable")); Select select2 = SelectUtils.buildSelectFromTableAndExpressions(new Table("mytable"), new Column("a"), new Column("b")); Select select3 = SelectUtils.buildSelectFromTableAndExpressions(new Table("mytable"), "a+b", "test"); System.out.println(select1.toString()); System.out.println(select2.toString()); System.out.println(select3.toString()); }
Insert 插入字段和值
@Test public void testCreateSelect2() throws JSQLParserException { Insert insert = (Insert) CCJSqlParserUtil.parse("insert into mytable (col1) values (1)"); System.out.println(insert.toString()); //adding a column insert.getColumns().add(new Column("col2")); //adding a value using a visitor insert.getItemsList().accept(new ItemsListVisitor() { public void visit(SubSelect subSelect) { throw new UnsupportedOperationException("Not supported yet."); } public void visit(ExpressionList expressionList) { expressionList.getExpressions().add(new LongValue(5)); } @Override public void visit(NamedExpressionList namedExpressionList) { } public void visit(MultiExpressionList multiExprList) { throw new UnsupportedOperationException("Not supported yet."); } }); System.out.println(insert.toString()); //adding another column insert.getColumns().add(new Column("col3")); //adding another value (the easy way) ((ExpressionList) insert.getItemsList()).getExpressions().add(new LongValue(10)); System.out.println(insert.toString()); }
import net.sf.jsqlparser.JSQLParserException; import net.sf.jsqlparser.expression.LongValue; import net.sf.jsqlparser.expression.StringValue; import net.sf.jsqlparser.parser.CCJSqlParserUtil; import net.sf.jsqlparser.statement.Statement; import net.sf.jsqlparser.util.deparser.ExpressionDeParser; import net.sf.jsqlparser.util.deparser.SelectDeParser; import net.sf.jsqlparser.util.deparser.StatementDeParser; public class ReplaceColumnValues { static class ReplaceColumnAndLongValues extends ExpressionDeParser { @Override public void visit(StringValue stringValue) { this.getBuffer().append("?"); } @Override public void visit(LongValue longValue) { this.getBuffer().append("?"); } } public static String cleanStatement(String sql) throws JSQLParserException { StringBuilder buffer = new StringBuilder(); ExpressionDeParser expr = new ReplaceColumnAndLongValues(); SelectDeParser selectDeparser = new SelectDeParser(expr, buffer); expr.setSelectVisitor(selectDeparser); expr.setBuffer(buffer); StatementDeParser stmtDeparser = new StatementDeParser(expr, selectDeparser, buffer); Statement stmt = CCJSqlParserUtil.parse(sql); stmt.accept(stmtDeparser); return stmtDeparser.getBuffer().toString(); } public static void main(String[] args) throws JSQLParserException { System.out.println(cleanStatement("SELECT 'abc', 5 FROM mytable WHERE col='test'")); System.out.println(cleanStatement("UPDATE table1 A SET A.columna = 'XXX' WHERE A.cod_table = 'YYY'")); System.out.println(cleanStatement("INSERT INTO example (num, name, address, tel) VALUES (1, 'name', 'test ', '1234-1234')")); System.out.println(cleanStatement("DELETE FROM table1 where col=5 and col2=4")); } }
輸出:
SELECT ?, ? FROM mytable WHERE col = ?
UPDATE table1 A SET A.columna = ? WHERE A.cod_table = ?
INSERT INTO example (num, name, address, tel) VALUES (?, ?, ?, ?)
DELETE FROM table1 WHERE col = ? AND col2 = ?
where條件中字段替換
替換條件字段col_1到col1
@Test public void replace () throws JSQLParserException { Select stmt = (Select) CCJSqlParserUtil.parse("SELECT col1 AS a, col2 AS b, col3 AS c FROM table WHERE col_1 = 10 AND col_2 = 20 AND col_3 = 30"); System.out.println("before " + stmt.toString()); ((PlainSelect)stmt.getSelectBody()).getWhere().accept(new ExpressionVisitorAdapter() { @Override public void visit(Column column) { column.setColumnName(column.getColumnName().replace("_", "")); } }); System.out.println("after " + stmt.toString()); }
解析SQL例子
Statement stmt = CCJSqlParserUtil.parse("SELECT * FROM tab1"); Statements stmt = CCJSqlParserUtil.parseStatements("SELECT * FROM tab1; SELECT * FROM tab2"); Expression expr = CCJSqlParserUtil.parseExpression("a*(5+mycolumn)");
可以直接將String SQL片段解析成Expression再將expr插入到SQL語句中。
獲取所有tableNames
Statement statement = CCJSqlParserUtil.parse("SELECT * FROM MY_TABLE1"); Select selectStatement = (Select) statement; TablesNamesFinder tablesNamesFinder = new TablesNamesFinder(); List<String> tableList = tablesNamesFinder.getTableList(selectStatement);
自動生成別名
Select select = (Select) CCJSqlParserUtil.parse("select a,b,c from test"); final AddAliasesVisitor instance = new AddAliasesVisitor(); select.getSelectBody().accept(instance);
結(jié)果:
SELECT a AS A1, b AS A2, c AS A3 FROM test
SQL函數(shù)
/** * SQL 函數(shù) * SELECT function(列) FROM 表 */ @Test public void testFun() throws JSQLParserException { Table t1 = new Table("tab1").withAlias(new Alias("t1").withUseAs(true)); // 表1 PlainSelect plainSelect = new PlainSelect(); plainSelect.setFromItem(t1); // 設(shè)置FROM t1= > SELECT FROM tab1 AS t1 List<SelectItem> selectItemList = new ArrayList<>(); // 查詢元素集合 SelectExpressionItem selectExpressionItem001 = new SelectExpressionItem(); // 元素1表達(dá)式 selectExpressionItem001.setExpression(new Column(t1,"col001")); SelectExpressionItem selectExpressionItem002 = new SelectExpressionItem(); // 元素2表達(dá)式 selectExpressionItem002.setExpression(new Column(t1,"col002")); selectItemList.add(0, selectExpressionItem001); // 添加入隊 selectItemList.add(1, selectExpressionItem002); // 添加入隊 // COUNT SelectExpressionItem selectExpressionItemCount = new SelectExpressionItem(); // 創(chuàng)建函數(shù)元素表達(dá)式 selectExpressionItemCount.setAlias(new Alias("count")); // 設(shè)置別名 Function function = new Function(); // 創(chuàng)建函數(shù)對象 Function extends ASTNodeAccessImpl implements Expression function.setName("COUNT"); // 設(shè)置函數(shù)名 ExpressionList expressionListCount = new ExpressionList(); // 創(chuàng)建參數(shù)表達(dá)式 expressionListCount.setExpressions(Collections.singletonList(new Column(t1, "id"))); function.setParameters(expressionListCount); // 設(shè)置參數(shù) selectExpressionItemCount.setExpression(function); selectItemList.add(2,selectExpressionItemCount); plainSelect.setSelectItems(selectItemList); // 添加查詢元素集合入select對象 System.err.println(plainSelect); // SELECT t1.col001, t1.col002, COUNT(t1.id) AS count FROM tab1 AS t1 }
單表where條件拼裝
/** * 單表SQL查詢 * * @throws JSQLParserException */ @Test public void testSelectOneTable() throws JSQLParserException { // 單表全量 Table table = new Table("test"); Select select = SelectUtils.buildSelectFromTable(table); System.err.println(select); // SELECT * FROM test // 指定列查詢 Select buildSelectFromTableAndExpressions = SelectUtils.buildSelectFromTableAndExpressions(new Table("test"), new Column("col1"), new Column("col2")); System.err.println(buildSelectFromTableAndExpressions); // SELECT col1, col2 FROM test // WHERE = EqualsTo equalsTo = new EqualsTo(); // 等于表達(dá)式 equalsTo.setLeftExpression(new Column(table, "user_id")); // 設(shè)置表達(dá)式左邊值 equalsTo.setRightExpression(new StringValue("123456"));// 設(shè)置表達(dá)式右邊值 PlainSelect plainSelect = (PlainSelect) select.getSelectBody(); // 轉(zhuǎn)換為更細(xì)化的Select對象 plainSelect.setWhere(equalsTo); System.err.println(plainSelect);// SELECT * FROM test WHERE test.user_id = '123456' // WHERE != <> NotEqualsTo notEqualsTo = new NotEqualsTo(); notEqualsTo.setLeftExpression(new Column(table, "user_id")); // 設(shè)置表達(dá)式左邊值 notEqualsTo.setRightExpression(new StringValue("123456"));// 設(shè)置表達(dá)式右邊值 PlainSelect plainSelectNot = (PlainSelect) select.getSelectBody(); plainSelectNot.setWhere(notEqualsTo); System.err.println(plainSelectNot);// SELECT * FROM test WHERE test.user_id <> '123456' // 其他運算符, 參考上面代碼添加表達(dá)式即可 GreaterThan gt = new GreaterThan(); // ">" GreaterThanEquals geq = new GreaterThanEquals(); // ">=" MinorThan mt = new MinorThan(); // "<" MinorThanEquals leq = new MinorThanEquals();// "<=" IsNullExpression isNull = new IsNullExpression(); // "is null" isNull.setNot(true);// "is not null" LikeExpression nlike = new LikeExpression(); nlike.setNot(true); // "not like" Between bt = new Between(); bt.setNot(true);// "not between" // WHERE LIKE LikeExpression likeExpression = new LikeExpression(); // 創(chuàng)建Like表達(dá)式對象 likeExpression.setLeftExpression(new Column("username")); // 表達(dá)式左邊 likeExpression.setRightExpression(new StringValue("張%")); // 右邊表達(dá)式 PlainSelect plainSelectLike = (PlainSelect) select.getSelectBody(); plainSelectLike.setWhere(likeExpression); System.err.println(plainSelectLike); // SELECT * FROM test WHERE username LIKE '張%' // WHERE IN Set<String> deptIds = Sets.newLinkedHashSet(); // 創(chuàng)建IN范圍的元素集合 deptIds.add("0001"); deptIds.add("0002"); ItemsList itemsList = new ExpressionList(deptIds.stream().map(StringValue::new).collect(Collectors.toList())); // 把集合轉(zhuǎn)變?yōu)镴SQLParser需要的元素列表 InExpression inExpression = new InExpression(new Column("dept_id "), itemsList); // 創(chuàng)建IN表達(dá)式對象,傳入列名及IN范圍列表 PlainSelect plainSelectIn = (PlainSelect) select.getSelectBody(); plainSelectIn.setWhere(inExpression); System.err.println(plainSelectIn); // SELECT * FROM test WHERE dept_id IN ('0001', '0002') // WHERE BETWEEN AND Between between = new Between(); between.setBetweenExpressionStart(new LongValue(18)); // 設(shè)置起點值 between.setBetweenExpressionEnd(new LongValue(30)); // 設(shè)置終點值 between.setLeftExpression(new Column("age")); // 設(shè)置左邊的表達(dá)式,一般為列 PlainSelect plainSelectBetween = (PlainSelect) select.getSelectBody(); plainSelectBetween.setWhere(between); System.err.println(plainSelectBetween); // SELECT * FROM test WHERE age BETWEEN 18 AND 30 // WHERE AND 多個條件結(jié)合,都需要成立 AndExpression andExpression = new AndExpression(); // AND 表達(dá)式 andExpression.setLeftExpression(equalsTo); // AND 左邊表達(dá)式 andExpression.setRightExpression(between); // AND 右邊表達(dá)式 PlainSelect plainSelectAnd = (PlainSelect) select.getSelectBody(); plainSelectAnd.setWhere(andExpression); System.err.println(plainSelectAnd); // SELECT * FROM test WHERE test.user_id = '123456' AND age BETWEEN 18 AND 30 // WHERE OR 多個條件滿足一個條件成立返回 OrExpression orExpression = new OrExpression();// OR 表達(dá)式 orExpression.setLeftExpression(equalsTo); // OR 左邊表達(dá)式 orExpression.setRightExpression(between); // OR 右邊表達(dá)式 PlainSelect plainSelectOr = (PlainSelect) select.getSelectBody(); plainSelectOr.setWhere(orExpression); System.err.println(plainSelectOr); // SELECT * FROM test WHERE test.user_id = '123456' OR age BETWEEN 18 AND 30 // ORDER BY 排序 OrderByElement orderByElement = new OrderByElement(); // 創(chuàng)建排序?qū)ο? orderByElement.isAsc(); // 設(shè)置升序排列 從小到大 orderByElement.setExpression(new Column("col01")); // 設(shè)置排序字段 PlainSelect plainSelectOrderBy = (PlainSelect) select.getSelectBody(); plainSelectOrderBy.addOrderByElements(orderByElement); System.err.println(plainSelectOrderBy); // SELECT * FROM test WHERE test.user_id = '123456' OR age BETWEEN 18 AND 30 ORDER BY col01 }
JOIN 拼裝
/** * 多表SQL查詢 * JOIN / INNER JOIN: 如果表中有至少一個匹配,則返回行 * LEFT JOIN: 即使右表中沒有匹配,也從左表返回所有的行 * RIGHT JOIN: 即使左表中沒有匹配,也從右表返回所有的行 * FULL JOIN: 只要其中一個表中存在匹配,就返回行 */ @Test public void testSelectManyTable() { Table t1 = new Table("tab1").withAlias(new Alias("t1").withUseAs(true)); // 表1 Table t2 = new Table("tab2").withAlias(new Alias("t2", false)); // 表2 PlainSelect plainSelect = new PlainSelect().addSelectItems(new AllColumns()).withFromItem(t1); // SELECT * FROM tab1 AS t1 // JOIN ON 如果表中有至少一個匹配,則返回行 Join join = new Join(); // 創(chuàng)建Join對象 join.withRightItem(t2); // 添加Join的表 JOIN t2 =>JOIN tab2 t2 EqualsTo equalsTo = new EqualsTo(); // 添加 = 條件表達(dá)式 t1.user_id = t2.user_id equalsTo.setLeftExpression(new Column(t1, "user_id ")); equalsTo.setRightExpression(new Column(t2, "user_id ")); join.withOnExpression(equalsTo);// 添加ON plainSelect.addJoins(join); System.err.println(plainSelect); // SELECT * FROM tab1 AS t1 JOIN tab2 t2 ON t1.user_id = t2.user_id // 設(shè)置join參數(shù)可實現(xiàn)其他類型join // join.setLeft(true); LEFT JOIN // join.setRight(true); RIGHT JOIN // join.setFull(true); FULL JOIN // join.setInner(true); }
校驗SQL
String sql = "DROP INDEX IF EXISTS idx_tab2_id;"; // validate statement if it's valid for all given databases. Validation validation = new Validation(Arrays.asList(DatabaseType.SQLSERVER, DatabaseType.MARIADB, DatabaseType.POSTGRESQL, DatabaseType.H2), sql); List<ValidationError> errors = validation.validate(); // validate against pre-defined FeaturesAllowed.DML set String sql = "CREATE TABLE tab1 (id NUMERIC(10), val VARCHAR(30))"; Validation validation = new Validation(Arrays.asList(FeaturesAllowed.DML), sql); List<ValidationError> errors = validation.validate(); // only DML is allowed, got error for using a DDL statement log.error (errors);
Validates metadata such as names of tables, views, columns for their existence or non-existence
java.sql.Connection connection = ...; String sql = "ALTER TABLE mytable ADD price numeric(10,5) not null"; Validation validation = new Validation(Arrays.asList(new JdbcDatabaseMetaDataCapability(connection, // NamesLookup: Databases handle names differently NamesLookup.UPPERCASE)), sql); List<ValidationError> errors = validation.validate(); // do something else with the parsed statements Statements statements = validation.getParsedStatements(); // check for validation-errors if (!errors.isEmpty()) { ... }
總結(jié)
我們可以借助JSqlparser來解析SQL并且動態(tài)拼接生成SQL,在Mybatis-plus中的租戶其實也是類似這樣實現(xiàn)的。
甚至有興趣的同學(xué)可以自己做一個SQL拼裝器,將前臺篩選的條件轉(zhuǎn)換為SQL進(jìn)行查詢。所有的查詢字段、條件、聯(lián)表等等都做成動態(tài)拼裝。
以上為個人經(jīng)驗,希望能給大家一個參考,也希望大家多多支持腳本之家。
相關(guān)文章
SpringBoot集成FTP文件服務(wù)器簡單應(yīng)用方式
這篇文章主要介紹了SpringBoot集成FTP文件服務(wù)器簡單應(yīng)用方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-07-07SpringMVC HttpMessageConverter報文信息轉(zhuǎn)換器
??HttpMessageConverter???,報文信息轉(zhuǎn)換器,將請求報文轉(zhuǎn)換為Java對象,或?qū)ava對象轉(zhuǎn)換為響應(yīng)報文。???HttpMessageConverter???提供了兩個注解和兩個類型:??@RequestBody,@ResponseBody???,??RequestEntity,ResponseEntity??2023-01-01一文帶你熟練掌握J(rèn)ava中的日期時間相關(guān)類
我們在開發(fā)時,除了數(shù)字、數(shù)學(xué)這樣的常用API之外,還有日期時間類,更是會被經(jīng)常使用,比如我們項目中必備的日志功能,需要記錄異常等信息產(chǎn)生的時間,本文就帶各位來學(xué)習(xí)一下相關(guān)的日期時間類有哪些2023-05-05