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

1 1 1 1 1 1 1 1 1 1 Rating 3.67 (54 Votes)


•    Create a Login page to enter user name and password
•    On submit, validate the user name / password against MySQL database
•    If the authentication is successful, forward to home page showing welcome message along with the user name.
•    If the authentication fails, return back to the login page with appropriate error message.
•    If there is exception / errors during authentication process return back to login page with appropriate error message.

•    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.

Concepts Covered
•    JDBC connectivity to MySQL from Servlet
•    Simple application of JSTL tag
•    Using session variable in Servlet
•    Polymorphism concept using JDBC
•    Basic error handling concepts

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

Checkout our similar article on simple login application using Struts 2 Framework

Analysis and Design

The application is pretty straightforward and it is not too hard to design. Looking through the requirement, we need a database instance in MySQL which has a table. We would name this table as USERS.

USERS table needs to have user_name and password field but can have other fields.

For the login screen, we will create a JSP. For the home page after successful login, we will create another JSP. Following MVC, we will let Servlet handle the request. The connectivity with DB should be extracted out of the Servlet for a complex application. However, for simplicity we have maintained it within the servlet.

Below are the identified elements:



Setup the database:

You can create a new database in MySQL or create the below table in an already existing database. Below is the script which you will have to execute in MySQL prompt for create the table.

`first_name` VARCHAR(100) NOT NULL,
`last_name` VARCHAR(100) NOT NULL,
`user_name` VARCHAR(100) NOT NULL,
`password` VARCHAR(100) NOT NULL

You can use the below insert to add users to the USERS table. You can add as many users as you want in this table.

INSERT INTO `users` (`first_name`, `last_name`, `user_name`, `password`) VALUES ('Tech', 'Freaks', 'tech_freaks', 'Passw0rd');

For Connecting to MySQL database from Servlet, you would require MySQL JDBC drivers. Below is the link to download the latest and greatest version of the drivers.
MySQL Driver :

Once we have this we are good to start the Java side development. Below screen explains how the directory structure will look in your development environment.

Application Directory Structure

Coding the Servlet:

Our servlet has is the work horse and does most of the work. We will just implement the doPost method of the servlet, as we will POST the information from the HTML Form.

We will write three private methods in the Servlet for doing the work internally.

-    getConnection()
-    authenticateLogin()
-    closeConnection()

The getConnection() will have the code for low level connectivity with MySQL using the JDBC driver. It will return a java.sql.Connection object. For a real life application, we might have a custom Connection pool implementation or use the Connection pool implementation managed by the Application Server / Web Container. It will use the javax.sql.Datasource object to reach to Connection object.

