Cross Join Table using Servlet

In this section we will discuss how can we join the two table using cross join query.

Cross Join Table using Servlet

In this section we will discuss how can we join the two table using cross join query.

Before going further we will have to first understand that what is cross join. Cross join is a type of table joining into which data of each row of table1 is joined with the data of each row of table2. To use such joining process in servlet I created a class named CrossJoinTable which extends the HttpServlet class, and override the doGet() method into which created the references of HttpServletRequest and HttpServletResponse interfaces. Using the reference of HttpServletResponse I call the getWriter() method which returns a PrintWriter object that displays the messages on browser. Also if I want to use the above query then I should have establish a connection between servlet and database package so, I did it into the code by using Connection, PreparedStatement, ResultSet interfaces. At first load the driver using Class.forName and then create a connection using DriverManager and then passed the query into prepareStatement() method of Connection interface which returns a PreparedStatement object and finally executed this query using executeQuery() method of PreparedStatement which returns a ResultSet object. Then at last I get the result by using the ResultSet object.

Example :

CrossJoinTable.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 CrossJoinTable 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;
String url = "jdbc:mysql://192.168.10.13/data";
String uid = "root";
String psw = "root";
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(url, uid, psw);
ps = con
.prepareStatement("SELECT * FROM Employee CROSS 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("</tr>");
pw.println("</table>");
while (rs.next()) {
Integer eId = rs.getInt("Id");
String name = rs.getString("Name");
Integer dId = rs.getInt("DepartmentId");
String dept = rs.getString("Department");
pw.println("<table><tr>");
pw.println("<td width = '30%'>" + eId + "</td>");
pw.println("<td width = '30%'>" + name + "</td>");
pw.println("<td width = '30%'>&nbsp;&nbsp;" + dId + "</td>");
pw.println("</tr>");
pw.println("</table>");
}
con.close();
ps.close();
} 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>CrossJoinTable</servlet-name>
<servlet-class>CrossJoinTable</servlet-class>
</servlet>

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

Output :

Initially we have the table were

employee

department

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

Download Source Code