Question

In this hands-on project, you will create an application that allows the user to connect to a database and query the database.

Write a Java query application that allows the user to connect to the books database and query the books database. Provide the following predefined queries:

1. Select all authors from the Authors table
2. Select a specific author and list all books for that author. Include each book’s title, year and ISBN. Display the appropriate data for each query.

Given sql file below:

DROP DATABASE IF EXISTS books;

CREATE DATABASE books;

USE books;

CREATE TABLE Authors

(

AuthorID int NOT NULL AUTO_INCREMENT PRIMARY KEY,

FirstName varchar(30) NOT NULL,

LastName varchar(30) NOT NULL

) ;

CREATE TABLE Titles

(

ISBN varchar(20) NOT NULL PRIMARY KEY,

Title varchar(100) NOT NULL,

EditionNumber int NOT NULL,

Copyright varchar(4) NOT NULL

) ;

CREATE TABLE AuthorISBN

(

AuthorID int NOT NULL,

ISBN varchar(20) NOT NULL,

FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID),

FOREIGN KEY (ISBN) References Titles(ISBN)

) ;

INSERT INTO Authors (FirstName,LastName) VALUES

('Harvey','Deitel') ;

INSERT INTO Authors (FirstName,LastName) VALUES ('Paul','Deitel') ;

INSERT INTO Authors (FirstName,LastName) VALUES ('Andrew','Goldberg') ;

INSERT INTO Authors (FirstName,LastName) VALUES ('David','Choffnes') ;

INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES

('0131869000','Visual Basic 2005 How to Program',3,'2006') ;

INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0131869000') ;

INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0131869000') ;

INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES

('0131525239','Visual C# 2005 How to Program',2,'2006') ;

INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0131525239') ;

INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0131525239') ;

INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES

('0132222205','Java How to Program',7,'2007') ;

INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES

(1,'0132222205') ;

INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES

(2,'0132222205') ;

INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES

('0131857576','C++ How to Program',5,'2005') ;

INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES

(1,'0131857576') ;

INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES

(2,'0131857576') ;

INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES

('0132404168','C How to Program',5,'2007') ;

INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES

(1,'0132404168') ;

INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES

(2,'0132404168') ;

INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES

('0131450913','Internet & World Wide Web How to Program',3,'2004') ;

INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES

(1,'0131450913') ;

INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES

(2,'0131450913') ;

INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES

(3,'0131450913') ;

INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES

('0131828274','Operating Systems',3,'2004') ;

INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES

(1,'0131828274') ;

INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES

(2,'0131828274') ;

INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES

(4,'0131828274') ;

Answer 1

question 1:

import java.sql.*;

public class JdbcSelectTest {
public static void main(String[] args) {
try (
  
Connection conn = DriverManager.getConnection();   
Statement stmt = conn.createStatement();
) {
  
String strSelect = "select * from Authors";


ResultSet rset = stmt.executeQuery(strSelect);


System.out.println("The records selected are:");
int rowCount = 0;
while(rset.next()) {
int AuthorID = rset.getInt("AuthorID");
String FirstName = rset.getString("FirstName");
String LastName = rset.getString("LastName");
System.out.println(AuthorID + ", " + FirstName + ", " + LastName);
++rowCount;
}
System.out.println("Total number of records = " + rowCount);

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

question 2:

import java.sql.*;

public class JdbcSelectTest {
public static void main(String[] args) {
try (
  
Connection conn = DriverManager.getConnection();   
Statement stmt = conn.createStatement();
) {
  
String strSelect = "select T.ISBN, T.Title, T.editionNumber, T.Copyright from Titles T, AuthorISBN A where T.ISBN=A.ISBN";


ResultSet rset = stmt.executeQuery(strSelect);


System.out.println("The records selected are:");
int rowCount = 0;
while(rset.next()) {
String ISBN = rset.getString("ISBN");
String Title = rset.getString("Title");
int EditionNumber = rset.getInt("EditionNumber");
String Copyright = rset.getString("Copyright");
System.out.println(ISBN + ", " + Title + ", " + EditionNumber + ", " + Copyright);
++rowCount;
}
System.out.println("Total number of records = " + rowCount);

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