JSP Database Access Example using JDBC and MySQL

In this example we will create a Login page to enter user name and password and one link for Registration page for new user registration.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.


Directory Structure Of Project

Directory Structure of project is shown below:

JSP Login Example

In this example we will create a Login page to enter user name and password and one link for Registration page for new user registration.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.


Directory Structure Of Project

Directory Structure of project is shown below:

JSP Login Example




Tools used :

  1. Eclipse
  2. JDK 1.6
  3. Tomcat 6.0

Step 1: Create a table Employee in mysql DB :

CREATE TABLE `EMPLOYEE` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `FIRST_NAME` varchar(45) NOT NULL,
  `LAST_NAME` varchar(45) NOT NULL,
  `EMAIL` varchar(45) NOT NULL,
  `USER_NAME` varchar(45) NOT NULL,
  `PASSWORD` varchar(45) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Project Directory Structure :

JSP Login Example


Step 1: Create Dynamic Web project

Open Eclipse and go to File -> New -> Project and select Dynamic Web Project in the New Project wizard screen.

JSP Login Example


Provide the name of the project as LoginExample . Once this is done, select the target runtime environment as Apache Tomcat v6.0 and click Next -> Next -> Finish.

JSP Login Example


Step 3: Create JSP files

Create all JSP files inside WebContent Directory

index.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!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=ISO-8859-1">
<title>Login Example</title>
</head>
<body>
<form method="post" action="login.jsp">
<center>
<table border="2" width="30%" cellpadding="3">
<thead>
<tr>
<th colspan="2">Login Example</th>
</tr>
</thead>
<tbody>
<tr><td>User Name</td>
<td>
<input type="text" name="username" value="" />
</td>
</tr>
<tr>
<td>Password</td>
<td><input type="password" name="password" value="" /></td>
</tr>
<tr>
<td><input type="submit" value="Login" /></td>
<td><input type="reset" value="Reset" /></td>
</tr>
<tr>
<td colspan="2">New User <a href="registerpage.jsp">Register Here</a></td>
</tr>
</tbody>
</table>
</center>
</form>
</body>
</html>

login.jsp

<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@page import="com.example.util.DBUtil"%>
<%
 String userName = request.getParameter("username");
 String password = request.getParameter("password");
 Connection con = DBUtil.getMySqlConnection();
 Statement st = con.createStatement();
 ResultSet rs;
 rs = st.executeQuery("select * from EMPLOYEE where USER_NAME='"
	+ userName + "' and PASSWORD='" + password + "'");
 if (rs.next()) {
 session.setAttribute("username", userName);
 response.sendRedirect("success.jsp");
 } 
 else 
 {
 out.println("Invalid password <a href='index.jsp'>try again</a>");
	}
%>

registerpage.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!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=ISO-8859-1">
<title>Registration Page</title>
</head>
<body>
<form method="post" action="registration.jsp">
<center>
<table border="1" width="30%" cellpadding="5" bgcolor="pink">
<thead>
<tr>
<th colspan="2">Registration Page</th>
</tr>
</thead>
<tbody>
<tr>
<td>First Name</td>
<td><input type="text" name="firstname" value="" /></td>
</tr>
<tr>
<td>Last Name</td>
<td><input type="text" name="lastname" value="" /></td>
</tr>
<tr>
<td>Email</td>
<td><input type="text" name="email" value="" /></td>
</tr>
<tr>
<td>User Name</td>
<td><input type="text" name="username" value="" /></td>
</tr>
<tr>
<td>Password</td>
<td><input type="password" name="password" value="" /></td>
</tr>
<tr>
<td><input type="submit" value="Submit" /></td>
<td><input type="reset" value="Reset" /></td>
</tr>
<tr>
<td colspan="2">Already registered?<ahref="index.jsp">Login Here</a></td>
</tr>
</tbody>
</table>
</center>
</form>
</body>
</html>

registration.jsp

<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@page import="com.example.util.DBUtil"%>
<%
String userName = request.getParameter("username");
String password = request.getParameter("password");
String firstName = request.getParameter("firstname");
String lastName = request.getParameter("lastname");
String email = request.getParameter("email");
Connection con = DBUtil.getMySqlConnection();
Statement st = con.createStatement();
int i = st.executeUpdate
("insert into EMPLOYEE
(FIRST_NAME, LAST_NAME, EMAIL, USER_NAME, PASSWORD) 
values ('"
					+ firstName
					+ "','"
					+ lastName
					+ "','"
					+ email
					+ "','" 
					+ userName
					+ "','" 
					+ password 
					+ "')");
	if (i > 0) {
		response.sendRedirect("welcome.jsp");
	} else {
		response.sendRedirect("index.jsp");
	}
%>

success.jsp

<%
    if ((session.getAttribute("username") == null) 
	|| (session.getAttribute("username") == "")) {
%>
You are not logged in<br/>
<ahref="index.jsp">Please Login</a>
<%} else {
%>
Welcome <%=session.getAttribute("username")%>
<ahref='logout.jsp'>Log out</a>
<%
    }
%>

welcome.jsp

Registration is Successful.
Please Login Here <a href='index.jsp'>Go to Login

logout.jsp

<%
session.setAttribute("username", null);
session.invalidate();
response.sendRedirect("index.jsp");
%>

Output :

Screen 1 :

JSP Login Example

Screen 2 :

JSP Login Example

Screen 3 :

JSP Login Example

Screen 4 :

JSP Login Example



You can download the source code of the example by clicking on the Download link below.

Source+lib (Developed in Eclipse) : Download




comments powered by Disqus