Home » Java programming language

How to search record with more than one field using JDBC in Java?

In this article, we are going to learn how to search record with more than one field in MYSQL table using JDBC through java program?
Submitted by Manu Jemini, on October 24, 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?
9. Search record by a pattern using JDBC in Java.
10. Count number of records available in a MYSQL table using JDBC in java?

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

Then, we need to take two inputs of the fields on which we want to search records. After that we create a query to select all data from MYSQL table where the record satisfies both the conditions.

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 multiple fields (ID and Name) using JDBC

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

public class SearchBySalary {
	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 id
			System.out.print("Enter id: ");
			String id=KB.readLine();
			//input name
			System.out.print("Enter name: ");
			String name=KB.readLine();

			//query to select salary between minimum and maximum values
			String q="Select * from employees where empname='"+name+"' and empid='"+id+"'";

			// to execute query
			ResultSet rs=smt.executeQuery(q);
			//to print the resultset on 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)

Enter id: 100
Enter name: Aman
100,Aman,10/10/1990,Delhi,35000