Now that we have figured out how to use the JDBC connection pool and get a connection from it, let's write the code to save a course to the database.

We will create Course Data Access Object (CourseDAO), which will have functions required to directly interact with the database. We are thus separating the code to access the database from the UI and business code.

Create package packt.book.jee.eclipse.ch4.dao. Create a class called CourseDAO in it:

package packt.book.jee.eclipse.ch4.dao; 
 
import java.sql.Connection; 
import java.sql.PreparedStatement; 
import java.sql.ResultSet; 
import java.sql.SQLException; 
import java.sql.Statement; 
 
import packt.book.jee.eclipse.ch4.bean.Course; 
import packt.book.jee.eclipse.ch4.db.connection.DatabaseConnectionFactory; 
 
public class CourseDAO { 
 
  public static void addCourse (Course course) throws SQLException 
   { 
    //get connection from connection pool 
    Connection con = 
 DatabaseConnectionFactory.getConnectionFactory().getConnection(); 
    try { 
      final String sql = "insert into Course (name, credits) 
       values (?,?)";      //create the prepared statement with an option to get auto- 
       generated keys      PreparedStatement stmt = con.prepareStatement(sql, 
       Statement.RETURN_GENERATED_KEYS); 
      //set parameters 
      stmt.setString(1, course.getName()); 
      stmt.setInt(2, course.getCredits()); 
 
      stmt.execute(); 
 
      //Get auto-generated keys 
      ResultSet rs = stmt.getGeneratedKeys(); 
 
      if (rs.next()) 
        course.setId(rs.getInt(1)); 
 
      rs.close(); 
      stmt.close(); 
    } 
    finally { 
      con.close(); 
    } 
  } 
} 

We have already seen how to insert a record using JDBC. The only new thing in the preceding code is to get the autogenerated ID. Recall that the id column in the Course table is autogenerated. This is the reason that we did not specify it in the insert SQL:

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

When we prepare a statement, we are telling the driver to get the autogenerated ID. After the row is inserted into the table, we get the autogenerated ID by calling the following:

ResultSet rs = stmt.getGeneratedKeys(); 

We have already created addCourse.jsp. Somehow addCourse.jsp needs to send the form data to CourseDAO in order to save the data in the database. addCourse.jsp already has access to the Course bean and saves the form data in it. So, it makes sense for the Course bean to interface between addCourse.jsp and CourseDAO. Let's modify the Course bean to add an instance of CourseDAO as a member variable and then create a function to add a course (instance of CourseDAO) to the database:

public class Course { 
.... 
 
  private CourseDAO courseDAO = new CourseDAO(); 
 
... 
 
  public void addCourse() throws SQLException { 
    courseDAO.addCourse(this); 
  } 
} 

We will then modify addCourse.jsp to call the addCourse method of the Course bean. We will have to add this code after the form is submitted and the data is validated:

<c:catch var="addCourseException"> 
  ${courseBean.addCourse()} 
</c:catch> 
<c:choose> 
  <c:when test="${addCourseException != null}"> 
    <c:set var="errMsg" value="${addCourseException.message}"/> 
  </c:when> 
  <c:otherwise> 
    <c:redirect url="listCourse.jsp"/> 
  </c:otherwise> 
</c:choose> 

One thing to note in the preceding code is the following statement:

${courseBean.addCourse()} 

You can insert Expression Language (EL) in JSP as discussed previously. This method does not return anything (it is a void method). Therefore, we didn't use the <c:set> tag. Furthermore, note that the call is made within the <c:catch> tag. If any SQLException is thrown from the method, then it will be assigned to the addCourseException variable. We then check whether addCourseException is set in the <c:when> tag. If the value is not null, then it means that the exception was thrown. We set the error message, which is later displayed on the same page. If no error is thrown, then the request is redirected to listCourse.jsp. Here is the complete code of addCourse.jsp:

<%@ 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>Insert title here</title> 
</head> 
<body> 
  <c:set var="errMsg" value="${null}"/> 
  <c:set var="displayForm" value="${true}"/> 
  <c:if 
 test="${"POST".equalsIgnoreCase(pageContext.request.method) 
&& pageContext.request.getParameter("submit") != null}"> 
  <jsp:useBean id="courseBean" 
   class="packt.book.jee.eclipse.ch4.bean.Course"> 
    <c:catch var="beanStorageException"> 
    <jsp:setProperty name="courseBean" property="*" /> 
    </c:catch> 
    </jsp:useBean> 
    <c:choose> 
      <c:when test="${!courseBean.isValidCourse() || 
       beanStorageException != null}">      <c:set var="errMsg" value="Invalid course details. Please 
       try again"/> 
      </c:when> 
      <c:otherwise> 
        <c:catch var="addCourseException"> 
        ${courseBean.addCourse()} 
        </c:catch> 
        <c:choose> 
          <c:when test="${addCourseException != null}"> 
          <c:set var="errMsg" 
           value="${addCourseException.message}"/> 
          </c:when> 
          <c:otherwise> 
            <c:redirect url="listCourse.jsp"/> 
          </c:otherwise> 
        </c:choose> 
      </c:otherwise> 
    </c:choose> 
  </c:if> 
 
  <h2>Add Course:</h2> 
  <c:if test="${errMsg != null}"> 
    <span style="color: red;"> 
      <c:out value="${errMsg}"></c:out> 
    </span> 
  </c:if> 
  <form method="post"> 
    Name: <input type="text" name="name"> <br> 
    Credits : <input type="text" name="credits"> <br> 
    <button type="submit" name="submit">Add</button> 
  </form> 
 
</body> 
</html> 

Run the page, either in Eclipse or outside (see Chapter 2, Creating a Simple JEE Web Application, to know how to run JSP in Eclipse and view it in Eclipse's internal browser) and add a couple of courses.