Contact Us
   
   
 
   
   
 
     
 
 
JSP Tutorial
JDBC Tutorial
 
Book Must Read
 
 

JDBC SubQuery

 
 
Print   Email
 
     
 

SubQuery is query that nested inside of other query like Select, insert, update, delete. Output of subquery effect on main query.

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 Sub query in JDBC

 
subQuery.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"+
                        " FROM staff_register s " +
                           " where s.iEmpID in(select iEmpID from staff_details)";
                           
    psSelectRecord=conn.prepareStatement(sqlSelectRecord);
    rsSelectRecord=psSelectRecord.executeQuery();
 
%>
<html>
<head>
<title>SubQuery  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>
  </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>
  </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 SubQuery

iEmpID sStaffName sStaffDept iStaffPhone
Vikas  Marketing  888888888 
MANUEL GANAK  Marketing  2334937 
Kula Saikia  Marketing  1234536348 
Uma  Marketing  1178493753 
Smriti Sahu  Account  237452342 
Sesmah Robidas  HR  342345642 
10  Fagoo Urang  HR  342451452 

 

 

 

     
 
If You Like this Article, share this

 
 
     
   
     
     
   
     
     
     
 
  Privacy Policy