Java Select.getSelectBody方法代码示例

import net.sf.jsqlparser.statement.select.Select; //导入方法依赖的package包/类
/**
 * Adds a simple join to a select statement. The introduced join is returned for
 * more configuration settings on it (e.g. left join, right join).
 * @param select
 * @param table
 * @param onExpression
 * @return 
 */
public static Join addJoin(Select select, final Table table, final Expression onExpression) {
	if (select.getSelectBody() instanceof PlainSelect) {
		PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
		List<Join> joins = plainSelect.getJoins();
		if (joins == null) {
			joins = new ArrayList<Join>();
			plainSelect.setJoins(joins);
		}
		Join join = new Join();
		join.setRightItem(table);
		join.setOnExpression(onExpression);
		joins.add(join);
		return join;
	}
	throw new UnsupportedOperationException(NOT_SUPPORTED_YET);
}
 

import net.sf.jsqlparser.statement.select.Select; //导入方法依赖的package包/类
/**
 * Re-build the query SQL to implement paging function. The new SQL Statement will contains limit if the database
 * type is MYSQL, CET wrapped if database type is SQL Server. Note: the final SQL will contain two %s, which should
 * be replaced in run time.
 *
 * @param sql The original SQL Statement
 * @param dbType The database type
 * @return Re-build SQL which contains limit if the database type is MYSQL, CET wrapped if database type is SQL
 *         Server.
 * @throws Exception
 */
public static String pagingQuerySql(String sql, DatabaseCategory dbType, CurrentLanguage lang) throws Exception {
    String sql_content = sql.replace("@", ":");
    boolean withNolock = StringUtils.containsIgnoreCase(sql_content, "WITH (NOLOCK)");
    if (withNolock)
        sql_content = sql_content.replaceAll("(?i)WITH \\(NOLOCK\\)", "");
    StringBuilder sb = new StringBuilder();
    try {
        Select select = (Select) parserManager.parse(new StringReader(sql_content));
        PlainSelect plain = (PlainSelect) select.getSelectBody();
        if (dbType == DatabaseCategory.MySql) {
            sb.append(plain.toString());
            sb.append(lang == CurrentLanguage.Java ? mysqlPageClausePattern : mysqlCSPageClausePattern);
        } else if (dbType == DatabaseCategory.SqlServer) {
            sb.append(plain.toString());
            sb.append(lang == CurrentLanguage.Java ? sqlserverPagingClausePattern : sqlseverCSPagingClausePattern);
        } else {
            throw new Exception("Unknow database category.");
        }
    } catch (Throwable e) {
        throw e;
    }
    return sb.toString().replace(":", "@");
}
 

import net.sf.jsqlparser.statement.select.Select; //导入方法依赖的package包/类
/**
 * 获取select的count语句
 *
 * @param originalSql
 *            selectSQL
 * @return
 */
public static String sqlCountOptimize(String originalSql) {
    Assert.hasLength(originalSql);
    String sqlCount;
    try {
        Select selectStatement = (Select) CCJSqlParserUtil.parse(originalSql);
        PlainSelect plainSelect = (PlainSelect) selectStatement.getSelectBody();
        Distinct distinct = plainSelect.getDistinct();
        List<Expression> groupBy = plainSelect.getGroupByColumnReferences();
        // 优化Order by
        List<OrderByElement> orderBy = plainSelect.getOrderByElements();
        // 添加包含groupby 不去除orderby
        if (CollectionUtils.isEmpty(groupBy) && CollectionUtils.isNotEmpty(orderBy)) {
            plainSelect.setOrderByElements(null);
        }
        if (distinct != null || CollectionUtils.isNotEmpty(groupBy)) {
            return String.format(SQL_BASE_COUNT, selectStatement.toString());
        }
        List<SelectItem> selectCount = countSelectItem();
        plainSelect.setSelectItems(selectCount);
        sqlCount = selectStatement.toString();
    } catch (Exception e) {
        sqlCount = String.format(SQL_BASE_COUNT, originalSql);
    }
    return sqlCount;
}
 

import net.sf.jsqlparser.statement.select.Select; //导入方法依赖的package包/类
public SqlQuery parse(String sqlString)
{
   final StringReader reader = new StringReader(sqlString);
   CCJSqlParser parser = new CCJSqlParser(reader);
   try {
      Statement stmt = parser.Statement();
      if (stmt instanceof Select) {
         Select ss = (Select) stmt;
         PlainSelect plainSelect = (PlainSelect) ss.getSelectBody();
         return createUserQuery(plainSelect, sqlString);
      }
      else {
         throw new UnsupportedSqlExpressionException("Only SELECT statement is valid"); //$NON-NLS-1$
      }
   }
   catch (ParseException e) {
      throw new SqlParserException("SQL syntax error", e); //$NON-NLS-1$
   }
}
 

