Modify this JAVA code to meet these requirements:

When your application starts, it reads a customer name and password from the command line. It validates them against the database and retains the customer id throughout the session. All rentals/returns are on behalf of this single customer: to change the customer you will quit the application and restart it with another customer. Much of this logic is already provided in the starter code. Once the application is started, the user can select one of the following transactions. (We call each action a transaction. You will need to write some of them as SQL transactions. Others are interactions with the database that do not require transactions.)

1.The "search" transaction: the user types in a string, and you return:

all movies whose title matches the string

their producer(s)

their actor(s)

an indication of whether the movie is available for rental (remember that you can rent the movie to only one customer at a time), or whether the movie is already rented by this customer (some customers forget: be nice to them), or whether it is unavailable (rented by someone else). The starter code already returns the movies and producers: you still need to retrieve and print the actors and the availability status. In the starter code, the producers are obtained using a "dependent join" (sometimes called "nested loop join"). You should use the same technique for retrieving the actors: there is a more efficient way, but we'll implement that in "fastsearch".

2.The "plan" transaction: Here, the customer types in a plan id and you set his/her new plan to this plan id. How do the customers know what plan ids are available? They type in "plan" without any plan id, and then you will list all available plans, their names, and their terms (maximum number of movies available for rental and monthly fees). What you will learn in 2: simple database updates from Java.

3.The "rent" transaction: The user types in a movie id, and you will "rent" that movie to the customer.

4. The "return" transaction: The user types in the movie id to be returned. You update your records to mark the return.

