Contact Us
   
   
 
   
   
 
     
 
 
JSP Tutorial
JDBC Tutorial
 
Book Must Read
 
 

JDBC Group By

 
 
Print   Email
 
     
 

Group by is used to grouping particular data in table. This is useful when we need to categorizes format of presentation of records. 

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 

Example of Group by in JDBC

 
groupBy.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 count(*) as cnt, sStaffName, sStaffDept" +
                                 " FROM staff_register group by sStaffDept";
                           
    psSelectRecord=conn.prepareStatement(sqlSelectRecord);
    rsSelectRecord=psSelectRecord.executeQuery();
%>
<html>
<head>
<title>JDBC group by</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>cnt</th>
    <th>sStaffName</th>
    <th>sStaffDept</th>
  </tr>
  <%
  int cnt=1;
  while(rsSelectRecord.next())
  {
  %>
  <tr>
    <td><%=rsSelectRecord.getString("cnt")%>&nbsp;</td>
    <td><%=rsSelectRecord.getString("sStaffName")%>&nbsp;</td>
    <td><%=rsSelectRecord.getString("sStaffDept")%>&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 Group By

cnt sStaffName sStaffDept
Smriti Sahu  Account 
Sesmah Robidas  HR 
Amit  IT 
Vikas  Marketing 
Anita Rabidas  Production 

 

 

 

     
 
If You Like this Article, share this

 
 
     
   
     
     
   
     
     
     
 
  Privacy Policy