ID : 2300031508

NAME : S.Ganesh Vamsi krishna

AOOP SKILL WEEK 12

1) Library Management System: Book Checkout and Return Objective: Build a basic Library Management
System where users can checkout and return books. Setup: Create a database named LibraryDB with
tables Books (columns: book_id, title, author, is_available) and Users (columns: user_id, name, email).
Tasks:

1. Write a JDBC program that allows users to view all available books.

2. Implement a checkoutBook() method that takes a user_id and book_id and updates the is_available
column in the Books table.

3. Implement a returnBook() method that updates is_available back to true. Challenges: Handle cases
where a book is not available for checkout or the user tries to return a book they haven't checked out.

Solution:

package week12;

import java.sql.*;
import java.util.Scanner;

public class LibraryManagementSystem {

// Database URL, username, and password

private static final String DB_URL =
"jdbc:mysql://localhost:3306/LibraryDB";
private static final String DB_USERNAME = "root";
private static final String DB_PASSWORD = "password";

public static void main(String[] args) {

try (Connection connection =
DriverManager.getConnection(DB_URL, DB_USERNAME,
DB_PASSWORD)) {
Scanner scanner = new Scanner(System.in);
 boolean exit = false;

while (!exit) {
System.out.println("\nLibrary Management
System");
System.out.println("1. View Available
Books");
System.out.println("2. Checkout Book");
System.out.println("3. Return Book");
System.out.println("4. Exit");
System.out.print("Choose an option: ");
int choice = scanner.nextInt();

switch (choice) {
case 1:
viewAvailableBooks(connection);
break;
case 2:
System.out.print("Enter User ID: ");
int userId = scanner.nextInt();
System.out.print("Enter Book ID: ");
int bookId = scanner.nextInt();
checkoutBook(connection, userId,
bookId);
break;
case 3:
System.out.print("Enter Book ID to
Return: ");
int returnBookId =
scanner.nextInt();
returnBook(connection,
returnBookId);
break;
case 4:
exit = true;
break;
default:
System.out.println("Invalid choice!
Please try again.");
 }
}

scanner.close();
} catch (SQLException e) {
e.printStackTrace();
}
}

// Method to view all available books

public static void viewAvailableBooks(Connection
connection) {
String query = "SELECT book_id, title, author FROM
Books WHERE is_available = TRUE";

try (Statement statement =

connection.createStatement();
ResultSet resultSet =
statement.executeQuery(query)) {

System.out.println("\nAvailable Books:");
while (resultSet.next()) {
System.out.printf("ID: %d, Title: %s,
Author: %s%n",
resultSet.getInt("book_id"),
resultSet.getString("title"),
resultSet.getString("author"));
}

} catch (SQLException e) {
e.printStackTrace();
}
}

// Method to checkout a book

public static void checkoutBook(Connection connection,
int userId, int bookId) {
String checkQuery = "SELECT is_available FROM Books
WHERE book_id = ?";
 String updateQuery = "UPDATE Books SET is_available
= FALSE WHERE book_id = ?";

try (PreparedStatement checkStmt =

connection.prepareStatement(checkQuery);
PreparedStatement updateStmt =
connection.prepareStatement(updateQuery)) {

// Check if the book is available

checkStmt.setInt(1, bookId);
ResultSet resultSet = checkStmt.executeQuery();

if (resultSet.next() &&
resultSet.getBoolean("is_available")) {
// Update the book status to unavailable
updateStmt.setInt(1, bookId);
int rowsAffected =
updateStmt.executeUpdate();

if (rowsAffected > 0) {
System.out.println("Book checked out
successfully!");
}
} else {
System.out.println("Book is not available
for checkout.");
}

} catch (SQLException e) {
e.printStackTrace();
}
}

// Method to return a book

public static void returnBook(Connection connection, int
bookId) {
String updateQuery = "UPDATE Books SET is_available
= TRUE WHERE book_id = ?";
 try (PreparedStatement updateStmt =
connection.prepareStatement(updateQuery)) {
updateStmt.setInt(1, bookId);

int rowsAffected = updateStmt.executeUpdate();

if (rowsAffected > 0) {
System.out.println("Book returned
successfully!");
} else {
System.out.println("Book ID not found or
already returned.");
}

} catch (SQLException e) {
e.printStackTrace();
}
}
}

2) Employee Database Management System Objective: Create a console-based Java application to

manage employee records in an SQLite or MySQL database. Tasks: Establish a connection to the
database.

• Implement CRUD operations for employee records (e.g., addEmployee,• viewEmployee,

updateEmployee, and deleteEmployee). Use PreparedStatement to avoid SQL injection.

• Add basic error handling to capture potential SQL exceptions.

• Expected Outcome: Practice managing database connections, performing CRUD operations, and
handling exceptions.

Solution:

import java.sql.*;
import java.util.Scanner;
public class EmployeeDatabaseManagementSystem {

// Database URL, username, and password

private static final String DB_URL =
"jdbc:mysql://localhost:3306/EmployeeDB"; // Update for
SQLite as needed
private static final String DB_USERNAME = "root"; //
Replace with SQLite username if needed
private static final String DB_PASSWORD = "password"; //
Replace with SQLite password if needed

public static void main(String[] args) {

try (Connection connection =
DriverManager.getConnection(DB_URL, DB_USERNAME,
DB_PASSWORD)) {
Scanner scanner = new Scanner(System.in);
boolean exit = false;

while (!exit) {
System.out.println("\nEmployee Database
Management System");
System.out.println("1. Add Employee");
System.out.println("2. View Employee");
System.out.println("3. Update Employee");
System.out.println("4. Delete Employee");
System.out.println("5. Exit");
System.out.print("Choose an option: ");
int choice = scanner.nextInt();

switch (choice) {
case 1:
addEmployee(connection, scanner);
break;
case 2:
viewEmployee(connection, scanner);
break;
case 3:
updateEmployee(connection, scanner);
break;
 case 4:
deleteEmployee(connection, scanner);
break;
case 5:
exit = true;
break;
default:
System.out.println("Invalid choice!
Please try again.");
}
}

scanner.close();
} catch (SQLException e) {
e.printStackTrace();
}
}

// Add a new employee

public static void addEmployee(Connection connection,
Scanner scanner) {
String insertQuery = "INSERT INTO Employees (name,
position, salary) VALUES (?, ?, ?)";

try (PreparedStatement preparedStatement =

connection.prepareStatement(insertQuery)) {
System.out.print("Enter Employee Name: ");
String name = scanner.next();
System.out.print("Enter Position: ");
String position = scanner.next();
System.out.print("Enter Salary: ");
double salary = scanner.nextDouble();

preparedStatement.setString(1, name);
preparedStatement.setString(2, position);
preparedStatement.setDouble(3, salary);

int rowsInserted =
preparedStatement.executeUpdate();
 if (rowsInserted > 0) {
System.out.println("Employee added
successfully!");
}
} catch (SQLException e) {
System.out.println("Error adding employee: " +
e.getMessage());
}
}

// View employee details

public static void viewEmployee(Connection connection,
Scanner scanner) {
String selectQuery = "SELECT * FROM Employees WHERE
id = ?";

try (PreparedStatement preparedStatement =

connection.prepareStatement(selectQuery)) {
System.out.print("Enter Employee ID: ");
int id = scanner.nextInt();

preparedStatement.setInt(1, id);
ResultSet resultSet =
preparedStatement.executeQuery();

if (resultSet.next()) {
System.out.printf("ID: %d, Name: %s,
Position: %s, Salary: %.2f%n",
resultSet.getInt("id"),
resultSet.getString("name"),
resultSet.getString("position"),
resultSet.getDouble("salary"));
} else {
System.out.println("Employee not found.");
}
} catch (SQLException e) {
System.out.println("Error retrieving employee: "
+ e.getMessage());
}
 }

// Update employee details

public static void updateEmployee(Connection connection,
Scanner scanner) {
String updateQuery = "UPDATE Employees SET name = ?,
position = ?, salary = ? WHERE id = ?";

try (PreparedStatement preparedStatement =

connection.prepareStatement(updateQuery)) {
System.out.print("Enter Employee ID: ");
int id = scanner.nextInt();
System.out.print("Enter New Name: ");
String name = scanner.next();
System.out.print("Enter New Position: ");
String position = scanner.next();
System.out.print("Enter New Salary: ");
double salary = scanner.nextDouble();

preparedStatement.setString(1, name);
preparedStatement.setString(2, position);
preparedStatement.setDouble(3, salary);
preparedStatement.setInt(4, id);

int rowsUpdated =
preparedStatement.executeUpdate();
if (rowsUpdated > 0) {
System.out.println("Employee updated
successfully!");
} else {
System.out.println("Employee ID not
found.");
}
} catch (SQLException e) {
System.out.println("Error updating employee: " +
e.getMessage());
}
}
 // Delete employee
public static void deleteEmployee(Connection connection,
Scanner scanner) {
String deleteQuery = "DELETE FROM Employees WHERE id
= ?";

try (PreparedStatement preparedStatement =

connection.prepareStatement(deleteQuery)) {
System.out.print("Enter Employee ID to Delete:
");
int id = scanner.nextInt();

preparedStatement.setInt(1, id);

int rowsDeleted =
preparedStatement.executeUpdate();
if (rowsDeleted > 0) {
System.out.println("Employee deleted
successfully!");
} else {
System.out.println("Employee ID not
found.");
}
} catch (SQLException e) {
System.out.println("Error deleting employee: " +
e.getMessage());
}
}
}

3) Building a Mini Student Database with CRUD Operations Objective: Develop a mini database
application to manage student records, with Create, Read, Update, and Delete (CRUD) functionality
Requirements: Create a Student table in a relational database (e.g., MySQL, PostgreSQL)

• with columns for student_id, name, age, and grade. Implement a Java class, StudentDAO, using JDBC
to:•
i. Insert new student records.

ii. Retrieve all student records or by student_id.

iii. Update existing student records by student_id.

iv. Delete a student record by student_id.

Use PreparedStatement for all SQL queries to prevent SQL injection.•

Solution:

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

// Student Data Access Object (DAO) Class

class StudentDAO {
private static final String DB_URL =
"jdbc:mysql://localhost:3306/StudentDB";
private static final String DB_USERNAME = "root";
private static final String DB_PASSWORD = "password";

private Connection connection;

// Constructor to establish database connection

public StudentDAO() throws SQLException {
connection = DriverManager.getConnection(DB_URL,
DB_USERNAME, DB_PASSWORD);
}

// Insert a new student record

public void insertStudent(String name, int age, String
grade) {
String query = "INSERT INTO Student (name, age,
grade) VALUES (?, ?, ?)";
 try (PreparedStatement preparedStatement =
connection.prepareStatement(query)) {
preparedStatement.setString(1, name);
preparedStatement.setInt(2, age);
preparedStatement.setString(3, grade);

int rowsInserted =
preparedStatement.executeUpdate();
if (rowsInserted > 0) {
System.out.println("Student record added
successfully!");
}
} catch (SQLException e) {
System.out.println("Error inserting student: " +
e.getMessage());
}
}

// Retrieve all student records

public List<String> getAllStudents() {
List<String> students = new ArrayList<>();
String query = "SELECT * FROM Student";

try (Statement statement =

connection.createStatement();
ResultSet resultSet =
statement.executeQuery(query)) {

while (resultSet.next()) {
students.add(String.format("ID: %d, Name:
%s, Age: %d, Grade: %s",
resultSet.getInt("student_id"),
resultSet.getString("name"),
resultSet.getInt("age"),
resultSet.getString("grade")));
}
} catch (SQLException e) {
System.out.println("Error retrieving students: "
+ e.getMessage());
 }
return students;
}

// Retrieve a student record by ID

public String getStudentById(int studentId) {
String query = "SELECT * FROM Student WHERE
student_id = ?";
String studentDetails = "Student not found.";

try (PreparedStatement preparedStatement =

connection.prepareStatement(query)) {
preparedStatement.setInt(1, studentId);
ResultSet resultSet =
preparedStatement.executeQuery();

if (resultSet.next()) {
studentDetails = String.format("ID: %d,
Name: %s, Age: %d, Grade: %s",
resultSet.getInt("student_id"),
resultSet.getString("name"),
resultSet.getInt("age"),
resultSet.getString("grade"));
}
} catch (SQLException e) {
System.out.println("Error retrieving student: "
+ e.getMessage());
}
return studentDetails;
}

// Update a student record by ID

public void updateStudent(int studentId, String name,
int age, String grade) {
String query = "UPDATE Student SET name = ?, age =
?, grade = ? WHERE student_id = ?";

try (PreparedStatement preparedStatement =

connection.prepareStatement(query)) {
 preparedStatement.setString(1, name);
preparedStatement.setInt(2, age);
preparedStatement.setString(3, grade);
preparedStatement.setInt(4, studentId);

int rowsUpdated =
preparedStatement.executeUpdate();
if (rowsUpdated > 0) {
System.out.println("Student record updated
successfully!");
} else {
System.out.println("Student ID not found.");
}
} catch (SQLException e) {
System.out.println("Error updating student: " +
e.getMessage());
}
}

// Delete a student record by ID

public void deleteStudent(int studentId) {
String query = "DELETE FROM Student WHERE student_id
= ?";

try (PreparedStatement preparedStatement =

connection.prepareStatement(query)) {
preparedStatement.setInt(1, studentId);

int rowsDeleted =
preparedStatement.executeUpdate();
if (rowsDeleted > 0) {
System.out.println("Student record deleted
successfully!");
} else {
System.out.println("Student ID not found.");
}
} catch (SQLException e) {
System.out.println("Error deleting student: " +
e.getMessage());
 }
}
}

// Main Class
public class StudentDatabaseApp {

public static void main(String[] args) {

try (Scanner scanner = new Scanner(System.in)) {
StudentDAO studentDAO = new StudentDAO();
boolean exit = false;

while (!exit) {
System.out.println("\nStudent Database
Management System");
System.out.println("1. Add Student");
System.out.println("2. View All Students");
System.out.println("3. View Student by ID");
System.out.println("4. Update Student");
System.out.println("5. Delete Student");
System.out.println("6. Exit");
System.out.print("Choose an option: ");
int choice = scanner.nextInt();

switch (choice) {
case 1:
System.out.print("Enter Name: ");
String name = scanner.next();
System.out.print("Enter Age: ");
int age = scanner.nextInt();
System.out.print("Enter Grade: ");
String grade = scanner.next();
studentDAO.insertStudent(name, age,
grade);
break;

case 2:
List<String> students =
studentDAO.getAllStudents();
students.forEach(System.out::println);
break;

case 3:
System.out.print("Enter Student ID:
");
int studentId = scanner.nextInt();

System.out.println(studentDAO.getStudentById(studentId));
break;

case 4:
System.out.print("Enter Student ID
to Update: ");
int updateId = scanner.nextInt();
System.out.print("Enter New Name:
");
String updateName = scanner.next();
System.out.print("Enter New Age: ");
int updateAge = scanner.nextInt();
System.out.print("Enter New Grade:
");
String updateGrade = scanner.next();
studentDAO.updateStudent(updateId,
updateName, updateAge, updateGrade);
break;

case 5:
System.out.print("Enter Student ID
to Delete: ");
int deleteId = scanner.nextInt();
studentDAO.deleteStudent(deleteId);
break;

case 6:
exit = true;
break;
 default:
System.out.println("Invalid choice!
Please try again.");
}
}
} catch (SQLException e) {
System.out.println("Error initializing
application: " + e.getMessage());
}
}
}