JDBC Like 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 Use the SQL LIKE Clause in Java JDBC
When searching for records in a database, you often don't have the exact text match. Instead, you need pattern-matching capabilities, such as finding all names starting with a specific letter or containing a keyword. In SQL, the LIKE operator is used for pattern matching, and with JDBC (Java Database Connectivity) , you can easily query dynamic patterns from your Java code.
Understanding SQL LIKE Wildcards
The SQL LIKE clause relies on two fundamental wildcards:
Percent sign (%): Represents zero, one, or multiple characters.Underscore (_): Represents a single character.
SELECT id, first_name, email FROM students WHERE first_name LIKE 'J%';
The example above matches names like John , Jane , or Jack .
Step-by-Step Implementation with PreparedStatement
When running pattern matching in Java, using a PreparedStatement prevents SQL injection:
Establish Connection: Connect to the database using DriverManager.getConnection().Define Query with Placeholder: Formulate your query using a parameter placeholder, e.g., WHERE first_name LIKE ?.Bind Pattern Value: Use setString(1, "J%") to pass your search pattern safely.Execute and Process: Run executeQuery() and process the matching rows with resultSet.next().Release Resources: Close the ResultSet, PreparedStatement, and Connection objects properly.
Watch More JDBC Tutorials
Click here to watch in Youtube : https://www.youtube.com/watch?v=IwqVxVdLgsA Click the below Image to Enlarge
JDBCLikeClauseDemo Project Dir Structure
JDBC Like Clause Demo
JDBC Like Clause Demo
JDBCLikeClauseDemo.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 JDBCLikeClauseDemo
{
// 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 )
{
JDBCLikeClauseDemo jdbcLikeClauseDemo = new JDBCLikeClauseDemo();
Scanner scanner = new Scanner(System. in);
System. out. print("Enter city name Starting letters :" );
while ( scanner. hasNext() )
{
String value = scanner. nextLine();
if ( value. equalsIgnoreCase("Exit" ) )
{
break ;
}
jdbcLikeClauseDemo. getCityInformation(value);
System. out. print("\n Enter city name Starting letters :" );
}
scanner. close();
}
private void getCityInformation ( String value )
{
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 name LIKE '" + value+ "%'" ;
System. out. println("\n sql : " + sql + "\n " );
/*
* Execute the query
*/
preparedStatement = connection. prepareStatement(sql);
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("Name: " + name);
System. out. print(",ID: " + id);
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 city name Starting letters : ab
sql : select * from city WHERE name LIKE 'ab%'
Name: Abu Dhabi,ID : 65 , CountryCode: ARE , District: Abu Dhabi, Population: 398695
Name: Abaetetuba,ID : 395 , CountryCode: BRA , District: Pará, Population: 111258
Name: Aberdeen,ID : 478 , CountryCode: GBR , District: Scotland, Population: 213070
Name: Abohar,ID : 1309 , CountryCode: IND , District: Punjab, Population: 107163
Name: Abadan,ID : 1404 , CountryCode: IRN , District: Khuzestan, Population: 206073
Name: Abiko,ID : 1703 , CountryCode: JPN , District: Chiba, Population: 126670
Name: Abbotsford,ID : 1849 , CountryCode: CAN , District: British Colombia, Population: 105403
Name: Abeokuta,ID : 2747 , CountryCode: NGA , District: Ogun, Population: 427400
Name: Abuja,ID : 2754 , CountryCode: NGA , District: Federal Capital Dist, Population: 350100
--
--
--
Enter city name Starting letters : ba
sql : select * from city WHERE name LIKE 'ba%'
Name: Batna,ID : 39 , CountryCode: DZA , District: Batna, Population: 183377
Name: Béjaïa,ID : 45 , CountryCode: DZA , District: Béjaïa, Population: 117162
Name: Béchar,ID : 49 , CountryCode: DZA , District: Béchar, Population: 107311
Name: BahÃa Blanca,ID : 96 , CountryCode: ARG , District: Buenos Aires, Population: 239810
Name: Baku,ID : 144 , CountryCode: AZE , District: Baki, Population: 1787800
Name: Barisal,ID : 157 , CountryCode: BGD , District: Barisal, Population: 170232
Name: Banja Luka,ID : 202 , CountryCode: BIH , District: Republika Srpska, Population: 143079
Name: Bauru,ID : 263 , CountryCode: BRA , District: São Paulo, Population: 313670
Name: Barueri,ID : 305 , CountryCode: BRA , District: São Paulo, Population: 208426
-- -
-- -
Enter city name Starting letters : ban
sql : select * from city WHERE name LIKE 'ban%'
Name: Banja Luka,ID : 202 , CountryCode: BIH , District: Republika Srpska, Population: 143079
Name: Bandar Seri Begawan,ID : 538 , CountryCode: BRN , District: Brunei and Muara, Population: 21484
Name: Bani Suwayf,ID : 627 , CountryCode: EGY , District: Bani Suwayf, Population: 172032
Name: Banha,ID : 632 , CountryCode: EGY , District: al- Qalyubiya, Population: 145792
Name: Banjul,ID : 904 , CountryCode: GMB , District: Banjul, Population: 42326
Name: Bandung,ID : 941 , CountryCode: IDN , District: West Java, Population: 2429000
Name: Bandar Lampung,ID : 948 , CountryCode: IDN , District: Lampung, Population: 680332
--
--
--
Enter city name Starting letters : exit
No comments:
Post a Comment