Inner Join Table using Servlet

In this section we will discuss how can we use the Inner Join query in java servlet.

Inner Join Table using Servlet

In this section we will discuss how can we use the Inner Join query in java servlet.

Feature of an Inner Join keyword is that it returns only those rows when there is at least one match is found in both of the tables. If the rows of table1 doesn't match with any row of table2, then that unmatched row will not be listed. So, to use this query in servlet I made a class named InnerJoinTable which extends the HttpServlet class. Further in the code I override the method doGet() into which made an object of HttpServletRequest and HttpServletResponse respectively. Then to show an output on browser I used the getWriter() method with the reference of HttpServletResponse interface that inherited from ServletResponse interface. Now to use a SQL query we will have to first establish a connection with the database package. So for establishing a connection I used the classes and interfaces of java.sql package as DriverManager, Connection, PreparedStatement and ResultSet. Passes the query into prepareStatement() method of Connection interface then execute it by executeQuery() method of PreparedStatement interface then extracted the result of query from the ResultSet object.

Example :

InnerJoinTable.java

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 InnerJoinTable 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 INNER 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>InnerJoinTable</servlet-name>
<servlet-class>InnerJoinTable</servlet-class>
</servlet>

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

</web-app>

Output :

The table which I had created are as follows :

1. department

2. person

When you will execute the above example you will get the following output

Download Source Code