JSQLParser 解析sql语句框架详解

发布时间:2022-03-01 12:00:05 作者:yexindonglai@163.com 阅读(72)

因公司有需求,需要根据用户输入的sql中获取到表名,网上查询到有个框架很实用,于是就小试牛刀,此框架是githup团队研发的一款sql解析框架,也是公认最好用的,不单可以解析sql,还可以用来拼接sql;

 

使用前先导入maven,目前最新版本已经更新到4.0了,童鞋们根据自己实际情况来选择相应版本

  1. <dependency>
  2. <groupId>com.github.jsqlparser</groupId>
  3. <artifactId>jsqlparser</artifactId>
  4. <version>1.2</version>
  5. </dependency>

 

代码案例,因为我笔者这边只用到获取表名的功能,所以案例里都是在各种sql里面获取表名的功能;

  1. package com.test;
  2. import net.sf.jsqlparser.JSQLParserException;
  3. import net.sf.jsqlparser.parser.CCJSqlParserUtil;
  4. import net.sf.jsqlparser.schema.Table;
  5. import net.sf.jsqlparser.statement.create.table.CreateTable;
  6. import net.sf.jsqlparser.statement.delete.Delete;
  7. import net.sf.jsqlparser.statement.drop.Drop;
  8. import net.sf.jsqlparser.statement.insert.Insert;
  9. import net.sf.jsqlparser.statement.select.Select;
  10. import net.sf.jsqlparser.statement.update.Update;
  11. import net.sf.jsqlparser.util.TablesNamesFinder;
  12. import java.util.ArrayList;
  13. import java.util.List;
  14. public class SqlParserUtil {
  15. /**
  16. * 获取 update 语句的表名
  17. *
  18. * @param sql
  19. * @return
  20. * @throws JSQLParserException
  21. */
  22. public static List<String> getUpdateTableName(String sql) throws JSQLParserException {
  23. Update model = (Update) CCJSqlParserUtil.parse(sql);
  24. List<Table> tables = model.getTables();
  25. List<String> tableNameList = new ArrayList<>();
  26. for (Table item : tables) {
  27. String schemaName = StringUtil.isBlank(item.getSchemaName()) ? "" : item.getSchemaName() + ".";
  28. tableNameList.add(schemaName + item.getName());
  29. }
  30. return tableNameList;
  31. }
  32. /**
  33. * 获取 select 语句的表名
  34. *
  35. * @param sql
  36. * @return
  37. * @throws JSQLParserException
  38. */
  39. public static List<String> getSelectTableName(String sql) throws JSQLParserException {
  40. Select model = (Select) CCJSqlParserUtil.parse(sql);
  41. TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
  42. List<String> tableNameList = tablesNamesFinder.getTableList(model);
  43. return tableNameList;
  44. }
  45. /**
  46. * 获取 delete 语句的表名
  47. *
  48. * @param sql
  49. * @return
  50. * @throws JSQLParserException
  51. */
  52. public static String getDeleteTableName(String sql) throws JSQLParserException {
  53. Delete model = (Delete) CCJSqlParserUtil.parse(sql);
  54. Table item = model.getTable();
  55. String schemaName = StringUtil.isBlank(item.getSchemaName()) ? "" : item.getSchemaName() + ".";
  56. return schemaName + item.getName();
  57. }
  58. /**
  59. * 获取insert 语句的表名
  60. *
  61. * @param sql
  62. * @return
  63. * @throws JSQLParserException
  64. */
  65. public static String getInsertTableName(String sql) throws JSQLParserException {
  66. Insert model = (Insert) CCJSqlParserUtil.parse(sql);
  67. Table item = model.getTable();
  68. String schemaName = StringUtil.isBlank(item.getSchemaName()) ? "" : item.getSchemaName() + ".";
  69. return schemaName + item.getName();
  70. }
  71. /**
  72. * 获取删表语句的表名
  73. *
  74. * @param sql
  75. * @return
  76. * @throws JSQLParserException
  77. */
  78. public static String getDropTableName(String sql) throws JSQLParserException {
  79. Drop model = (Drop) CCJSqlParserUtil.parse(sql);
  80. Table item = model.getName();
  81. String schemaName = StringUtil.isBlank(item.getSchemaName()) ? "" : item.getSchemaName() + ".";
  82. return schemaName + item.getName();
  83. }
  84. /**
  85. * 获取建表语句的表名
  86. *
  87. * @param sql
  88. * @return
  89. * @throws JSQLParserException
  90. */
  91. public static String getCreateTableName(String sql) throws JSQLParserException {
  92. CreateTable model = (CreateTable) CCJSqlParserUtil.parse(sql);
  93. Table item = model.getTable();
  94. String schemaName = StringUtil.isBlank(item.getSchemaName()) ? "" : item.getSchemaName() + ".";
  95. return schemaName + item.getName();
  96. }
  97. static class StringUtil {
  98. /**
  99. * 是否为空
  100. *
  101. * @param s
  102. * @return
  103. */
  104. public static boolean isBlank(String s) {
  105. if (null == s || "".equals(s)) {
  106. return true;
  107. } else {
  108. return false;
  109. }
  110. }
  111. }
  112. public static void main(String[] args) throws JSQLParserException {
  113. System.out.println(getCreateTableName("create table js.cm_template ( id bigint(10) );"));
  114. System.out.println(getDeleteTableName("delete from hj.cc where 1=1"));
  115. System.out.println(getDropTableName("drop table jh.xxx"));
  116. System.out.println(getInsertTableName("insert into jhjc.cm_table (id) values(1);"));
  117. System.out.println(getSelectTableName("select * from jhjc.cm_group where id = (select id from cm.cm_gg )"));
  118. System.out.println(getUpdateTableName("update jh.cm_123 set id = 1"));
  119. }
  120. }

 打印结果如下

关键字Java