Join Table using servlet

In this section we will discuss how can we join the two tables.

Join Table using servlet

In this section we will discuss how can we join the two tables.

Join of two tables means combining the records of two or more than two tables into the database based on their relationship. The set created by the join can be used or saved as a table. So, it is clear from the above lines that we will required at least two tables for joining them. That's why first I created a database named 'data' and two tables 'employee' and 'department'. Further I started to write java servlet code, in this code make a class named JoinTableServlet that extends the HttpServlet class. In the next step override the doGet() method of HttpServlet class into which createed the references of HttpServletRequest and HttpServletResponse. To use the query of database we will also required a connection between the java code and database package, so I establish a connection and then passes the query into the parameter of prepareStatement() method of Connection interface which returns the PreparedStatement and then execute this query by executeQuery() method of  PreparedStatement and stored it into the ResultSet. Then extract the data using the ResultSet's method getInt() and getString().

Example :

JoinTableServlet.java

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class JoinTableServlet extends HttpServlet
{
public void doGet(HttpServletRequest req, HttpServletResponse res)
throws IOException, ServletException
{
res.setContentType("text/html");
PrintWriter pw = res.getWriter();
Connection con;
PreparedStatement ps;
ResultSet rs;
try
{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://192.168.10.13/data","root","root");
ps = con.prepareStatement("SELECT * FROM Employee NATURAL JOIN Department");
rs = ps.executeQuery();
pw.println("<table><tr>");
pw.println("<td width = '30%'>EmpId</td>");
pw.println("<td width = '30%'>Name</td>");
pw.println("<td width = '30%'>DepId</td>");
pw.println("<td>Salary</td>");
pw.println("</tr>");
pw.println("</table>");
while(rs.next())
{
Integer eId = rs.getInt("Id");
String name = rs.getString("Name");
Integer dId = rs.getInt("DepartmentId");
Integer salary = rs.getInt("Salary");
pw.println("<table><tr>");
pw.println("<td width = '30%'>"+eId+"</td>");
pw.println("<td width = '30%'>"+name+"</td>");
pw.println("<td width = '30%'>"+dId+"</td>");
pw.println("<td >"+salary+"</td>");
pw.println("</tr>");
pw.println("</table>");
}
try
{
con.close();
ps.close();
}
catch(Exception e)
{
pw.println(e);
}
}
catch(SQLException sx)
{
pw.println(sx);
}
catch(ClassNotFoundException cx)
{
pw.println(cx);
}
}
}

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app id="WebApp_ID" version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
<display-name>join</display-name>
<servlet>
<servlet-name>NaturalJoin</servlet-name>
<servlet-class>JoinTableServlet</servlet-class>
</servlet>

<servlet-mapping>
<servlet-name>NaturalJoin</servlet-name>
<url-pattern>/NaturalJoin</url-pattern>
</servlet-mapping>
</web-app>

Output :

Initially the table are :

1. employee

2. department

When you will execute the above example you will get the following output:

Download Source Code