Web Based Contact Management System using JSP and MySQL Part 1

Hello people

Here I will show you a web based contact management system using JSP, JDBC and MySQL Database. This is a very simple application to make you clear your basics about building any Web Based Database Application using Java. This Web Application uses JSP and JDBC where a user can create and manage his Contacts online. It’s like an online Contact Book

For running this application you will need Tomcat and MySQL DatabaseYou can customize it to other web server like Glassfish and any other Database.

For using MySQL you need to download the JDBC Driver, you can get the driver from here.

http://www.mysql.com/products/connector/

Extract the JAR file and put it inside the lib directory in the Tomcat directory

This is the JAR FileName- mysql-connector-java-5.1.17-bin.jar

To download the Tomcat visit the below link

http://tomcat.apache.org/download-70.cgi

It is simple web based application where a user can manage his Contacts online.

It contains all the basic Functionalities like

  • Viewing all Contacts at once
  • Adding New Contact
  • Updating an Existing Contact
  • Removing a Contact
  • Searching for an existing Contact

Keep all the files for this application inside a directory called ContactApp (or whatever name you want to give to your application) and keep this inside the webapps directory of Tomcat.

The main purpose of this application is to understand How to use JDBC for creating a web based database application.

Understanding of Java language and JSP is important for this application.

For this application you need a Database Table. I am using MySQL Database that came with WAMP installation on Windows

In MySQL I created a database called contactdb. In that I created a table called contact_details.

You can see the structure of the Table in this screenshot

Now we will give a brief description about all the files that we need for this application

db_connect.jsp

We have separated the Database Connectivity part in another file called db_connect.jsp and included it in the files where we need to connect to the database.

This makes it easier to make any change to the code at a later stage and you don’t have to make a change in every file.

This file is in the Includes directory in ContactApp


<%
	String dbURL = "jdbc:mysql://localhost:3306/contactdb";
	String username = "Annna";
	String password = "MySecretPwd";
	Connection con = DriverManager.getConnection(dbURL, username, password);
%>

ContactDetails.jsp

This is the main page of this application. It shows how to fetch all the records from the data base table and display it on the web page.

SQL SELECT Statement has been used to fetch all the records from the Database Table.

This page displays all the contact details in Tabular Format. Below every contact there is a link for Editing and Deleting the Contact.


<%@page import="java.util.*"%>
<%@page import="java.sql.*"%>

<html>

<head><title>My Contacts</title></head>

<body>

<%@include file='Includes/db_connect.jsp'%>

<p align='left'>
<a href="Contact_New.html">Add New Contact</a> | <a href="ContactSearchForm.jsp">Search Contact</a>
</p>

<table cellpadding='10' border='1' align='left'>

<tr>
<td colspan="3" align="center"><h2>My Contacts</h2></td>
</tr>

<tr>
	<th>Name</th>
	<th>Mobile</th>
	<th>Email</th>
</tr>
<%

	String sql = "SELECT * FROM contact_details ORDER BY name";

	Statement stmt = con.createStatement();
	ResultSet rs = stmt.executeQuery(sql);

	while(rs.next())
	{
		String name = rs.getString("name");
		String mobile = rs.getString("mobile");
		String email = rs.getString("email");
		int id = rs.getInt("contact_id");

%>

	<tr>
		<td><%=name%> <br /><a href='Contact_Edit.jsp?contact_id=<%=id%>'>Edit</a> |

		<a href='Contact_Delete.jsp?contact_id=<%=id%>'>Delete</a>
		</td>
		<td><%=mobile%></td>
		<td><%=email%></td>
	</tr>

<%
}

rs.close();
rs = null;
stmt.close();
stmt = null;
con.close();

%>
</table>

</body>
</html>

Screenshot of this page is here

Adding New Contact-For this you need an HTML Form

Contact_New.html
This is a simple HTML Form that contains fields for a new contact.

<html>
<head>
<title>Add New Contact</title>
</head>

<body>

<form action="Contact_New.jsp"  name="NewContactForm" method="post">

<table class='login' cellpadding='10' align='left'>

<tr>
<td colspan="2" align="center"><h2>Add New Contact</h2></td>
</tr>

<tr>
<td>Name: </td>
<td><input type="text" name ="contact_name" size="40"> </td>
</tr>

<tr>
<td>Mobile: </td>
<td><input type="text" name ="mobile" size="40"></td>
</tr>

<tr>
<td>Email: </td>
<td><input type="text" name ="email" size="40"></td>
</tr>

<tr>
<td colspan="2" align="center"><br/><br/><input type="Submit" value ="Save">
<input type="Reset" value ="Reset"></td>
</tr>

</table>

</form>

</body>
</html>

Snapshot of the Form

Add a New Contact and Click on Save button

Contact_New.jsp
This jsp file takes the form data from Contact_New.html and stores it in the Database Table.
INSERT Functionality has been demonstrated in this file.
SQL INSERT Statement has been used to add the contact to db.


<%@page import="java.util.*"%>
<%@page import="java.sql.*"%>

<html>

<head><title>My Contacts</title></head>

<body>

<%@include file='Includes/db_connect.jsp'%>

<%! boolean success = false; %>
<%
	String contact_name = request.getParameter("contact_name");
	String mobile = request.getParameter("mobile");
	String email = request.getParameter("email");

	String sql= "INSERT INTO contact_details(name, mobile, email) VALUES(?, ?, ?)";

	PreparedStatement pstmt = con.prepareStatement(sql);
	pstmt.setString(1, contact_name);
	pstmt.setString(2, mobile);
	pstmt.setString(3, email);

	int nRows = pstmt.executeUpdate(); //returns the number of rows affected by the query

	if(nRows==0){
		success = false;
	}

	else{
		success = true;
	}

	pstmt.close();con.close();

%>

	<%
			if(success==true){
	%>

	<h2>Contact have been successfully added</h2>

	<%
		}//if end
		else{
	%>

	<h2> Error -- Contact could not be added </h2>

	<%
		}//else end

	%>

<p align='center'>
<a href="ContactDetails.jsp">View Contacts</a>
</p>

</body>
</html>

When the Contact gets added a screen is displayed with the message that the Contact has been added successfully
Snapshot of the o/p

Here is the List of Contacts with the New Contact

As this post grew very large we will continue with Updating, Deleting and Searching for a Contact in the next post.

Hope you enjoyed learning till now. Run this program. Make Changes to the source code and try other methods to do the same thing. Keep Programming because that’s the best you to learn this skill.

Check out the Part 2 of this Tutorial here
Web Based Contact Management System Using JSP and MySQL-Part2

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s