H2 DB only support 1 resultset?

Question

I'm trying return two tables from one query using the H2 database. However, I only get one table as result

Here is my code:

try {
    Connection connection = DriverManager.getConnection(h2_conn);
    Statement statement = connection.createStatement();

    // Execute a SQL query that returns multiple result sets
    String sql = "select * from `test1234` limit 1;" +
            "select * from `test1234` limit 2;";
    boolean hasMoreResults = statement.execute(sql);

    // Process each result set
    int resultSetNumber = 1;
    do {
        ResultSet resultSet = statement.getResultSet();
        while (resultSet.next()) {
            // Process the current result set
            System.out.println("Result Set " + resultSetNumber);
            System.out.println(resultSet.getString(1));
            System.out.println(resultSet.getString(2));
        }

        resultSetNumber++;

        hasMoreResults = statement.getMoreResults();
    } while (hasMoreResults);

    statement.close();
    connection.close();
} catch (Exception e) {
    e.printStackTrace();
}

Does this mean it is impossible to get two table results in a single query?

Regarding the document, http://www.h2database.com/javadoc/org/h2/jdbc/JdbcStatement.html#getMoreResults--.

Moves to the next result set - however there is always only one result set. This call also closes the current result set (if there is one). Returns true if there is a next result set (that means - it always returns false).

Answer 1

According to the documentation you already included, yes, you are correct. You cannot do two queries in one statement like that.

Fortunately, the solution is simple: Just split it up into two statements, and you'll be fine.