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

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

How do I format 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;
    }
    

Any help would be great. I would like to call the "checkUpcomingAppointments" method and if it returns anything other than 0 rows, it would return true and false for 0. Hopefully I'm phrasing this correctly.

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.