Union using Servlet

In this section we will discuss how an union operator is used in java servlet.

Union using Servlet

In this section we will discuss how an union operator is used in java servlet.

UNION is a keyword used in database system for finding an unique elements from the two or more tables. For implementing this operator the column number of all the table must be the equal and must have the same data types as well as the order of columns in every SELECT statement must be similar. So to implement this operator in java servlet first I created a table named 'department' and 'department1'. Then I made a class in java servlet named UnionUsingServlet which extends the HttpServlet class. Inside this class I overridden the doGet() method into which the objects of HttpServletRequest and HttpServletResponse is to be created. Using the object of HttpServletResponse called the getWriter() method of ServletResponse interface which is inherited by the HttpServletResponse interface that helps in to show output on browser. Since this query is used in database system and the database system is not a directly accessible by java so, we will have to first establish a connection between database package and java code and we will do it possible using the package java.sql available in java. So I established a connection then after pass the query into prepareStatement() method of Connection which returns an object of PreparedStatement interface then execute this query using the method executeQuery() of PreparedStatement interface which returns a ResultSet object and then extracted the result from its object.

Example :

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class UnionUsingServlet 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 driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://192.168.10.13/data";
String uid = "root";
String psw = "root";
String sql = "(SELECT Department FROM department)" +
"UNION (SELECT Department FROM department1)";

try
{
Class.forName(driver);
con = DriverManager.getConnection(url,uid,psw);
ps = con.prepareStatement(sql);
rs = ps.executeQuery();

pw.println("<table><tr>");
pw.println("<td><b>Department</b></td>");
pw.println("</tr>");
while(rs.next())
{
String dept = rs.getString("Department");
pw.println("<tr>");
pw.println("<td>"+dept+"</td>");
pw.println("</tr>");
}
pw.println("</table>");
}
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>UnionUsingServlet</servlet-name>
<servlet-class>UnionUsingServlet</servlet-class>
</servlet>

<servlet-mapping>
<servlet-name>UnionUsingServlet</servlet-name>
<url-pattern>/UnionUsingServlet</url-pattern>
</servlet-mapping>

</web-app>

Output :

Tables which I had created earlier are as follows :

1. department

2. department1

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

Download Source Code