java - jsp display random amount of rows from database

Question

I'm making a web-application with JSP and Servlets and I came accross this problem: I have a table users and a myeducations table. These table relationship is established on user_id.

This is the table:

myeducation

----------------------------------------------------------------------------------
| ed_id | user_id | school_name | year_attended_from | year_attended_to | degree |
----------------------------------------------------------------------------------

Now, I want to display on a website (jsp) all of the user educations, but the number of educations user has is random: 1 user could have finished just 1 school, other could 3 and so on...

I understand how to retrieve just 1 row, the SQL is:

SELECT school_name 
FROM myeducation
INNER JOIN users ON users.user_id = myeducation.user_id;

But how to make this statement run in a loop, so that I get all the educations of a user, say if he has more than just 1. Then put them all in a session variable and display in a jsp's div.

Answer 1

You achieve this using joins or nested Query

As per your current DB Design, you can achieve this by a simple query itself, Since Education table holds the USER_ID

SELECT school_name FROM myeducation myedu where user_id="SOME_ID_HERE"

For looping String Array in servlet, simply do like this

ArrayList<String> arr = new ArrayList<String>();
while (rs.next()) {
  arr.add(rs.getString("school_name "));
}