Karo darmoyan
Karo darmoyan

Reputation: 35

Set mySQL MAX value to java variable

Hey guys I'm trying to get the value from mySQL MAX function (trying to get highest customer ID) and store it in a variable in my java code. Can't seem to get the thing to work.

public static int findMaxID() {
    int maxID = 0;
    String updateStmt =
            "SELECT @maxID := MAX(idCustomer)\n" +
                    "FROM customers\n";
    try {
        DBUtil.dbExecuteQuery(updateStmt);
        System.out.println(maxID);
    } catch (SQLException e) {
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }
    return maxID;
}

Upvotes: 0

Views: 509

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

Use a statement, ideally a prepared statement:

int maxID = 0;
String sql = "SELECT MAX(idCustomer) AS max_id FROM customers";
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
    int maxID = rs.getInt("max_id");  // access the max value via its alias
}

While your current query might be valid MySQL, the session variable @maxID is only available on MySQL and not in your Java code. To access it you would need to yet again write another query.

Upvotes: 2

Related Questions