We still haven't completed the functionality for adding a new course; we need to provide an option to assign a teacher to a course when adding a new course. Assuming that you have implemented TeacherDAO and created addTeacher and getTeachers methods in the Teacher bean, we can now complete the add course functionality.

First, modify addCourse in CourseADO to save the teacher ID for each course, if it is not zero. The SQL statement to insert course changes is as follows:

String sql = "insert into Course (name, credits, Teacher_id) values (?,?,?)"; 

We have added the Teacher_id column and the corresponding parameter holder ?. We will set Teacher_id to null if it is zero; or else the actual value:

if (course.getTeacherId() == 0) 
  stmt.setNull(3, Types.INTEGER); 
else 
  stmt.setInt(3,course.getTeacherId()); 

We will then modify the Course bean to save the teacher ID that will be passed along with the POST request from the HTML form:

public class Course { 
 
  private int teacherId; 
  public int getTeacherId() { 
    return teacherId; 
} 
  public void setTeacherId(int teacherId) { 
    this.teacherId = teacherId; 
  } 
} 

Next, we will modify addCourse.jsp to display the drop-down list of teachers when adding a new course. We first need to get the list of teachers. Therefore, we will create a Teacher bean and call the getTeachers method on it. We will do this just before the Add Course header:

<jsp:useBean id="teacherBean" class="packt.book.jee.eclipse.ch4.bean.Teacher"/> 
<c:catch var="teacherBeanErr"> 
<c:set var="teachers" value="${teacherBean.getTeachers()}"/> 
</c:catch> 
<c:if test="${teacherBeanErr != null}"> 
  <c:set var="errMsg" value="${err.message}"/> 
</c:if> 

Finally, we will display the HTML drop-down list in the form and populate it with teacher names:

Teacher : 
<select name="teacherId"> 
<c:forEach items="${teachers}" var="teacher"> 
<option value="${teacher.id}">${teacher.firstName} 
</option> 
</c:forEach> 
</select> 

Download the accompanying code for this chapter to see the complete source code of CourseDAO and addCourse.jsp.

With this, we conclude our discussion on using JDBC to create a web application that uses a database. With the examples that you have seen so far, you should be in a good position to complete the remaining application by adding functionality to modify and delete records in the database. The update and delete SQL statements can be executed by Statement or PreparedStatement, just as insert statements are executed using these two classes.