JSqlParser基本应用文档-select部分

最新推荐文章于 2024-06-21 15:30:17 发布

weixin_34364071

最新推荐文章于 2024-06-21 15:30:17 发布

阅读量3.9k

点赞数

文章标签： java 数据库 python

原文链接：https://my.oschina.net/youfen/blog/1936319

版权

2019独角兽企业重金招聘Python工程师标准>>>

JSqlParser基本应用文档

该文档旨在快速了解JSqlParser的基本结构设计和JSqlParser中的select部分的应用(alter、delete、insert等其他部分视具体情况而定)。主要从普通查询（select）、子查询（subselect）、连接查询（union select）三个方面来了解JSqlParser的直接调用和SelectObject（已封装）的调用两个方面来简单构造。在构建sql之前先了解下JSqlParser的基本构造。

JSqlParser组成部分：

JSqlParser基本应用文档-select部分 - youfenvs - youfenvs的博客

selectBody结构：

JSqlParser基本应用文档-select部分 - youfenvs - youfenvs的博客

基本应用

基础数据：

直销客户业务量汇总：KB_ZX_CUST_PROD_SUM

字段	名称
month	统计月份
VIP_TYPE	客户类型
ZX_CUST_CODE	直销客户编码
ZX_CUST_NAME	直销客户名称
CUST_SUBST_NAME	客户局向
SUM_TOTAL	全部总用户数

生成表别名：

selectObject.generateTableAlias(key, tableName, aliasName);

等价于

selectObject.generateTableAlias(key, tableName, aliasName,false);

获取表别名：

selectObject.getTableAlias(key);

获取表：

selectObject.getTableByKey(key);

构造查询语句：

普通查询

条件设置
统计月份	12
客户类型	高值商客
直销客户编码	12
输出字段
统计月份	不使用函数
全部总用户数	不使用函数

目的SQL：

SELECT T_A.month, T_A.SUM_TOTAL FROM KB_ZX_CUST_PROD_SUM T_A WHERE T_A.MONTH = '201512' AND ( T_A.month = '12') AND ( T_A.VIP_TYPE = '高值商客') AND ( T_A.ZX_CUST_CODE = '12')

Demo：

package com.chinatmg.ecpp.helper;

import java.util.ArrayList;

import java.util.List;

import net.sf.jsqlparser.JSQLParserException;

import net.sf.jsqlparser.expression.Alias;

import net.sf.jsqlparser.expression.BinaryExpression;

import net.sf.jsqlparser.expression.CastExpression;

import net.sf.jsqlparser.expression.Expression;

import net.sf.jsqlparser.expression.Function;

import net.sf.jsqlparser.expression.LongValue;

import net.sf.jsqlparser.expression.Parenthesis;

import net.sf.jsqlparser.expression.StringValue;

import net.sf.jsqlparser.expression.operators.conditional.AndExpression;

import net.sf.jsqlparser.expression.operators.relational.EqualsTo;

import net.sf.jsqlparser.expression.operators.relational.ExpressionList;

import net.sf.jsqlparser.schema.Column;

import net.sf.jsqlparser.schema.Table;

import net.sf.jsqlparser.statement.create.table.ColDataType;

import net.sf.jsqlparser.statement.select.AllColumns;

import net.sf.jsqlparser.statement.select.Limit;

import net.sf.jsqlparser.statement.select.PlainSelect;

import net.sf.jsqlparser.statement.select.SelectExpressionItem;

import org.apache.commons.lang.StringUtils;