import net.sf.jsqlparser.statement.select.Select; //导入方法依赖的package包/类
@Test
public void testWithNolock(){
    String sql = "SELECT * FROM A WITH(NOLOCK) INNER JOIN B WITH(NOLOCK) ON A.TypeId = B.Id";
    System.out.println(sql);
    sql = sql.replaceAll("((?i)\\s*(\\w?)\\s*with\\s*\\(nolock\\))", " $2_PAGEWITHNOLOCK");
    System.out.println(sql);
    //解析SQL
    Statement stmt = null;
    try {
        stmt = CCJSqlParserUtil.parse(sql);
    } catch (Throwable e) {
        e.printStackTrace();
        return;
    }
    Select select = (Select) stmt;
    SelectBody selectBody = select.getSelectBody();
    sql = selectBody.toString();

    sql = sql.replaceAll("\\s*(\\w*?)_PAGEWITHNOLOCK", " $1 WITH(NOLOCK)");

    System.out.println(sql);
}
 

import net.sf.jsqlparser.statement.select.Select; //导入方法依赖的package包/类
/**
 * Determines whether the given sql statement must be executed in a single
 * use read context. This must be done for queries against the information
 * schema. This method sets the <code>forceSingleUseReadContext</code> to
 * true if necessary.
 * 
 * @param select
 *            The sql statement to be examined.
 */
protected void determineForceSingleUseReadContext(Select select)
{
	if (select.getSelectBody() != null)
	{
		select.getSelectBody().accept(new SelectVisitorAdapter()
		{
			@Override
			public void visit(PlainSelect plainSelect)
			{
				if (plainSelect.getFromItem() != null)
				{
					plainSelect.getFromItem().accept(new FromItemVisitorAdapter()
					{
						@Override
						public void visit(Table table)
						{
							if (table.getSchemaName() != null
									&& table.getSchemaName().equalsIgnoreCase("INFORMATION_SCHEMA"))
							{
								setForceSingleUseReadContext(true);
							}
						}
					});
				}
			}

		});
	}
}
 

import net.sf.jsqlparser.statement.select.Select; //导入方法依赖的package包/类
/**
 * jsqlparser方式获取select的count语句
 *
 * @param originalSql selectSQL
 * @return
 */
public static String jsqlparserCount(String originalSql) {
    String sqlCount;
    try {
        Select selectStatement = (Select) CCJSqlParserUtil.parse(originalSql);
        PlainSelect plainSelect = (PlainSelect) selectStatement.getSelectBody();
        Distinct distinct = plainSelect.getDistinct();
        List<Expression> groupBy = plainSelect.getGroupByColumnReferences();
        // 优化Order by
        List<OrderByElement> orderBy = plainSelect.getOrderByElements();
        // 添加包含groupby 不去除orderby
        if (CollectionUtils.isEmpty(groupBy) && CollectionUtils.isNotEmpty(orderBy)) {
            plainSelect.setOrderByElements(null);
        }
        // 包含 distinct、groupBy不优化
        if (distinct != null || CollectionUtils.isNotEmpty(groupBy)) {
            sqlCount = String.format(SqlUtils.SQL_BASE_COUNT, selectStatement.toString());
            return sqlCount;
        }
        List<SelectItem> selectCount = countSelectItem();
        plainSelect.setSelectItems(selectCount);
        sqlCount = selectStatement.toString();
    } catch (Exception e) {
        sqlCount = String.format(SqlUtils.SQL_BASE_COUNT, originalSql);
    }
    return sqlCount;
}
 

import net.sf.jsqlparser.statement.select.Select; //导入方法依赖的package包/类
public static String getLimitSql(String sql, int nowPage, int pageSize) throws JSQLParserException {
    Select select = (Select) CCJSqlParserUtil.parse(sql);
    PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
    Limit limit = new Limit();
    limit.setOffset((nowPage - 1) * pageSize);
    limit.setRowCount(pageSize);
    plainSelect.setLimit(limit);
    return select.toString();
}
 

import net.sf.jsqlparser.statement.select.Select; //导入方法依赖的package包/类
@Override
public void visit(Select select) {
    if (select.getSelectBody() != null) {
        select.getSelectBody().accept(this);
    }
    if (select.getWithItemsList() != null) {
        select.getWithItemsList().forEach(
            i -> {
                if (i != null) {
                    i.accept(this);
                }
            }
        );
    }
}
 

