Contact Us
   
   
 
   
   
 
     
 
 
JSP Tutorial
JDBC Tutorial
 
 
 

JDBC Query

AddThis Social Bookmark Button          AddThis Feed Button 		 
 
Print   Email
 
     
 

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

We have a table staff_register in database

iEmpID sStaffName sStaffDept iStaffPhone
1  Amit  IT  45678 
2  Vikas  Marketing  888888888 
3  MANUEL GANAK  Marketing  2334937 
4  Kula Saikia  Marketing  1234536348 
5  Uma  Marketing  1178493753 
6  Nipon  IT  2229849586 
7  Smriti Sahu  Account  237452342 
8  Sukumar Das  Account  435217243 
9  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 where query in JDBC

Where clause give filtration of records when fetch from database and limit to particular specified records according to our requirement.
 
whereClause.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 * FROM staff_register where sStaffDept=?";
        psSelectRecord=conn.prepareStatement(sqlSelectRecord);
        psSelectRecord.setString(1,"Marketing");
        rsSelectRecord=psSelectRecord.executeQuery();
 
%>
<html>
<head>
<title>where clause 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 this where clause

iEmpID sStaffName sStaffDept iStaffPhone
2  Vikas  Marketing  888888888 
3  MANUEL GANAK  Marketing  2334937 
4  Kula Saikia  Marketing  1234536348 
5  Uma  Marketing  1178493753 

Example of operator query in JDBC

Operator provide us conditions, should be greater than (>), or less than (<), or not equal to (<>), or equals to (<=>)

We are using same table with greater than condition on iiEmpID

operatorJDBC.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 * FROM staff_register where iEmpID>6";
    //sqlSelectRecord ="SELECT * FROM staff_register where iEmpID>6"; //example 1
    //sqlSelectRecord ="SELECT * FROM staff_register where iEmpID<6"; //example 2
    //sqlSelectRecord ="SELECT * FROM staff_register where iEmpID<>6"; //example 3
    //sqlSelectRecord ="SELECT * FROM staff_register where iEmpID<=>6";//example 4
    
    // to Run example 1 or 2 comment all except which you want to execute 
    
    psSelectRecord=conn.prepareStatement(sqlSelectRecord);
    rsSelectRecord=psSelectRecord.executeQuery();
 
%>
<html>
<head>
<title>operator clause 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 query

Output of Example 1 Query

iEmpID sStaffName sStaffDept iStaffPhone
7  Smriti Sahu  Account  237452342 
8  Sukumar Das  Account  435217243 
9  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 

Output of Example 2 Query

iEmpID sStaffName sStaffDept iStaffPhone
1  Amit  IT  45678 
2  Vikas  Marketing  888888888 
3  MANUEL GANAK  Marketing  2334937 
4  Kula Saikia  Marketing  1234536348 
5  Uma  Marketing  1178493753 

Output of Example 3 Query

iEmpID sStaffName sStaffDept iStaffPhone
1  Amit  IT  45678 
2  Vikas  Marketing  888888888 
3  MANUEL GANAK  Marketing  2334937 
4  Kula Saikia  Marketing  1234536348 
5  Uma  Marketing  1178493753 
7  Smriti Sahu  Account  237452342 
8  Sukumar Das  Account  435217243 
9  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 

Output of Example 4 Query

iEmpID sStaffName sStaffDept iStaffPhone
6  Nipon  IT  2229849586 

Example of JOIN query in JDBC

Join provide us combined result from two or more different tables.
JOIN

Inner JOIN
   Provides only matched rows from both tables

Outer JOIN
  Left join
All rows from left table and match rows from next table.

 Right join
All rows from right table and match rows from next table.
Check Join query in JDBC

Example of Sub Query in JDBC

SubQuery is query that nested inside of other query like Select, insert, update, delete.
Check SubQuery in JDBC

Example of Group by Query in JDBC

Group by is used to grouping particular data in table.
Check Group by in JDBC

 

 

 
     
 
If You Like this Article, share this

 		  
     
   
     
     
   
     
     
     
 
  Privacy Policy