I'm currently trying to retrieve a list of appointments that occurs in the next 15 minutes by querying the User_ID. The query I'm using :

SELECT * FROM appointments WHERE User_ID = ? and Start BETWEEN NOW() AND ADDDATE(NOW(), INTERVAL 15 MINUTE);

How do I implement, that if it returns any appointments it will be true and if it returns 0 rows, it will be false?

I currently have written

public static boolean checkUpcomingAppointments(int userID) {
    try {
        String sqlCheck = "SELECT * FROM appointments WHERE User_ID = ? and Start BETWEEN NOW() AND ADDDATE(NOW(), INTERVAL 15 MINUTE);";
        PreparedStatement ps = JDBC.getConnection().prepareStatement(sqlCheck);
        ps.setInt(1, userID);
        ps.execute();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
    return true;
}

CodePudding user response：

Change "select " to "select count()" and use Prepared statement.executeQuery() instead of Prepared statement execute()

Then the return value will be a ResultSet, from which you can get the count.

String sqlCheck = "SELECT count(*) FROM appointments WHERE User_ID = ? and Start BETWEEN NOW() AND ADDDATE(NOW(), INTERVAL 15 MINUTE);";     
   
PreparedStatement ps = JDBC.getConnection().prepareStatement(sqlCheck);    

ps.setInt(1, userID);        

ResultSet rs = ps.executeQuery();    

rs.next();

return rs.getInt(1) > 0;

CodePudding user response：

In the Java Doc, you have this :

boolean execute() throws SQLException Executes the SQL statement in this PreparedStatement object, which may be any kind of SQL statement. Some prepared statements return multiple results; the execute method handles these complex statements as well as the simpler form of statements handled by the methods executeQuery and executeUpdate. The execute method returns a boolean to indicate the form of the first result. You must call either the method getResultSet or getUpdateCount to retrieve the result; you must call getMoreResults to move to any subsequent result(s).

Returns: true if the first result is a ResultSet object; false if the first result is an update count or there is no result

Here you your method could look like this :

public static boolean checkUpcomingAppointments(int userID) {
    boolean isResultPresent = false;
    try {
        String sqlCheck = "SELECT * FROM appointments WHERE User_ID = ? and Start BETWEEN NOW() AND ADDDATE(NOW(), INTERVAL 15 MINUTE);";
        PreparedStatement ps = JDBC.getConnection().prepareStatement(sqlCheck);
        ps.setInt(1, userID);
        isResultPresent = ps.execute();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
    return isResultPresent;
}

You can also use a SELECT COUNT(*) and the call the executeQuery(). This tutorial can help you.