import net.sf.jsqlparser.statement.select.Select; //导入方法依赖的package包/类
@Override
public SqlInfo optimizeSql(MetaObject metaObject, String sql) {
    if (logger.isDebugEnabled()) {
        logger.debug(" JsqlParserCountOptimize sql=" + sql);
    }
    SqlInfo sqlInfo = SqlInfo.newInstance();
    try {
        Select selectStatement = (Select) CCJSqlParserUtil.parse(sql);
        PlainSelect plainSelect = (PlainSelect) selectStatement.getSelectBody();
        Distinct distinct = plainSelect.getDistinct();
        List<Expression> groupBy = plainSelect.getGroupByColumnReferences();
        List<OrderByElement> orderBy = plainSelect.getOrderByElements();

        // 添加包含groupBy 不去除orderBy
        if (CollectionUtils.isEmpty(groupBy) && CollectionUtils.isNotEmpty(orderBy)) {
            plainSelect.setOrderByElements(null);
            sqlInfo.setOrderBy(false);
        }
        //#95 Github, selectItems contains #{} ${}, which will be translated to ?, and it may be in a function: power(#{myInt},2)
        for (SelectItem item : plainSelect.getSelectItems()) {
            if (item.toString().contains("?")) {
                sqlInfo.setSql(String.format(SqlUtils.SQL_BASE_COUNT, selectStatement.toString()));
                return sqlInfo;
            }
        }
        // 包含 distinct、groupBy不优化
        if (distinct != null || CollectionUtils.isNotEmpty(groupBy)) {
            sqlInfo.setSql(String.format(SqlUtils.SQL_BASE_COUNT, selectStatement.toString()));
            return sqlInfo;
        }
        // 优化 SQL
        plainSelect.setSelectItems(countSelectItem);
        sqlInfo.setSql(selectStatement.toString());
        return sqlInfo;
    } catch (Throwable e) {
        // 无法优化使用原 SQL
        sqlInfo.setSql(String.format(SqlUtils.SQL_BASE_COUNT, sql));
        return sqlInfo;
    }
}
 

import net.sf.jsqlparser.statement.select.Select; //导入方法依赖的package包/类
/**
 * 往where sql里面插入AND关系的表达式。
 * 
 * 例如：whereSql为 where a!=3 or a!=2 limit 1
 *      condExpress为 deleted=0
 * 那么返回：where deleted=0 and (a!=3 or a!=2) limit 1
 * 
 * @param whereSql 从where起的sql子句，如果有where必须带上where关键字。
 * @param condExpression 例如a=?  不带where或and关键字。
 * @return 注意返回字符串前面没有空格
 * @throws JSQLParserException 
 */
public static String insertWhereAndExpression(String whereSql, String condExpression) 
		throws JSQLParserException {
	
	if(condExpression == null || condExpression.trim().isEmpty()) {
		return whereSql == null ? "" : whereSql;
	}
	if(whereSql == null || whereSql.trim().isEmpty()) {
		return "WHERE " + condExpression;
	}
	
	whereSql = whereSql.trim();
	if(!whereSql.toUpperCase().startsWith("WHERE ")) {
		return "WHERE " + condExpression + " " + whereSql;
	}
	
	
	String selectSql = "select * from dual "; // 辅助where sql解析用
	Statement statement = CCJSqlParserUtil.parse(selectSql + whereSql);
	Select selectStatement = (Select) statement;
	PlainSelect plainSelect = (PlainSelect)selectStatement.getSelectBody();
	
	Expression ce = CCJSqlParserUtil.parseCondExpression(condExpression);
	Expression oldWhere = plainSelect.getWhere();
	Expression newWhere = new FixedAndExpression(ce, oldWhere);
	plainSelect.setWhere(newWhere);
	
	return plainSelect.toString().substring(selectSql.length());
}
 

import net.sf.jsqlparser.statement.select.Select; //导入方法依赖的package包/类
/**
 * select 语句解析
 * @param select
 */
private void selectCase(Select select){
    PlainSelect plain = (PlainSelect) select.getSelectBody();
    Expression where = plain.getWhere();
    handleWhereCause(where, paramNames);
}
 

import net.sf.jsqlparser.statement.select.Select; //导入方法依赖的package包/类
public QueryTypeExtractor(Select selectStatement, List<Parameter> passedParameters) throws NoSuchTableException, AmbiguousCoalesceException {
	this(selectStatement.getSelectBody(), passedParameters);
}