使⽤javasqlparser插件Jsqlparser实例（⼀）
JsqlParser插件⽤来对于SQL语句进⾏解析和组装，将SQL语句关键词之间的内容⽤List<String>进⾏保存，同时可以进⾏更改List<String>的内容后重新组装成⼀个新的SQL语句。项⽬中的需求多⽤于更改SQL中table name。(其实感觉⽤正则会更好⼀点) JsqlParser其实就是将sql语句各个关键词⽤Java类的形式进⾏了对应解析，包括很多函数可以让我们得到各个部分的内容。我⽬前只是简单的测试使⽤，更深⼊的实现，⽐如javacc语法树没有了解太多。很简单的东西，主要是关于JsqlParser类型转换总结。
环境：
后⾯我就把⼀些测试实例代码放上来了。主要是2个部分，⼀部分是parsing解析，⼀部分是building组装。主函数是测试函数，分别测试
select，insert，update三个主流的内容。特别需要注意的是select中获取List<table>以及List<Join>部分内容。
主函数说明：
parsing解析那部分我是把sql语句从⽂件汇总读取测试的，building组装那部分直接赋值改变List<Stirn
g>重新组装的
看得到是parsing select，insert，update测试 以及 building select，insert，uodate
Parsing类以及其中函数说明：
各个函数都有说明，参数Sting sql，得到List<String>或者String，不多说了，很简单的东西
TableNameFinder类多注意⼀些，很好使⽤，任何类型的SQL语句、⽆论是否包含嵌套SQL 此类的静态函数都能将table name提取出来，后⾯直接使⽤String replace函数进⾏替换即可。
不过有个bug，就是tablenamefinder只能得到所有不重复的表名tablename，如果发⽣重复只能得到⼀个，不能重复获取，没到源码出现这种情况的原因，留待以后学习多了再进⾏解决。
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.StringReader;
import java.util.ArrayList;
import java.util.List;
import net.sf.jsqlparser.JSQLParserException;
import net.pression.Expression;
import net.lational.ExpressionList;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.Join;
import net.sf.jsqlparser.statement.select.OrderByElement;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectItem;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.util.TablesNamesFinder;
public class Prasing_Test {
//三个 sql语句测试例⼦，select，insert，update
//由已知的 sql语句分别得到对应的 List<String> 或 String
public static void test_select(String sql) throws JSQLParserException {
// *********select body items内容
List<String> str_items = st_select_items(sql);
// **********select table
List<String> tableList = st_select_table(sql);
List<String> tableList = st_select_table(sql);
// **********select table with join
List<String> tablewithjoin = st_select_join(sql);
// // *******select where
String str = st_select_where(sql);
// // ******select group by
List<String> str_groupby = st_select_groupby(sql);
// //**************select order by
List<String> str_orderby = st_select_orderby(sql);
}
public static void test_insert(String sql) throws JSQLParserException { // ****insert table
String string_tablename = st_insert_table(sql);
// ********* insert table column
List<String> str_column = test_insert_column(sql);
// ********Insert values ExpressionList强制转换，参见InsertTest.java
List<String> str_values = test_insert_values(sql);
}
public static void test_update(String sql) throws JSQLParserException {
// *********update table name
List<String> str_table = test_update_table(sql);
// *********update column
List<String> str_column = test_update_column(sql);
// *********update values
List<String> str_values = test_update_values(sql);
// *******uodate where
String str_where = test_update_where(sql);
}
// *********select body items内容
public static List<String> test_select_items(String sql)
throws JSQLParserException {
CCJSqlParserManager parserManager = new CCJSqlParserManager(); Select select = (Select) parserManager.parse(new StringReader(sql)); PlainSelect plain = (PlainSelect) SelectBody();
List<SelectItem> selectitems = SelectItems();
List<String> str_items = new ArrayList<String>();
if (selectitems != null) {
for (int i = 0; i < selectitems.size(); i++) {
str_items.(i).toString());
}
}error parse new
return str_items;
}
// **********select table
// **********TablesNamesFinder:Find all used tables within an select
public static List<String> test_select_table(String sql)
throws JSQLParserException {
Statement statement = (Statement) CCJSqlParserUtil.parse(sql);
Select selectStatement = (Select) statement;
TablesNamesFinder tablesNamesFinder = new TablesNamesFinder(); List<String> tableList = tablesNamesFinder
.getTableList(selectStatement);
return tableList;
}
//******************* select join
public static List<String> test_select_join(String sql)
throws JSQLParserException {
Statement statement = (Statement) CCJSqlParserUtil.parse(sql);
Select selectStatement = (Select) statement;
PlainSelect plain = (PlainSelect) SelectBody();
List<Join> joinList = Joins();
List<String> tablewithjoin = new ArrayList<String>();
if (joinList != null) {
for (int i = 0; i < joinList.size(); i++) {
tablewithjoin.(i).toString());
//注意， leftjoin rightjoin 等等的to string()区别
}
}
return tablewithjoin;
}
// *******select where
public static String test_select_where(String sql)
throws JSQLParserException {
CCJSqlParserManager parserManager = new CCJSqlParserManager(); Select select = (Select) parserManager.parse(new StringReader(sql)); PlainSelect plain = (PlainSelect) SelectBody();
Expression where_expression = Where();
String str = String();
return str;
}
// ******select group by
public static List<String> test_select_groupby(String sql)
throws JSQLParserException {
CCJSqlParserManager parserManager = new CCJSqlParserManager(); Select select = (Select) parserManager.parse(new StringReader(sql)); PlainSelect plain = (PlainSelect) SelectBody();
List<Expression> GroupByColumnReferences = plain
.getGroupByColumnReferences();
List<String> str_groupby = new ArrayList<String>();
if (GroupByColumnReferences != null) {
for (int i = 0; i < GroupByColumnReferences.size(); i++) {
str_groupby.(i).toString());
}
}
return str_groupby;
}
// **************select order by
public static List<String> test_select_orderby(String sql)
throws JSQLParserException {
CCJSqlParserManager parserManager = new CCJSqlParserManager(); Select select = (Select) parserManager.parse(new StringReader(sql)); PlainSelect plain = (PlainSelect) SelectBody();
List<OrderByElement> OrderByElements = OrderByElements(); List<String> str_orderby = new ArrayList<String>();
if (OrderByElements != null) {
for (int i = 0; i < OrderByElements.size(); i++) {
str_orderby.(i).toString());
}
}
return str_orderby;
}
// ****insert table
public static String test_insert_table(String sql)
throws JSQLParserException {
Statement statement = CCJSqlParserUtil.parse(sql);
Insert insertStatement = (Insert) statement;
String string_tablename = Table().getName();
return string_tablename;
}
// ********* insert table column
public static List<String> test_insert_column(String sql)
throws JSQLParserException {
Statement statement = CCJSqlParserUtil.parse(sql);
Insert insertStatement = (Insert) statement;
List<Column> table_column = Columns();
List<String> str_column = new ArrayList<String>();
for (int i = 0; i < table_column.size(); i++) {
str_column.add((i).toString());
}
return str_column;
}
// ********* Insert values ExpressionList
public static List<String> test_insert_values(String sql)
throws JSQLParserException {
Statement statement = CCJSqlParserUtil.parse(sql);
Insert insertStatement = (Insert) statement;
List<Expression> insert_values_expression = ((ExpressionList) insertStatement .getItemsList()).getExpressions();
List<String> str_values = new ArrayList<String>();
for (int i = 0; i < insert_values_expression.size(); i++) {
str_values.add(insert_(i).toString());
}
return str_values;
}
// *********update table name
public static List<String> test_update_table(String sql)
throws JSQLParserException {
Statement statement = CCJSqlParserUtil.parse(sql);
Update updateStatement = (Update) statement;
List<Table> update_table = Tables();
List<String> str_table = new ArrayList<String>();
if (update_table != null) {
for (int i = 0; i < update_table.size(); i++) {
str_table.add((i).toString());
}
}
return str_table;
}
// *********update column
public static List<String> test_update_column(String sql)
throws JSQLParserException {
Statement statement = CCJSqlParserUtil.parse(sql);
Update updateStatement = (Update) statement;
List<Column> update_column = Columns();
List<String> str_column = new ArrayList<String>();
if (update_column != null) {
for (int i = 0; i < update_column.size(); i++) {
str_column.add((i).toString());
}
}
return str_column;
}
// *********update values
public static List<String> test_update_values(String sql)
throws JSQLParserException {
Statement statement = CCJSqlParserUtil.parse(sql);
Update updateStatement = (Update) statement;
List<Expression> update_values = Expressions();
List<String> str_values = new ArrayList<String>();
if (update_values != null) {
for (int i = 0; i < update_values.size(); i++) {
str_values.add((i).toString());
}
}
return str_values;
}
// *******update where
public static String test_update_where(String sql)
throws JSQLParserException {
Statement statement = CCJSqlParserUtil.parse(sql);
Update updateStatement = (Update) statement;
Expression where_expression = Where();
String str = String();
return str;
}
public static void select_fromtxt() throws Exception {
@SuppressWarnings("resource")
BufferedReader reader = new BufferedReader(
new FileReader(
"D:\\java：eclipse\\workspace for javaSE\\JSql-test\\src\\test\\"));
List<String> statement_list = new ArrayList<String>();
while (true) { // 输出所有语句
String line = adLine();
if (line == null) {
break;
} else
statement_list.add(line);
}
for (int i = 0; i < 1; i++) {
st_select((i));
}
}
public static void insert_fromtxt() throws Exception {
@SuppressWarnings("resource")
BufferedReader reader = new BufferedReader(
new FileReader("⽂件位置"));
List<String> statement_list = new ArrayList<String>();
while (true) { // 输出所有语句
String line = adLine();
if (line == null) {
break;
} else
statement_list.add(line);
}
for (int i = 0; i < 1; i++) {
st_insert((i));
}
}
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论