Design a set of database tables to store library books and patrons. A book has a
ID: 3627602 • Letter: D
Question
Design a set of database tables to store library books and patrons. A book has an ISBN (International Standard Book Number), an author, and a title. The library may have multiple copies of each book, each with a different book ID. A patron has a name, a unique ID, and an address. A book may be checked out by at most one patron, but one patron can check out multiple books.Write a Java program that creates a library database of books and patron data as described above. Patrons should be able to check out and return books. Supply commands to print the books that a patron has checked out and to find who has checked out a particular book. You may create and populate Patron and Book tables before running the program.
Explanation / Answer
There are 2 Java files for this. The first one will create the database: Also, remember to google how to install a database program like SQL or this will not work
package LibraryProject;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class CreateLibrary{
public static void main(String[]args) {
Connection con = null;
String url = "localhost";
String driverName = "mariadb-java-client-1.1.3.jar";
try{
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/library", "root", "YOUR PASSWORD GOES HERE");
Statement st = connection.createStatement();
//Create Columns
st.execute("CREATE TABLE Books(isbn VARCHAR(20), author VARCHAR(30), title VARCHAR (60), numberOfCopies INTEGER)");
//Create Rows
st.execute("INSERT INTO Books VALUES('792-446-2-1690', 'Thompson, Paul', '4 million leagues under the sea', '4')");
st.execute("INSERT INTO Books VALUES('809-455-2-9899', 'Bobson, Bob', 'The Portrait of Dorian Gray', '3')");
st.execute("INSERT INTO Books VALUES('901-772-4-0465', 'Del Toro, Guillermo', 'Intro to Java', '4')");
st.execute("INSERT INTO Books VALUES('374-566-8-1690', 'Richardson, Pat', 'SQL Databases: a Guide', '1')");
st.execute("INSERT INTO Books VALUES('955-111-0-1690', 'Rowling, JK', 'Harry Potter and the Goblet of Fire', '11')");
System.out.println("Book table success");
} catch (Exception e){
e.printStackTrace();
}
try{
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/library", "root", "YOUR PASSWORD GOES HERE");
Statement st = connection.createStatement();
//Create Columns
st.execute("CREATE TABLE Customers(idNumber VARCHAR(12), name VARCHAR(20), address VARCHAR (50), checkedOutBooks VARCHAR(50))");
//Create Rows
st.execute("INSERT INTO Customers VALUES('714-557-2011', 'Sharp, Al', '123 Fake St, Faketown, WI 84005', 'NONE')");
st.execute("INSERT INTO Customers VALUES('714-802-6446', 'Sau, Yoon', '1950 Second st Apt 2, Super Faketown, NV 05504','NONE')");
st.execute("INSERT INTO Customers VALUES('555-555-5555', 'Lawrys, Laura', '205 First ave, Florida Faketown, FL 76567','NONE')");
System.out.println("Customer table success");
} catch (Exception e){
e.printStackTrace();
}
}
}
And the second one here will access the database
package LibraryProject;
import java.util.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.*;
public class AccessLibrary{
public static void main(String[]args) {
Connection con = null;
String url = "localhost";
String driverName = "mariadb-java-client-1.1.3.jar";
Scanner myScanner = new Scanner(System.in);
System.out.println("Welcome to the Library! What would you like to do today? (Please Choose a number for your response)");
System.out.println("0: See what book titles are available");
System.out.println("1: Checkout a book");
System.out.println("2: Return a book");
System.out.println("3: See what books you have checked out");
System.out.println("4: Find out who has a book you want");
System.out.println("5: Nothing at all");
int response = myScanner.nextInt();
if (response == 0){
try{ //0
String output;
int counter = 1;
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/library", "root", "YOUR PASSWORD GOES HERE");
Statement st0 = connection.createStatement();
//Print out the books via query
System.out.println("Here are the books that are available: ");
ResultSet res = st0.executeQuery("SELECT title FROM Books");
//Print out the books via query
while(res.next()){
output = res.getString("title");
System.out.println(counter + ": " + output);
counter++;
}
connection.close();
} catch (Exception e){
e.printStackTrace();
}
}
if (response == 1){
try{ //0
String output, input, customerID;
int counter = 1;
Scanner checkoutBook = new Scanner(System.in);
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/library", "root", "YOUR PASSWORD GOES HERE");
Statement st1 = connection.createStatement();
//Print out the books via query
System.out.println("You want to checkout a book? No problem! From this list of books, type the name of the one you want to checkout (type exactly please)");
ResultSet res = st1.executeQuery("SELECT title FROM Books");
//Print out the books via query
while(res.next()){
output = res.getString("title");
System.out.println(counter + ": " + output);
counter++;
}
input = checkoutBook.nextLine();
System.out.println("Please enter your ID Number (Please remember that it is the same as your cell phone number with dashes xxx-xxx-xxxx");
customerID = checkoutBook.nextLine();
//Remove the book from the Book database
PreparedStatement stat1= connection.prepareStatement("UPDATE Books SET numberOfCopies = numberOfCopies-1 WHERE title = ?");
stat1.setString(1, input);
stat1.executeUpdate();
//Add the book to the customer database
PreparedStatement stat2= connection.prepareStatement("UPDATE Customers SET checkedOutBooks = ? WHERE idNumber = ?");
stat2.setString(1, input);
stat2.setString(2, customerID);
stat2.executeUpdate();
System.out.println("Enjoy your book and remember to return it in 2 weeks.");
checkoutBook.close();
stat1.close();
stat2.close();
res.close();
connection.close();
} catch (Exception e){
e.printStackTrace();
}
}
if (response == 2){
try{ //0
String customerID, bookToEnter;
Scanner checkoutBook = new Scanner(System.in);
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/library", "root", "YOUR PASSWORD GOES HERE");
//Print out the books via query
System.out.println("You want to Return your book? No problem!");
System.out.println("Please enter your ID Number (Please remember that it is the same as your cell phone number with dashes xxx-xxx-xxxx");
customerID = checkoutBook.nextLine();
PreparedStatement stat0= connection.prepareStatement("SELECT checkedOutBooks FROM customers WHERE idNumber = ?");
stat0.setString(1, customerID);
ResultSet res = stat0.executeQuery();
if(res.next()) {
bookToEnter = res.getString(1);
//Add the book to the Book database
PreparedStatement stat1= connection.prepareStatement("UPDATE Books SET numberOfCopies = numberOfCopies+1 WHERE title = ?");
stat1.setString(1, bookToEnter);
stat1.executeUpdate();
//Remove the book from the customer database
PreparedStatement stat2= connection.prepareStatement("UPDATE Customers SET checkedOutBooks = ? WHERE idNumber = ?");
stat2.setString(1, "NONE");
stat2.setString(2, customerID);
stat2.executeUpdate();
stat1.close();
stat2.close();
}
System.out.println("Thank you for returning your book!");
checkoutBook.close();
res.close();
connection.close();
} catch (Exception e){
e.printStackTrace();
}
}
if (response == 3){
try{ //0
String customerID, bookCheckedOut;
Scanner checkoutBook = new Scanner(System.in);
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/library", "root", "YOUR PASSWORD GOES HERE");
System.out.println("You want to see what books you have checked out?");
System.out.println("Please enter your ID Number (Please remember that it is the same as your cell phone number with dashes xxx-xxx-xxxx");
customerID = checkoutBook.nextLine();
PreparedStatement stat0= connection.prepareStatement("SELECT checkedOutBooks FROM customers WHERE idNumber = ?");
stat0.setString(1, customerID);
ResultSet res = stat0.executeQuery();
if(res.next()) {
bookCheckedOut = res.getString(1);
System.out.println("The books you currently have checked out is: " + bookCheckedOut);
}
checkoutBook.close();
} catch (Exception e){
e.printStackTrace();
}
}
if (response == 4){ //BTW, no library would ever give this name out... just sayin'!
try{
String output, checkoutBook, checkoutBooktoPrint;
int counter = 1;
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/library", "root", "YOUR PASSWORD GOES HERE");
Statement st1 = connection.createStatement();
System.out.println("You want to checkout a book? No problem! From this list of books, type the name of the one you want to checkout (type exactly please)");
ResultSet res = st1.executeQuery("SELECT title FROM Books");
//Print out the books via query
while(res.next()){
output = res.getString("title");
System.out.println(counter + ": " + output);
counter++;
}
Scanner checkoutBookScanner = new Scanner(System.in);
checkoutBook = checkoutBookScanner.nextLine();
PreparedStatement stat0= connection.prepareStatement("SELECT name FROM customers WHERE checkedOutBooks = ?");
stat0.setString(1, checkoutBook);
ResultSet res1 = stat0.executeQuery();
if(res1.next()) {
checkoutBooktoPrint = res1.getString(1);
System.out.println("The person who has the book you want checked out is named: " + checkoutBooktoPrint);
}
res1.close();
stat0.close();
checkoutBookScanner.close();
res.close();
st1.close();
connection.close();
} catch (Exception e){
e.printStackTrace();
}
}
else {
System.out.println("Have a great day!");
}
myScanner.close();
}
}
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.