我要评论基础篇
jsqlparser是一个sql语句解析器。它将sql转换为java类的可遍历层次结构。 支持oracle，sqlserver，mysql，postgresql等常用数据库。但各种数据库系统的sql语法都在动态变化，可以解析某些（不是全部）。
jsqlparser就是一个把sql转换为java对象操作的工具包，但是发现此类文章较少，文档也不太详细，所以写个博客供参考。 github地址
入门
添加pom
<dependency>
<groupid>com.github.jsqlparser</groupid>
<artifactid>jsqlparser</artifactid>
<version>3.2</version>
</dependency>
编写简单的测试类
public void testselect() throws jsqlparserexception {
// 使用工具类把sql转换为select对象
select select = (select) ccjsqlparserutil.parse("select username,age,sex from user");
selectbody selectbody = select.getselectbody();
system.err.println(selectbody);
}
源码结构
expression：sql构建相关类，比如equalsto、inexpression等表达式用于构建sql。
parser： sql解析相关类，比如ccjsqlparserutil。
schema：主要存放数据库schema相关的类 ，比如表、列等。
statement：封装了数据库操作对象，create、insert、delete、select等
util： 各种工具类、不同db版本、sql标准等处理类，如selectutils、databasetype等。
jsqlparser支持where及多表操作sql构建
单表where
/**
* 单表sql查询
*
* @throws jsqlparserexception
*/
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(); // 等于表达式
equalsto.setleftexpression(new column(table, "user_id")); // 设置表达式左边值
equalsto.setrightexpression(new stringvalue("123456"));// 设置表达式右边值
plainselect plainselect = (plainselect) select.getselectbody(); // 转换为更细化的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")); // 设置表达式左边值
notequalsto.setrightexpression(new stringvalue("123456"));// 设置表达式右边值
plainselect plainselectnot = (plainselect) select.getselectbody();
plainselectnot.setwhere(notequalsto);
system.err.println(plainselectnot);// select * from test where test.user_id <> '123456'
// 其他运算符, 参考上面代码添加表达式即可
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(); // 创建like表达式对象
likeexpression.setleftexpression(new column("username")); // 表达式左边
likeexpression.setrightexpression(new stringvalue("张%")); // 右边表达式
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(); // 创建in范围的元素集合
deptids.add("0001");
deptids.add("0002");
itemslist itemslist = new expressionlist(deptids.stream().map(stringvalue::new).collect(collectors.tolist())); // 把集合转变为jsqlparser需要的元素列表
inexpression inexpression = new inexpression(new column("dept_id "), itemslist); // 创建in表达式对象，传入列名及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)); // 设置起点值
between.setbetweenexpressionend(new longvalue(30)); // 设置终点值
between.setleftexpression(new column("age")); // 设置左边的表达式，一般为列
plainselect plainselectbetween = (plainselect) select.getselectbody();
plainselectbetween.setwhere(between);
system.err.println(plainselectbetween); // select * from test where age between 18 and 30
// where and 多个条件结合,都需要成立
andexpression andexpression = new andexpression(); // and 表达式
andexpression.setleftexpression(equalsto); // and 左边表达式
andexpression.setrightexpression(between); // and 右边表达式
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 表达式
orexpression.setleftexpression(equalsto); // or 左边表达式
orexpression.setrightexpression(between); // or 右边表达式
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(); // 创建排序对象
orderbyelement.isasc(); // 设置升序排列 从小到大
orderbyelement.setexpression(new column("col01")); // 设置排序字段
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: 只要其中一个表中存在匹配，就返回行
*/
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(); // 创建join对象
join.withrightitem(t2); // 添加join的表 join t2 =>join tab2 t2
equalsto equalsto = new equalsto(); // 添加 = 条件表达式 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
// 设置join参数可实现其他类型join
// join.setleft(true); left join
// join.setright(true); right join
// join.setfull(true); full join
// join.setinner(true);
}
sql函数
/**
* sql 函数
* select function(列) from 表
*/
public void testfun() throws jsqlparserexception {
table t1 = new table("tab1").withalias(new alias("t1").withuseas(true)); // 表1
plainselect plainselect = new plainselect();
plainselect.setfromitem(t1); // 设置from t1= > select from tab1 as t1
list<selectitem> selectitemlist = new arraylist<>(); // 查询元素集合
selectexpressionitem selectexpressionitem001 = new selectexpressionitem(); // 元素1表达式
selectexpressionitem001.setexpression(new column(t1,"col001"));
selectexpressionitem selectexpressionitem002 = new selectexpressionitem(); // 元素2表达式
selectexpressionitem002.setexpression(new column(t1,"col002"));
selectitemlist.add(0, selectexpressionitem001); // 添加入队
selectitemlist.add(1, selectexpressionitem002); // 添加入队
// count
selectexpressionitem selectexpressionitemcount = new selectexpressionitem(); // 创建函数元素表达式
selectexpressionitemcount.setalias(new alias("count")); // 设置别名
function function = new function(); // 创建函数对象 function extends astnodeaccessimpl implements expression
function.setname("count"); // 设置函数名
expressionlist expressionlistcount = new expressionlist(); // 创建参数表达式
expressionlistcount.setexpressions(collections.singletonlist(new column(t1, "id")));
function.setparameters(expressionlistcount); // 设置参数
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
}
jsqlparser进行sql解析
import net.sf.jsqlparser.jsqlparserexception;
import net.sf.jsqlparser.expression.expression;
import net.sf.jsqlparser.expression.function;
import net.sf.jsqlparser.expression.operators.relational.namedexpressionlist;
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.create.table.createtable;
import net.sf.jsqlparser.statement.delete.delete;
import net.sf.jsqlparser.statement.insert.insert;
import net.sf.jsqlparser.statement.select.*;
import net.sf.jsqlparser.statement.update.update;
import net.sf.jsqlparser.statement.values.valuesstatement;
import net.sf.jsqlparser.util.tablesnamesfinder;
import org.apache.commons.collections.collectionutils;
import sun.net.www.content.text.plain;
import java.util.list;
import java.util.objects;
public class main {
static string sql1 = "select t1.f1,t1.f2,t2.id,count(*) from table t1 left join table1 t2 right join (select * from table2) t3 where t1.id='12121' or (t1.id between 1 and 3 and t1.id>'22112') group by t.f1 order by t.f1 desc,tf2 asc limit 1,20";
static string sql2 = "insert into table(f1,f2) values (1,2)";
static string sql2_1 = "insert into table(f1,f2) (select f1,f2 from table1)";
static string sql3 = "update table set f1=2,f2=3 where f1=1212";
static string sql3_1 = "insert into table(f1,f2) (select f1,f2 from table1)";
static string sql4_1 = "delete from table where 1=1";
static string sql_5 = "create table table_name2 as select * from table_name1 t1 where t1.id = '333'";
static string sql5_1 = "create table `gen_table` (\n" +
" `table_id` bigint(20) not null auto_increment comment '编号',\n" +
" `table_name` varchar(200) default '' comment '表名称',\n" +
" `table_comment` varchar(500) default '' comment '表描述',\n" +
" `sub_table_name` varchar(64) default null comment '关联子表的表名',\n" +
" `sub_table_fk_name` varchar(64) default null comment '子表关联的外键名',\n" +
" `class_name` varchar(100) default '' comment '实体类名称',\n" +
" `tpl_category` varchar(200) default 'crud' comment '使用的模板（crud单表操作 tree树表操作 sub主子表操作）',\n" +
" `package_name` varchar(100) default null comment '生成包路径',\n" +
" `module_name` varchar(30) default null comment '生成模块名',\n" +
" `business_name` varchar(30) default null comment '生成业务名',\n" +
" `function_name` varchar(50) default null comment '生成功能名',\n" +
" `function_author` varchar(50) default null comment '生成功能作者',\n" +
" `gen_type` char(1) default '0' comment '生成代码方式（0zip压缩包 1自定义路径）',\n" +
" `gen_path` varchar(200) default '/' comment '生成路径（不填默认项目路径）',\n" +
" `options` varchar(1000) default null comment '其它生成选项',\n" +
" `create_by` varchar(64) default '' comment '创建者',\n" +
" `create_time` datetime default null comment '创建时间',\n" +
" `update_by` varchar(64) default '' comment '更新者',\n" +
" `update_time` datetime default null comment '更新时间',\n" +
" `remark` varchar(500) default null comment '备注',\n" +
" primary key (`table_id`)\n" +
") engine=innodb default charset=utf8 comment='代码生成业务表';";
static string sql1_1 = "select `t2a_cust_c`.`cust_id` as `cust_id`,`t2a_cust_c`.`cust_name` as `cust_name`,`t2a_cust_c`.`cust_en_name` as `cust_en_name`,`t2a_cust_c`.`cust_sts` as `cust_sts`,`t2a_cust_c`.`cust_type` as `cust_type`,`t2a_cust_c`.`cert_type` as `cert_type`,`t2a_cust_c`.`cert_no` as `cert_no`,`t2a_cust_c`.`org_id` as `org_id`,`t2a_cust_c`.`biz_scope` as `biz_scope`,'' as `nation_cd`,null as `income_amt`,`t2a_cust_c`.`create_dt` as `create_dt`,'' as `is_staff`,`t2a_cust_c`.`is_free_trade` as `is_free_trade`,`t2a_cust_c`.`cust_nat` as `cust_nat`,`t2a_cust_c`.`pbc_indus` as `pbc_indus`,'' as `pbc_ocp`,`t2a_cust_c`.`cert_invalid_dt` as `cert_invalid_dt` from `t2a_cust_c` union all select `t2a_cust_i`.`cust_id` as `cust_id`,`t2a_cust_i`.`cust_name` as `cust_name`,`t2a_cust_i`.`cust_en_name` as `cust_en_name`,`t2a_cust_i`.`cust_sts` as `cust_sts`,`t2a_cust_i`.`cust_type` as `cust_type`,`t2a_cust_i`.`cert_type` as `cert_type`,`t2a_cust_i`.`cert_no` as `cert_no`,`t2a_cust_i`.`org_id` as `org_id`,'' as `biz_scope`,`t2a_cust_i`.`nation_cd` as `nation_cd`,`t2a_cust_i`.`income_amt` as `income_amt`,`t2a_cust_i`.`create_dt` as `create_dt`,`t2a_cust_i`.`is_staff` as `is_staff`,`t2a_cust_i`.`is_free_trade` as `is_free_trade`,`t2a_cust_i`.`cust_nat` as `cust_nat`,'' as `pbc_indus`,`t2a_cust_i`.`pbc_ocp` as `pbc_ocp`,`t2a_cust_i`.`cert_invalid_dt` as `cert_invalid_dt` from `t2a_cust_i`";
public static void main(string[] args) {
testsimpleselectsql(sql1_1);
// testsimpleinsertsql(sql2);
// testsimpleinsertsql(sql2_1);
// testsimpleupdatesql(sql3);
// testsimpledeletesql(sql4_1);
// testsimplecreatesql(sql5_1);
}
private static void testsimplecreatesql(string sql_5) {
try {
statement statement = ccjsqlparserutil.parse(sql_5);
if (statement instanceof createtable) {
table table = ((createtable) statement).gettable();
system.out.println(table);
select select = ((createtable) statement).getselect();
if (select != null){
string s = select.tostring();
testsimpleselectsql(s);
}
}
} catch (exception e) {
e.printstacktrace();
}
}
//解析sql
public static void testsimpleselectsql(string sql1) {
system.out.println("=================测试查询==================");
try {
select select = (select) ccjsqlparserutil.parse(sql1);
tablesnamesfinder tablesnamesfinder = new tablesnamesfinder();
list<string> tablelist = tablesnamesfinder.gettablelist(select);
// 获取到查询sql中的所有表名,下面的逻辑是对sql的细致拆分
system.out.println("表名:" + tablelist);
if (select.getselectbody() instanceof plainselect) { // 普通查询
// 复杂sql会多次调用此处方法，所以抽出作为公共类使用
getselectmsg(select);
}else if (select.getselectbody() instanceof withitem){ // with语句
}else if (select.getselectbody() instanceof setoperationlist){ // intersect、except、minus、union语句
setoperationlist setoperationlist = (setoperationlist)select.getselectbody();
list<selectbody> selects = setoperationlist.getselects();
for (int i = 0; i < selects.size(); i++) {
// 此处又是符合普通sql的拆解逻辑
getselectmsg(select);
}
}else if (select.getselectbody() instanceof valuesstatement){ // values语句
}
} catch (jsqlparserexception e) {
e.printstacktrace();
}
system.out.println("=================测试查询==================");
}
public static void testsimpleinsertsql(string sql) {
system.out.println("=================测试插入sql==================");
system.out.println("测试sql:" + sql);
try {
insert insert = (insert) ccjsqlparserutil.parse(sql);
system.out.println("插入的表" + insert.gettable());
system.out.println("插入的列" + insert.getcolumns());
if (objects.nonnull(insert.getselect())) {
selectbody selectbody = insert.getselect().getselectbody();
system.out.println("来自：" + selectbody);
} else {
system.out.println("普通插入");
system.out.println("插入的值" + insert.getitemslist());
}
} catch (jsqlparserexception e) {
e.printstacktrace();
}
system.out.println("=================测试插入sql==================");
}
public static void testsimpleupdatesql(string sql) {
system.out.println("=================测试更新sql==================");
system.out.println("测试sql:" + sql);
try {
update update = (update) ccjsqlparserutil.parse(sql);
system.out.println("更新的表" + update.gettable());
system.out.println("更新的列" + update.getcolumns());
system.out.println("更新的值" + update.getexpressions());
system.out.println("条件" + update.getwhere());
} catch (jsqlparserexception e) {
e.printstacktrace();
}
system.out.println("=================测试更新sql==================");
}
public static void testsimpledeletesql(string sql) {
system.out.println("=================测试删除sql==================");
system.out.println("测试sql:" + sql);
try {
delete delete = (delete) ccjsqlparserutil.parse(sql);
system.out.println("删除的表" + delete.gettable());
system.out.println("条件的列" + delete.getwhere());
} catch (jsqlparserexception e) {
e.printstacktrace();
}
system.out.println("=================测试删除sql==================");
}
public static string jointypestr(join join) {
if (join.isleft()) {
return "左连接";
}
if (join.isright()) {
return "左连接";
}
if (join.isfull()) {
return "全连接";
}
if (join.iscross()) {
return "交叉连接";
}
return null;
}
public static void getselectmsg(select select){
plainselect plain = (plainselect) select.getselectbody();
list<join> joins = plain.getjoins();
if (collectionutils.isnotempty(joins)) {
for (join join : joins) {
fromitem rightitem = join.getrightitem();
if (rightitem instanceof table) {
table table = (table) (rightitem);
system.out.println("连接类型:" + jointypestr(join) + " 表：" + table.getname() + " 别名：" + table.getalias());
} else if (rightitem instanceof subselect) {
subselect subselect = (subselect) (rightitem);
system.out.println("连接类型:" + jointypestr(join) + " 子查询：" + subselect.getselectbody() + " 别名：" + rightitem.getalias());
}
}
}
list<selectitem> selectitems = plain.getselectitems();
for (selectitem selectitem : selectitems) {
if (selectitem instanceof allcolumns) {
system.out.println("获取的是表中的全部列: * ");
continue;
}
selectexpressionitem selectexpressionitem = (selectexpressionitem) selectitem;
expression expression = selectexpressionitem.getexpression();
//判断表达式是否是函数
if (expression instanceof function) {
function function = (function) expression;
namedexpressionlist namedparameters = function.getnamedparameters();
if (namedparameters != null) {
list<expression> expressions = namedparameters.getexpressions();
system.out.println(expressions);
}
system.out.println("函数：" + ((function) expression).getname());
boolean allcolumns = function.isallcolumns();
system.out.println("传入的是全部列：" + allcolumns);
//判断表达式是否是列
} else if (expression instanceof column) {
system.out.println("查询值：" + ((column) expression).getcolumnname());
}
}
// system.out.println("表名:" + tablelist);
expression where = plain.getwhere();
if (where != null) {
system.out.println("条件:" + where);
}
//排序
list<orderbyelement> orderbyelements = plain.getorderbyelements();
if (objects.nonnull(orderbyelements)) {
for (orderbyelement orderbyelement : orderbyelements) {
expression expression = orderbyelement.getexpression();
if (expression instanceof column) {
column column = (column) (expression);
system.out.println("排序字段:" + column.getcolumnname() + "," + (orderbyelement.isasc() ? "正序" : "倒序"));
}
}
}
//获取分组
groupbyelement groupby = plain.getgroupby();
if (objects.nonnull(groupby)) {
list<expression> groupbyexpressions = groupby.getgroupbyexpressions();
for (expression groupbyexpression : groupbyexpressions) {
if (groupbyexpression instanceof column) {
column column = (column) (groupbyexpression);
system.out.println("分组字段:" + column.getcolumnname());
}
}
}
//分页
limit limit = plain.getlimit();
if (objects.nonnull(limit)) {
system.out.println("行:" + limit.getrowcount());
system.out.println("偏移量:" + limit.getoffset());
}
}
}
发表评论