In this section we will discuss about the Right Join Table.
Right Join or Right Outer Join Table using Servlet
In this section we will discuss about the Right Join Table.
Before going further I want to briefly introducing you about the Right Join keyword in database. Right Join Table means joining of columns of more than one tables as our requirement. Work of Right Join is to join the columns of two different tables and returned all the rows of right table (table2) without caring to unmatched row in the left table (table1). Now come to the point we have to use the Right join query in java servlet. So, I created a class named RightJoinTable which extends the class HttpServlet. Further I override the doGet() method of this class into which created an object of HttpServletRequest and HttpServletResponse to take a request and send the correspondence response respectively to the client. Using the reference of HttpServletResponse I uses the getWriter() method of ServletResponse interface which is further inherited by the HttpServletResponse interface which returns an object of PrintWriter that helps in to show output on browser. In the next step I established a connection with the database package 'mysql' because to execute a SQL query we will have to require a connection with database. So, I established a connection by using the interfaces and classes of java.sql package, after that I passes the query in prepareStatement() method of Connection interface which returns a PreparedStatement then this statement is executed by its own method executeQuery() which returns a ResultSet object through which I extracted the result of this query.
Example :
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
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 RightJoinTable extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html");
PrintWriter pw = response.getWriter();
String url = "jdbc:mysql://192.168.10.13/data";
String uid = "root";
String psw = "root";
Connection con;
PreparedStatement ps;
ResultSet rs;
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(url, uid, psw);
ps = con.prepareStatement("SELECT person.id, person.name, department.departmentId from person RIGHT JOIN department on person.departmentId=department.departmentId");
rs = ps.executeQuery();
pw.println("<table><tr>");
pw.println("<td><b>PersonId</b></td>");
pw.println("<td><b>Name</b></td>");
pw.println("<td><b>DeptId</b></td>");
pw.println("</tr>");
while (rs.next()) {
Integer id = rs.getInt("Id");
String name = rs.getString("Name");
Integer did = rs.getInt("DepartmentId");
pw.println("<tr>");
pw.println("<td>" + id + "</td>");
pw.println("<td>" + name + "</td>");
pw.println("<td>" + did + "</td>");
pw.println("</tr>");
}
pw.println("</table>");
con.close();
ps.close();
} catch (SQLException sx) {
pw.println(sx);
} catch (ClassNotFoundException cx) {
System.out.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>RightJoinTable</servlet-name> <servlet-class>RightJoinTable</servlet-class> </servlet> <servlet-mapping> <servlet-name>RightJoinTable</servlet-name> <url-pattern>/RightJoinTable</url-pattern> </servlet-mapping> </web-app>
Output :
Initially I had created the tables these are as follows :
1. department
2. person
When you will execute this example you will get the following output :
[ 0 ] Comments