JDBC Where Clause Demo
VIDEO
Enjoying Java & Database Tutorials?
Subscribe to Ram N Java on YouTube for beginner-friendly coding lessons and hands-on demos!
🔔 Subscribe to Channel
How to Filter Database Records with the WHERE Clause in Java JDBC
In real-world applications, you rarely need to pull every single record stored in a database table. Filtering records based on specific criteria—such as finding a user by ID, email, or status—is a fundamental task. In SQL, this is accomplished using the WHERE clause. Using JDBC (Java Database Connectivity) , you can dynamically apply filter criteria to query only the data you need.
The SQL WHERE Clause
The WHERE clause specifies conditional filters for your SQL statements:
SELECT id, first_name, last_name, age FROM students WHERE age >= 18;
Only rows satisfying the specified condition are returned in the database response.
Step-by-Step Java Implementation
Follow these standard steps to filter data using JDBC:
Connect to Database: Obtain a connection via DriverManager.getConnection().Create Parameterized Query: Formulate a SQL query with a placeholder (?) to prevent SQL injection, e.g., WHERE id = ?.Set Query Parameters: Bind input variables using methods like setInt() or setString() on your PreparedStatement.Execute Query: Run executeQuery() to retrieve the filtered rows into a ResultSet.Process and Clean Up: Loop through the matching results using resultSet.next() and close resources properly using try-with-resources.
Watch More JDBC Tutorials
Click here to watch in Youtube : https://www.youtube.com/watch?v=rqjmHnLKXOQ Click the below Image to Enlarge
JDBCWhereClauseDemo Project Dir Structure
JDBC Where Clause Demo
JDBC Where Clause Demo
JDBCWhereClauseDemo.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class JDBCWhereClauseDemo
{
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver" ;
static final String DB_URL = "jdbc:mysql://localhost:3306/world" ;
// Database credentials
static final String USERNAME = "root" ;
static final String PASSWORD = "root" ;
public static void main (String[] args )
{
JDBCWhereClauseDemo jdbcWhereClauseDemo = new JDBCWhereClauseDemo();
Scanner scanner = new Scanner(System. in);
System. out. print("Enter countryCodeValue :" );
while (scanner. hasNext())
{
String countryCodeValue = scanner. nextLine();
if (countryCodeValue. equalsIgnoreCase("Exit" ))
{
break ;
}
jdbcWhereClauseDemo. getCityInformation(countryCodeValue);
System. out. print("\n Enter countryCodeValue :" );
}
scanner. close();
}
/*
* Get City Information based on Country Code
*/
private void getCityInformation (String countryCodeValue )
{
Connection connection = null ;
PreparedStatement preparedStatement = null ;
try
{
/*
* Register the JDBC driver in DriverManager
*/
Class. forName(JDBC_DRIVER );
/*
* Establish connection to the Database using DriverManager
*/
connection = DriverManager
.getConnection(DB_URL , USERNAME , PASSWORD );
String sql = "select * from city where countrycode=?" ;
/*
* Execute the query
*/
preparedStatement = connection. prepareStatement(sql);
preparedStatement. setString(1 , countryCodeValue);
ResultSet rs = preparedStatement. executeQuery();
while (rs. next())
{
int id = rs. getInt(1 );
String name = rs. getString(2 );
String countryCode = rs. getString(3 );
String district = rs. getString(4 );
int population = rs. getInt(5 );
/*
* Display values
*/
System. out. print("ID: " + id);
System. out. print(", Name: " + name);
System. out. print(", CountryCode: " + countryCode);
System. out. print(", District: " + district);
System. out. println(", Population: " + population);
}
rs. close();
}
catch (SQLException se)
{
/*
* Handle errors for JDBC
*/
se. printStackTrace();
}
catch (ClassNotFoundException e)
{
/*
* Handle errors for Class.forName
*/
e. printStackTrace();
}
catch (Exception e)
{
e. printStackTrace();
}
finally
{
/*
* finally block used to close resources
*/
try
{
if (preparedStatement != null )
{
preparedStatement. close();
}
}
catch (SQLException sqlException)
{
sqlException. printStackTrace();
}
try
{
if (connection != null )
{
connection. close();
}
}
catch (SQLException sqlException)
{
sqlException. printStackTrace();
}
}
}
}
Output
Enter countryCodeValue : IND
ID : 1024 , Name: Mumbai (Bombay), CountryCode: IND , District: Maharashtra, Population: 10500000
ID : 1025 , Name: Delhi, CountryCode: IND , District: Delhi, Population: 7206704
ID : 1026 , Name: Calcutta [Kolkata], CountryCode: IND , District: West Bengali, Population: 4399819
ID : 1027 , Name: Chennai (Madras), CountryCode: IND , District: Tamil Nadu, Population: 3841396
ID : 1028 , Name: Hyderabad, CountryCode: IND , District: Andhra Pradesh, Population: 2964638
ID : 1029 , Name: Ahmedabad, CountryCode: IND , District: Gujarat, Population: 2876710
ID : 1030 , Name: Bangalore, CountryCode: IND , District: Karnataka, Population: 2660088
----
----
Enter countryCodeValue : AFG
ID : 1 , Name: Kabul, CountryCode: AFG , District: Kabol, Population: 1780000
ID : 2 , Name: Qandahar, CountryCode: AFG , District: Qandahar, Population: 237500
ID : 3 , Name: Herat, CountryCode: AFG , District: Herat, Population: 186800
ID : 4 , Name: Mazar- e- Sharif, CountryCode: AFG , District: Balkh, Population: 127800
Enter countryCodeValue : USA
ID : 3793 , Name: New York, CountryCode: USA , District: New York, Population: 8008278
ID : 3794 , Name: Los Angeles, CountryCode: USA , District: California, Population: 3694820
ID : 3795 , Name: Chicago, CountryCode: USA , District: Illinois, Population: 2896016
ID : 3796 , Name: Houston, CountryCode: USA , District: Texas, Population: 1953631
ID : 3797 , Name: Philadelphia, CountryCode: USA , District: Pennsylvania, Population: 1517550
ID : 3798 , Name: Phoenix, CountryCode: USA , District: Arizona, Population: 1321045
ID : 3799 , Name: San Diego, CountryCode: USA , District: California, Population: 1223400
ID : 3800 , Name: Dallas, CountryCode: USA , District: Texas, Population: 1188580
ID : 3801 , Name: San Antonio, CountryCode: USA , District: Texas, Population: 1144646
ID : 3802 , Name: Detroit, CountryCode: USA , District: Michigan, Population: 951270
---
---
---
Enter countryCodeValue : exit
No comments:
Post a Comment