0% found this document useful (0 votes)

2 views12 pages

Experiment 6 Java

The document outlines an experiment to connect a Java application to a relational database using JDBC for CRUD operations. It includes objectives, outcomes, prerequisites, and a case study for a Library Management System, detailing the necessary SQL schema and sample Java code for implementing the functionality. The program allows users to add, view, borrow, return, and delete books while managing transactions to ensure data integrity.

Uploaded by

botlapavan90

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as PDF, TXT or read online on Scribd

0% found this document useful (0 votes)

2 views12 pages

Experiment 6 Java

Uploaded by

botlapavan90

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as PDF, TXT or read online on Scribd

Name : Sayyed Sumaiya Waqar Rollno : 68 Batch-C

Experiment No. 6
Title: Connect to a MySQL/Postgres/SQLite database using JDBC to perform basic CRUD
operations

Aim
To design and implement a Java application that connects to a relational database (MySQL /
PostgreSQL / SQLite) using JDBC and performs CRUD operations (Create, Read, Update, Delete)
with transaction handling.

Objectives
• To understand JDBC (Java Database Connectivity) and database drivers.
• To set up and connect Java applications with MySQL, PostgreSQL, or SQLite.
• To perform CRUD operations using PreparedStatement.

• To implement transaction management (commit/rollback).

• To develop a case-study-based Library Management System.

Outcomes
After completing this experiment, the student will be able to:
• Establish a JDBC connection to multiple relational databases.
• Create and manipulate database tables via Java programs.
• Write CRUD operations using PreparedStatement to prevent SQL injection.
• Use transactions for atomic borrow/return operations.
• Switch between embedded (SQLite) and client-server (MySQL/PostgreSQL) databases.

Prerequisite
• Basic SQL knowledge (DDL, DML).
• Java programming (classes, exceptions, I/O).
• JDBC drivers for MySQL, PostgreSQL, or SQLite.

Requirements
• JDK (version 8 or above).
• MySQL / PostgreSQL server, or SQLite installed.
• JDBC driver JARs:
• MySQL: [Link]
Name : Sayyed Sumaiya Waqar Rollno : 68 Batch-C

• PostgreSQL: [Link]
• SQLite: [Link]
• SQL client (phpMyAdmin, pgAdmin, or sqlite3 CLI).

Brief Theory
• JDBC (Java Database Connectivity): A Java API for connecting to relational databases.
• JDBC Drivers: Each DB requires its own driver (e.g., MySQL Connector/J, PostgreSQL
JDBC, SQLite JDBC).
• Connection URL Examples:
• MySQL: jdbc:mysql://localhost:3306/librarydb

• PostgreSQL: jdbc:postgresql://localhost:5432/librarydb

• SQLite: jdbc:sqlite:[Link]

• PreparedStatement: Prevents SQL injection by binding parameters.

• Transactions:
• [Link](false); → Begin transaction

• [Link](); → Save changes

CREATE TABLE members (

member_id SERIAL PRIMARY KEY,
name VARCHAR(200) NOT NULL,
email VARCHAR(150) UNIQUE,
join_date DATE DEFAULT CURRENT_DATE
);

CREATE TABLE loans (

loan_id SERIAL PRIMARY KEY,
book_id INTEGER REFERENCES books(book_id),
member_id INTEGER REFERENCES members(member_id),
loan_date DATE DEFAULT CURRENT_DATE,
due_date DATE,
returned BOOLEAN DEFAULT FALSE
);
Name : Sayyed Sumaiya Waqar Rollno : 68 Batch-C

2. Write a Java program with the following features:

• addBook() → INSERT book record

• getBookByISBN() → SELECT query

• updateBookCopies() → UPDATE book copies

• deleteBook() → DELETE record

• borrowBook() → Transaction (decrement book copies + INSERT loan record)

• returnBook() → Transaction (increment copies + mark loan as returned)

3. Use try-with-resources to close connections, statements, and result sets.
4. Demonstrate CRUD operations on all tables with a menu-driven console program.

