We will now modify listCourses.jsp to display the courses that we have added using addCourse.jsp. However, we first need to add a method in CourseDAO to get all courses from the database.

Note that the Course table has a one-to-many relationship with Teacher. It stores the teacher ID in it. Further, the teacher ID is not a required field, so a course can exist in the Course table with null teacher_id. To get all the details of a course, we need to get the teacher for the course too. However, we cannot create a simple join in an SQL query to get the details of a course and of the teacher for each course, because a teacher may not have been set for the course. In such cases, we use the left outer join, which returns all records from the table on the left-hand side of the join, but only matching records from the table on the right-hand side of the join. Here is the SQL statement to get all courses and teachers for each course:

select course.id as courseId, course.name as courseName, 
  course.credits as credits, Teacher.id as teacherId, 
  Teacher.first_name as firstName,Teacher.last_name as lastName, 
  Teacher.designation designation 
from Course left outer join Teacher on 
course.Teacher_id = Teacher.id 
order by course.name 

We will use the preceding query in CourseDAO to get all courses. Open the CourseDAO class and add the following method:

public List<Course> getCourses () throws SQLException { 
  //get connection from connection pool 
  Connection con = 
 DatabaseConnectionFactory.getConnectionFactory().getConnection(); 
 
  List<Course> courses = new ArrayList<Course>(); 
  Statement stmt = null; 
  ResultSet rs = null; 
  try { 
    stmt = con.createStatement(); 
 
    //create SQL statement using left outer join 
    StringBuilder sb = new StringBuilder("select course.id as 
     courseId, course.name as courseName,")      .append("course.credits as credits, Teacher.id as teacherId, 
       Teacher.first_name as firstName, ")      .append("Teacher.last_name as lastName, Teacher.designation 
       designation ") 
      .append("from Course left outer join Teacher on ") 
      .append("course.Teacher_id = Teacher.id ") 
      .append("order by course.name"); 
 
//execute the query 
    rs = stmt.executeQuery(sb.toString()); 
 
//iterate over result set and create Course objects 
//add them to course list 
    while (rs.next()) { 
      Course course = new Course(); 
      course.setId(rs.getInt("courseId")); 
      course.setName(rs.getString("courseName")); 
      course.setCredits(rs.getInt("credits")); 
      courses.add(course); 
 
      int teacherId = rs.getInt("teacherId"); 
//check whether teacher id was null in the table 
      if (rs.wasNull()) //no teacher set for this course. 
        continue; 
      Teacher teacher = new Teacher(); 
      teacher.setId(teacherId); 
      teacher.setFirstName(rs.getString("firstName")); 
      teacher.setLastName(rs.getString("lastName")); 
      teacher.setDesignation(rs.getString("designation")); 
      course.setTeacher(teacher); 
    } 
 
    return courses; 
  } 
finally { 
  try {if (rs != null) rs.close();} catch (SQLException e) {} 
  try {if (stmt != null) stmt.close();} catch (SQLException e) {} 
  try {con.close();} catch (SQLException e) {} 
  } 
} 

We have used Statement to execute the query because it is a static query. We have used StringBuilder to build the SQL statement because it is a relatively large query (compared to those that we have written so far) and we would like to avoid concatenation of string objects, because Strings are immutable. After executing the query, we iterate over the resultset and create a Course object and add it to the list of courses, which is returned at the end.

One interesting thing here is the use of ResultSet.wasNull. We want to check whether the teacher_id field in the Course table for that particular row was null. Therefore, immediately after calling rs.getInt("teacherId"), we check whether the value fetched by ResultSet was null by calling rs.wasNull. If teacher_id was null, then the teacher was not set for that course, so we continue the loop, skipping the code to create a Teacher object.

In the final block, we catch an exception when closing ResultSet, Statement, and Connection and ignore it.

Let's now add a method in the Course bean to fetch courses by calling the getCourses method of CourseDAO. Open the Course bean and add the following method:

public List<Course> getCourses() throws SQLException { 
  return courseDAO.getCourses(); 
} 

We are now ready to modify listCourse.jsp to display courses. Open the JSP and replace the existing code with the following:

<%@ page language="java" contentType="text/html; charset=UTF-8" 
    pageEncoding="UTF-8"%> 
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> 
 
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> 
<html> 
<head> 
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> 
<title>Courses</title> 
</head> 
<body> 
  <c:catch var="err"> 
    <jsp:useBean id="courseBean" 
     class="packt.book.jee.eclipse.ch4.bean.Course"/> 
    <c:set var="courses" value="${courseBean.getCourses()}"/> 
  </c:catch> 
  <c:choose> 
    <c:when test="${err != null}"> 
      <c:set var="errMsg" value="${err.message}"/> 
    </c:when> 
    <c:otherwise> 
    </c:otherwise> 
  </c:choose> 
  <h2>Courses:</h2> 
  <c:if test="${errMsg != null}"> 
    <span style="color: red;"> 
      <c:out value="${errMsg}"></c:out> 
    </span> 
  </c:if> 
  <table> 
    <tr> 
      <th>Id</th> 
      <th>Name</th> 
      <th>Credits</th> 
      <th>Teacher</th> 
    </tr> 
    <c:forEach items="${courses}" var="course"> 
      <tr> 
        <td>${course.id}</td> 
        <td>${course.name}</td> 
        <td>${course.credits}</td> 
        <c:choose> 
          <c:when test="${course.teacher != null}"> 
            <td>${course.teacher.firstName}</td> 
          </c:when> 
          <c:otherwise> 
            <td></td> 
          </c:otherwise> 
        </c:choose> 
      </tr> 
    </c:forEach> 
  </table> 
</body> 
</html> 

Most of the code should be easy to understand because we have used similar code in previous examples. At the beginning of the script, we create a Course bean and get all the courses and assign the course list to a variable called courses:

<c:catch var="err"> 
    <jsp:useBean id="courseBean" 
     class="packt.book.jee.eclipse.ch4.bean.Course"/> 
    <c:set var="courses" value="${courseBean.getCourses()}"/> 
</c:catch> 

To display courses, we create a HTML table and set its headers. A new thing in the preceding code is the use of the <c:forEach> JSTL tag to iterate over the list. The forEach tag takes the following two attributes:

• List of objects
• Variable name of a single item when iterating over the list

In the preceding case, the list of objects is provided by the courses variable that we set at the beginning of the script and we identify a single item in the list with the variable name course. We then display the course details and teacher for the course, if any.

Writing code to add Teacher and Student and list them is left to readers as an exercise. The code would be very similar to that for course, but with different table and class names.