Simple Registration Application using JSP, Servlets and JDBC connectivity to MySQL

Article Index

1 1 1 1 1 1 1 1 1 1 Rating 3.64 (14 Votes)

Requirement

  • Create a Registration Form to enter user name, password, first name, last name, email address, secret question, answer and register a new user to system.
  • Validate if the user name and password is not blank. Display error message to user, if either of them is blank.
  • Validate if the user name already exists and display an error message to the user
  • In the event of an error, ensure that registration form is displayed again with all prefilled information
  • On successful registration, set the user name in session and forward to home page.

Pre-requisites

  • MySQL and Tomcat is already installed
  • You have basic knowledge of MySQL to create tables and insert / update records
  • You have basic knowledge of Java and J2EE web applications
  • Have your favorite IDE for developing / reviewing code
  • This workshop adds the registration functionality to the previous article Simple Login Application. Reviewing that article prior to this article would help.

 

Concepts Covered

  • JDBC connectivity to MySQL from Servlet
  • Simple application of JSTL tag
  • Using request attribute and request parameters
  • Basic error handling concepts

NOTE: Don’t even think of using the code as is for a production environment Tongue Out
This is just for education purpose but you can take inspiration from this code.


Analysis and Design

We start with the Simple Login application article as a baseline. First, we need to update the database to add more fields and constraints. We will make the user_name field as the primary key for the USERS table.

We create a new class for database connectivity code unlike in the simple login application and refactor the connectivity code out of the Servlet code. This is a good design practice to keep the servlet layer uncoupled to the database code layer. We would still following the MVC design model. 

Below is a list of elements identified as part of the design:

  • com.techfreaks.db.util.ConnectionUtil - Utility class for getting connection, closing connection and executing insert / update query
  • com.techfreaks.registration.servlet.RegistrationServlet - This servlet is the controller. It calls the ConnectionUtil and forwards to the home page or Registration page
  • RegistrationForm.jsp - UI page with fields for registration form
  • Home.jsp - UI page picked up after successful registration. It is the same page taken from Login Application
  • web.xml - Deployment Descriptor in which the servlet is registered.

 

Development


Setup the database:

We alter the USERS table to add the new fields. However, for quick reference we provide the create USERS table DDL. You will have to drop and recreate the table using the below DML, if you already have an USER table. You could very well manually edit the existing table based on the below DDL.

