Fill a pojo that contains an arraylist of another pojo with a single SQL sentence and properly iterate resultset

Question

in my project I have this POJO class:

CourseBean.java:

@Expose
private Integer id;
@Expose
private String title = "";
@Expose
private String description = "";
@Expose
ArrayList<SubjectBean> alSubjects = new ArrayList();

It contains the columns of the Course table and an arraylist that would have anothers pojos representing the subjects of this course.

To fill this pojo the procedure that Im using is the following:

1. I make two SQL sentences: The first one to get the course by ID and the second one to get all the subjects filtered by the same course ID.
2. Now to fill the pjo I have a fill method to iterate the first resulset and inside iterate the second one

Is possible to optimize that procedure? maybe with a single SQL sentence?.

----------------------------------EDIT------------------------------------

I will use the statement that @Patrick Piatkowski sugested

SELECT * FROM Course
JOIN Subject
ON Course.id = Subject.course_id
WHERE /* Your filters here */

But now this leads me to another question. When I iterate the resulset, the columns of the course are the same in every row.

this.setId(oResultSetCourse.getInt("id")); this.setTitle(oResultSetCourse.getString("title")); this.setDescription(oResultSetCourse.getString("description"));

Those should be filled only once meanwhile the pojo of the subjects should be filled every iteration

What is the best method to handle this?

Answer 1

You could use a join:

SELECT * FROM Course
JOIN Subject
ON Course.id = Subject.course_id
WHERE /* Your filters here */

Or alternatively

SELECT * FROM Subject
WHERE course_id IN 
    (SELECT course_id FROM Course
    WHERE /* Your filters here */)

The advantage of the second approach is that you only get columns from Subject while you also get the columns from Course by using the first statement.