CODEWITHSUNDEEP
javasqljdbc
thehollow
thehollow

Reputation: 17

Comparing sql dates gone wrong in java

I have a messaging feature in my code. It goes like this: a user, let's say user A, sends user B a message on Monday. We have two cases (user B is online/offline), but in either case, the code will write to the database anyway. Basically, the message is transferred from user A to the database (get timestamped (1)) then bounces to the user B. Now user B logins the next day, Tuesday. The code will then compare the timestamp (2) at the moment user B successfully logins, with the timestamp (1) when user A sends it.

I intend to do: 1) if the timestamp (1) is smaller than or equal to the timestamp (2) of user B's login (Monday vs Tuesday as in the example above) then display "You have n messages" along with the messages themselves, or 2) if the timestamp (1) is greater than the timestamp (2) of user B's login (Tuesday vs Monday), then clearly B must have read those messages - so we ignore it and only checks if he has any newer ones and display them for him.

To do so, I have crafted this code but it does not work. It does not return anything even if there are new messages. What I am doing wrong? I do suspect that my date comparison is wrong. The column of the timestamp (1) - when the message gets sent, is in DATETIME.

public static String receiveMessage(String email) throws Exception {
    String returnStatement = "";
    Connection connection = null;
    connection = establishConnection();
    String queryCheck = "SELECT * from usersmessages WHERE receiver = ?";
    PreparedStatement prepstate = connection.prepareStatement(queryCheck);
    prepstate.setString(1, email);
    ResultSet resultSet = prepstate.executeQuery();
    while(resultSet.next()) {
        //fine
        String queryCheck_1 = "SELECT * from usersmessages WHERE senttime < DATE(NOW())";
        PreparedStatement prepstate_1 = connection.prepareStatement(queryCheck_1);
        ResultSet resultSet_1 = prepstate_1.executeQuery();

        /* This means the message time is older than the login time */
        if(resultSet_1.next()) {
            System.out.println("check");
            String queryCheck_2 = "SELECT COUNT(*) from usersmessages WHERE senttime < DATE(NOW())";
            PreparedStatement prepstate_2 = connection.prepareStatement(queryCheck_1);
            ResultSet resultSet_2 = prepstate_1.executeQuery();
            int messagecount = resultSet_2.getInt(1);
            returnStatement = "You have " + messagecount + "new messages.";
            for(int i = 0; i < messagecount; i++) {
                returnStatement += "Message " + i + ":\n" + resultSet_2.getString(3);
            }
        }
    }
    return returnStatement;
}

EDIT: I have 4 columns in the DB. Here they are along with the types:

Upvotes: 0

Views: 50

Answers (1)

Jitesh Shivnani
Jitesh Shivnani

Reputation: 351

You are finding the messages for a receiver, then iterating the result where you are scanning for all messages again (irrespective of the receiver).

You could change the first to query to following:-

String queryCheck = "SELECT * from usersmessages WHERE receiver = ? and senttime < DATE(NOW()) and isSeen=false";

Then iterate over the result to count new messages and display. Also you will need to mark the message isSeen flag as true because otherwise the messages will come up again when user logs in next time.

Upvotes: 2

Related Questions