コード例 #1
0
  public void testLimit() throws JSQLParserException {
    String statement = "SELECT * FROM mytable WHERE mytable.col = 9 LIMIT 3, ?";

    Select select = (Select) parserManager.parse(new StringReader(statement));

    assertEquals(3, ((PlainSelect) select.getSelectBody()).getLimit().getOffset());
    assertTrue(((PlainSelect) select.getSelectBody()).getLimit().isRowCountJdbcParameter());
    assertFalse(((PlainSelect) select.getSelectBody()).getLimit().isOffsetJdbcParameter());
    assertFalse(((PlainSelect) select.getSelectBody()).getLimit().isLimitAll());

    // toString uses standard syntax
    statement = "SELECT * FROM mytable WHERE mytable.col = 9 LIMIT ? OFFSET 3";
    assertEquals(statement, "" + select);

    statement = "SELECT * FROM mytable WHERE mytable.col = 9 OFFSET ?";
    select = (Select) parserManager.parse(new StringReader(statement));

    assertEquals(0, ((PlainSelect) select.getSelectBody()).getLimit().getRowCount());
    assertTrue(((PlainSelect) select.getSelectBody()).getLimit().isOffsetJdbcParameter());
    assertFalse(((PlainSelect) select.getSelectBody()).getLimit().isLimitAll());
    assertEquals(statement, "" + select);

    statement =
        "(SELECT * FROM mytable WHERE mytable.col = 9 OFFSET ?) UNION "
            + "(SELECT * FROM mytable2 WHERE mytable2.col = 9 OFFSET ?) LIMIT 3, 4";
    select = (Select) parserManager.parse(new StringReader(statement));
    Union union = (Union) select.getSelectBody();
    assertEquals(3, union.getLimit().getOffset());
    assertEquals(4, union.getLimit().getRowCount());

    // toString uses standard syntax
    statement =
        "(SELECT * FROM mytable WHERE mytable.col = 9 OFFSET ?) UNION "
            + "(SELECT * FROM mytable2 WHERE mytable2.col = 9 OFFSET ?) LIMIT 4 OFFSET 3";
    assertEquals(statement, "" + select);

    statement =
        "(SELECT * FROM mytable WHERE mytable.col = 9 OFFSET ?) UNION ALL "
            + "(SELECT * FROM mytable2 WHERE mytable2.col = 9 OFFSET ?) UNION ALL "
            + "(SELECT * FROM mytable3 WHERE mytable4.col = 9 OFFSET ?) LIMIT 4 OFFSET 3";
    select = (Select) parserManager.parse(new StringReader(statement));
    assertEquals(statement, "" + select);
  }
コード例 #2
0
  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);
  }