CREATE TABLE `users` (
	`first_name` VARCHAR(100) NOT NULL,
	`last_name` VARCHAR(100) NOT NULL,
	`email_address` VARCHAR(50) NOT NULL,
	`secret_question` VARCHAR(200) NULL DEFAULT NULL,
	`secret_answer` VARCHAR(200) NULL DEFAULT NULL,
	`user_name` VARCHAR(100) NOT NULL,
	`password` VARCHAR(100) NOT NULL,
	PRIMARY KEY (`user_name`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;

A screenshot of the development environment is provided below. Make sure the mysql drivers, jstl libraries and tlds are all available. For making your life easy, we have included a hot deployable WAR file at the end of the workshop.

Simple Registration App Environment Folder structure



Coding and Unit testing:

First, we would look at the ConnectionUtil. Below are the methods we would need for the ConnectionUtil. Note that we create this class as a static class (A class with static methods). 

- getConnection()
- closeConnection()
- executeQuery(String strQuery)

If you have read the article about simple login application, you would have already noticed thatt we just refactored the first two methods out of the LoginServlet. We prevent the code to be spread across multiple servlets this way. It is used to get a SQL Connection object and to close an open connection respectively.

In this example, we would use java.sql.Statement instead of java.sql.PrepareStatement which we used in LoginServlet, to spice things up. We need to pass the complete query to the Statement object to execute a query. The executeQuery can take an insert / update query and execute it. It would internally call getConnection() and closeConnection() to fetch and close a connection.

Below is the method executeQuery:

	public static void executeQuery(String strQuery) throws Exception {
		Connection conn = null;
		
		try {
			conn = getConnection();
			Statement stmt  = conn.createStatement();
			stmt.executeUpdate(strQuery);
			
		} catch (SQLException sqle) {
			System.out.println("SQLException: Unable to execute query : "+strQuery);
			throw sqle;
		} catch (Exception e) {
			System.out.println("Exception: Unable to execute query: "+strQuery);
			throw e;
		} finally {
			closeConnection(conn);
		}
	}

We also moved the static variables like database name, db user name and db password from the servlet to the ConnectionUtil.

	private static final String DBNAME = "tf_loginappdb";
	private static final String DB_USERNAME = "root";
	private static final String DB_PASSWORD = "admin*";

If your database has different dbname, username or password, you will need to update this and recompile you java file for the application to be able to connect to MySQL database.


Next, we would look at the code in the RegistrationServlet. It gets all the fields from the RegistrationForm in the request parameters. Below are the methods which we would add to this servlet.

- doPost()
- validateData()
- setRequestAttributes()
- generateInsertQuery()

The doPost is the entry point to the servlet for html form POST. We have all the controller logic in here. The other methods are in a way, do not do the controller work. We use them for simple tasks which do not warranty creating other classes in our case. However, in a real life application, if the task is complex we might want to create new classes to delegate the task.

validateData() is used to implement the requirement that the userName and password cannot be empty. It validates and returns a boolean indicating success or failure of the validation.

generateInsertQuery() fetches the request parameters and generates the insert query which will be passed on to the executeQuery method of the ConnectionUtil. Below is the code for the method:

	private String generateInsertQuery(HttpServletRequest request) {
		String strUserName = request.getParameter("userName");
		String strPassword = request.getParameter("password");
		String strFirstName = request.getParameter("firstName");
		String strLastName = request.getParameter("lastName");
		String strEmail = request.getParameter("email");
		String strSecretQuestion = request.getParameter("secretQuestion");
		String strSecretAnswer = request.getParameter("secretAnswer");

		StringBuffer strQuery = new StringBuffer(INSERT_QUERY_START);
		strQuery.append(strFirstName);
		strQuery.append("', '");
		strQuery.append(strLastName);
		strQuery.append("', '");
		strQuery.append(strEmail);
		strQuery.append("', '");
		strQuery.append(strSecretQuestion);
		strQuery.append("', '");
		strQuery.append(strSecretAnswer);
		strQuery.append("', '");
		strQuery.append(strUserName);
		strQuery.append("', '");
		strQuery.append(strPassword);
		strQuery.append("')");
		
		System.out.println("Insert query : "+strQuery.toString());
		
		return strQuery.toString();

	}



setRequestAttributes() is used to implement the requirement about maintaining the data entered in the registration form in the even of an error. Do you remember banging your head against the keyboard after filling up a long registration form and clicking submit, only to find the form reloading with an error message and all the data you entered spending hours, boom! gone!Yell

Instead of the classic get each parameter and set to attribute, we would just do a generic loop through. This would keep the code small. Imagine your registration form is 100 fields instead, the below code will not require any modification. Below is the code:

	private void setRequestAttributes(HttpServletRequest request) {
		Enumeration  enumKeys =  request.getParameterNames();
		while(enumKeys.hasMoreElements()) {
			String key  = enumKeys.nextElement();
			request.setAttribute(key, request.getParameter(key))  ;
		}
	}

The doPost() method is provided below. It has inline comment to explain key control logic. Once the insert in the database is successful, we set the userName is session and redirect to Home.jsp.

The error messages are managed by putting a request attribute on error situation and forwarding back to RegistrationForm. You will see the difference between the redirect and forward. You will see that the request attributes are available and also the URL of the servlet remains the same after the request is forwarded to the JSP. Also, note how we utilize the primary key duplicate error message and set the error message. We use the same pattern to set error message, set the request attributes and forward to registration page on all the error conditions.

	protected void doPost(HttpServletRequest request, HttpServletResponse 
			response) throws ServletException, IOException {
		
		String strUserMsg = null;
		HttpSession session = request.getSession();
		RequestDispatcher reqDisp =  request.getRequestDispatcher(REGISTRATION_PAGE);
		
		try {
			//Check if data is valid
			if(validateData(request)) {
				ConnectionUtil.executeQuery(generateInsertQuery(request));
				System.out.println("Insert into database successful");
				session.setAttribute("userName", request.getParameter("userName"));
				response.sendRedirect(getServletContext().getContextPath()+HOME_PAGE);
			} else {//If data is invalid
				strUserMsg = "User Name and Password cannot be empty";
				setRequestAttributes(request);
				request.setAttribute("userMsg", strUserMsg);
				reqDisp.forward(request, response);
			}
			
		} catch(SQLException sqle ) {
			System.out.println("Unable to register user: "+sqle.getMessage());
			//Check if we are getting duplicate key exception on userName
			if(sqle.getMessage().indexOf("Duplicate entry")!=-1) {
				System.out.println("User already exists");
				strUserMsg = "User name "+request.getParameter("userName")+" already " +
						"exists. Please try another user name.";
			} else { //If other SQLException than dup key exception
				strUserMsg = "Unable to register user "+request.getParameter("userName")+
				". Please try again later.";
			}
			setRequestAttributes(request);
			request.setAttribute("userMsg", strUserMsg);
			reqDisp.forward(request, response);

		} catch(Exception e) {//If it goes into Exception other than SQLException
			System.out.println("Unable to register user: "+e.getMessage());
			strUserMsg = "Unable to register user "+request.getParameter("userName")
			+". Please try again later.";
			setRequestAttributes(request);
			request.setAttribute("userMsg", strUserMsg);
			reqDisp.forward(request, response);

		}
		
		

	}


Next, let us look at our RegistrationForm.jsp. We highlight small snippet of code here

<p><font color="#ff0000"><c:out value="${userMsg}"/></font></p>
<form name="frmRegistration" method="post" action="<c:out value="${pageContext.servletContext.contextPath}" />/servlet/RegistrationServlet">
<table border="1">
<tbody>
<tr>
<td>First Name</td>
<td><input type="text" name="firstName" value ="<c:out value="${firstName}"/>" size="20"></td>

 

  • JSTL is used display the userMsg which we set in the request attribute in our servlet.
  • JSTL is used to pick the contextPath from servletContext
  • JSTL is used to display user entered data in the event of an error

We picked up the Home.jsp from the simple login application. You can get the complete WAR file to the simple registration application by clicking SimpleRegistrationApp.war.

You just need to hot deploy SimpleRegistrationApp.war to webapps folder in the Tomcat program file and it would explode. Verify the application by accessing http://localhost:8080/SimpleRegistrationApp/RegistrationForm.jsp and run some tests to figure out how it works for you.

Feel free to leave a comment below, if you run into any issues. Enjoy!


Joomla SEO by MijoSEF