C C++ Java Data Structure Python JavaScript CSS Ajax PL/SQL PHP Puzzles C programs C++ programs Java programs

Home » Java programming language

How to search record by a field (salary) using JDBC in Java?

In this article, we are going to learn how to search record by salary 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?

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

Then, we need to take inputs of the minimum and maximum values. After that we create a query to select all data from MYSQL table where salary lies between minimum and maximum value.

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 field salary 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 mimimum salary
			System.out.print("Enter Min Salary: ");
			String min=KB.readLine();
			//input maximum salary
			System.out.print("Enter Max Salary: ");
			String max=KB.readLine();

			//query to select salary between minimum and maximum values
			String q="Select * from employees where salary between "+min+" and "+max;

			// 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 Min Salary: 25000
Enter Max Salary: 37000
100,Aman,10/10/1990,Delhi,35000








COMMENTS