Contact Us
   
   
 
   
   
 
     
 
 
JSP Tutorial
JDBC Tutorial
 
Book Must Read
 
 

JDBC Join

 
 
Print   Email
 
     
 

JDBC queries are any thing used in SQL Statement of Relational database management system.

We have two tables in database

staff_register

iEmpID sStaffName sStaffDept iStaffPhone
Amit  IT  45678 
Vikas  Marketing  888888888 
MANUEL GANAK  Marketing  2334937 
Kula Saikia  Marketing  1234536348 
Uma  Marketing  1178493753 
Nipon  IT  2229849586 
Smriti Sahu  Account  237452342 
Sukumar Das  Account  435217243 
Sesmah Robidas  HR  342345642 
10  Fagoo Urang  HR  342451452 
11  Babita Thakur  HR  654243234 
12  Jagoo Sobor  Account  425464245 
13  Anita Rabidas  Production  435224545 
14  Bhaktiram Yean  IT  120580287 
15  Dipali Bhuyan  IT  102343928 

staff_details

idetID iEmpID sAddress
1 2 Bor Ghuguloni Gaon
2 3 Pandhua Gaon
3 5 street 4, japan
4 4 gali 45, noida, Uttar pradesh
5 7 flat 110, New apart colony, New delhi
6 9 Flat A12, Singapore
7 10 Holi colony, tamil nadu

Example of INNER JOIN query in JDBC

Provides only matched rows from both tables
 
innerJoin.jsp

<%@ page  language="java" import="java.sql.*" %>
<%
    Connection conn = null;
    
    Class.forName("com.mysql.jdbc.Driver").newInstance();
    String jdbcURL="jdbc:mysql://localhost:3306/jsp";
    conn = DriverManager.getConnection(jdbcURL,"root", "");
    
    PreparedStatement psSelectRecord=null;
    ResultSet rsSelectRecord=null;
    String sqlSelectRecord=null;

    sqlSelectRecord="SELECT s.iEmpID, s.sStaffName, s.sStaffDept, s.iStaffPhone,"+
                        "d.sAddress FROM staff_register s " +
                            "inner join staff_details d on d.iEmpID=s.iEmpID";    
                           
    psSelectRecord=conn.prepareStatement(sqlSelectRecord);
    rsSelectRecord=psSelectRecord.executeQuery();
 
%>
<html>
<head>
<title>inner join  in JDBC Query</title>
<style>
th
{
 text-align:left;
 padding-left:5px;
}
td
{
 text-align:left;
 padding-left:5px;
}
</style>
</head>

<body>
<table width="100%" border="1" cellspacing="0" cellpadding="0">
  <tr>
    <th>iEmpID</th>
    <th>sStaffName</th>
    <th>sStaffDept</th>
    <th>iStaffPhone</th>
    <th>sAddress</th>
  </tr>
  <%
  int cnt=1;
  while(rsSelectRecord.next())
  {
  %>
  <tr>
    <td><%=rsSelectRecord.getString("iEmpID")%>&nbsp;</td>
    <td><%=rsSelectRecord.getString("sStaffName")%>&nbsp;</td>
    <td><%=rsSelectRecord.getString("sStaffDept")%>&nbsp;</td>
    <td><%=rsSelectRecord.getString("iStaffPhone")%>&nbsp;</td>
    <td><%=rsSelectRecord.getString("sAddress")%>&nbsp;</td>
  </tr>
  <%
   cnt++;   /// increment of counter
  } /// End of while loop
  %>
</table>

</body>
</html>
<%
try{
          if(psSelectRecord!=null)
          {
            psSelectRecord.close();
          }
          if(rsSelectRecord!=null)
          {
            rsSelectRecord.close();
          }
          
          
          if(conn!=null)
          {
           conn.close();
          }
        }
        catch(Exception e)
        {
          e.printStackTrace(); 
        }
%>

Output of this inner join

iEmpID sStaffName sStaffDept iStaffPhone sAddress
Vikas  Marketing  888888888  Bor Ghuguloni Gaon 
MANUEL GANAK  Marketing  2334937  Pandhua Gaon 
Uma  Marketing  1178493753  street 4, japan 
Kula Saikia  Marketing  1234536348  gali 45, noida, Uttar pradesh 
Smriti Sahu  Account  237452342  flat 110, New apart colony, New delhi 
Sesmah Robidas  HR  342345642  Flat A12, Singapore 
10  Fagoo Urang  HR  342451452  Holi colony, tamil nadu 

Example of Left Join in JDBC

Left join include all records from left table and take only matched records from another table. We are using same table above

leftJoin.jsp

<%@ page  language="java" import="java.sql.*" %>
<%
    Connection conn = null;
    
    Class.forName("com.mysql.jdbc.Driver").newInstance();
    String jdbcURL="jdbc:mysql://localhost:3306/jsp";
    conn = DriverManager.getConnection(jdbcURL,"root", "");
    
    PreparedStatement psSelectRecord=null;
    ResultSet rsSelectRecord=null;
    String sqlSelectRecord=null;

    sqlSelectRecord="SELECT s.iEmpID, s.sStaffName, s.sStaffDept, s.iStaffPhone,"+
                        "d.sAddress FROM staff_register s " +
                            "left join staff_details d on d.iEmpID=s.iEmpID";    
                           
    psSelectRecord=conn.prepareStatement(sqlSelectRecord);
    rsSelectRecord=psSelectRecord.executeQuery();
 
%>
<html>
<head>
<title>Left join  in JDBC Query</title>
<style>
th
{
 text-align:left;
 padding-left:5px;
}
td
{
 text-align:left;
 padding-left:5px;
}
</style>
</head>

