Comparing sql dates gone wrong in java

Question

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:

sender: varchar
receiver: varchar
message: varchar
senttime: datetime

Yes. I have now updated the columns and the datatypes as well. — thehollow
– thehollow, Commented May 8, 2020 at 5:22

Jitesh Shivnani · Accepted Answer · 2020-05-08 06:12:43Z

2

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.

edited May 8, 2020 at 6:12

answered May 8, 2020 at 6:03

Jitesh Shivnani

3511 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

thehollow Over a year ago

So probably I should add a new column namely isSeen after the senttime?

Jitesh Shivnani Over a year ago

yes otherwise condition senttime < DATE(NOW()) is valid forever even when the user has seen the message.

thehollow Over a year ago

Actually, can you help me identify how the message is not printing please? I did follow your way and get the count of messages correctly but it is only displaying the last message.

Jitesh Shivnani Over a year ago

probably you are not concatenating the msgs right. If you can add the updated code then I will be able to point out the mistake.

thehollow Over a year ago

I actually got it! Thanks again for your kind help!

Collectives™ on Stack Overflow

Comparing sql dates gone wrong in java

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related