Java Select类代码示例

import net.sf.jsqlparser.statement.select.Select; //导入依赖的package包/类
@Override
public void visit(Select select) {
    SelectDeParser selectDeParser = new SelectDeParser();
    selectDeParser.setBuffer(buffer);
    ExpressionDeParser expressionDeParser = new ExpressionDeParser(selectDeParser, buffer);
    selectDeParser.setExpressionVisitor(expressionDeParser);
    if (select.getWithItemsList() != null && !select.getWithItemsList().isEmpty()) {
        buffer.append("WITH ");
        for (Iterator<WithItem> iter = select.getWithItemsList().iterator(); iter.hasNext();) {
            WithItem withItem = iter.next();
            withItem.accept(selectDeParser);
            if (iter.hasNext()) {
                buffer.append(",");
            }
            buffer.append(" ");
        }
    }
    select.getSelectBody().accept(selectDeParser);
}
 

import net.sf.jsqlparser.statement.select.Select; //导入依赖的package包/类
private static Select createSelect(CloudSpannerConnection connection, Delete delete) throws SQLException
{
	TableKeyMetaData table = connection.getTable(CloudSpannerDriver.unquoteIdentifier(delete.getTable().getName()));
	List<String> keyCols = table.getKeyColumns().stream()
			.map(x -> CloudSpannerDriver.quoteIdentifier(delete.getTable().getName()) + "."
					+ CloudSpannerDriver.quoteIdentifier(x))
			.collect(Collectors.toList());
	StringBuilder sql = new StringBuilder();
	sql.append("SELECT ").append(String.join(", ", keyCols));
	sql.append("\nFROM ").append(CloudSpannerDriver.quoteIdentifier(delete.getTable().getName()));
	sql.append("\nWHERE ").append(delete.getWhere().toString());

	try
	{
		return (Select) CCJSqlParserUtil.parse(sql.toString());
	}
	catch (JSQLParserException e)
	{
		throw new CloudSpannerSQLException("Could not parse generated SELECT statement: " + sql,
				Code.INVALID_ARGUMENT);
	}
}
 

import net.sf.jsqlparser.statement.select.Select; //导入依赖的package包/类
private InsertWorker createInsertWithSelectStatement(Insert insert, boolean forceUpdate) throws SQLException
{
	Select select = insert.getSelect();
	if (select == null)
	{
		throw new CloudSpannerSQLException("Insert statement must contain a select statement",
				Code.INVALID_ARGUMENT);
	}
	boolean isDuplicate = insert.isUseDuplicate();
	InsertWorker.DMLOperation mode;
	if (forceUpdate)
		mode = DMLOperation.Update;
	else if (isDuplicate)
		mode = DMLOperation.OnDuplicateKeyUpdate;
	else
		mode = DMLOperation.Insert;
	return new InsertWorker(getConnection(), select, insert, getConnection().isAllowExtendedMode(), mode);
}
 

import net.sf.jsqlparser.statement.select.Select; //导入依赖的package包/类
/**
 * Adds an expression to select statements. E.g. a simple column is an
 * expression.
 *
 * @param select
 * @param expr
 */
public static void addExpression(Select select, final Expression expr) {
	select.getSelectBody().accept(new SelectVisitor() {

		@Override
		public void visit(PlainSelect plainSelect) {
			plainSelect.getSelectItems().add(new SelectExpressionItem(expr));
		}

		@Override
		public void visit(SetOperationList setOpList) {
			throw new UnsupportedOperationException(NOT_SUPPORTED_YET);
		}

		@Override
		public void visit(WithItem withItem) {
			throw new UnsupportedOperationException(NOT_SUPPORTED_YET);
		}
	});
}
 

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包/类
/**
   * Adds group by to a plain select statement.
   * @param select
   * @param expr 
   */
  public static void addGroupBy(Select select, final Expression expr) {
      select.getSelectBody().accept(new SelectVisitor() {

	@Override
	public void visit(PlainSelect plainSelect) {
              plainSelect.addGroupByColumnReference(expr);
	}

	@Override
	public void visit(SetOperationList setOpList) {
		throw new UnsupportedOperationException(NOT_SUPPORTED_YET);
	}

	@Override
	public void visit(WithItem withItem) {
		throw new UnsupportedOperationException(NOT_SUPPORTED_YET);
	}
});
  }
 

