In this section we will discuss about how the FULL OUTER JOIN keyword is used with the java servlet code.
Full Outer Join Table using Servlet
In this section we will discuss about how the FULL OUTER JOIN keyword is used with the java servlet code.
First of all I want to briefly introduce you about the full outer join. So what is full outer join ? Full outer join is a keyword which combines the two tables as it returns the rows of both tables (left table and right table). Conceptually the full outer join may be called the combination of results of implementing the both left join and right joins. It returns the rows into which the corresponding column is to be set null if there is no match do for the row and it returns a single row if the corresponding column is matched. Here the one thing is essential to mention that the different database systems implemented it in different ways I will use the MySQL so, I use it as MySQL. Now focuses on our goal that is the use of Full Outer Join in java servlet code. So I first created a database table named 'employee' and 'department'. Then I made a class named FullOuterJoin which extends the class HttpServlet into which overridden the doGet() method. Then with the help of the object of HttpServletResponse called the getWriter() method (a ServletResponse interface method which is further inherited by HttpServletResponse interface) which helps in to show output on browser. To use a SQL query in java we will have to establish a connection with a database system, so I establish a connection with mysql using the classes and interfaces of java.sql package. Then after create a statement by using prepareStatement() method of Connection interface which returns a PreparedStatement object that is executed by its own method executeQuery() which stores the result into a ResultSet interface object from which I extracted the result of query. Finally I have closed the connection.
Example :
FullOuterJoin.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 FullOuterJoin 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 * FROM employee LEFT JOIN department ON " +
"employee.department LIKE department.department UNION " +
"SELECT * FROM employee RIGHT JOIN department ON " +
"employee.department LIKE department.department";
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>EmpId</b></td>");
pw.println("<td><b>EmpName</b></td>");
pw.println("<td><b>EmpDept</b></td>");
pw.println("<td><b>EmpSalary</b></td>");
pw.println("<td><b>DeptId</b></td>");
pw.println("<td><b>Department</b></td>");
pw.println("</tr>");
while (rs.next()) {
Integer id = rs.getInt("Id");
String name = rs.getString("Name");
String edept = rs.getString("Department");
Integer s = rs.getInt("Salary");
Integer did = rs.getInt("DepartmentId");
String dept = rs.getString("Department");
pw.println("<tr>");
pw.println("<td>" + id + "</td>");
pw.println("<td>" + name + "</td>");
pw.println("<td>" + edept + "</td>");
pw.println("<td>" + s + "</td>");
pw.println("<td>" + did + "</td>");
pw.println("<td>" + dept + "</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>FullOuterJoin</servlet-name> <servlet-class>FullOuterJoin</servlet-class> </servlet> <servlet-mapping> <servlet-name>FullOuterJoin</servlet-name> <url-pattern>/FullOuterJoin</url-pattern> </servlet-mapping> </web-app>
Output :
The tables which I had created earlier are as follows :
1. employee
2. department
When you will execute the above query you will get the following output :
[ 0 ] Comments