<body>
<table width="100%" border="1" cellspacing="0" cellpadding="0">
  <tr>
    <th>iEmpID</th>
    <th>sStaffName</th>
    <th>sStaffDept</th>
    <th>iStaffPhone</th>
    <th>sAddress</th>
  </tr>
  <%
  int cnt=1;
  while(rsSelectRecord.next())
  {
  %>
  <tr>
    <td><%=rsSelectRecord.getString("iEmpID")%>&nbsp;</td>
    <td><%=rsSelectRecord.getString("sStaffName")%>&nbsp;</td>
    <td><%=rsSelectRecord.getString("sStaffDept")%>&nbsp;</td>
    <td><%=rsSelectRecord.getString("iStaffPhone")%>&nbsp;</td>
    <td><%=rsSelectRecord.getString("sAddress")%>&nbsp;</td>
  </tr>
  <%
   cnt++;   /// increment of counter
  } /// End of while loop
  %>
</table>

</body>
</html>
<%
try{
          if(psSelectRecord!=null)
          {
            psSelectRecord.close();
          }
          if(rsSelectRecord!=null)
          {
            rsSelectRecord.close();
          }
          
          
          if(conn!=null)
          {
           conn.close();
          }
        }
        catch(Exception e)
        {
          e.printStackTrace(); 
        }
%>

Output of Left Join

iEmpID sStaffName sStaffDept iStaffPhone sAddress
Amit  IT  45678  null 
Vikas  Marketing  888888888  Bor Ghuguloni Gaon 
MANUEL GANAK  Marketing  2334937  Pandhua Gaon 
Kula Saikia  Marketing  1234536348  gali 45, noida, Uttar pradesh 
Uma  Marketing  1178493753  street 4, japan 
Nipon  IT  2229849586  null 
Smriti Sahu  Account  237452342  flat 110, New apart colony, New delhi 
Sukumar Das  Account  435217243  null 
Sesmah Robidas  HR  342345642  Flat A12, Singapore 
10  Fagoo Urang  HR  342451452  Holi colony, tamil nadu 
11  Babita Thakur  HR  654243234  null 
12  Jagoo Sobor  Account  425464245  null 
13  Anita Rabidas  Production  435224545  null 
14  Bhaktiram Yean  IT  120580287  null 
15  Dipali Bhuyan  IT  102343928  null 

Example of Right Join

rightJoin.jsp

<%@ page  language="java" import="java.sql.*" %>
<%
    Connection conn = null;
    
    Class.forName("com.mysql.jdbc.Driver").newInstance();
    String jdbcURL="jdbc:mysql://localhost:3306/jsp";
    conn = DriverManager.getConnection(jdbcURL,"root", "");
    
    PreparedStatement psSelectRecord=null;
    ResultSet rsSelectRecord=null;
    String sqlSelectRecord=null;

    sqlSelectRecord="SELECT s.iEmpID, s.sStaffName, s.sStaffDept, s.iStaffPhone,"+
                        "d.sAddress FROM staff_register s " +
                            "right join staff_details d on d.iEmpID=s.iEmpID";    
                           
    psSelectRecord=conn.prepareStatement(sqlSelectRecord);
    rsSelectRecord=psSelectRecord.executeQuery();
 
%>
<html>
<head>
<title>Right join  in JDBC Query</title>
<style>
th
{
 text-align:left;
 padding-left:5px;
}
td
{
 text-align:left;
 padding-left:5px;
}
</style>
</head>

<body>
<table width="100%" border="1" cellspacing="0" cellpadding="0">
  <tr>
    <th>iEmpID</th>
    <th>sStaffName</th>
    <th>sStaffDept</th>
    <th>iStaffPhone</th>
    <th>sAddress</th>
  </tr>
  <%
  int cnt=1;
  while(rsSelectRecord.next())
  {
  %>
  <tr>
    <td><%=rsSelectRecord.getString("iEmpID")%>&nbsp;</td>
    <td><%=rsSelectRecord.getString("sStaffName")%>&nbsp;</td>
    <td><%=rsSelectRecord.getString("sStaffDept")%>&nbsp;</td>
    <td><%=rsSelectRecord.getString("iStaffPhone")%>&nbsp;</td>
    <td><%=rsSelectRecord.getString("sAddress")%>&nbsp;</td>
  </tr>
  <%
   cnt++;   /// increment of counter
  } /// End of while loop
  %>
</table>

</body>
</html>
<%
try{
          if(psSelectRecord!=null)
          {
            psSelectRecord.close();
          }
          if(rsSelectRecord!=null)
          {
            rsSelectRecord.close();
          }
          
          
          if(conn!=null)
          {
           conn.close();
          }
        }
        catch(Exception e)
        {
          e.printStackTrace(); 
        }
%>

Output of Right Join

iEmpID sStaffName sStaffDept iStaffPhone sAddress
Vikas  Marketing  888888888  Bor Ghuguloni Gaon 
MANUEL GANAK  Marketing  2334937  Pandhua Gaon 
Uma  Marketing  1178493753  street 4, japan 
Kula Saikia  Marketing  1234536348  gali 45, noida, Uttar pradesh 
Smriti Sahu  Account  237452342  flat 110, New apart colony, New delhi 
Sesmah Robidas  HR  342345642  Flat A12, Singapore 
10  Fagoo Urang  HR  342451452  Holi colony, tamil nadu 

Example of join in SQL

 

 

     
 
If You Like this Article, share this

 
 
     
   
     
     
   
     
     
     
 
  Privacy Policy