import net.sf.jsqlparser.statement.select.Select; //导入依赖的package包/类
@Test
public void test() throws JSQLParserException {
    String sql = "select max(id) from (\n" +
            "            select distinct(t.id) as id\n" +
            "            from (select i.sale_order_item_id as id,o.order_id as orderId,o.marketplace as marketplace,\n" +
            "            replace(json_extract(o.others,'$.warehouse_upload'),'\"','') as swname\n" +
            "            from fantasia.sale_order_item i\n" +
            "            left join fantasia.sale_order o on o.order_id=i.order_id\n" +
            "            where o.type in ('7','8') and o.state in ('3','4') and o.upload_date>DATE_SUB(CURDATE(), INTERVAL 3 MONTH) and i.sale_order_item_id not in (\n" +
            "            select order_item_id from fantasia.customs_declaration_order_item di\n" +
            "            left join fantasia.customs_declaration_form f on di.form_id=f.id where f.removed=0)) t\n" +
            "            left join fantasia.`storage_warehouse` sw on t.swname = sw.name\n" +
            "            left join fantasia.`company` c on replace(json_extract(sw.others,'$.company'),'\"','') = c.id where c.`inland` = '1') as a";

    ReplaceTablesNamesFinder tablesNamesFinder = new ReplaceTablesNamesFinder();
    Statement statement = CCJSqlParserUtil.parse(sql);
    List<String> tables = tablesNamesFinder.getTableList((Select) statement);
    Assert.assertTrue(tables.size() == 6);
}
 

import net.sf.jsqlparser.statement.select.Select; //导入依赖的package包/类
@Test
public void test2() throws JSQLParserException {
    String sql = "select max(id) from (\n" +
            "            select distinct(t.id) as id\n" +
            "            from (select i.sale_order_item_id as id,o.order_id as orderId,o.marketplace as marketplace,\n" +
            "            replace(json_extract(o.others,'$.warehouse_upload'),'\"','') as swname\n" +
            "            from fantasia.sale_order_item i\n" +
            "            left join fantasia.sale_order o on o.order_id=i.order_id\n" +
            "            where o.type in ('7','8') and o.state in ('3','4') and o.upload_date>DATE_SUB(CURDATE(), INTERVAL 3 MONTH) and i.sale_order_item_id not in (\n" +
            "            select order_item_id from fantasia.customs_declaration_order_item di\n" +
            "            left join fantasia.customs_declaration_form f on di.form_id=f.id where f.removed=0)) t\n" +
            "            left join fantasia.`storage_warehouse` sw on t.swname = sw.name\n" +
            "            left join fantasia.`company` c on replace(json_extract(sw.others,'$.company'),'\"','') = c.id where c.`inland` = '1') as a";

    ReplaceTablesNamesFinder tablesNamesFinder = new ReplaceTablesNamesFinder();
    Statement statement = CCJSqlParserUtil.parse(sql);
    tablesNamesFinder.getTableList((Select) statement);
    System.out.println(statement.toString());
}
 

import net.sf.jsqlparser.statement.select.Select; //导入依赖的package包/类
JoinVisitor(String queryId, net.sf.jsqlparser.statement.Statement statement)
{
   this.queryId = queryId;
   this.statement = statement;

   if (statement instanceof Select)
   {
      SelectDeParser deparser = new SelectDeParser()
      {
         @Override
         public void visit(Table table)
         {
            if (table.getAlias() != null && !table.getAlias().getName().equals(""))
               aliases.put(table.getAlias().getName().toLowerCase(), table.getName().toLowerCase());
         }
      };
      ((Select)statement).getSelectBody().accept(deparser);
   }
}
 

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包/类
final public Select Select() throws ParseException {
      Select select = new Select();
      SelectBody selectBody = null;
      List with = null;
  switch ((jj_ntk==-1)?jj_ntk():jj_ntk) {
  case K_WITH:
    with = WithList();
                          select.setWithItemsList(with);
    break;
  default:
    jj_la1[24] = jj_gen;
    ;
  }
  selectBody = SelectBody();
              select.setSelectBody(selectBody);
              {if (true) return select;}
  throw new Error("Missing return statement in function");
}
 