5. The "fastsearch" function. Here, you will fix the performance problem in the "search" function: "fastsearch" has exactly the same functionality as "search" (except that it doesn't have to return the availability status). However, in fastsearch all the dependent joins are replaced with regular joins, executed in the database engine. Specifically, you will first have a query that retrieves all the movie_ids that satisfy your search criterion, and the corresponding titles.

Second, you will have a query that retrieves all the movie_ids that satisfy your search criterion, and the corresponding producers.

Third, you will have a query that retrieves all the movie_ids that satisfy your query and the corresponding actors. Add ORDER BY to each of these queries, so you can merge-join the three streams efficiently in your Java code.

/*JAVA CODE Query.java*/

import java.util.Properties;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.io.FileInputStream;

/**

* Runs queries against a back-end database

*/

public class Query {

private static Properties configProps = new Properties();

private static String MySqlServerDriver;

private static String MySqlServerUrl;

private static String MySqlServerUser;

private static String MySqlServerPassword;

private static String PsotgreSqlServerDriver;

private static String PostgreSqlServerUrl;

private static String PostgreSqlServerUser;

private static String PostgreSqlServerPassword;

// DB Connection

private Connection _mySqlDB; //IMDB

private Connection _postgreSqlDB; //customer_DB

// Canned queries

private String _search_sql = "SELECT * FROM movie_info WHERE movie_name like ? ORDER BY movie_id";

private PreparedStatement _search_statement;

private String _producer_id_sql = "SELECT y.* "

+ "FROM producer_movies x, producer_ids y "

+ "WHERE x.movie_id = ? and x.producer_id = y.producer_id";

private PreparedStatement _producer_id_statement;

/* uncomment, and edit, after your create your own customer database

private String _customer_login_sql = "SELECT * FROM customer WHERE login = ? and password = ?";

private PreparedStatement _customer_login_statement;

private String _begin_transaction_read_write_sql = "START TRANSACTION";

private PreparedStatement _begin_transaction_read_write_statement;

private String _commit_transaction_sql = "COMMIT";

private PreparedStatement _commit_transaction_statement;

private String _rollback_transaction_sql = "ROLLBACK";

private PreparedStatement _rollback_transaction_statement;

*/

public Query() {

}

/**********************************************************/

/* Connection to MySQL database */

public void openConnections() throws Exception {

/* open connections to TWO databases: movie and customer databases */

configProps.load(new FileInputStream("dbconn.config"));

MySqlServerDriver = configProps.getProperty("MySqlServerDriver");

MySqlServerUrl = configProps.getProperty("MySqlServerUrl");

MySqlServerUser = configProps.getProperty("MySqlServerUser");

MySqlServerPassword = configProps.getProperty("MySqlServerPassword");

PsotgreSqlServerDriver = configProps.getProperty("PostgreSqlServerDriver");

PostgreSqlServerUrl = configProps.getProperty("PostgreSqlServerUrl");

PostgreSqlServerUser = configProps.getProperty("PostgreSqlServerUser");

PostgreSqlServerPassword = configProps.getProperty("PostgreSqlServerPassword");

/* load jdbc driver for MySQL */

Class.forName(MySqlServerDriver);

/* open a connection to your mySQL database that contains the movie database */

_mySqlDB = DriverManager.getConnection(MySqlServerUrl, // database

MySqlServerUser, // user

MySqlServerPassword); // password

/* load jdbc driver for PostgreSQL */

Class.forName(PsotgreSqlServerDriver);

/* connection string for PostgreSQL */

String PostgreSqlConnectionString = PostgreSqlServerUrl+"?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory&user="+

PostgreSqlServerUser+"&password=" + PostgreSqlServerPassword;

/* open a connection to your postgreSQL database that contains the customer database */

_postgreSqlDB = DriverManager.getConnection(PostgreSqlConnectionString);

}

public void closeConnections() throws Exception {

_mySqlDB.close();

_postgreSqlDB.close();

}

/**********************************************************/

/* prepare all the SQL statements in this method.

"preparing" a statement is almost like compiling it. Note

that the parameters (with ?) are still not filled in */

public void prepareStatements() throws Exception {

_search_statement = _mySqlDB.prepareStatement(_search_sql);

_producer_id_statement = _mySqlDB.prepareStatement(_producer_id_sql);

/* uncomment after you create your customers database */

/*

_customer_login_statement = _postgreSqlDB.prepareStatement(_customer_login_sql);

_begin_transaction_read_write_statement = _postgreSqlDB.prepareStatement(_begin_transaction_read_write_sql);

_commit_transaction_statement = _postgreSqlDB.prepareStatement(_commit_transaction_sql);

_rollback_transaction_statement = _postgreSqlDB.prepareStatement(_rollback_transaction_sql);

*/

/* add here more prepare statements for all the other queries you need */

/* . . . . . . */

}

/**********************************************************/

/* suggested helper functions */

public int helper_compute_remaining_rentals(int cid) throws Exception {

/* how many movies can she/he still rent ? */

/* you have to compute and return the difference between the customer's plan

and the count of outstanding rentals */

return (99);

}

public String helper_compute_customer_name(int cid) throws Exception {

/* you find the name of the current customer */

return ("Joe Name");

}

public boolean helper_check_plan(int plan_id) throws Exception {

/* is plan_id a valid plan id? you have to figure out */

return true;

}

public boolean helper_check_movie(String movie_id) throws Exception {

/* is movie_id a valid movie id? you have to figure out */

return true;

}

private int helper_who_has_this_movie(String movie_id) throws Exception {

/* find the customer id (cid) of whoever currently rents the movie movie_id; return -1 if none */

return (77);

}

/**********************************************************/

/* login transaction: invoked only once, when the app is started */

public int transaction_login(String name, String password) throws Exception {

/* authenticates the user, and returns the user id, or -1 if authentication fails */

/* Uncomment after you create your own customers database */

/*

int cid;

_customer_login_statement.clearParameters();

_customer_login_statement.setString(1,name);

_customer_login_statement.setString(2,password);

ResultSet cid_set = _customer_login_statement.executeQuery();

if (cid_set.next()) cid = cid_set.getInt(1);

else cid = -1;

return(cid);

*/

return (55); //comment after you create your own customers database

}

public void transaction_personal_data(int cid) throws Exception {

/* println the customer's personal data: name and number of remaining rentals */

}

/**********************************************************/

/* main functions in this project: */

public void transaction_search(int cid, String movie_name)

throws Exception {

/* searches for movies with matching names: SELECT * FROM movie WHERE movie_name LIKE name */

/* prints the movies, producers, actors, and the availability status:

AVAILABLE, or UNAVAILABLE, or YOU CURRENTLY RENT IT */

/* set the first (and single) '?' parameter */

_search_statement.clearParameters();

_search_statement.setString(1, '%' + movie_name + '%');

ResultSet movie_set = _search_statement.executeQuery();

while (movie_set.next()) {

String movie_id = movie_set.getString(1);

System.out.println("ID: " + movie_id + " NAME: "

+ movie_set.getString(2) + " YEAR: "

+ movie_set.getString(3) + " RATING: "

+ movie_set.getString(4));

/* do a dependent join with producer */

_producer_id_statement.clearParameters();

_producer_id_statement.setString(1, movie_id);

ResultSet producer_set = _producer_id_statement.executeQuery();

while (producer_set.next()) {

System.out.println("\t\tProducer name: " + producer_set.getString(2));

}

producer_set.close();

/* now you need to retrieve the actors, in the same manner */

/* then you have to find the status: of "AVAILABLE" "YOU HAVE IT", "UNAVAILABLE" */

}

System.out.println();

}

public void transaction_choose_plan(int cid, int pid) throws Exception {

/* updates the customer's plan to pid: UPDATE customer SET plid = pid */

/* remember to enforce consistency ! */

}

public void transaction_list_plans() throws Exception {

/* println all available plans: SELECT * FROM plan */

}

public void transaction_rent(int cid, String movie_id) throws Exception {

/* rend the movie movie_id to the customer cid */

/* remember to enforce consistency ! */

}

public void transaction_return(int cid, String movie_id) throws Exception {

/* return the movie_id by the customer cid */

}

public void transaction_fast_search(int cid, String movie_name)

throws Exception {

/* like transaction_search, but uses joins instead of dependent joins

Needs to run three SQL queries: (a) movies, (b) movies join producers, (c) movies join actors

Answers are sorted by movie_id.

Then merge-joins the three answer sets */

}

}