Accessing database from JSP & Servlets

All of the examples below are written in the document base of the ROOT application using Eclipse. Build a working environment for your ROOT application in Eclipse with reference to Namecard web application 3rd Test.

Add Servlet API to BuildPath of ROOT application

In Eclipse, open the GetEmpServlet servlet, which you had previously practiced. You will encounter numerous compilation errors. The reason for the error is that you did not add the servlet API to the ROOT application's BuildPath. Select the Libraries tab as shown below and click the Add External JARs .. button to find and add the Servlet API in CATALINA_HOME/lib. Add Servlet API to BuildPath of ROOT application

First JSP example

Create getEmp1.jsp in the ROOT application's document base as shown below. (This JSP is a JSP version of the GetEmpServlet servlet)

getEmp1.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>First JSP Example</title>
</head>
<body>
<%
String DB_URL = "jdbc:oracle:thin:@127.0.0.1:1521:XE";
String DB_USER = "scott";
String DB_PASSWORD = "tiger";

try {
	Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
	e.printStackTrace();
}

Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;

String sql = "select * from emp";

try {
	con = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
	stmt = con.prepareStatement(sql);
	rs = stmt.executeQuery();

	while (rs.next()) {
		String empno = rs.getString(1);
		String ename = rs.getString(2);
		String job = rs.getString(3);
		String mgr = rs.getString(4);
		String hiredate = rs.getString(5);
		String sal = rs.getString(6);
		String comm = rs.getString(7);
		String depno = rs.getString(8);
		
		out.println( empno + " : " + ename + " : " + job + " : " + mgr + 
		" : " + hiredate + " : " + sal + " : " + comm + " : " + depno + "<br />" );
	}

} catch (SQLException e) {
	System.out.println("Error Source : getEmp1.jsp : SQLException");
	System.out.println("SQLState : " + e.getSQLState());
	System.out.println("Message : " + e.getMessage());
	System.out.println("Oracle Error Code : " + e.getErrorCode());
	System.out.println("sql : " + sql);
} finally {
	if (rs != null) {
		try {
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	if (stmt != null) {
		try {
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	if (con != null) {
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
%>
</body>
</html>

You do not need to rerun Tomcat because it is a JSP file. Visit http://localhost:8080/getEmp1.jsp and test it.

Second JSP example

Connection pooling

It takes a long time to acquire a connection object in JDBC. Connection pooling is the solution. Connection pooling is the idea of creating multiple connections in advance, storing them in collections like vectors, and popping them out whenever you need them.

The next exercise is to modify getEmp1.jsp to get a connection through the connection pool. We have already added connection pooling to the ROOT application in the servlet chapter lab. (See: Using Custom Connection Pool) Open getEmp1.jsp. Use the Save As... menu to create getEmp2.jsp and modify it as shown below.

getEmp2.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="net.java_school.db.dbpool.*" %>
<%@ page import="net.java_school.util.*" %>
<jsp:useBean id="dbmgr" class="net.java_school.db.dbpool.OracleConnectionManager" scope="application" />
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Second JSP example</title>
</head>
<body>
<%
Connection con = null;
Statement stmt = null;
ResultSet rs = null;

String sql = "select * from emp";

try {
	con = dbmgr.getConnection();
	stmt = con.createStatement();
	rs = stmt.executeQuery(sql);

	while (rs.next()) {
		String empno = rs.getString(1);
		String ename = rs.getString(2);
		String job = rs.getString(3);
		String mgr = rs.getString(4);
		String hiredate = rs.getString(5);
		String sal = rs.getString(6);
		String comm = rs.getString(7);
		String depno = rs.getString(8);
		
		out.println( empno + " : " + ename + " : " + job + " : " + mgr + 
		" : " + hiredate + " : " + sal + " : " + comm + " : " + depno + "<br />" );
	}

} catch (SQLException e) {
	System.out.println("Error Source : getEmp2.jsp : SQLException");
	System.out.println("SQLState : " + e.getSQLState());
	System.out.println("Message : " + e.getMessage());
	System.out.println("Oracle Error Code : " + e.getErrorCode());
	System.out.println("sql : " + sql);
} finally {
	if (rs != null) {
		try {
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	if (stmt != null) {
		try {
			stmt.close();
		} catch (SQLException e) {

			e.printStackTrace();
		}
	}
	if (con != null) {
		dbmgr.freeConnection(con);
	}
}
%>
</body>
</html>

The second JSP uses the jsp: useBean action to obtain the OracleConnectionManager object reference. The second JSP can obtain a connection by calling the getConnection() method of the OracleConnectionManager whenever a connection is needed. One thing to keep in mind when using this custom connection pool is that you must use the freeConnection() method of the OracleConnectionManager instead of using the close() method of Connection after using the connection. If you use the close() of Connection, this custom connection pool will not work.

Third JSP Example

Using custom log file

So far we have used System.out.println() to print a log message. If you install Tomcat with the installer on a Windows system, System.out.println() will print a message to the file starting with stdout_ in the CATALINA_HOME/logs directory. To provide a better environment for development and operation, log messages need to be output to the file that the programmer or operator wants. There is a lot of open source about logging. However, we need to understand the basic concepts of logging, so let's use a custom log file, Log.java. Open the getEmp2.jsp file. Use the Save As ... menu to create the getEmp3.jsp file and modify it as follows:

getEmp3.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="net.java_school.db.dbpool.*" %>
<%@ page import="net.java_school.util.*" %>
<jsp:useBean id="dbmgr" class="net.java_school.db.dbpool.OracleConnectionManager" scope="application" />
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Third JSP Example</title>
</head>
<body>
<%
Log log = new Log();
Connection con = null;
Statement stmt = null;
ResultSet rs = null;

String sql = "select * from emp";

try {
	con = dbmgr.getConnection();
	stmt = con.createStatement();
	rs = stmt.executeQuery(sql);

	while (rs.next()) {
		String empno = rs.getString(1);
		String ename = rs.getString(2);
		String job = rs.getString(3);
		String mgr = rs.getString(4);
		String hiredate = rs.getString(5);
		String sal = rs.getString(6);
		String comm = rs.getString(7);
		String depno = rs.getString(8);
		
		out.println( empno + " : " + ename + " : " + job + " : " + mgr + 
		" : " + hiredate + " : " + sal + " : " + comm + " : " + depno + "<br />" );
	}

} catch (SQLException e) {
	log.debug("Error Source:getEmp3.jsp : SQLException");
	log.debug("SQLState : " + e.getSQLState());
	log.debug("Message : " + e.getMessage());
	log.debug("Oracle Error Code : " + e.getErrorCode());
	log.debug("sql : " + sql);
} finally {
	if (rs != null) {
		try {
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	if (stmt != null) {
		try {
			stmt.close();
		} catch (SQLException e) {

			e.printStackTrace();
		}
	}
	if (con != null) {
		dbmgr.freeConnection(con);
	}
	log.close();
}
%>
</body>
</html>

The catch block must be executed to check the log message. In getemp3.jsp, modify the query to String sql = " select * fromemp ". Visit http://localhost:8080/getemp3.jsp. Check the log message in the file specified in Log.java.

Thu Jun 12 14:30:51 KST 2014 : Oracle Error Code : 923
Thu Jun 12 14:30:51 KST 2014 : sql : select * fromemp
Thu Jun 12 14:30:52 KST 2014 : Error Source:getEmp3.jsp : SQLException
Thu Jun 12 14:30:52 KST 2014 : SQLState : 42000
Thu Jun 12 14:30:52 KST 2014 : Message : ORA-00923: FROM keyword not found where expected

Fourth JSP example

The GetEmpServlet servlet loaded the JDBC driver in the init() method. In the lesson entitled "Servlet", you read that the init() method is called once by the servlet container after the servlet is instantiated. In JSP, there is a method corresponding to the init() of Servlet. if our JSP load the JDBC driver in sort of init() method, it will improve performance. The JSP method corresponding to the init() of Servlet can be found at: http://docs.oracle.com/javaee/7/api/javax/servlet/jsp/JspPage.html#jspInit()

Open getEmp3.jsp. Use the Save As .. menu to create getEmp4.jsp. Modify getEmp4.jsp so that the JSP method corresponding to the init() of the servlet loads the JDBC driver.

getEmp4.jsp is your challenge.