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:
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:
Tools used :
- Eclipse
- JDK 1.6
- 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 :
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.
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.
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 :
Screen 2 :
Screen 3 :
Screen 4 :
You can download the source code of the example by clicking on the Download link below.
Source+lib (Developed in Eclipse) : Download
Related Articles