JDBC - WHERE Clause

Previous
Next

This chapter provides examples on how to select records from a table using JDBC application. This would add additional conditions using WHERE clause while selecting records from the table. Before executing the following example, make sure you have the following in place −

  • To execute the following example you can replace the username and password with your actual user name and password.

  • Your MySQL or whatever database you are using, is up and running.

Required Steps

The following steps are required to create a new Database using JDBC application −

  • Import the packages − Requires that you include the packages containing the JDBC classes needed for the database programming. Most often, using import java.sql.* will suffice.

  • Register the JDBC driver − Requires that you initialize a driver so you can open a communications channel with the database.

  • Open a connection − Requires using the DriverManager.getConnection() method to create a Connection object, which represents a physical connection with a database server.

  • Execute a query − Requires using an object of type Statement for building and submitting an SQL statement to fetch records from a table, which meet the given condition. This Query makes use of the WHERE clause to select records.

  • Clean up the environment − try with resources automatically closes the resources.

Example: Selecting Records from a Table on Given Condition

In this example, we've four static strings containing a dababase connection url, username, password and a SELECT query. Now using DriverManager.getConnection() method, we've prepared a database connection. Once connection is prepared, we've prepared a Statement object using createStatement() method. As next step, We've prepared and executed the SELECT query on table REGISTRATION by calling statement.executeQuery() method. All records are fetched and stored in a ResultSet object. ResultSet is iterated and all records are printed.

Now another query with WHERE clause is fired in similar fashion. This query limits the records for only id which are greater than 101. Records are fetched and printed.

Copy and paste the following example in JDBCExample.java, compile and run as follows −

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCExample {
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";
   static final String QUERY = "SELECT id, first, last, age FROM Registration";

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement();) {		      
         System.out.println("Fetching records without condition...");
         ResultSet rs = stmt.executeQuery(QUERY);
         while(rs.next()){
            //Display values
            System.out.print("ID: " + rs.getInt("id"));
            System.out.print(", Age: " + rs.getInt("age"));
            System.out.print(", First: " + rs.getString("first"));
            System.out.println(", Last: " + rs.getString("last"));
         }

         // Select all records having ID equal or greater than 101
         System.out.println("Fetching records with condition...");
         String sql = "SELECT id, first, last, age FROM Registration" +
            " WHERE id >= 101 ";
         rs = stmt.executeQuery(sql);

         while(rs.next()){
            //Display values
            System.out.print("ID: " + rs.getInt("id"));
            System.out.print(", Age: " + rs.getInt("age"));
            System.out.print(", First: " + rs.getString("first"));
            System.out.println(", Last: " + rs.getString("last"));
         }
         rs.close();
      } catch (SQLException e) {
         e.printStackTrace();
      } 
   }
}

Output

Now let us compile the above example as follows −

C:\>javac JDBCExample.java
C:\>

When you run JDBCExample, it produces the following result −

C:\>java JDBCExample
Fetching records without condition...
ID: 100, Age: 30, First: Zara, Last: Ali
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 28, First: Sumit, Last: Mittal
Fetching records with condition...
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 28, First: Sumit, Last: Mittal
C:\>

Example: Selecting Record from a Table on Multiple Conditions

In this example, we've three static strings containing a dababase connection url, username and password. Now using DriverManager.getConnection() method, we've prepared a database connection. Once connection is prepared, we've prepared a Statement object using createStatement() method. As next step, We've prepared and executed the SELECT query on table employees by calling statement.executeQuery() method. This query limits the records for ids which are greater than 1 and employee age being greater than 20. Records are fetched and printed.

Copy and paste the following example in JDBCExample.java, compile and run as follows −

import java.sql.*;

// This class demonstrates use of multiple conditions on WHERE
public class JDBCExample {

   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "root";
   static final String PASS = "guest123";

   public static void main(String args[]) {
      try{
         Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement();
         String sel_qry = "select id, first, last from employees where id > 1 and age > 20";
         ResultSet rs = stmt.executeQuery(sel_qry);

         System.out.println("Displaying records depending on the conditions set in WHERE clause");
         System.out.println("-------------------------------------------------------");
         while(rs.next()){
            System.out.print("id: " + rs.getInt(1));
            System.out.print(", First: " + rs.getString(2));
            System.out.println(", Last: " + rs.getString(3));
         }

         System.out.println("--------------------------------------------");

         rs.close();
         stmt.close();
         conn.close();

      }catch( SQLException e){
         e.printStackTrace();
      }
   }
}

Output

Now let us compile the above example as follows −

C:\>javac JDBCExample.java
C:\>

When you run JDBCExample, it produces the following result −

C:\>java JDBCExample
Displaying records depending on the conditions set in WHERE clause
-------------------------------------------------------
id: 2, First: Mahnaz, Last: Fatma
id: 4, First: Sumit, Last: Mittal
id: 21, First: Jeevan, Last: Rao
id: 22, First: Aditya, Last: Chaube
id: 25, First: Jeevan, Last: Rao
id: 26, First: Aditya, Last: Chaube
id: 34, First: Ahmed, Last: Ali
id: 35, First: Raksha, Last: Agarwal
--------------------------------------------

C:\>

Example: Updating Record of a Table based on Given Condition

In this example, we've three static strings containing a dababase connection url, username and password. Now using DriverManager.getConnection() method, we've prepared a database connection. Once connection is prepared, we've prepared a Statement object using createStatement() method. As next step, We've prepared and executed an UPDATE query on table employees by calling statement.executeUpdate() method. This query updates the first name and last name of the record where id is 22.

Then we executed the SELECT query on table employees by calling statement.executeQuery() method to select a particular record. Result is stored in a ResultSet object. ResultSet is iterated and record is printed to show the updated value.

Records are fetched and printed.

Copy and paste the following example in JDBCExample.java, compile and run as follows −

import java.sql.*;

// This class demonstrates use of WHERE clause in an UPDATE statement
public class JDBCExample {

   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "root";
   static final String PASS = "guest123";

   public static void main(String args[]) {
      try{
         Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement();
         String upd_qry = "update employees set first='Dinesh', last='Kumar' where id=22";
         stmt.executeUpdate(upd_qry);
         ResultSet rs = stmt.executeQuery("select id, first, last from employees where id=22");
         System.out.println("Displaying records depending on the conditions set in WHERE clause");
         System.out.println("-------------------------------------------------------");
         while(rs.next()){
            System.out.print("id: " + rs.getInt(1));
            System.out.print(", First: " + rs.getString(2));
            System.out.println(", Last: " + rs.getString(3));
         }

         System.out.println("--------------------------------------------");

         rs.close();
         stmt.close();
         conn.close();
      }catch( SQLException e){
         e.printStackTrace();
      }
   }
}

Output

Now let us compile the above example as follows −

C:\>javac JDBCExample.java
C:\>

When you run JDBCExample, it produces the following result −

C:\>java JDBCExample
Displaying records depending on the conditions set in WHERE clause
-------------------------------------------------------
id: 22, First: Dinesh, Last: Kumar
--------------------------------------------


C:\>
Print Page
Previous
Next
Advertisements