Displaying data from two tables in java mysql

Question

i want to display messages with the sender name. The outer while loop is working fine but there is some problem in the inner while loop. I have tried a lot to figure out but got no result. Can anyone help me? I'll very thankful.

package db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

public class Messages extends Login {
    static Scanner in = new Scanner(System.in);

public static void main(String args[]) throws Exception{
    boolean isLoggedin = login();

    String msg = "";
    String senName = "";

    if(isLoggedin) {
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo?verifyServerCertificate=false&useSSL=false", "root", "");
        Statement st = con.createStatement();
        Statement st1 = con.createStatement();
        ResultSet rs = st.executeQuery("SELECT * FROM chat");
        ResultSet rs1 = st1.executeQuery("SELECT * FROM data");
        while(rs.next()) {
            int dbRecID = rs.getInt("reciever_id");
            if(dbRecID == user_id) {
                msg = rs.getString("message");
                int dbSenID = rs.getInt("sender_id");
                while(rs1.next()) {
                    int senID = rs1.getInt("id");
                    if(senID == dbSenID) {
                        senName = rs1.getString("name");
                    }
                }
                System.out.println(senName+" sent you a message: "+msg);
            }
        }

    }
    else {
        System.out.print("Login Unsuccessful");
    }
}
}

Output

Ali Ahmed sent you a message: How are you?
Ali Ahmed sent you a message: Hi!

Required Output

Ali Ahmed sent you a message: How are you?
Hamza sent you a message: Hi!

Answer 1

The issue comes from the two nested while loops.

The query ResultSet rs1 = st1.executeQuery("SELECT * FROM data"); is executed just once.

The first time you loop on while(rs.next()) you will fetch the whole content of rs1 to check if the sender id is the right one.

Then on the second iteration on while(rs.next()) because rs1 has been already fetched while(rs1.next()) wil return false.

In order to have your code working you should move the execution of the second query to get something like this:

while(rs.next()) {
    ...
    ResultSet rs1 = st1.executeQuery("SELECT * FROM data");
    while(rs1.next()) {
        ...
    }
    ...
}

But I think that it would be a better solution to make just one SQL request joining the datas of the two tables and including the where condition.