Question: Java -- Working with Databases (classes used: PreparedStatement, Scanner) Use at least 2 tables in a multi-table SQLite database. (This is provided Below) Take an
Java -- Working with Databases (classes used: PreparedStatement, Scanner)
Use at least 2 tables in a multi-table SQLite database. (This is provided Below)
Take an input from the user (use the Scanner class) and use the input to build and run a query (use PreparedStatement) against the database. The input must be validated/sanitized appropriately, preventing SQL injection.
Print output that includes data read from the database. Output should include information from at least 2 tables and be in a user-friendly form.
Provided examples for input to be used:
Ask the user for a maximum part cost, retrieve only parts with that cost or less, and print those part details along with the average quantity ordered on a purchase order. (PART and PO_LINE_ITEM tables involved)
Main:
import java.util.Scanner;
public class Main {
public static void main(String[] args) {
/**
* Add the Scanner Class to ask the user questions
*/
Scanner scanner = new Scanner(System.in);
System.out.print("Enter the maximum part cost: ");
String stateName = scanner.nextLine();
/**
* Create the PreparedStatements below
*/
System.out.println("FiredUp Customers:");
FiredUpDB firedUp = new FiredUpDB();
List
for (Customer cust : customers) {
System.out.println("ID: " + cust.getId() +
", Name: " + cust.getName() +
", City: " + cust.getCity() +
", State: " + cust.getState() +
", Email Address(es): " + cust.getEmailAddresses());
}
}
}
Other Java files used along with Main:
FiredUpDB.java
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class FiredUpDB {
private static final String FIREDUP_DB = "jdbc:sqlite:FiredUp.db";
private static final String CUSTOMER_SQL = "SELECT CustomerID, Name, City, StateProvince FROM CUSTOMER";
private static final String EMAIL_SQL = "SELECT EmailAddress FROM EMAIL WHERE FK_CustomerID = ?";
/**
* Read all customers from the FiredUp database and return them as a list of Customer objects
* @return a list of customers from the FiredUp database
*/
public List
ArrayList
readEmailAddresses(customers);
return customers;
}
/**
* Read from the FiredUp database customers from the given state
* @param state the state of interest (US state or Canadian province)
* @return a list of customers from the given state
*/
// Create method readCustomersFromState
/**
* @return a connection to the FiredUp database
* @throws SQLException if unable to connect
*/
private Connection getConnection() throws SQLException {
return DriverManager.getConnection(FIREDUP_DB);
}
/**
* Read customers from the database, including their basic properties from the CUSTOMER table,
* but not including customer data from related tables such as email addresses or phone numbers
* @return a list of Customer objects
*/
private ArrayList
ArrayList
try (
Connection conn = getConnection();
PreparedStatement stmt = conn.prepareStatement(CUSTOMER_SQL);
ResultSet rs = stmt.executeQuery()
) {
while (rs.next()) {
customers.add(new Customer(rs.getInt("CustomerID"),
rs.getString("Name"),
rs.getString("City"),
rs.getString("StateProvince")));
}
} catch (SQLException e) {
e.printStackTrace();
}
return customers;
}
/**
* Read email addresses from the database for each customer in the given list,
* adding the email addresses found to the corresponding Customer object
* @param customers list of customers whose email addresses should be read
*/
private void readEmailAddresses(ArrayList
try (
Connection conn = getConnection();
PreparedStatement stmt = conn.prepareStatement(EMAIL_SQL)
) {
for (Customer cust : customers) {
stmt.setInt(1, cust.getId());
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
cust.addEmailAddress(rs.getString("EmailAddress"));
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Final Note: Output of running Main should ask the user to input an int value for maximum value of a part output parts with that cost or less. Table names are in bold above.
Step by Step Solution
There are 3 Steps involved in it
Get step-by-step solutions from verified subject matter experts
Study Help