public class SimpleSelectDemo {

public static void main(String[] args){

SimpleSelectDemo demo = new SimpleSelectDemo();

String table= "KB_ZX_CUST_PROD_SUM";

String[][] condition ={{"MONTH","12"},{"VIP_TYPE","高值商客"},{"ZX_CUST_CODE","12"}};

String[][] outColumn = {{"MONTH"},{"SUM_TOTAL"}};

String limit = "122";

demo.selectObjectDemo(table, null,condition, outColumn,null,limit);

demo.jsqlDemo(table, null,condition, outColumn,null,limit);

}

//SelectObject使用

SelectObject selectObjectDemo(String table,String aliasName,String[][] condition,String[][] outColumn,String[][] groubyColumn,String limit){

if(aliasName==null){

aliasName="T_A";

}

SelectObject selectObject = new SelectObject();

//生成表别名，默认userAs为true

selectObject.generateTableAlias(table, table, aliasName);

//设置查询的主体

selectObject.setFromItem(selectObject.getTableByKey(table));

//加入查询字段

if(outColumn!=null){

for(String[] out:outColumn){

switch (out.length){

case 1:

selectObject.addSelectItem(aliasName+"."+out[0]);

break;

case 2:

selectObject.addSelectItem(out[1]+"("+aliasName+"."+out[0]+")");

break;

case 4:

selectObject.addSelectItem(out[1]+"("+out[2]+"("+aliasName+"."+out[0]+" as "+out[3]+"))");

break;

}

}else{

selectObject.addSelectItem("(*)");

}

//加入查询条件

if(condition!=null){

for(String[] cond:condition){

try {

selectObject.addWhereExpression("("+aliasName+"."+cond[0]+"='"+cond[1]+"')");

} catch (JSQLParserException e) {

e.printStackTrace();

}

//添加group by

if(groubyColumn!=null){

for(String[] group:groubyColumn){

selectObject.addGroupByExpression(aliasName+"."+group[0]);

}

//设置Limit

if(StringUtils.isNotEmpty(limit)){

selectObject.setLimit(limit);

}

System.out.println("selectObject:"+selectObject);

return selectObject;

}

//JSqlParser直接调用

PlainSelect jsqlDemo(String ta,String aliasName,String[][] condition,String[][] outColumn,String[][] groubyColumn,String rowCount){

if(aliasName==null){

aliasName="T_A";

}

PlainSelect plainSelect = new PlainSelect();

Table table = new Table(ta);

Alias alias = new Alias(aliasName);

table.setAlias(alias);

//设置查询的主体

plainSelect.setFromItem(table);

//加入查询字段

if(outColumn!=null){

SelectExpressionItem selectItem = null;

Expression selectExpression = null;

Function func = null;

List<Expression> funcExpression = null;

CastExpression castExpression = null;

ColDataType colDataType = null;

for(String[] out:outColumn){

selectExpression = null;

selectItem = new SelectExpressionItem();

switch (out.length){

case 1:

selectExpression = new Column(aliasName+"."+out[0]);

break;

case 2:

func = new Function();

func.setName(out[1]);

funcExpression = new ArrayList<Expression>();

funcExpression.add(new Column(aliasName+"."+out[0]));

func.setParameters(new ExpressionList(funcExpression));

selectExpression = func;

break;

case 4:

//此处以cast 为例

func = new Function();

func.setName(out[1]);

funcExpression = new ArrayList<Expression>();

castExpression = new CastExpression();

castExpression.setLeftExpression(new Column(aliasName+"."+out[0]));

colDataType = new ColDataType();

colDataType.setDataType(out[3]);

castExpression.setType(colDataType);

castExpression.setUseCastKeyword(true);

funcExpression.add(castExpression);

func.setParameters(new ExpressionList(funcExpression));

selectExpression = func;

break;

}

selectItem.setExpression(selectExpression);

plainSelect.addSelectItems(selectItem);

}

}else{

plainSelect.addSelectItems(new AllColumns());

}

//加入查询条件

if(condition!=null){

BinaryExpression whereExpression =null;

for(String[] cond:condition){

whereExpression = new EqualsTo();

whereExpression.setLeftExpression(new Column(aliasName+"."+cond[0]));

whereExpression.setRightExpression(new StringValue(cond[1]));

if(plainSelect.getWhere()==null){

plainSelect.setWhere(new Parenthesis(whereExpression));

}else{

plainSelect.setWhere(new AndExpression(plainSelect.getWhere(),new Parenthesis(whereExpression)));

}

//添加group by

if(groubyColumn!=null){

for(String[] group:groubyColumn){

plainSelect.addGroupByColumnReference(new Column(aliasName+"."+group[0]));

}

//设置Limit

if(StringUtils.isNotEmpty(rowCount)){

Limit limit = new Limit();

limit.setRowCount(new LongValue(rowCount));

plainSelect.setLimit(limit);

}

System.out.println("JSqlDemo:"+plainSelect);

return plainSelect;

}

子查询:基于普通查询

条件设置
统计月份	12
客户类型	高值商客
直销客户编码	12
分组维度
直销客户名称
客户局向
输出字段
统计月份	统计
全部总用户数	求和

目的SQL：

select COUNT(a.month), SUM(CAST(a.SUM_TOTAL as double)), a.ZX_CUST_NAME, a.CUST_SUBST_NAME from (SELECT T_A.month, T_A.SUM_TOTAL, T_A.ZX_CUST_NAME, T_A.CUST_SUBST_NAME FROM KB_ZX_CUST_PROD_SUM T_A WHERE ( T_A.month = '12') AND ( T_A.VIP_TYPE = '高值商客') AND ( T_A.ZX_CUST_CODE = '12') AND ( ( T_A.ZX_CUST_CODE = '3232') AND ( T_A.CUST_SUBST_NAME = '23') ) ) a group by a.ZX_CUST_NAME, a.CUST_SUBST_NAME limit 122

Demo:

package com.chinatmg.ecpp.helper;

import net.sf.jsqlparser.expression.Alias;

import net.sf.jsqlparser.statement.select.PlainSelect;

import net.sf.jsqlparser.statement.select.SubSelect;

public class SimpleSubselectDemo {

public static void main(String[] args){

String table= "KB_ZX_CUST_PROD_SUM";

String[][] condition ={{"MONTH","12"},{"VIP_TYPE","高值商客"},{"ZX_CUST_CODE","12"}};

String[][] outColumn = {{"MONTH","COUNT"},{"SUM_TOTAL","SUM","CAST","double"},{"ZX_CUST_NAME"},{"CUST_SUBST_NAME"}};

String[][] groubyColumn = {{"MONTH"},{"SUM_TOTAL"}};

//子查询输出的字段

String[][] suboutColumn = {{"MONTH"},{"SUM_TOTAL"},{"ZX_CUST_NAME"},{"CUST_SUBST_NAME"}};

String limit = "122";

SimpleSubselectDemo demo= new SimpleSubselectDemo();

demo.suselectObjectDemo(table, condition, outColumn, groubyColumn, suboutColumn,limit);

demo.jsqlDemo(table, condition, outColumn, groubyColumn, suboutColumn,limit);

}

//SelectObject使用

SelectObject suselectObjectDemo(String table,String[][] condition,String[][] outColumn,String[][] groubyColumn,String[][] suboutColumn,String limit){

SimpleSelectDemo demo = new SimpleSelectDemo();

SelectObject selectObject = demo.selectObjectDemo("a", "a", null, outColumn, groubyColumn, limit);

SelectObject subselectObject = demo.selectObjectDemo(table,null, condition, suboutColumn,null,null);

selectObject.setSubselectObject(subselectObject, "a", false);;

System.out.println("subselect:"+selectObject);

return selectObject;

}

JSqlParser直接调用

PlainSelect jsqlDemo(String ta,String[][] condition,String[][] outColumn,String[][] groubyColumn,String[][] suboutColumn,String rowCount){

SimpleSelectDemo demo = new SimpleSelectDemo();

PlainSelect plainSelect = demo.jsqlDemo("a", "a", null, outColumn, groubyColumn, rowCount);

PlainSelect subplainSelect = demo.jsqlDemo(ta,null, condition, suboutColumn,null,null);

SubSelect subselect = new SubSelect();

subselect.setSelectBody(subplainSelect);

subselect.setAlias(new Alias("a",false));

plainSelect.setFromItem(subselect);

System.out.println("subselect:"+plainSelect);

return plainSelect;

}

连接查询:基于普通查询

条件设置
统计月份	12
客户类型	高值商客
直销客户编码	12
输出字段
统计月份	不使用函数
全部总用户数	不使用函数

目的SQL：

SELECT T_A.month, T_A.SUM_TOTAL FROM KB_ZX_CUST_PROD_SUM T_A WHERE T_A.MONTH = '201512' AND ( T_A.month = '12') AND ( T_A.VIP_TYPE = '高值商客') AND ( T_A.ZX_CUST_CODE = '12') union all SELECT T_A.month, T_A.SUM_TOTAL FROM KB_ZX_CUST_PROD_SUM T_A WHERE T_A.MONTH = '201512' AND ( T_A.month = '12') AND ( T_A.VIP_TYPE = '高值商客') AND ( T_A.ZX_CUST_CODE = '12')

Demo:

package com.chinatmg.ecpp.helper;

import java.util.ArrayList;

import java.util.List;

import net.sf.jsqlparser.statement.select.PlainSelect;

import net.sf.jsqlparser.statement.select.SelectBody;

import net.sf.jsqlparser.statement.select.SetOperation;

import net.sf.jsqlparser.statement.select.SetOperationList;

import net.sf.jsqlparser.statement.select.UnionOp;

public class SimpleUnionSelect {

public static void main(String[] args){

String table= "KB_ZX_CUST_PROD_SUM";

String[][] condition ={{"MONTH","12"},{"VIP_TYPE","高值商客"},{"ZX_CUST_CODE","12"}};

String[][] outColumn = {{"MONTH"},{"SUM_TOTAL"}};

String limit = "122";

SimpleUnionSelect demo = new SimpleUnionSelect();

demo.selectDemo(table, null, condition, outColumn, null, limit);

demo.jsqlDemo(table, null, condition, outColumn, null, limit);

}

//SelectObject使用

SelectObject selectDemo(String table,String aliasName,String[][] condition,String[][] outColumn,String[][] groubyColumn,String limit){

SimpleSelectDemo demo = new SimpleSelectDemo();

SelectObject selectObject = demo.selectObjectDemo(table, aliasName, condition, outColumn, groubyColumn, limit);

selectObject.addUnionSelect(demo.selectObjectDemo(table, aliasName, condition, outColumn, groubyColumn, limit), false, true);

System.out.println("unionSelect:"+selectObject);

return selectObject;

}

//JSqlParser直接调用

SetOperationList jsqlDemo(String table,String aliasName,String[][] condition,String[][] outColumn,String[][] groubyColumn,String limit){

SimpleSelectDemo demo = new SimpleSelectDemo();

PlainSelect plainSelectA = demo.jsqlDemo(table, aliasName, condition, outColumn, groubyColumn, limit);

PlainSelect plainSelectB = demo.jsqlDemo(table, aliasName, condition, outColumn, groubyColumn, limit);

SetOperationList unionSelect = new SetOperationList();

List<SelectBody> select = new ArrayList<SelectBody>();

List<Boolean> brackets = new ArrayList<Boolean>();

List<SetOperation> ops = new ArrayList<SetOperation>();

//添加第一个Union语句

select.add(plainSelectA);

brackets.add(false);

//添加其他的Union语句

select.add(plainSelectB);

brackets.add(false);

UnionOp op = new UnionOp();

op.setAll(true);

ops.add(op);

unionSelect.setBracketsOpsAndSelects(brackets, select, ops);

System.out.println("unionSelect:"+unionSelect);

return unionSelect;

}

补充：

Join查询

条件设置
统计月份	12
客户类型	高值商客
直销客户编码	12
输出字段
统计月份	不使用函数
全部总用户数	不使用函数
Join条件
Id	Id

简单连接

目的SQL：

SELECT T_A.month, T_A.SUM_TOTAL FROM KB_ZX_CUST_PROD_SUM T_A,KB_ZX_CUST_PROD_SUM T_B WHERE T_A.MONTH = '201512' AND ( T_A.month = '12') AND ( T_A.VIP_TYPE = '高值商客') AND ( T_A.ZX_CUST_CODE = '12')

Demo:

package com.chinatmg.ecpp.helper;

import java.util.ArrayList;

import java.util.List;

import net.sf.jsqlparser.expression.Alias;

import net.sf.jsqlparser.schema.Table;

import net.sf.jsqlparser.statement.select.Join;

import net.sf.jsqlparser.statement.select.PlainSelect;

public class SimpleJoinSelect {

public static void main(String[] args){

SimpleJoinSelect demo = new SimpleJoinSelect();

String table= "KB_ZX_CUST_PROD_SUM";

String[][] condition ={{"MONTH","12"},{"VIP_TYPE","高值商客"},{"ZX_CUST_CODE","12"}};

String[][] outColumn = {{"MONTH"},{"SUM_TOTAL"}};

String limit = "122";

demo.selectObjectDemo(table, null,condition, outColumn,null,limit);

demo.jsqlDemo(table, null,condition, outColumn,null,limit);

}

//SelectObject使用

SelectObject selectObjectDemo(String table,String aliasName,String[][] condition,String[][] outColumn,String[][] groubyColumn,String limit){

SimpleSelectDemo demo = new SimpleSelectDemo();

SelectObject selectObject = demo.selectObjectDemo(table, aliasName, condition, outColumn, groubyColumn, limit);

selectObject.addJoin(table, "T_B", false).setSimple(true);

System.out.println("simple Join :"+selectObject);

return selectObject;

}

//JSqlParser直接调用

PlainSelect jsqlDemo(String ta,String aliasName,String[][] condition,String[][] outColumn,String[][] groubyColumn,String rowCount){

SimpleSelectDemo demo = new SimpleSelectDemo();

PlainSelect plainSelect = demo.jsqlDemo(ta, aliasName, condition, outColumn, groubyColumn, rowCount);

List<Join> joins = new ArrayList<Join>();

Join join = new Join();

Table table = new Table(ta);

table.setAlias(new Alias("T_B",false));

join.setRightItem(table);

join.setSimple(true);

joins.add(join);

plainSelect.setJoins(joins);

System.out.println("simple Join :"+plainSelect);

return plainSelect;

}

Join连接

目的SQL：

SELECT T_A.month, T_A.SUM_TOTAL FROM KB_ZX_CUST_PROD_SUM T_A JOIN KB_ZX_CUST_PROD_SUM T_B ON T_A.ID = T_B.ID WHERE T_A.MONTH = '201512' AND ( T_A.month = '12') AND ( T_A.VIP_TYPE = '高值商客') AND ( T_A.ZX_CUST_CODE = '12')

Demo:

package com.chinatmg.ecpp.helper;

import java.util.ArrayList;

import java.util.List;

import net.sf.jsqlparser.JSQLParserException;

import net.sf.jsqlparser.expression.Alias;

import net.sf.jsqlparser.expression.BinaryExpression;

import net.sf.jsqlparser.expression.operators.relational.EqualsTo;

import net.sf.jsqlparser.schema.Column;

import net.sf.jsqlparser.schema.Table;

import net.sf.jsqlparser.statement.select.Join;

import net.sf.jsqlparser.statement.select.PlainSelect;

public class SimpleJoinSelect {

public static void main(String[] args){

SimpleJoinSelect demo = new SimpleJoinSelect();

String table= "KB_ZX_CUST_PROD_SUM";

String[][] condition ={{"MONTH","12"},{"VIP_TYPE","高值商客"},{"ZX_CUST_CODE","12"}};

String[][] outColumn = {{"MONTH"},{"SUM_TOTAL"}};

String limit = "122";

demo.selectObjectDemo(table, null,condition, outColumn,null,limit);

demo.jsqlDemo(table, null,condition, outColumn,null,limit);

}

/*//SelectObject使用

SelectObject selectObjectDemo(String table,String aliasName,String[][] condition,String[][] outColumn,String[][] groubyColumn,String limit){

SimpleSelectDemo demo = new SimpleSelectDemo();

SelectObject selectObject = demo.selectObjectDemo(table, aliasName, condition, outColumn, groubyColumn, limit);

selectObject.addJoin(table, "T_B", false).setSimple(true);

System.out.println("simple Join :"+selectObject);

return selectObject;

}

//JSqlParser直接调用

PlainSelect jsqlDemo(String ta,String aliasName,String[][] condition,String[][] outColumn,String[][] groubyColumn,String rowCount){

SimpleSelectDemo demo = new SimpleSelectDemo();

PlainSelect plainSelect = demo.jsqlDemo(ta, aliasName, condition, outColumn, groubyColumn, rowCount);

List<Join> joins = new ArrayList<Join>();

Join join = new Join();

Table table = new Table(ta);

table.setAlias(new Alias("T_B",false));

join.setRightItem(table);

join.setSimple(true);

joins.add(join);

plainSelect.setJoins(joins);

System.out.println("simple Join :"+plainSelect);

return plainSelect;

}*/

//SelectObject使用

SelectObject selectObjectDemo(String table,String aliasName,String[][] condition,String[][] outColumn,String[][] groubyColumn,String limit){

SimpleSelectDemo demo = new SimpleSelectDemo();

SelectObject selectObject = demo.selectObjectDemo(table, aliasName, condition, outColumn, groubyColumn, limit);

Join join = selectObject.addJoin(table, "T_B", false);

//join.setSimple(false);

try {

SelectObject.addJoinOnExpression(join, "T_A.ID = T_B.ID");

} catch (JSQLParserException e) {

e.printStackTrace();

}

System.out.println("Join :"+selectObject);

return selectObject;

}

//JSqlParser直接调用

PlainSelect jsqlDemo(String ta,String aliasName,String[][] condition,String[][] outColumn,String[][] groubyColumn,String rowCount){

SimpleSelectDemo demo = new SimpleSelectDemo();

PlainSelect plainSelect = demo.jsqlDemo(ta, aliasName, condition, outColumn, groubyColumn, rowCount);

List<Join> joins = new ArrayList<Join>();

Join join = new Join();

Table table = new Table(ta);

table.setAlias(new Alias("T_B",false));

join.setRightItem(table);

//join.setSimple(true);

BinaryExpression onExpression = new EqualsTo();

onExpression.setLeftExpression(new Column(new Table("T_A"), "ID"));

onExpression.setRightExpression(new Column("T_B.ID"));

join.setOnExpression(onExpression);

joins.add(join);

plainSelect.setJoins(joins);

System.out.println("Join :"+plainSelect);

return plainSelect;

}

maven:

<dependency>
   <groupId>com.github.jsqlparser</groupId>
   <artifactId>jsqlparser</artifactId>
   <version>0.9.7</version>
   </dependency>

package com.chinatmg.ecpp.helper;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.select.FromItem;
import net.sf.jsqlparser.statement.select.Join;
import net.sf.jsqlparser.statement.select.Limit;
import net.sf.jsqlparser.statement.select.Offset;
import net.sf.jsqlparser.statement.select.OrderByElement;
import net.sf.jsqlparser.statement.select.OrderByElement.NullOrdering;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.SelectBody;
import net.sf.jsqlparser.statement.select.SelectExpressionItem;
import net.sf.jsqlparser.statement.select.SelectItem;
import net.sf.jsqlparser.statement.select.SetOperation;
import net.sf.jsqlparser.statement.select.SetOperationList;
import net.sf.jsqlparser.statement.select.SubSelect;
import net.sf.jsqlparser.statement.select.UnionOp;

import org.apache.commons.lang.StringUtils;
public class SelectObject {
   private PlainSelect plainSelect;
   private Map<String,Table> mapJPTable;
private SetOperationList unionSelect;

   public SelectObject() {
       super();
       setPlainSelect(new PlainSelect());
   }
   public SelectObject(PlainSelect plainSelect) {
       super();
       setPlainSelect(plainSelect);
   }

   /**
   * 生成表别名
   * @param key
   * @param tableName
   * @param aliasName
   * @param useAs 默认为true
   */
   public void generateTableAlias(String key,String tableName,String aliasName,boolean useAs){
       Table jpTable = new Table(tableName);
       jpTable.setAlias(new Alias(aliasName, useAs));
       if(mapJPTable==null){
           mapJPTable = new HashMap<String, Table>();
       }
       mapJPTable.put(key, jpTable);
   }

   /**
   * 生成表别名
   * @param key
   * @param tableName
   * @param aliasName useAs 默认为true
   */
   public void generateTableAlias(String key,String tableName,String aliasName){
       generateTableAlias(key, tableName, aliasName, true);
   }

   /**
   * 根据key获取Table
   * @param key
   * @return Table
   */
   public Table getTableByKey(String key){
       if(mapJPTable==null){
           return null;
       }
       return mapJPTable.get(key);
   }

   /**
   * 根据key获取别名
   * @param key
   * @return String
   */
   public String getTableAlias(String key){
       Table table = getTableByKey(key);
       if(table!=null&&table.getAlias()!=null){
           return table.getAlias().getName();
       }
       return null;
   }

   /**
   * 添加查询体
   * @param table
   * @param columnName
   * @param aliasName useAs 默认为true
   */
   public void addSelectItem(Table table,String columnName,String aliasName){
       addSelectItem(table, columnName, aliasName, true);
   }

   /**
   * 添加查询体
   * @param table
   * @param columnName
   * @param aliasName
   * @param useAs 默认为true
   */
   public void addSelectItem(Table table,String columnName,String aliasName,boolean useAs){
       SelectExpressionItem selectItem = new SelectExpressionItem();
       selectItem.setExpression(new Column(table,columnName));
       if(StringUtils.isNotEmpty(aliasName)){
           selectItem.setAlias(new Alias(aliasName,useAs));
       }
       addSelectItem(selectItem);
   }

   /**
   * 添加查询体
   * @param selectItem
   */
   public void addSelectItem(SelectItem selectItem){
       if(plainSelect.getSelectItems()==null){
           plainSelect.addSelectItems(selectItem);
           return;
       }
       if(!contains(plainSelect.getSelectItems(),selectItem)){
           plainSelect.addSelectItems(selectItem);
       }
   }

   /**
   * 添加查询体该方法不会生成别名
   * @param selectString
   */
   public void addSelectItem(String selectString){
       SelectExpressionItem selectItem = new SelectExpressionItem();
       try {
           selectItem.setExpression(CCJSqlParserUtil.parseCondExpression(selectString));
           addSelectItem(selectItem);
       } catch (JSQLParserException e) {
           e.printStackTrace();
       }
   }

   /**
   * 添加查询体
   * @param selectString
   * @param aliasName
   * @param useAs 默认为true
   */
   public void addSelectItem(String selectString,String aliasName,boolean useAs){
       SelectExpressionItem selectItem = new SelectExpressionItem();
       try {
           selectItem.setExpression(CCJSqlParserUtil.parseCondExpression(selectString));
           if(StringUtils.isNotEmpty(aliasName)){
               selectItem.setAlias(new Alias(aliasName,useAs));
           }
           addSelectItem(selectItem);
       } catch (JSQLParserException e) {
           e.printStackTrace();
       }
   }

   /**
   * 添加查询体
   * @param selectString useAs 默认为true
   */
   public void addSelectItem(String selectString,String aliasName){
       addSelectItem(selectString, aliasName, true);
   }
   /**
   * 设置查询表
   * @param item
   */
   public void setFromItem(FromItem item) {
       plainSelect.setFromItem(item);
   }
   /**
   * 添加join左外连接
   * @param table
   * @return
   */
   public Join addJoinLO(FromItem rightItem){
       return addJoin(rightItem, true, false, true, false);
   }

   /**
   * 添加join左内连接
   * @param table
   * @return
   */
   public Join addJoinLI(FromItem rightItem){
       return addJoin(rightItem, true, true, false, false);
   }

   /**
   * 添加join连接
   * @param table
   * @return
   */
   public Join addJoin(FromItem rightItem){
       return addJoin(rightItem, false, false, false, false);
   }

   /**
   * 添加join连接
   * @param join
   * @return Join
   */
   public Join addJoin(Join join){
       return addJoin(join, false, false, false, false);
   }

   /**
   * 添加join连接
   * @param tableName
   * @return
   */
   public Join addJoin(String tableName){
       return addJoin(tableName, null,false);
   }

   /**
   * 添加join连接
   * @param tableName
   * @param aliasName
   * @param useAs
   * @return
   */
   public Join addJoin(String tableName,String aliasName,boolean useAs){
       Table table = new Table(tableName);
       if(StringUtils.isNotEmpty(aliasName)){
           table.setAlias(new Alias(aliasName, useAs));
       }
       return addJoin(table, false, false, false, false);
   }

   /**
   * 添加join连接
   * @param rightItem
   * @param left
   * @param inner
   * @param outter
   * @param right
   * @return Join
   */
   public Join addJoin(FromItem rightItem,boolean left,boolean inner,boolean outter,boolean right){
       Join join = new Join();
       join.setRightItem(rightItem);
       return addJoin(join, left, inner, outter, right);
   }

   /**
   * 添加join连接
   * @param join
   * @param left
   * @param inner
   * @param outter
   * @param right
   * @return
   */
   public Join addJoin(Join join,boolean left,boolean inner,boolean outter,boolean right){
       if(left){
           join.setLeft(left);
       }else if(right){
           join.setRight(right);
       }
       if(inner){
           join.setInner(inner);
       }else if(outter){
           join.setOuter(outter);
       }
       if(!contains(getListJoin(),join)){
           getListJoin().add(join);
       }
       return join;
   }
   /**
   * 添加join连接条件
   * @param join
   * @param onExpression
   * @param isOr
   */
   public static void addJoinOnExpression(Join join,Expression onExpression,boolean isOr){
       if(join==null){
           return;
       }
       if(join.getOnExpression() ==null){
           join.setOnExpression(onExpression);
           return;
       }
       if(isOr){
           join.setOnExpression(new OrExpression(join.getOnExpression(), onExpression));
       }else{
           join.setOnExpression(new AndExpression(join.getOnExpression(),onExpression));
       }
   }

   /**
   * 添加join连接条件
   * @param join
   * @param onExpression 默认为 and连接
   */
   public static void addJoinOnExpression(Join join,Expression onExpression){
       addJoinOnExpression(join,onExpression, false);
   }

   /**
   * 添加join连接条件
   * @param join
   * @param onExpression
   * @throws JSQLParserException 默认为 and连接
   */
   public static void addJoinOnExpression(Join join,String onExpression) throws JSQLParserException{
       addJoinOnExpression(join,CCJSqlParserUtil.parseCondExpression(onExpression));
   }

   /**
   * 添加join连接条件
   * @param join
   * @param onExpression
   * @param isOr
   * @throws JSQLParserException
   */
   public static void addJoinOnExpression(Join join,String onExpression,boolean isOr) throws JSQLParserException{
       addJoinOnExpression(join,CCJSqlParserUtil.parseCondExpression(onExpression),isOr);
   }

   /**
   * 添加where条件
   * @param whereExpression
   * @param isOr
   */
   public void addWhereExpression(Expression whereExpression,boolean isOr){
       if(plainSelect.getWhere() ==null){
           plainSelect.setWhere(whereExpression);
           return;
       }
       if(isOr){
           plainSelect.setWhere(new OrExpression(plainSelect.getWhere(), whereExpression));
       }else{
           plainSelect.setWhere(new AndExpression(plainSelect.getWhere(),whereExpression));
       }
   }

   /**
   * 添加where条件
   * @param whereExpression 默认为 and连接
   */
   public void addWhereExpression(Expression whereExpression){
       addWhereExpression(whereExpression, false);
   }

   /**
   * 添加where条件
   * @param whereExpression
   * @throws JSQLParserException 默认为 and连接
   */
   public void addWhereExpression(String whereExpression) throws JSQLParserException{
       addWhereExpression(CCJSqlParserUtil.parseCondExpression(whereExpression));
   }

   /**
   * 添加where条件
   * @param whereExpression
   * @param isOr
   * @throws JSQLParserException
   */
   public void addWhereExpression(String whereExpression,boolean isOr) throws JSQLParserException{
       addWhereExpression(CCJSqlParserUtil.parseCondExpression(whereExpression),isOr);
   }

   /**
   * 添加groupBy语句
   * @param groupByString
   */
   public void addGroupByExpression(String groupByString){
       try {
           Expression groupByExpression = CCJSqlParserUtil.parseCondExpression(groupByString);
           addGroupByExpression(groupByExpression);
       } catch (JSQLParserException e) {
           e.printStackTrace();
       }
   }

   /**
   * 添加groupBy语句
   * @param groupByExpression
   */
   public void addGroupByExpression(Expression groupByExpression){
       List<Expression> listGroupByExpression =plainSelect.getGroupByColumnReferences();
       if(listGroupByExpression==null){
           plainSelect.addGroupByColumnReference(groupByExpression);
           return;
       }
       if(!contains(listGroupByExpression,groupByExpression)){
           //如果为空说明有问题
           plainSelect.addGroupByColumnReference(groupByExpression);
       }
   }

   /**
   * 添加having语句
   * @param havingExpression
   * @param isOr
   */
   public void addHavingExpression(Expression havingExpression,boolean isOr){
       if(plainSelect.getHaving() ==null){
           plainSelect.setHaving(havingExpression);
           return;
       }
       if(isOr){
           plainSelect.setHaving(new OrExpression(plainSelect.getHaving(), havingExpression));
       }else{
           plainSelect.setHaving(new AndExpression(plainSelect.getHaving(),havingExpression));
       }
   }

   /**
   * 添加having语句
   * @param havingExpression
   */
   public void addHavingExpression(Expression havingExpression){
       addWhereExpression(havingExpression, false);
   }

   /**
   * 添加having语句
   * @param havingExpression
   * @throws JSQLParserException
   */
   public void addHavingExpression(String havingExpression) throws JSQLParserException{
       addWhereExpression(CCJSqlParserUtil.parseCondExpression(havingExpression));
   }

   /**
   * 添加having语句
   * @param havingExpression
   * @param isOr
   * @throws JSQLParserException
   */
   public void addHavingExpression(String havingExpression,boolean isOr) throws JSQLParserException{
       addWhereExpression(CCJSqlParserUtil.parseCondExpression(havingExpression),isOr);
   }


   /**
   * 添加orderBy语句
   * @param orderByElement
   * @return
   */
   public boolean addOrderByExpression(OrderByElement orderByElement){
       boolean result=false;
       if(!contains(getOrderByElements(),orderByElement)){
           //如果为空说明有问题
           result |=getOrderByElements().add(orderByElement);
       }
       return result;
   }

   /**
   * 添加orderBy语句
   * @param orderByExpression
   * @param isDesc
   * @param isNullsFirst
   * @return
   */
   public boolean addOrderByExpression(Expression orderByExpression,boolean isDesc,boolean isNullsFirst){
       OrderByElement orderByElement = new OrderByElement();
       orderByElement.setExpression(orderByExpression);
       if(isDesc){
           orderByElement.setAsc(!isDesc);
       }else{
           orderByElement.setAscDescPresent(!isDesc);
       }
       if(isNullsFirst){
           orderByElement.setNullOrdering(NullOrdering.NULLS_FIRST);
       }else{
           orderByElement.setNullOrdering(NullOrdering.NULLS_LAST);
       }
       return addOrderByExpression(orderByElement);
   }
   /**
   * 添加orderBy语句
   * @param orderByExpression
   * @param isDesc
   * @return
   */
   public boolean addOrderByExpression(Expression orderByExpression,boolean isDesc){
       OrderByElement orderByElement = new OrderByElement();
       orderByElement.setExpression(orderByExpression);
       if(isDesc){
           orderByElement.setAsc(!isDesc);
       }else{
           orderByElement.setAscDescPresent(!isDesc);
       }
       return addOrderByExpression(orderByElement);
   }

   /**
   * 添加orderBy语句
   * @param orderByExpression
   * @return
   */
   public boolean addOrderByExpression(Expression orderByExpression){
       return addOrderByExpression(orderByExpression,false);
   }

   /**
   * 添加orderBy语句
   * @param orderByExpression
   * @param isDesc
   * @param isNullsFirst
   * @return
   * @throws JSQLParserException
   */
   public boolean addOrderByExpression(String orderByExpression,boolean isDesc,boolean isNullsFirst) throws JSQLParserException{
       return addOrderByExpression(CCJSqlParserUtil.parseCondExpression(orderByExpression), isDesc, isNullsFirst);
   }

   /**
   * 添加orderBy语句
   * @param orderByExpression
   * @param isDesc
   * @return
   * @throws JSQLParserException
   */
   public boolean addOrderByExpression(String orderByExpression,boolean isDesc) throws JSQLParserException{
       return addOrderByExpression(CCJSqlParserUtil.parseCondExpression(orderByExpression),isDesc);
   }

   /**
   * 添加orderBy语句
   * @param orderByExpression
   * @return
   * @throws JSQLParserException
   */
   public boolean addOrderByExpression(String orderByExpression) throws JSQLParserException{
       return addOrderByExpression(orderByExpression,false);
   }

   private void setSubselectBody(SelectBody selectBody,String aliasName,boolean userAs){
       SubSelect subSelect =new SubSelect();
       subSelect.setSelectBody(selectBody);
       if(StringUtils.isNotEmpty(aliasName)){
           subSelect.setAlias(new Alias(aliasName,userAs));
       }
       setFromItem(subSelect);
   }
   /**
   * 添加子查询
   * @param selectObject
   * @param aliasName
   * @param userAs
   */
   public void setSubselectObject(SelectObject selectObject,String aliasName,boolean userAs){
       if(selectObject!=null){
           setSubselectBody(selectObject.getSelectBody(),aliasName,userAs);
       }
   }

   /**
   * 添加子查询
   * @param selectObject
   */
   public void setSubselectObject(SelectObject selectObject){
       setSubselectObject(selectObject, null, false);
   }

   /**
   * 设置Limit
   * @param rowCount
   */
   public void setLimit(String rowCount){
       Limit limit = new Limit();
       limit.setRowCount(new LongValue(rowCount));
       plainSelect.setLimit(limit);
   }

   /**
   * 获取Limit
   * @param rowCount
   */
   public Limit getLimit(){
       return plainSelect.getLimit();
   }

   /**
   * 设置Offset
   * @param start
   */
   public void setOffset(long start){
       Offset offset = new Offset();
       offset.setOffset(start);
       plainSelect.setOffset(offset);
   }

   /**
   * 获取Offset
   * @param start
   */
   public Offset getOffset(){
       return plainSelect.getOffset();
   }

   /**
   * 添加unionSelect
   * @param selectObject
   * @param isBracket 默认为false
   * @param isUnionAll 默认为false
   */
   public void addUnionSelect(SelectObject selectObject,boolean isBracket,boolean isUnionAll){
       if(selectObject!=null){
           addUnionSelectBody(selectObject.getSelectBody(), isBracket, isUnionAll);
       }
   }

   /**
   * 添加unionSelect
   * @param selectObject
   * @param isBracket isUnionAll 默认为false
   */
   public void addUnionSelect(SelectObject selectObject,boolean isBracket){
       addUnionSelect(selectObject, isBracket, false);
   }

   /**
   * 添加unionSelect
   * @param selectObject
   */
   public void addUnionSelect(SelectObject selectObject){
       addUnionSelect(selectObject, false, false);
   }

   /**
   * 添加unionSelect
   * @param plainSelect
   * @param isBracket
   * @param isUnionAll
   */
   public void addUnionSelect(PlainSelect plainSelect,boolean isBracket,boolean isUnionAll){
       if(plainSelect!=null){
           addUnionSelectBody(plainSelect, isBracket, isUnionAll);
       }
   }

   /**
   * 添加unionSelect
   * @param plainSelect
   * @param isBracket
   */
   public void addUnionSelect(PlainSelect plainSelect,boolean isBracket){
       addUnionSelect(plainSelect, isBracket, false);
   }

   /**
   * 添加unionSelect
   * @param plainSelect
   */
   public void addUnionSelect(PlainSelect plainSelect){
       addUnionSelect(plainSelect, false, false);
   }

   private void addUnionSelectBody(SelectBody selectBody,boolean isBracket,boolean isUnionAll){
       if(selectBody!=null){
           if(unionSelect==null){
               unionSelect = new SetOperationList();
           }
           List<SelectBody> selects = unionSelect.getSelects();
           List<Boolean> brackets = unionSelect.getBrackets();
           if(selects==null){
               selects = new ArrayList<SelectBody>();
           }
           if(brackets==null){
               brackets = new ArrayList<Boolean>();
           }
           List<SetOperation> operations = unionSelect.getOperations();
           if(operations==null){
               operations = new ArrayList<SetOperation>();
           }
           //本身作为第一条语句
           if(selects.size()==0){
               selects.add(getSelectBody());
               brackets.add(false);
           }

           selects.add(selectBody);
           brackets.add(isBracket);
           UnionOp operation=new UnionOp();
           if(isUnionAll){
               operation.setAll(isUnionAll);
           }
           operations.add(operation);
           unionSelect.setBracketsOpsAndSelects(brackets, selects, operations);
       }
   }



   /**
   * 添加orderBy语句
   * @param orderByElement
   * @return
   */
   public boolean addUnionOrderByExpression(OrderByElement orderByElement){
       boolean result=false;
       if(!contains(getUnionOrderByElements(),orderByElement)){
           //如果为空说明有问题
           result |=getUnionOrderByElements().add(orderByElement);
       }
       return result;
   }

   /**
   * 添加orderBy语句
   * @param orderByExpression
   * @param isDesc
   * @param isNullsFirst
   * @return
   */
   public boolean addUnionOrderByExpression(Expression orderByExpression,boolean isDesc,boolean isNullsFirst){
       OrderByElement orderByElement = new OrderByElement();
       orderByElement.setExpression(orderByExpression);
       if(isDesc){
           orderByElement.setAsc(!isDesc);
       }else{
           orderByElement.setAscDescPresent(!isDesc);
       }
       if(isNullsFirst){
           orderByElement.setNullOrdering(NullOrdering.NULLS_FIRST);
       }else{
           orderByElement.setNullOrdering(NullOrdering.NULLS_LAST);
       }
       return addUnionOrderByExpression(orderByElement);
   }
   /**
   * 添加orderBy语句
   * @param orderByExpression
   * @param isDesc
   * @return
   */
   public boolean addUnionOrderByExpression(Expression orderByExpression,boolean isDesc){
       OrderByElement orderByElement = new OrderByElement();
       orderByElement.setExpression(orderByExpression);
       if(isDesc){
           orderByElement.setAsc(!isDesc);
       }else{
           orderByElement.setAscDescPresent(!isDesc);
       }
       return addUnionOrderByExpression(orderByElement);
   }

   /**
   * 添加orderBy语句
   * @param orderByExpression
   * @return
   */
   public boolean addUnionOrderByExpression(Expression orderByExpression){
       return addUnionOrderByExpression(orderByExpression,false);
   }

   /**
   * 添加orderBy语句
   * @param orderByExpression
   * @param isDesc
   * @param isNullsFirst
   * @return
   * @throws JSQLParserException
   */
   public boolean addUnionOrderByExpression(String orderByExpression,boolean isDesc,boolean isNullsFirst) throws JSQLParserException{
       return addUnionOrderByExpression(CCJSqlParserUtil.parseCondExpression(orderByExpression), isDesc, isNullsFirst);
   }

   /**
   * 添加orderBy语句
   * @param orderByExpression
   * @param isDesc
   * @return
   * @throws JSQLParserException
   */
   public boolean addUnionOrderByExpression(String orderByExpression,boolean isDesc) throws JSQLParserException{
       return addUnionOrderByExpression(CCJSqlParserUtil.parseCondExpression(orderByExpression),isDesc);
   }

   /**
   * 添加orderBy语句
   * @param orderByExpression
   * @return
   * @throws JSQLParserException
   */
   public boolean addUnionOrderByExpression(String orderByExpression) throws JSQLParserException{
       return addUnionOrderByExpression(orderByExpression,false);
   }



   /**
   * 设置unionLimit
   * @param rowCount
   */
   public void setUnionLimit(String rowCount){
       Limit limit = new Limit();
       limit.setRowCount(new LongValue(rowCount));
       unionSelect.setLimit(limit);
   }

   /**
   * 获取unionLimit
   * @param rowCount
   */
   public Limit getUnionLimit(){
       return unionSelect.getLimit();
   }

   /**
   * 设置unionOffset
   * @param start
   */
   public void setUnionOffset(long start){
       Offset offset = new Offset();
       offset.setOffset(start);
       unionSelect.setOffset(offset);
   }

   /**
   * 获取unionOffset
   * @param start
   */
   public Offset getUnionOffset(){
       return unionSelect.getOffset();
   }

   public PlainSelect getPlainSelect() {
       return plainSelect;
   }
   public void setPlainSelect(PlainSelect plainSelect) {
       if(plainSelect==null){
           plainSelect =new PlainSelect();
       }
       this.plainSelect = plainSelect;
   }
   public Map<String, Table> getMapJPTable() {
       if(mapJPTable==null){
           mapJPTable =new HashMap<String,Table>();
       }
       return mapJPTable;
   }
   public void setMapJPTable(Map<String, Table> mapJPTable) {
       this.mapJPTable = mapJPTable;
   }
   public List<Join> getListJoin() {
       if(plainSelect.getJoins()==null){
           plainSelect.setJoins(new ArrayList<Join>());
       }
       return plainSelect.getJoins();
   }
   public void setListJoin(List<Join> listJoin) {
       plainSelect.setJoins(listJoin);
   }
   public List<OrderByElement> getOrderByElements() {
       if(plainSelect.getOrderByElements()==null){
           plainSelect.setOrderByElements(new ArrayList<OrderByElement>());
       }
       return plainSelect.getOrderByElements();
   }
   public void setOrderByElements(List<OrderByElement> orderByElements) {
       plainSelect.setOrderByElements(orderByElements);
   }
   public SetOperationList getUnionSelect() {
       return unionSelect;
   }
   public void setUnionSelect(SetOperationList unionSelect) {
       this.unionSelect = unionSelect;
   }

   public List<OrderByElement> getUnionOrderByElements() {
       if(unionSelect.getOrderByElements()==null){
           unionSelect.setOrderByElements(new ArrayList<OrderByElement>());
       }
       return unionSelect.getOrderByElements();
   }
   @Override
   public String toString() {
       return plainSelect==null?"":getSelectBody().toString();
   }

   public SelectBody getSelectBody(){
       getPlainSelect();

       /*if(plainSelect.getSelectItems()==null){
           SelectExpressionItem selectItem = new SelectExpressionItem();
           selectItem.setExpression(new LongValue("*"));
           addSelectItem(selectItem);
       }*/
       if(unionSelect!=null&&unionSelect.getSelects()!=null){
           return unionSelect;
       }
       /*if(plainSelect.getFromItem()!=null){
           setSubselectBody(unionSelect, null, false);
       }*/
       return plainSelect;
   }
   public static boolean contains(List<?> list,Object object){
       if(list==null||list.size()==0){
           return false;
       }
       for(Object obj:list){
           if(obj==null) continue;
           if(object.toString().trim().equalsIgnoreCase(obj.toString().trim())){
               return true;
           }
       }
       return false;
   }
}