Sample Java Program (Core Parts)

import [Link].*;

public class LibraryApp {

private static final String URL = "jdbc:sqlite:[Link]"; // Change for
MySQL/Postgres
private static final String USER = "root"; // For MySQL/Postgres
private static final String PASSWORD = "password";

// Establish connection
private static Connection getConnection() throws SQLException {
return [Link](URL, USER, PASSWORD);
}

// Add a book
public static void addBook(String isbn, String title, String author, int
copies) {
String sql = "INSERT INTO books(isbn, title, author, copies)
VALUES(?, ?, ?, ?)";
try (Connection conn = getConnection(); PreparedStatement ps =
[Link](sql)) {
[Link](1, isbn);
[Link](2, title);
[Link](3, author);
[Link](4, copies);
[Link]();
[Link](" Book added successfully.");
} catch (SQLException e) {
[Link](" Error: " + [Link]());
}
}

// Borrow a book (Transaction)

public static void borrowBook(int memberId, int bookId) {
String updateBook = "UPDATE books SET copies = copies - 1 WHERE book_id
= ? AND copies > 0";
String insertLoan = "INSERT INTO loans(book_id, member_id, due_date)
VALUES(?, ?, DATE('now', '+14 day'))";

try (Connection conn = getConnection()) {

[Link](false);
try (PreparedStatement ps1 = [Link](updateBook);
PreparedStatement ps2 = [Link](insertLoan)) {
Name : Sayyed Sumaiya Waqar Rollno : 68 Batch-C

[Link](1, bookId);
int updated = [Link]();

if (updated == 0) {
throw new SQLException("No copies available for book " +
bookId);
}

[Link](1, bookId);
[Link](2, memberId);
[Link]();

[Link]();
[Link](" Book borrowed successfully.");
} catch (SQLException e) {
[Link]();
[Link](" Transaction failed: " + [Link]());
}
} catch (SQLException e) {
[Link](" Connection error: " + [Link]());
}
}

// Main menu (simplified)

public static void main(String[] args) {
addBook("123456", "Effective Java", "Joshua Bloch", 5);
borrowBook(1, 1); // Assume member_id=1, book_id=1
}
}

Steps to Execute
1. Install database (MySQL/Postgres/SQLite).
2. Create librarydb and run the schema SQL.
3. Download and add the respective JDBC driver JAR to classpath.
4. Compile:
javac [Link]

5. Run:
java -cp ".:[Link]" LibraryApp

(Adjust classpath for MySQL/Postgres drivers.)

Name : Sayyed Sumaiya Waqar Rollno : 68 Batch-C

CODE:
import [Link].*;
import [Link];

