Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

SQL USING CHINOOK DATABASE SCHEMA 1.1 IN JAVA CAN SOMEONE PLEASE HELP ME FIX THIS CODE. I AM HAVING TROUBLE GETTING THE PREPARED STATEMENT TO

SQL USING CHINOOK DATABASE SCHEMA 1.1 IN JAVA

CAN SOMEONE PLEASE HELP ME FIX THIS CODE. I AM HAVING TROUBLE GETTING THE PREPARED STATEMENT TO RETURN THE RESULTS.

import java.sql.Connection;

public class ChinookApp { /** * Exception handler for Usage statement */ private static class UsageException extends RuntimeException { UsageException() { usage(); } public void usage() { System.out.println("Usage: java ChinookApp [parameter value]"); System.out.println(); System.out.println("1) How many customers live in country [parameter value]?"); System.out.println("2) List all employees (sort by employee id)"); System.out.println("3) How many customers have been supported by employee id [parameter value]?"); System.out.println("4) List all customers (sort by customer id)"); System.out.println("5) List all invoices for customer #[parameter value] (sort by invoice id, each line by invoice line id)"); System.out.println(); } } /** * Validates command-line arguments * * @param args command-line arguments * @return query number */ private static int getQueryNumber(String[] args) { // must have at least two arguments if (args.length < 2) { throw new UsageException(); } // make sure second argument is a valid query number // and third is appropriate to query try { final int queryNum = Integer.valueOf(args[1]); switch (queryNum) { case 1: case 3: case 5: if (args.length != 3) { throw new UsageException(); } if (queryNum != 1) { Integer.valueOf(args[2]); } break; case 2: case 4: if (args.length != 2) { throw new UsageException(); } break; default: throw new UsageException(); } return queryNum; } catch (NumberFormatException e) { throw new UsageException(); } }

/** * Command-line Chinook utility * * @param args command-line arguments * @throws ClassNotFoundException cannot find JDBC driver * @throws SQLException SQL gone bad */ public static void main(String[] args) throws ClassNotFoundException { String sql= ""; // validates the inputs, exits if bad int queryNum = getQueryNumber(args); // load the sqlite-JDBC driver using the current class loader Class.forName( "org.sqlite.JDBC" ); String param=""; // makes a connection to the database try (Connection connection = DriverManager.getConnection("jdbc:sqlite:" + args[0])) { PreparedStatement stmt = connection.prepareStatement(sql); //stmt.setString(1, param); if (queryNum == 1) { sql = "SELECT *"+ "FROM Customer"+ "WHERE Customer.Country = ?";

} else if (queryNum == 2) { sql = "SELECT * Employee"+ "FROM Employee"+ "GROUP BY Employee.EmployeeId";

} else if (queryNum == 3) { sql = "SELECT EmployeeId"+ "count(CustomerId)"+ "FROM Employee"+ "customer"+ "WHERE SupportRepid=EmployeeId";

} else if (queryNum == 4) { sql = "SELECT * Customer"+ "FROM Customer"+ "GROUP BY Customer.CustomerId";

} else if (queryNum == 5) { sql = "SELECT InvoiceId"+ "CustomerId"+ "FROM invoices"+ "GROUP BY customerId"+ "ORDER BY InvoiceId";

} System.out.println(" SQL: " + sql + " "); // get results ResultSet res = stmt.executeQuery(sql); while ( res.next() ) { System.out.println("nSQL: " + res + " " ); } }catch (SQLException e) { System.out.println("Invalid database: " + e.getMessage()); throw new UsageException(); } } }

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Oracle PL/SQL Programming Database Management Systems

Authors: Steven Feuerstein

1st Edition

978-1565921429

More Books

Students also viewed these Databases questions