import net.sf.jsqlparser.statement.select.Select; //导入依赖的package包/类
public void testUnion() throws JSQLParserException {
	String statement =
		"SELECT * FROM mytable WHERE mytable.col = 9 UNION "
			+ "SELECT * FROM mytable3 WHERE mytable3.col = ? UNION "
			+ "SELECT * FROM mytable2 LIMIT 3,4";
	
	Union union = (Union) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
	assertEquals(3, union.getPlainSelects().size());
	assertEquals("mytable", ((Table) ((PlainSelect) union.getPlainSelects().get(0)).getFromItem()).getName());
	assertEquals("mytable3", ((Table) ((PlainSelect) union.getPlainSelects().get(1)).getFromItem()).getName());
	assertEquals("mytable2", ((Table) ((PlainSelect) union.getPlainSelects().get(2)).getFromItem()).getName());
	assertEquals(3, ((PlainSelect) union.getPlainSelects().get(2)).getLimit().getOffset());
	
	//use brakets for toString
	//use standard limit syntax
	String statementToString =
		"(SELECT * FROM mytable WHERE mytable.col = 9) UNION "
			+ "(SELECT * FROM mytable3 WHERE mytable3.col = ?) UNION "
			+ "(SELECT * FROM mytable2 LIMIT 4 OFFSET 3)";
	assertEquals(statementToString, ""+union);
}
 

import net.sf.jsqlparser.statement.select.Select; //导入依赖的package包/类
public void testExists() throws JSQLParserException {
	String statement = "SELECT * FROM tab1 WHERE";
	String where = " EXISTS (SELECT * FROM tab2)";
	statement += where;
	Statement parsed = parserManager.parse(new StringReader(statement));
	assertEquals(statement, ""+parsed);

	PlainSelect plainSelect =	(PlainSelect) ((Select) parsed).getSelectBody();
	ExpressionDeParser expressionDeParser = new ExpressionDeParser();
	StringBuffer stringBuffer = new StringBuffer();
	expressionDeParser.setBuffer(stringBuffer);
	SelectDeParser deParser = new SelectDeParser(expressionDeParser, stringBuffer);
	expressionDeParser.setSelectVisitor(deParser);
	plainSelect.getWhere().accept(expressionDeParser);
	assertEquals(where, stringBuffer.toString());

}
 

import net.sf.jsqlparser.statement.select.Select; //导入依赖的package包/类
public void testGetTableList() throws Exception {

		String sql =
			"SELECT * FROM MY_TABLE1, MY_TABLE2, (SELECT * FROM MY_TABLE3) LEFT OUTER JOIN MY_TABLE4 "
				+ " WHERE ID = (SELECT MAX(ID) FROM MY_TABLE5) AND ID2 IN (SELECT * FROM MY_TABLE6)";
		net.sf.jsqlparser.statement.Statement statement = pm.parse(new StringReader(sql));
		 
		//now you should use a class that implements StatementVisitor to decide what to do
		//based on the kind of the statement, that is SELECT or INSERT etc. but here we are only
		//interested in SELECTS
		
		if (statement instanceof Select) {
			Select selectStatement = (Select) statement;
			TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
			List tableList = tablesNamesFinder.getTableList(selectStatement);
			assertEquals(6, tableList.size());
			int i = 1;
			for (Iterator iter = tableList.iterator(); iter.hasNext(); i++) {
				String tableName = (String) iter.next();
				assertEquals("MY_TABLE" + i, tableName);
			}
		}

	}