How do I get the result from my query? (MySQL)

Question

I am ranking players, and I need to get the result into Java.

My code is

public static int getRank(UUID uuid) throws SQLException {
    checkConnection();
    PreparedStatement s = con.prepareStatement(
            "SET @rownum := 0; SELECT rank FROM ( SELECT @rownum := @rownum + 1 AS rank, wins, uuid FROM `SKYWARS` ORDER BY wins DESC ) as result WHERE uuid=?");
    s.setString(1, uuid.toString());

    ResultSet r = s.executeQuery();
    r.next();
    return r.getInt(1);
}

But s.execute() returns false which means there is no resultset. How do I get the resultset? In MySQL workbench it returns the values rank and wins in a grid.

SOLUTION: I added ?allowMultiQueries=true to the connection statement. This made my code work as is.

From your description, this sounds more like a Java problem. Adding a tag might help you. — UncleCarl, Jul 25 '18 at 20:39
I don't use Java, but many MySQL APIs don't allow you to execute multiple queries in one call. Put the `SET` statement in a separate call. — Barmar, Jul 25 '18 at 20:51
See https://stackoverflow.com/questions/18297689/show-sql-error-message for how to print the SQL error message. — Barmar, Jul 25 '18 at 21:19
@Barmar Well there is not really an error if I use the code as is. But if I don't check if s.execute() is false, it gives "SQLException: ResultSet is from UPDATE. No Data." — Mark Cockram, Jul 25 '18 at 21:21
Why are you calling both `s.execute()` and `s.executeQuery()`? You only need to execute the query once. — Barmar, Jul 25 '18 at 21:24
@Barmar that went wrong in me trying to fix this issue. But you can copy my answer and I'll select your answer as accepted — Mark Cockram, Jul 25 '18 at 21:25
See https://stackoverflow.com/questions/10797794/multiple-queries-executed-in-java-in-single-statement for how to get the results when you use multi-query. — Barmar, Jul 25 '18 at 21:26

score 0 · Answer 1 · answered Jul 25 '18 at 21:24

@barmar suggested a good solution:

    public static int getRank(UUID uuid) throws SQLException {
    checkConnection();
    PreparedStatement s1 = con.prepareStatement(
            "SET @rownum := 0;");
    s1.execute();
    PreparedStatement s = con.prepareStatement(
            " SELECT rank FROM ( SELECT @rownum := @rownum + 1 AS rank, wins, uuid FROM `SKYWARS` ORDER BY wins DESC ) as rank WHERE uuid=?");
    s.setString(1, uuid.toString());
    if (!s.execute()) {
        System.out.println(123);
    } else {
        ResultSet r = s.executeQuery();
        r.next();
        return r.getInt(1);
    }
    return 0;
}

The SET statement could be split off.

How do I get the result from my query? (MySQL)

1 Answers1