POJO CLASS :
package com.payilagam.entity;
public class Book {
private int id;
private String name;
private String author;
private int quantity;
public Book() {
}
public Book(String name, String author, int quantity) {
this.name = name;
this.author = author;
this.quantity = quantity;
}
public Book(int id, String name, String author, int quantity) {
this.id = id;
this.name = name;
this.author = author;
this.quantity = quantity;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public int getQuantity() {
return quantity;
}
public void setQuantity(int quantity) {
this.quantity = quantity;
}
public String toString() {
return "Book ID: " + id
+ ", Name: " + name
+ ", Author: " + author
+ ", Quantity: " + quantity;
}
}
SERVICE CLASS :
package com.payilagam.service;
import com.payilagam.db.DatabaseConnection;
import com.payilagam.entity.Book;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class BookService {
public void addBook(Book book) throws SQLException {
String sql = "INSERT INTO books (name, author, quantity) VALUES (?, ?, ?)";
try (Connection con = DatabaseConnection.getConnection();
PreparedStatement ps = con.prepareStatement(sql)) {
ps.setString(1, book.getName());
ps.setString(2, book.getAuthor());
ps.setInt(3, book.getQuantity());
ps.executeUpdate();
}
}
public List<Book> getAllBooks() throws SQLException {
List<Book> books = new ArrayList<>();
String sql = "SELECT * FROM books ORDER BY id";
try (Connection con = DatabaseConnection.getConnection();
PreparedStatement ps = con.prepareStatement(sql);
ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
Book book = new Book(
rs.getInt("id"),
rs.getString("name"),
rs.getString("author"),
rs.getInt("quantity")
);
books.add(book);
}
}
return books;
}
public Book findById(int id) throws SQLException {
String sql = "SELECT * FROM books WHERE id = ?";
try (Connection con = DatabaseConnection.getConnection();
PreparedStatement ps = con.prepareStatement(sql)) {
ps.setInt(1, id);
try (ResultSet rs = ps.executeQuery()) {
if (rs.next()) {
return new Book(
rs.getInt("id"),
rs.getString("name"),
rs.getString("author"),
rs.getInt("quantity")
);
}
}
}
return null;
}
public List<Book> findByName(String name) throws SQLException {
List<Book> books = new ArrayList<>();
String sql = "SELECT * FROM books WHERE name ILIKE ? ORDER BY id";
try (Connection con = DatabaseConnection.getConnection();
PreparedStatement ps = con.prepareStatement(sql)) {
ps.setString(1, "%" + name + "%");
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
books.add(new Book(
rs.getInt("id"),
rs.getString("name"),
rs.getString("author"),
rs.getInt("quantity")
));
}
}
}
return books;
}
public List<Book> findByAuthor(String author) throws SQLException {
List<Book> books = new ArrayList<>();
String sql = "SELECT * FROM books WHERE author ILIKE ? ORDER BY id";
try (Connection con = DatabaseConnection.getConnection();
PreparedStatement ps = con.prepareStatement(sql)) {
ps.setString(1, "%" + author + "%");
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
books.add(new Book(
rs.getInt("id"),
rs.getString("name"),
rs.getString("author"),
rs.getInt("quantity")
));
}
}
}
return books;
}
public void updateBook(Book book) throws SQLException {
String sql = "UPDATE books SET name = ?, author = ?, quantity = ? WHERE id = ?";
try (Connection con = DatabaseConnection.getConnection();
PreparedStatement ps = con.prepareStatement(sql)) {
ps.setString(1, book.getName());
ps.setString(2, book.getAuthor());
ps.setInt(3, book.getQuantity());
ps.setInt(4, book.getId());
ps.executeUpdate();
}
}
public void deleteBook(int id) throws SQLException {
String sql = "DELETE FROM books WHERE id = ?";
try (Connection con = DatabaseConnection.getConnection();
PreparedStatement ps = con.prepareStatement(sql)) {
ps.setInt(1, id);
ps.executeUpdate();
}
}
public int getBookCount() throws SQLException {
String sql = "SELECT COUNT(*) FROM books";
try (Connection con = DatabaseConnection.getConnection();
PreparedStatement ps = con.prepareStatement(sql);
ResultSet rs = ps.executeQuery()) {
if (rs.next()) {
return rs.getInt(1);
}
}
return 0;
}
public int getMaxQuantity() throws SQLException {
String sql = "SELECT MAX(quantity) FROM books";
try (Connection con = DatabaseConnection.getConnection();
PreparedStatement ps = con.prepareStatement(sql);
ResultSet rs = ps.executeQuery()) {
if (rs.next()) {
return rs.getInt(1);
}
}
return 0;
}
public int getMinQuantity() throws SQLException {
String sql = "SELECT MIN(quantity) FROM books";
try (Connection con = DatabaseConnection.getConnection();
PreparedStatement ps = con.prepareStatement(sql);
ResultSet rs = ps.executeQuery()) {
if (rs.next()) {
return rs.getInt(1);
}
}
return 0;
}
}
CONTROLLER CLASS :
package com.payilagam.controller;
import com.payilagam.entity.Book;
import com.payilagam.service.BookService;
import java.sql.SQLException;
import java.util.List;
public class Controller {
public static void main(String[] args) {
BookService service = new BookService();
try {
/*Book newBook = new Book("Java Basics","James Gosling",10);
service.addBook(newBook);
System.out.println("Book added successfully");*/
System.out.println("\nAll Books:");
List<Book> books = service.getAllBooks();
for (Book book : books) {
System.out.println(book);
}
System.out.println("\nSearch by Book ID:");
Book book = service.findById(1);
if (book != null) {
System.out.println(book);
} else {
System.out.println("Book not found");
}
System.out.println("\nSearch by Book Name:");
List<Book> bookNames = service.findByName("Java");
for (Book b : bookNames) {
System.out.println(b);
}
System.out.println("\nSearch by Author:");
List<Book> authors = service.findByAuthor("James");
for (Book b : authors) {
System.out.println(b);
}
System.out.println("\nUpdate Book:");
Book updateBook = service.findById(1);
if (updateBook != null) {
updateBook.setName("Java Programming");
updateBook.setAuthor("James Gosling");
updateBook.setQuantity(20);
service.updateBook(updateBook);
System.out.println("Book updated successfully");
} else {
System.out.println("Book not found for update");
}
service.deleteBook(2);
System.out.println("Book deleted successfully");
System.out.println("\nTotal Books: "
+ service.getBookCount());
System.out.println("Maximum Quantity: "
+ service.getMaxQuantity());
System.out.println("Minimum Quantity: "
+ service.getMinQuantity());
} catch (SQLException exp) {
System.out.println("Database error: "
+ exp.getMessage());
}
}
}
DATABASE CONNECTION :
package com.payilagam.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DatabaseConnection {
private static final String URL =
"jdbc:postgresql://localhost:5432/library";
private static final String USERNAME = "postgres";
// Replace this with your actual PostgreSQL password.
private static final String PASSWORD = "20223465";
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(
URL, USERNAME, PASSWORD
);
}
}
Top comments (0)