Question

Question 2: consider the following library relational database schema Write a program segment to retrieves the list of books that became overdue yesterday and that prints the book title and borrower name for each.

1- Use JDBC with Java as the host language

Answer 1

Answer:

Java Code:

package com.java2novice.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MyResultSetEx //Main Class
{

    public static void main(String a[]) //Main Function
{
         
        try
   {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection con = DriverManager.getConnection("jdbc:oracle:thin:@<hostname>:<port num>:<DB name>","user","password"); // Provide the database name,username and password details to establish the connection with database
            Statement stmt = con.createStatement();
            System.out.println("Created DB Connection...."); //Displaying if connection is established with database
            ResultSet rs = stmt.executeQuery("SELECT BK.Title, BW.Name FROM BOOK_LOANS BL JOIN BORROWER BW ON BL.Card_no=BW.Card_no JOIN BOOK BK ON BL.Book_id=BK.Book_id WHERE BL.Due_date < SYSDATE - 1;"); //SQL query to fetch book title and borrower name for whom due date was yesterday
            while(rs.next())
   {
                System.out.println(rs.getString(1)); //Printing the book title
                System.out.println(rs.getString(2)); //Printing the borrower name
            }
            rs.close();
            con.close(); //Closing the connection with database
        }
  catch (ClassNotFoundException e)
  {
            System.err.print("SQLException: ");
   e.printStackTrace();
        } catch (SQLException e)
  {
            System.err.print("SQLException: ");
   e.printStackTrace();
        }
    }
}