Question 4:

-----------------------------------------------
Employee Data Processing System
Problem Statement:
Design and implement a Java application that performs the following operations:

File Handling:
Read employee data from a file ([Link]).
Each line in the file contains details of one employee in the following format:
id, name, department, salary
Example:
1, Alice, HR, 50000
2, Bob, IT, 60000
3, Charlie, Sales, 55000

Database Operations:
Insert the employee data into a database using JDBC.
The database schema should have a table named employees with columns:
id: INT (Primary Key)
name: VARCHAR
department: VARCHAR
salary: DOUBLE.

Multithreading:

Use separate threads for the following tasks:

Thread 1: Read data from the file and store it in a collection (e.g., ArrayList).
Thread 2: Insert data from the collection into the database.
Thread 3: Process the employee data to calculate the average salary of all
employees.
Exception Handling:

Handle exceptions that may occur during:

File reading/writing.
Database connection and queries.
Thread synchronization issues.

Collections:
Store the employee data in a collection (ArrayList or HashMap) for further
processing.
Use this collection to calculate the average salary of employees.

===================================================================================
===================================================================================
=

import [Link].*;
import [Link].*;
import [Link].*;
import [Link].*;

class Employee {
int id;
String name;
String department;
double salary;

Employee(int id, String name, String department, double salary) {

[Link] = id;
 [Link] = name;
[Link] = department;
[Link] = salary;
}
}

public class EmployeeDataProcessing {

private static final String DB_URL =

"jdbc:mysql://localhost:3306/employees_db";
private static final String DB_USER = "root";
private static final String DB_PASSWORD = "password";
private static final String FILE_NAME = "[Link]";

private static final List<Employee> employeeList = new ArrayList<>();

private static final Lock lock = new ReentrantLock();

public static void main(String[] args) {

Thread fileReaderThread = new Thread(new FileReaderTask());
Thread databaseInserterThread = new Thread(new DatabaseInserterTask());
Thread salaryProcessorThread = new Thread(new SalaryProcessorTask());

[Link]();
try {
[Link]();
} catch (InterruptedException e) {
[Link]("Error waiting for FileReaderThread: " +
[Link]());
}

[Link]();
[Link]();

try {
[Link]();
[Link]();
} catch (InterruptedException e) {
[Link]("Error waiting for threads: " + [Link]());
}
}

static class FileReaderTask implements Runnable {

@Override
public void run() {
try (BufferedReader br = new BufferedReader(new FileReader(FILE_NAME)))
{
String line;
while ((line = [Link]()) != null) {
String[] details = [Link](",");
if ([Link] == 4) {
int id = [Link](details[0].trim());
String name = details[1].trim();
String department = details[2].trim();
double salary = [Link](details[3].trim());

[Link]();
try {
[Link](new Employee(id, name, department,
salary));
 } finally {
[Link]();
}
}
}
[Link]("File reading completed. Data loaded into the
collection.");
} catch (IOException e) {
[Link]("Error reading file: " + [Link]());
}
}
}

static class DatabaseInserterTask implements Runnable {

@Override
public void run() {
try (Connection connection = [Link](DB_URL,
DB_USER, DB_PASSWORD);
PreparedStatement statement = [Link]("INSERT
INTO employees (id, name, department, salary) VALUES (?, ?, ?, ?)");) {

[Link]();
try {
for (Employee emp : employeeList) {
[Link](1, [Link]);
[Link](2, [Link]);
[Link](3, [Link]);
[Link](4, [Link]);
[Link]();
}
[Link]();
[Link]("Data inserted into the database
successfully.");
} finally {
[Link]();
}

} catch (SQLException e) {
[Link]("Error inserting data into database: " +
[Link]());
}
}
}

static class SalaryProcessorTask implements Runnable {

@Override
public void run() {
[Link]();
try {
double totalSalary = 0;
for (Employee emp : employeeList) {
totalSalary += [Link];
}
double averageSalary = [Link]() ? 0 : totalSalary /
[Link]();
[Link]("Average salary of employees: " +
averageSalary);
} finally {
[Link]();
 }
}
}
}

Database Creation:-
CREATE DATABASE employees_db;
USE employees_db;
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(50),
salary DOUBLE
);

Sample Output:-
File reading completed. Data loaded into the collection.
Data inserted into the database successfully.
Average salary of employees: 55000.0

==============================================================================
Test Cases:

Test Case 1: Valid Input Data

Description: Test with a valid CSV file containing employee details.

Input:
[Link] content:
1, Alice, HR, 50000
2, Bob, IT, 60000
3, Charlie, Sales, 55000

Expected Output:
Console Output:
File reading completed. Data loaded into the collection.
Data inserted into the database successfully.
Average salary of employees: 55000.0

DataBase output:
SELECT * FROM employees;
Result:
id name department salary
1 Alice HR 50000.0
2 Bob IT 60000.0
3 Charlie Sales 55000.0

===================================================================================
==

Test Case 2: Empty File

Description: Test with an empty [Link] file.

Input:
[Link] content: (Empty)
Expected Output:
Console Output:
File reading completed. Data loaded into the collection.
Data inserted into the database successfully.
Average salary of employees: 0.0

Database output:
No records inserted.

==============================================================================
Test Case 3: Missing or Malformed Data
Description: Test with a CSV file containing missing or incorrect fields.

Input:
[Link] content:
1, Alice, HR, 50000
2, , IT, 60000
3, Charlie, Sales

Expected Output:
Console Output:
File reading completed. Data loaded into the collection.
Data inserted into the database successfully.
Average salary of employees: 50000.0

Database output:
SELECT * FROM employees;
Result:
id name department salary
1 Alice HR 50000.0

Notes: Rows with missing fields are skipped.

===============================================================================
Test Case 4: Duplicate Employee IDs
Description: Test with duplicate IDs in the CSV file.

Input:
[Link] content:
1, Alice, HR, 50000
1, Bob, IT, 60000

Expected Output:
Console Output:
File reading completed. Data loaded into the collection.
Error inserting data into database: Duplicate entry '1' for key 'PRIMARY'
Average salary of employees: 50000.0

Database output:
SELECT * FROM employees;
Result:
id name department salary
1 Alice HR 50000.0

===================================================================================
=
Test Case 5: Non-Numeric Salary
Description: Test with invalid (non-numeric) salary values.
Input:
[Link] content:
1, Alice, HR, 50000
2, Bob, IT, sixty_thousand
3, Charlie, Sales, 55000

Expected Output:
Console Output:
Error reading file: For input string: \"sixty_thousand\"
File reading completed. Data loaded into the collection.
Data inserted into the database successfully.
Average salary of employees: 52500.0

Database output:
SELECT * FROM employees;
Result:
id name department salary
1 Alice HR 50000.0
3 Charlie Sales 55000.0

===================================================================================
===
Test Case 6: Large Data Set
Description: Test with a large CSV file containing 10,000+ employee records.

Input:
[Link] content:
1, Alice, HR, 50000
2, Bob, IT, 60000
...
10000, Zed, Marketing, 75000

Expected Output:
Console Output:
File reading completed. Data loaded into the collection.
Data inserted into the database successfully.
Average salary of employees: <Calculated Average>

Database output:
SELECT COUNT(*) FROM employees;
10000 rows inserted