public class LibraryApp {

private static final String URL = "jdbc:postgresql://localhost:5432/librarydb";

private static final String USER = "postgres";
private static final String PASSWORD = "sumaiya";

static {
try {
[Link]("[Link]");
} catch (ClassNotFoundException e) {
[Link]("❌ PostgreSQL JDBC Driver not found. Include it in
your library path!");
[Link]();
}
}

private static Connection getConnection() throws SQLException {

return [Link](URL, USER, PASSWORD);
}

// ----------------------- BOOK METHODS -----------------------

public static void addBook(String isbn, String title, String author, int cop-
ies) {
String sql = "INSERT INTO books(isbn, title, author, copies) VALUES(?, ?,
?, ?)";
try (Connection conn = getConnection();
PreparedStatement ps = [Link](sql)) {

[Link](1, isbn);
[Link](2, title);
[Link](3, author);
[Link](4, copies);
[Link]();
[Link]("Book added successfully.");
} catch (SQLException e) {
[Link](" Error: " + [Link]());
}
}

public static void viewBooks() {

String sql = "SELECT * FROM books";
try (Connection conn = getConnection();
Statement stmt = [Link]();
ResultSet rs = [Link](sql)) {
Name : Sayyed Sumaiya Waqar Rollno : 68 Batch-C

[Link]("Book List:");
[Link]("%-5s %-15s %-30s %-20s %-6s%n", "ID", "ISBN", "Ti-
tle", "Author", "Copies");
while ([Link]()) {
[Link]("%-5d %-15s %-30s %-20s %-6d%n",
[Link]("book_id"),
[Link]("isbn"),
[Link]("title"),
[Link]("author"),
[Link]("copies"));
}
} catch (SQLException e) {
[Link]("Error: " + [Link]());
}
}

public static void deleteBook(int bookId) {

String deleteLoans = "DELETE FROM loans WHERE book_id = ?";
String deleteBook = "DELETE FROM books WHERE book_id = ?";

try (Connection conn = getConnection()) {

[Link](false);

try (PreparedStatement ps1 = [Link](deleteLoans);

PreparedStatement ps2 = [Link](deleteBook)) {

[Link](1, bookId);
[Link]();

[Link](1, bookId);
int rows = [Link]();

[Link]();

if (rows > 0) {
[Link](" Book and its loans deleted success-
fully.");
} else {
[Link]("Book ID not found.");
}

} catch (SQLException e) {
[Link]();
[Link]("Transaction failed: " + [Link]());
}
} catch (SQLException e) {
[Link]("Connection error: " + [Link]());
}
}
Name : Sayyed Sumaiya Waqar Rollno : 68 Batch-C

public static void getBookByISBN(Scanner sc) {

[Link]("Enter ISBN: ");
String isbn = [Link]();

String sql = "SELECT * FROM books WHERE isbn = ?";

try (Connection conn = getConnection();
PreparedStatement ps = [Link](sql)) {

[Link](1, isbn);
try (ResultSet rs = [Link]()) {
if ([Link]()) {
[Link]("Book Found: ID=%d, Title=%s, Author=%s, Cop-
ies=%d%n",
[Link]("book_id"),
[Link]("title"),
[Link]("author"),
[Link]("copies"));
} else {
[Link]("No book found with ISBN: " + isbn);
}
}

} catch (SQLException e) {
[Link]("Error: " + [Link]());
}
}

// ----------------------- BORROW/RETURN -----------------------

public static void borrowBook(int bookId) {
String sql = "UPDATE books SET copies = copies - 1 WHERE book_id = ? AND copies
> 0";

try (Connection conn = getConnection();

PreparedStatement ps = [Link](sql)) {

[Link](1, bookId);
int updated = [Link]();

if (updated > 0) {
[Link]("Book borrowed successfully!");
} else {
[Link]("Book not available or invalid ID!");
}

} catch (SQLException e) {
[Link]("Error: " + [Link]());
}
}

public static void returnBook(int bookId) {

Name : Sayyed Sumaiya Waqar Rollno : 68 Batch-C

String sql = "UPDATE books SET copies = copies + 1 WHERE book_id = ?";

try (Connection conn = getConnection();

PreparedStatement ps = [Link](sql)) {

[Link](1, bookId);
int updated = [Link]();

if (updated > 0) {
[Link]("Book returned successfully!");
} else {
[Link]("Invalid Book ID!");
}

} catch (SQLException e) {
[Link]("Error: " + [Link]());
}
}

// ----------------------- MAIN MENU -----------------------

public static void main(String[] args) {
Scanner sc = new Scanner([Link]);
while (true) {
[Link]("\n=== Library Management Menu ===");
[Link]("1. Add Book");
[Link]("2. View Books");
[Link]("3. Borrow Book");
[Link]("4. Return Book");
[Link]("5. Delete Book");
[Link]("6. Get Book by ISBN");
[Link]("7. Exit");

[Link]("Enter choice: ");

int choice = [Link]();
[Link](); // consume newline

switch (choice) {
case 1 -> {
[Link]("Enter ISBN: ");
String isbn = [Link]();
[Link]("Enter Title: ");
String title = [Link]();
[Link]("Enter Author: ");
String author = [Link]();
[Link]("Enter Copies: ");
int copies = [Link]();
[Link]();
addBook(isbn, title, author, copies);
}
case 2 -> viewBooks();
Name : Sayyed Sumaiya Waqar Rollno : 68 Batch-C

case 3 -> { // Borrow Book

[Link]("Enter Book ID to borrow: ");
int bookId = [Link]();
[Link]();
borrowBook(bookId);
}

case 4 -> { // Return Book

[Link]("Enter Book ID to return: ");
int bookId = [Link]();
[Link]();
returnBook(bookId);
}

case 5 -> {
[Link]("Enter Book ID to delete: ");
int deleteId = [Link]();
[Link]();
deleteBook(deleteId);
}
case 6 -> getBookByISBN(sc);
case 7 -> {
[Link]("Exiting...");
[Link]();
[Link](0);
}
default -> [Link]("Invalid choice. Try again.");
}
}
}
}
Name : Sayyed Sumaiya Waqar Rollno : 68 Batch-C

Output:
Adding new Book:

View Books:

Borrow a Book:

After borrowing a book, the number of available

copies is automatically reduced, as reflected when
you view the books list.
Name : Sayyed Sumaiya Waqar Rollno : 68 Batch-C

View Books

Return Book:

After returning a book, the number of available copies is automatically increased, as shown when
you view the books list.
Name : Sayyed Sumaiya Waqar Rollno : 68 Batch-C

Get Book By ISBN :

Delete Book:

Exit:

IT Project
No ratings yet
IT Project
20 pages
Java Library Management System Guide
No ratings yet
Java Library Management System Guide
7 pages
Thư viện MySQL trên usbwebserver
No ratings yet
Thư viện MySQL trên usbwebserver
44 pages
Java Report
No ratings yet
Java Report
7 pages
Library Management System Overview
No ratings yet
Library Management System Overview
4 pages
BookDAO: CRUD Operations Overview
No ratings yet
BookDAO: CRUD Operations Overview
3 pages
Assignment Week 4
No ratings yet
Assignment Week 4
3 pages
Java Servlet CRUD Operations Guide
No ratings yet
Java Servlet CRUD Operations Guide
9 pages
JDBC Book Management Application
No ratings yet
JDBC Book Management Application
33 pages
Java JDBC Database Connectivity Guide
No ratings yet
Java JDBC Database Connectivity Guide
8 pages
JDBC Application for Book Management
No ratings yet
JDBC Application for Book Management
59 pages
JDBC Guide: Connecting Java to Databases
No ratings yet
JDBC Guide: Connecting Java to Databases
7 pages
Book Management System - Java & MySQL Project
No ratings yet
Book Management System - Java & MySQL Project
22 pages
Lms Yamuna
No ratings yet
Lms Yamuna
11 pages
Connect MySQL Database with Java
No ratings yet
Connect MySQL Database with Java
8 pages
Unit 1
No ratings yet
Unit 1
26 pages
SQLite JDBC Tutorial for Java
No ratings yet
SQLite JDBC Tutorial for Java
8 pages
JDBC MySQL Connection Example
No ratings yet
JDBC MySQL Connection Example
6 pages
JDBC Book Management Application
No ratings yet
JDBC Book Management Application
11 pages
Establishing JDBC Connection in Java
No ratings yet
Establishing JDBC Connection in Java
7 pages
Advanced Java JDBC and Database Management
No ratings yet
Advanced Java JDBC and Database Management
107 pages
JDBC ResultSet Example in Java
No ratings yet
JDBC ResultSet Example in Java
6 pages
Aj Lab Manual
No ratings yet
Aj Lab Manual
57 pages
Library and Employee Database Systems
No ratings yet
Library and Employee Database Systems
17 pages
Library Management System Features
No ratings yet
Library Management System Features
11 pages
Crud JDBC Programs
No ratings yet
Crud JDBC Programs
7 pages
AJP Record Codes
No ratings yet
AJP Record Codes
53 pages
OOP Project Report
No ratings yet
OOP Project Report
8 pages
JDBC
No ratings yet
JDBC
7 pages
Java Unit 5
No ratings yet
Java Unit 5
18 pages
JSP User Registration and Login System
100% (1)
JSP User Registration and Login System
21 pages
Java Library Management System Design
No ratings yet
Java Library Management System Design
42 pages
Java Database Connectivity
No ratings yet
Java Database Connectivity
11 pages
JDBC Java Database Operations Guide
No ratings yet
JDBC Java Database Operations Guide
10 pages
Advanced Java - Vineeth Sir
No ratings yet
Advanced Java - Vineeth Sir
24 pages
Library Management JDBC Mini Project v2
No ratings yet
Library Management JDBC Mini Project v2
3 pages
JDBC CRUD Operations in Java Guide
No ratings yet
JDBC CRUD Operations in Java Guide
11 pages
Java Database Connectivity Example
No ratings yet
Java Database Connectivity Example
3 pages
JDBC Java Database Connectivity Guide
No ratings yet
JDBC Java Database Connectivity Guide
22 pages
MySQL User Management with JDBC
No ratings yet
MySQL User Management with JDBC
6 pages
JDBC Database Operations in Java
No ratings yet
JDBC Database Operations in Java
9 pages
Practical 22 Mad
No ratings yet
Practical 22 Mad
7 pages
Accessing SQL via Programming Languages
No ratings yet
Accessing SQL via Programming Languages
90 pages
Institute Management System Project
No ratings yet
Institute Management System Project
16 pages
IDB Project Report
No ratings yet
IDB Project Report
16 pages
Java MySQL Books Database Example
No ratings yet
Java MySQL Books Database Example
3 pages
Understanding JDBC: Java Database Connectivity
No ratings yet
Understanding JDBC: Java Database Connectivity
7 pages
Spring JDBC CRUD with JdbcTemplate
No ratings yet
Spring JDBC CRUD with JdbcTemplate
12 pages
OOP II With Java II For BCS2 - BSE2 Semester I - December 10th - 2024 - Guide
No ratings yet
OOP II With Java II For BCS2 - BSE2 Semester I - December 10th - 2024 - Guide
17 pages
JDBC Student Management System
No ratings yet
JDBC Student Management System
7 pages
Assignment No 5
No ratings yet
Assignment No 5
3 pages
Part B DB
No ratings yet
Part B DB
17 pages
JDBC Database Connectivity Guide
No ratings yet
JDBC Database Connectivity Guide
5 pages
Library Management System Project
No ratings yet
Library Management System Project
17 pages
Java JDBC CRUD Operations Guide
No ratings yet
Java JDBC CRUD Operations Guide
7 pages
Book Reading App Using JDBC
No ratings yet
Book Reading App Using JDBC
9 pages
AdvJava Assignment05 Solution
No ratings yet
AdvJava Assignment05 Solution
27 pages
COMP3161 Final Project Overview
No ratings yet
COMP3161 Final Project Overview
3 pages
SAP HANA Parameter Recommendations Guide
No ratings yet
SAP HANA Parameter Recommendations Guide
42 pages
Dot Net Technology Exam Paper 2019
No ratings yet
Dot Net Technology Exam Paper 2019
3 pages
Database Systems Introduction To Databases and Data Warehouses 2nd Edition Nenad Jukić All Chapters Available
100% (9)
Database Systems Introduction To Databases and Data Warehouses 2nd Edition Nenad Jukić All Chapters Available
202 pages
OLAP: Data Management & Analysis Guide
No ratings yet
OLAP: Data Management & Analysis Guide
31 pages
PL/SQL Overview: Strings, Arrays, Functions
No ratings yet
PL/SQL Overview: Strings, Arrays, Functions
15 pages
Simple Banking Application Project Report
No ratings yet
Simple Banking Application Project Report
22 pages
MongoDB Mastery Roadmap Guide
No ratings yet
MongoDB Mastery Roadmap Guide
7 pages
SQL vs. Procedural Languages Explained
No ratings yet
SQL vs. Procedural Languages Explained
1 page
Service Discovery in Microservices
No ratings yet
Service Discovery in Microservices
7 pages
C++ Bank Management System with BST
No ratings yet
C++ Bank Management System with BST
3 pages
DrillNET 2.0.2 Installation Guide
No ratings yet
DrillNET 2.0.2 Installation Guide
73 pages
QA Process Evaluation for Hospital System
No ratings yet
QA Process Evaluation for Hospital System
7 pages
Data Dictionary and Large Pool Management
No ratings yet
Data Dictionary and Large Pool Management
12 pages
Course Registration System Overview
No ratings yet
Course Registration System Overview
4 pages
AWS Data Architect with BI Expertise
No ratings yet
AWS Data Architect with BI Expertise
4 pages
AWS Storage Gateway Data Storage Overview
No ratings yet
AWS Storage Gateway Data Storage Overview
47 pages
Oracle Database 11g Recovery & Management Guide
No ratings yet
Oracle Database 11g Recovery & Management Guide
8 pages
Types of Database Users Explained
No ratings yet
Types of Database Users Explained
8 pages
Mohd Yasir SW
No ratings yet
Mohd Yasir SW
1 page
TIBCO ActiveMatrix BW Admin Guide 6.7
No ratings yet
TIBCO ActiveMatrix BW Admin Guide 6.7
281 pages
Hibernate Framework Guide
No ratings yet
Hibernate Framework Guide
13 pages
Indexing in Database Systems Explained
No ratings yet
Indexing in Database Systems Explained
83 pages
Oracle SQL Basics and Commands Guide
No ratings yet
Oracle SQL Basics and Commands Guide
53 pages
Accounting Systems Multiple Choice Quiz
No ratings yet
Accounting Systems Multiple Choice Quiz
4 pages
Change Autoextend for Datafiles
No ratings yet
Change Autoextend for Datafiles
7 pages
SAP HANA on Cloud Platform Neo
No ratings yet
SAP HANA on Cloud Platform Neo
64 pages
Database Access Control Overview
No ratings yet
Database Access Control Overview
21 pages
Lesson 17 - Distributed Database Management System
No ratings yet
Lesson 17 - Distributed Database Management System
23 pages
NL2SPARQL Curriculum Dataset Generation
No ratings yet
NL2SPARQL Curriculum Dataset Generation
60 pages

Experiment 6 Java

Uploaded by

Experiment 6 Java

Uploaded by

Name : Sayyed Sumaiya Waqar Rollno : 68 Batch-C

• To implement transaction management (commit/rollback).

• PreparedStatement: Prevents SQL injection by binding parameters.

• [Link](); → Save changes

• [Link](); → Revert on error

CREATE TABLE members (

CREATE TABLE loans (

2. Write a Java program with the following features:

• getBookByISBN() → SELECT query

• updateBookCopies() → UPDATE book copies

• deleteBook() → DELETE record

• borrowBook() → Transaction (decrement book copies + INSERT loan record)

• returnBook() → Transaction (increment copies + mark loan as returned)

Sample Java Program (Core Parts)

public class LibraryApp {

// Borrow a book (Transaction)

try (Connection conn = getConnection()) {

// Main menu (simplified)

(Adjust classpath for MySQL/Postgres drivers.)

public class LibraryApp {

private static final String URL = "jdbc:postgresql://localhost:5432/librarydb";

private static Connection getConnection() throws SQLException {

// ----------------------- BOOK METHODS -----------------------

public static void viewBooks() {

public static void deleteBook(int bookId) {

try (Connection conn = getConnection()) {

try (PreparedStatement ps1 = [Link](deleteLoans);

public static void getBookByISBN(Scanner sc) {

String sql = "SELECT * FROM books WHERE isbn = ?";

// ----------------------- BORROW/RETURN -----------------------

try (Connection conn = getConnection();

public static void returnBook(int bookId) {

try (Connection conn = getConnection();

// ----------------------- MAIN MENU -----------------------

[Link]("Enter choice: ");

case 3 -> { // Borrow Book

case 4 -> { // Return Book

After borrowing a book, the number of available

Get Book By ISBN :

You might also like