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!

please explain why you think that the output should be like "Required Output" — isaace
– isaace, Commented Dec 20, 2017 at 14:58
Because in the database there are two messages. One sent by Ali Ahmed and the other one by Hamza. The program is printing all the messages perfectly but it is not updating the sender name. — Zain Ul Abidin
– Zain Ul Abidin, Commented Dec 20, 2017 at 15:00

Guillaume Barré · Accepted Answer · 2017-12-20 15:13:54Z

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.

edited Dec 20, 2017 at 15:13

answered Dec 20, 2017 at 14:58

Guillaume Barré

4,2462 gold badges30 silver badges53 bronze badges

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

1 Comment

Zain Ul Abidin Over a year ago

Hi Nirekin, Thank you so much for your help! This method worked for me. Thanks again! :)

Collectives™ on Stack Overflow

Displaying data from two tables in java mysql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related