private Connection getConnection() throws Exception {
   Connection conn = null;
   try {
     String url = "jdbc:mysql://localhost/"+DBNAME+"?user="+DB_USERNAME+"&password="+DB_PASSWORD;
     conn = DriverManager.getConnection(url);
   } catch (SQLException sqle) {
      System.out.println("SQLException: Unable to open connection to db: "+sqle.getMessage());
      throw sqle;
   } catch(Exception e) {
      System.out.println("Exception: Unable to open connection to db: "+e.getMessage());
      throw e;
   return conn;

The above code is pretty straight forward for fetching Connection object. However, it has some key polymorphism concepts which are generally not clearly understood.

Question 1: Connection, Statement, ResultSet are all interface. We all know, that interface cannot be instantiated. From where do we get the implementation? Who is instantiating it?
Answer: All the interface provided above are specification or contract provided as part of JDBC specification. The driver JAR which we added has implementation classes. So, if we look at Oracle DB driver JAR, it would be different. The implementation classes will be different but it would be implementing the interfaces provided in JDBC. Our code should always ‘code to the interface’ and NEVER to the implementation class. This will help us make our code generic for using with other JDBC drivers and with other databases. Ofcourse, this is just a step to make the code database independent. Neverthless this is an awesome example of Polymorphism. Try this in your next interview when they ask you about a real life example about polymorphism, instead of giving the bookish examples. You might just have the interviewer’s mouth wide open Cool

Question 2: Thanks for explaining. However, it still does not explain how the implementation class is instantiated and what the name of the implementation class is.
Answer: The string as a parameter to the Class.forName (“com.mysql.jdbc.Driver”) is the Driver class for the MySQL JDBC driver. It is loaded in memory. The further calls on the DriverManager works with the Driver instance com.mysql.jdbc.Driver. If you unjar the JDBC driver, you will see a implementation class for all Connection, Statement, ResultSet and so on. Once the JDBC driver is registered, the DriverManager.getConnection will give the Connection instance specific to the MySQL JDBC driver.

Let us get back from the interview mode, to development mode again ;)

The authenticateLogin() method will look something like this:

private boolean authenticateLogin(String strUserName, String strPassword) throws Exception {
   boolean isValid = false;
   Connection conn = null;
   try {
     conn = getConnection();
     PreparedStatement prepStmt = conn.prepareStatement(LOGIN_QUERY);
     prepStmt.setString(1, strUserName);
     prepStmt.setString(2, strPassword);
     ResultSet rs = prepStmt.executeQuery();
     if( {
       System.out.println("User login is valid in DB");
       isValid = true; 
  } catch(Exception e) {
    System.out.println("validateLogon: Error while validating password: "+e.getMessage());
    throw e;
  } finally {
  return isValid;

Notice that we have used PreparedStatement instead of Statement. We have purposely used PreparedStatement, as simple query using Statement might be easily prone to SQL Injection. For a Select query, the threat is relatively less, however, this needs to be taken care for update and delete operations.
We have maintained the queries, passwords as static variables in the Servlet as it will not change. Ideally, they should be taken from some properties or XML file.

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

private static final String LOGIN_QUERY = "select * from users where user_name=? and password=?";
private static final String HOME_PAGE = "../Home.jsp";
private static final String LOGIN_PAGE = "../Login.jsp";

You might have to update the value specific to your database instance.

Another important aspect is the error handling. Any exception occurring which will impact processing should be intimated to the end user. However, it should hide the technical issues to prevent panic amongst the end user. Imagine as an end user trying to login to your bank account in which you have all your fortune and seeing the error message which reads, ‘Sorry the database has been deleted. All your money is gone!’ ;)

The approach which we use is to provide the end user with error message as ‘Unable to validate your login in our database’. However, in the System.out you might want to add more information about the error for debugging. You can even put the stackTrace. Using Log4J or similar libraries in high quality application is recommended for logs which can be enabled / disabled at component level depending on the environment.  The approach is indicated in the doPost method of the servlet.

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
   String strUserName = request.getParameter("userName");
   String strPassword = request.getParameter("password");
   String strErrMsg = null;
   HttpSession session = request.getSession();
   boolean isValidLogon = false;
   try {
     isValidLogon = authenticateLogin(strUserName, strPassword);
     if(isValidLogon) {
        session.setAttribute("userName", strUserName);
     } else {
        strErrMsg = "User name or Password is invalid. Please try again.";
   } catch(Exception e) {
     strErrMsg = "Unable to validate user / password in database";

   if(isValidLogon) {
   } else {
     session.setAttribute("errorMsg", strErrMsg);


See how we get the username and password from the request and pass to the method for authentication. Also, at the end we do a send redirect with either the errorMsg or username in session for displaying in Login.jsp or Home.jsp respectively. Ideally, the errorMsg should be put in requestScope and forwarded to JSP. However, since we are doing a redirect, we had to put it in session scope. Try changing the code for forwarding and using the requestScope instead. If you have queries, please feel free to add your comments / queries in the comments section at the end of the article.

Once the Servlet is ready, we need to registered and map it in the Web application’s deployment descriptor, web.xml. The web.xml is pretty straightforward.

<?xml version="1.0" encoding="UTF-8"?>
<web-app id="WebApp_ID" version="2.5" xmlns="" xmlns:xsi="" xsi:schemaLocation="">

The JSP code is pretty straightforward. The call of Form POST to Servlet and use of c:out tag to display errorMsg from the servlet is shown in the below snippet.

<form name="frmLogin" method="POST" action="servlet/LogonServlet">
 <table border="1">
      <td colspan="2"><c:out value="${errorMsg}"/> </td>

The JSP is pretty straightforward. It uses the c:out exactly the same way as in Login.jsp to display the logged in username.

That completes our simple login application. You can test it by deploying the WAR file in Tomcat or any other favorite Application Server. The complete WAR along with the source code can be downloaded by clicking here.

Bugs are inevitable! There is a complaint that you can go the Home.jsp by directly typing the URL. Also, after you login and reach the Home.jsp, if you leave the session open for a long time and then refresh the username disappears. We leave the privilege of fixing the bug with you ;) However, your approach would be to add a check in Home.jsp for session variable “userName”. If not available, redirect to Login.jsp. If you have any queries or need assistance, feel free to add your comments in the section below.

Try similar article which extends to the login application and adds Registration functionality by clicking Simple Registration Application using JSP, Servlet and MySQL.

private Connection getConnection() throws Exception {

            Connection conn = null;

            try {

                  String url = "jdbc:mysql://localhost/"+DBNAME+"?user="+DB_USERNAME+"&password="+DB_PASSWORD;


                  conn = DriverManager.getConnection(url);

            } catch (SQLException sqle) {

                  System.out.println("SQLException: Unable to open connection to db: "+sqle.getMessage());

                  throw sqle;

            } catch(Exception e) {

                  System.out.println("Exception: Unable to open connection to db: "+e.getMessage());

                  throw e;


            return conn;



-1 #2 RE: Simple Login Application using JSP, Servlets and JDBC connectivity to MySQLaboobakar 2014-03-21 18:26
I need this sample app to understand the basics
+4 #1 simple login appSYAM KUMAR 2013-09-03 14:27
i need sample login web application by using servlet

Add comment

Security code

Joomla SEO by MijoSEF