Program 1: Display All Employee Records Using Statement
This program retrieves all employee records from the database using the Statement interface. The executeQuery() method executes the SELECT query and returns the records through a ResultSet.
import java.sql.*;
public class DisplayEmployees {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/jdbc_demo";
String username = "root";
String password = "password";
String sql = "SELECT * FROM employee";
try {
Connection con =
DriverManager.getConnection(url, username, password);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
System.out.println("Employee Records");
System.out.println("--------------------------------");
while (rs.next()) {
System.out.println(
rs.getInt("employee_id") + " | " +
rs.getString("employee_name") + " | " +
rs.getString("department") + " | " +
rs.getDouble("salary"));
}
rs.close();
stmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" DisplayEmployees.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" DisplayEmployees
Employee Records
--------------------------------
101 | Ravi | IT | 50000.0
102 | Priya | HR | 42000.0
103 | Arjun | Finance | 50000.0
104 | Arun | null | 38000.0
105 | Mahesh | null | 200000.0Code language: JavaScript (javascript)Program 2: Insert Employee Using Statement
This program inserts a new employee record using the Statement interface. The executeUpdate() method executes the INSERT query.
import java.sql.*;
public class InsertEmployee {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/jdbc_demo";
String username = "root";
String password = "password";
String sql =
"INSERT INTO employee " +
"(employee_id, employee_name, department, salary) " +
"VALUES (106, 'Suresh', 'IT', 45000)";
try {
Connection con =
DriverManager.getConnection(url, username, password);
Statement stmt = con.createStatement();
int rows = stmt.executeUpdate(sql);
System.out.println(
rows + " employee inserted successfully.");
stmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" InsertEmployee.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" InsertEmployee
1 employee inserted successfully.
PS D:\test>Code language: CSS (css)Program 3: Update Employee Salary Using Statement
This program updates the salary of an employee using the Statement interface. Here, the salary of employee 102 is changed to 48000.
import java.sql.*;
public class UpdateEmployeeSalary {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/jdbc_demo";
String username = "root";
String password = "password";
String sql =
"UPDATE employee " +
"SET salary = 48000 " +
"WHERE employee_id = 102";
try {
Connection con =
DriverManager.getConnection(url, username, password);
Statement stmt = con.createStatement();
int rows = stmt.executeUpdate(sql);
System.out.println(
rows + " employee salary updated successfully.");
stmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" UpdateEmployeeSalary.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" UpdateEmployeeSalary
1 employee salary updated successfully.Code language: CSS (css)Program 4: Delete Employee Using Statement
This program deletes an employee using the employee ID. The executeUpdate() method executes the DELETE query.
import java.sql.*;
public class DeleteEmployee {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/jdbc_demo";
String username = "root";
String password = "password";
String sql =
"DELETE FROM employee " +
"WHERE employee_id = 106";
try {
Connection con =
DriverManager.getConnection(url, username, password);
Statement stmt = con.createStatement();
int rows = stmt.executeUpdate(sql);
if (rows > 0) {
System.out.println(
"Employee deleted successfully.");
} else {
System.out.println(
"Employee not found.");
}
stmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" DeleteEmployee.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" DeleteEmployee
Employee deleted successfully.Code language: CSS (css)Program 5: Search Employee by ID Using PreparedStatement
This program searches for an employee using an employee ID entered by the user. The ? placeholder is replaced using the setInt() method.
import java.sql.*;
import java.util.Scanner;
public class SearchEmployee {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/jdbc_demo";
String username = "root";
String password = "password";
Scanner sc = new Scanner(System.in);
System.out.print("Enter employee ID: ");
int id = sc.nextInt();
String sql =
"SELECT * FROM employee " +
"WHERE employee_id = ?";
try {
Connection con =
DriverManager.getConnection(url, username, password);
PreparedStatement pstmt =
con.prepareStatement(sql);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
System.out.println(
"Employee ID: " +
rs.getInt("employee_id"));
System.out.println(
"Employee Name: " +
rs.getString("employee_name"));
System.out.println(
"Department: " +
rs.getString("department"));
System.out.println(
"Salary: " +
rs.getDouble("salary"));
} else {
System.out.println("Employee not found.");
}
rs.close();
pstmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
sc.close();
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" SearchEmployee.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" SearchEmployee
Enter employee ID: 105
Employee ID: 105
Employee Name: Mahesh
Department: null
Salary: 200000.0Code language: CSS (css)Program 6: Insert Employee Using PreparedStatement
This program inserts employee details entered by the user. The setInt(), setString(), and setDouble() methods assign values to the parameters.
import java.sql.*;
import java.util.Scanner;
public class PreparedInsertEmployee {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/jdbc_demo";
String username = "root";
String password = "password";
Scanner sc = new Scanner(System.in);
System.out.print("Enter employee ID: ");
int id = sc.nextInt();
sc.nextLine();
System.out.print("Enter employee name: ");
String name = sc.nextLine();
System.out.print("Enter department: ");
String department = sc.nextLine();
System.out.print("Enter salary: ");
double salary = sc.nextDouble();
String sql =
"INSERT INTO employee " +
"(employee_id, employee_name, department, salary) " +
"VALUES (?, ?, ?, ?)";
try {
Connection con =
DriverManager.getConnection(url, username, password);
PreparedStatement pstmt =
con.prepareStatement(sql);
pstmt.setInt(1, id);
pstmt.setString(2, name);
pstmt.setString(3, department);
pstmt.setDouble(4, salary);
int rows = pstmt.executeUpdate();
System.out.println(
rows + " employee inserted successfully.");
pstmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
sc.close();
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" PreparedInsertEmployee.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" PreparedInsertEmployee
Enter employee ID: 106
Enter employee name: Chakrapani
Enter department: IT
Enter salary: 200000
1 employee inserted successfully.Code language: CSS (css)Program 7: Update Employee Department Using PreparedStatement
This program updates the department of an employee using parameterized SQL. The department and employee ID are supplied using setString() and setInt().
import java.sql.*;
import java.util.Scanner;
public class PreparedUpdateDepartment {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/jdbc_demo";
String username = "root";
String password = "password";
Scanner sc = new Scanner(System.in);
System.out.print("Enter employee ID: ");
int id = sc.nextInt();
sc.nextLine();
System.out.print("Enter new department: ");
String department = sc.nextLine();
String sql =
"UPDATE employee " +
"SET department = ? " +
"WHERE employee_id = ?";
try {
Connection con =
DriverManager.getConnection(url, username, password);
PreparedStatement pstmt =
con.prepareStatement(sql);
pstmt.setString(1, department);
pstmt.setInt(2, id);
int rows = pstmt.executeUpdate();
if (rows > 0) {
System.out.println(
"Department updated successfully.");
} else {
System.out.println(
"Employee not found.");
}
pstmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
sc.close();
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" PreparedUpdateDepartment.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" PreparedUpdateDepartment
Enter employee ID: 106
Enter new department: Finance
Department updated successfully.Code language: JavaScript (javascript)Program 8: Delete Employee Using PreparedStatement
This program deletes an employee based on the employee ID entered by the user. The setInt() method supplies the employee ID to the parameterized DELETE query.
import java.sql.*;
import java.util.Scanner;
public class PreparedDeleteEmployee {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/jdbc_demo";
String username = "root";
String password = "password";
Scanner sc = new Scanner(System.in);
System.out.print("Enter employee ID to delete: ");
int id = sc.nextInt();
String sql =
"DELETE FROM employee " +
"WHERE employee_id = ?";
try {
Connection con =
DriverManager.getConnection(url, username, password);
PreparedStatement pstmt =
con.prepareStatement(sql);
pstmt.setInt(1, id);
int rows = pstmt.executeUpdate();
if (rows > 0) {
System.out.println(
"Employee deleted successfully.");
} else {
System.out.println(
"Employee not found.");
}
pstmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
sc.close();
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" PreparedDeleteEmployee.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" PreparedDeleteEmployee
Enter employee ID to delete: 103
Employee deleted successfully.Code language: JavaScript (javascript)Program 9: Search Employees by Department Using PreparedStatement
This program retrieves all employees belonging to a particular department. The department entered by the user is passed to the SQL query using setString().
import java.sql.*;
import java.util.Scanner;
public class SearchByDepartment {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/jdbc_demo";
String username = "root";
String password = "password";
Scanner sc = new Scanner(System.in);
System.out.print("Enter department: ");
String department = sc.nextLine();
String sql =
"SELECT employee_id, employee_name, salary " +
"FROM employee " +
"WHERE department = ?";
try {
Connection con =
DriverManager.getConnection(url, username, password);
PreparedStatement pstmt =
con.prepareStatement(sql);
pstmt.setString(1, department);
ResultSet rs = pstmt.executeQuery();
boolean found = false;
while (rs.next()) {
found = true;
System.out.println(
rs.getInt("employee_id") + " | " +
rs.getString("employee_name") + " | " +
rs.getDouble("salary"));
}
if (!found) {
System.out.println(
"No employees found in this department.");
}
rs.close();
pstmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
sc.close();
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" SearchByDepartment.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" SearchByDepartment
Enter department: Finance
106 | Chakrapani | 200000.0Code language: JavaScript (javascript)