Exercise 1 Using the techniques shown in the Reading & Study materials, create a
ID: 3826824 • Letter: E
Question
Exercise 1
Using the techniques shown in the Reading & Study materials, create a Java application that performs the following queries on the books database:
Select all authors from the Authors table.
Select a specific author and list all books for that author. Include each book’s title, year, and ISBN. Order the information chronologically.
Select a specific title and list all authors for that title. Order the authors alphabetically by last name and then by first name.
Provide 2 additional queries of your own choosing that you feel would be appropriate for the books database.
Exercise 2
Create a Java GUI application that allows the user to perform the tasks below on the books database. The user must be able to enter new data and edit existing data using a graphical user interface.
Add a new author.
Edit the existing information for an author.
Add a new title for an author. (Remember that the book must have an entry in the AuthorISBN table).
Explanation / Answer
This is Book class:
package com.bookStoareUsingDatabase;
public class Book {
String auther;
String title;
String year;
int ISBN;
//0-arg constructor
public Book() {
super();
// TODO Auto-generated constructor stub
}
//parameterized constructor
public Book(String auther, String title, String year, int ISBN) {
super();
this.auther = auther;
this.title = title;
this.year = year;
ISBN = ISBN;
}
//setters and getters
public String getAuther() {
return auther;
}
public void setAuther(String auther) {
this.auther = auther;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getYear() {
return year;
}
public void setYear(String year) {
this.year = year;
}
public int getISBN() {
return ISBN;
}
public void setISBN(int iSBN) {
ISBN = iSBN;
}
}
This is DbUtility class having MySQL database properties for connection:
package com.bookStoareUsingDatabase;
import java.sql.Connection;
import java.sql.DriverManager;
public class DbUtility {
public static Connection getConnection() {
Connection con = null;
try {
System.out.println("i m dbutil");
String url = "jdbc:mysql://localhost:3306/test";
Class.forName("com.mysql.jdbc.Driver");
// System.out.println(url);
con = DriverManager.getConnection(url, "root", "root");
System.out.println("connected");
} catch (Exception e) {
e.printStackTrace();
}
return con;
}
}
This is Driver class:
package com.bookStoareUsingDatabase;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
public class BookSystem {
public static void main(String[] args) {
Book b=null;
Scanner sc=new Scanner(System.in);
System.out.println(" Book Operations ");
System.out.println("Enter A to Add Book");
System.out.println("Enter B to Edit Book");
System.out.println("Enter C to Display Requred Result");
System.out.println("Enter D to Exit");
while(true){
System.out.print("Enter your choice [A,B,C & D]: ");
String choice = sc.next();
switch (choice)
{
case "A" :
System.out.print("Enter the Auther Name: ");
String auther=sc.next();
System.out.print("Enter the Book Title: ");
String title=sc.next();
System.out.print("Enter the year: ");
String year=sc.next();
System.out.print("Enter the ISBN: ");
int ISBN=sc.nextInt();
b=new Book(auther, title, year, ISBN);
//al.add(s);
try{
Connection con=DbUtility.getConnection();
PreparedStatement ps=con.prepareStatement("insert into book values(?,?,?,?)");
ps.setString(1, b.getAuther());
ps.setString(2, b.getTitle());
ps.setString(3, b.getYear());
ps.setString(4, String.valueOf(b.getISBN()));
int x=ps.executeUpdate();
System.out.println(x);
}catch(Exception e){
System.out.println(e);
e.printStackTrace();
}
break;
case "B" :
System.out.print("Update the existing information: ");
try{
Connection con=DbUtility.getConnection();
System.out.println("before query");
PreparedStatement ps=con.prepareStatement("update book set id=?,name=?,email=?,phone=?,uname=?,pasword=? where id=");
//ps.setInt(1, id);
ps.setString(2, b.getAuther());
ps.setString(3, b.getTitle());
ps.setString(4, b.getYear());
ps.setString(5, String.valueOf(b.getISBN()));
System.out.println(3);
int z=ps.executeUpdate();
System.out.println("update operation success");
}catch(Exception e){
System.out.println(e.getMessage());
}
break;
case "C" :
System.out.print("Select the book details from database: ");
try{
//DbUtility db=new DbUtility();
Connection con=DbUtility.getConnection();
Statement st=con.createStatement();
ResultSet rs=st.executeQuery("Select * from book");
while(rs.next()){
System.out.println(1);
System.out.println("i m in showdata ");
System.out.println(2);
System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3)+" "+rs.getString(4)+" "+rs.getString(5));
}
}catch(Exception e){
System.out.println(e.getMessage());
e.printStackTrace();
}
break;
case "D" :
System.out.println("Bye....THANK YOU");
System.exit(0);
break;
default :
System.out.println("Wrong Entry ");
break;
}
}
}
}
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.