SQL USING CHINOOK DATABASE SCHEMA 1.1 IN JAVA CAN SOMEONE PLEASE HELP ME FIX THI
ID: 3865114 • Letter: S
Question
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 <path to Chinook database> <query #> [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();
}
}
}
Explanation / Answer
/*
I have added comments for reference and corrected your prepared statement.
However to correctly work the program you need to set the appropriate parameters by their type
I have shown one example but since i didn't know the type of your columns in your table I haven't added them
Also take care of spaces in your sql queries while creating them through concatenation, I have corrected the few.
*/
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 <path to Chinook database> <query #> [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])) {
// I have commented this code as statement is prepared after setting the proper sql.
//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 + " ");
// After you have set the sql using query number we can now use prepare statement
// kindly note you do have to set the proper parameters through functions such as
// stmt.setString() and stmt.setInt() etc depending on your sql function
PreparedStatement stmt = connection.prepareStatement(sql);
// get results
// I have commented out the code as executeQuery does not need sql as
// you have already specified it in prepared statement
// ResultSet res = stmt.executeQuery(sql);
ResultSet res = stmt.executeQuery();
while ( res.next() ) {
// We dont print the result set but retrieve the data through column names
// For eg.
// String userid = rs.getString("USER_ID");
// String username = rs.getString("USERNAME");
// Use similar notations and change the in quote values to match column name in your table
//System.out.println("nSQL: " + res + " " );
}
}catch (SQLException e) {
System.out.println("Invalid database: " + e.getMessage());
throw new UsageException();
}
}
}
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.