Left Join or Left Outer JoinTable using Servlet

In this section we will discuss how can we join tables using Left Join.

Left Join or Left Outer Join Table using servlet

In this section we will discuss how can we join tables using Left Join.

At first I want to briefly introduce you about the Left Join, because if you have no idea that what is Left Join and what is its purpose to use, you will be not able for where it to be implemented. So, what is Left Join Table, Left Join is a keyword that joins the tables into which all the rows of left table are returned without care to matching all the elements in Right Table. In other word we can say that we can get all the records which are matched in left table as well as we gets an extra unmatched record in left table. So, to solve this problem at first I have created two tables named 'employee' and 'department' and then made a class in java code named LeftJoinTable which extends the class HttpServlet. Further overridden its method doGet() into which created a reference of HttpServletRequest and HttpServletResponse which would be used further in the example. Using the reference of HttpServeltResponse I called the getWriter() method which returns an object of PrintWriter that helps in to show output on browser. In the java code we will have to required a database connection to use Left Join query. So I establish a connection with database using some interfaces of java.sql package as Connection and the class DriverManager. Then pass the query into the prepareStatement() method of Connection interface which returns a PreparedStatement object, then executed this query using executeQuery() method of PreparedStatement which returns an object of ResultSet through which I extracted the result of the query. Finally closed the connection.

Example :

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;

public class LeftJoinTable 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 employee.id, employee.name, 
employee.department, department.departmentId from employee LEFT JOIN department on 
employee.department LIKE department.department");
rs = ps.executeQuery();

pw.println("<table><tr>");
pw.println("<td><b>Id</b></td>");
pw.println("<td><b>Name</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");
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>" + 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>LeftJoinTable</servlet-name>
<servlet-class>LeftJoinTable</servlet-class>
</servlet>

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

</web-app>

Output :

The tables which I had made are

employee table

department table

And when you will execute the above example you will get the following table as output :

Download Source Code