Union All using Servlet

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

Union All using Servlet

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

UNION ALL is a keyword used in database system for finding an elements with the duplicate values from the two or more tables. This keyword is also implemented as the Union but the only difference is that it follows an 'All' with Union. 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 UnionAllUsingServlet 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 passed the query into prepareStatement() method of Connection interface 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 :

UnionAllUsingServlet.java

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 UnionAllUsingServlet 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 ALL (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>UnionAllUsingServlet</servlet-name>
<servlet-class>UnionAllUsingServlet</servlet-class>
</servlet>

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

</web-app>

Output

The 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