Home » Java programming language

Search record by a pattern using JDBC in Java

In this article, we are going to learn how to search record by patterns in MYSQL table using JDBC through java program?
Submitted by Manu Jemini, on October 19, 2017

Prerequisite/recommended:

1. How to create a table using JDBC in Java?
2. How to insert records through JDBC in Java?
3. How to display all records using JDBC in Java?
4. How to display particular record by a field using JDBC in Java?
5. How to delete a particular record using JDBC in Java?
6. How to edit a record using JDBC in Java?
7. Insert a record with PreparedStatement using JDBC in Java?
8. How to search record by a field (salary) using JDBC in Java?

Create an object of Connection class and connect to the database.

Then, we need to take input of a pattern for name on which we want to search the record. After that we create a query to select all data from MYSQL table where name like input pattern.

Then, we execute our query by using executeQuery () method, which is a method of Statement class and print the result with the help of ResultSet.

Database details:

• Hostname: localhost
• Port number: 3306
• Username: root
• Password: 123
• Database name: demo
• Table name: employees

Java program to search record by a pattern using JDBC

import java.io.DataInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class SearchByPattern {
	public static void main(String[] args) {
		try{
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			
			//serverhost = localhost, port=3306, username=root, password=123
			Connection cn=DriverManager.getConnection("jdbc:mysql://localhost:3306/demo","root","123");
			
			Statement smt=cn.createStatement();
			
			//creating object of DataInputStream
			DataInputStream KB=new DataInputStream(System.in);
			
			//input pattern to search
			System.out.print("Enter Pattern:");
			String pat=KB.readLine();
			
			//query to select data similar to the input patern
			String q="Select * from employees where empname like '%"+pat+"%'";
			
			//to execute query
			ResultSet rs=smt.executeQuery(q);
			//to print result in console
			if(rs.next())
			{
				do{
					System.out.println(rs.getString(1)+","+rs.getString(2)+","+rs.getString(3)+","+rs.getString(4)+","+rs.getString(5));
				}while(rs.next());
			}
			else
			{
				System.out.println("Record Not Found...");
			}
			cn.close();
		}
		catch(Exception e)
		{
			System.out.println(e);
		}
	}
}

Output (In Console)

Output (In Console)
Enter Pattern: an
100,Aman,10/10/1990,Delhi,35000