JAVA SQL QUERY HELP I AM HAVING TROUBLE GETTING THE PREPARED STATEMENT TO RETURN
ID: 3801881 • Letter: J
Question
JAVA SQL QUERY HELP
I AM HAVING TROUBLE GETTING THE PREPARED STATEMENT TO RETURN THE RESULTS.
IT IS RETURNING THE ACTUAL SQL SELECT STATEMENTS.
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 Count(*)"+ "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
//Corrected the program as below
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class ChinookApp {
/** * Exception handler for Usage statement */
@SuppressWarnings("serial")
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])) {
try{
Connection connection = DriverManager.getConnection("jdbc:sqlite:" + args[0]);
//stmt.setString(1, param);
int queryNum = getQueryNumber(args);
if (queryNum == 1) {
sql = "SELECT Count(*)"+ " FROM Customer "+ "WHERE Customer.Country = ?";} //replace the ? with some value
else if (queryNum == 2) {
sql = "SELECT * "+ "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 * "+ "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
PreparedStatement stmt = connection.prepareStatement(sql);
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();
}
}}
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.