I am connecting to a SQLite database. The program is supposed to print out information based on the category the user selects. I tested my SQL statement inside SQLite and it works. The issue I am having is I can't seem to change the placeholder to the corresponding choice. I keep getting the error
the try-with-resources must either be a variable declaration or an expression denoting a reference to a final or effectively final variable
The error comes from
ps.setInt(1, c.getCategory());
Here is my sql statement you will also find it in the Database class
SELECT productCode, productName, listPrice
FROM Product JOIN Category ON Product.categoryID = Category.categoryID
WHERE Category.categoryID = '?';
Here is my code:
Main:
package productsbycategory;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
public class ProductsbyCategory_SharpR_Chap19 {
private static ProductDB pDB = new ProductDB();
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
//Setting header
System.out.println("Products by Category");
System.out.println();
int choice = 0;
while (choice != 999) {
//Setting menu options
System.out.print("""
CATEGORIES
1 - Guitars
2 - Basses
3 - Drums
Enter a category id (999 to exit): """);
//saving input from user
choice = Integer.parseInt(sc.nextLine());
//using switch case to select categories
switch(choice) {
case 1:
//pulling in the ProductDB Class
List<Product> product = pDB.getProduct(choice);
//Calling category class and passing choice into it
Category c = new Category(choice);
c.setCategoryID(choice);
//printing results
if (product == null) {
System.out.println("There are no products");
} else {
Product p;
for (int i = 0; i<product.size(); i++) {
p = product.get(i);
System.out.println(p.getCode() + "\t" + p.getName() + "\t" + p.getPrice());
}
}
System.out.println();
break;
case 2:
System.out.println("#2 works\n");
System.out.println();
break;
case 3:
System.out.println("#3 works\n");
System.out.println();
break;
default:
System.out.println("Bye!\n");
}
}//end of while
}
}
Database:
package productsbycategory;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class ProductDB {
private Connection getConnection() throws SQLException {
//Connection to sqlite database
String dbURL = "jdbc:sqlite:guitar_shop.sqlite";
Connection connection = DriverManager.getConnection(dbURL);
return connection;
} //end of getConnection
public List<Product> getProduct(Category c) {
String sql = """
SELECT productCode, productName, listPrice
FROM Product JOIN Category ON Product.categoryID = Category.categoryID
WHERE Category.categoryID = '?';
""";
List<Product> product = new ArrayList<>();
try (Connection connection = getConnection();
PreparedStatement ps = connection.prepareStatement(sql);
ps.setInt(1, c.getCategoryID());
ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
String code = rs.getString("productCode");
String name = rs.getString("productName");
double price = rs.getDouble("listPrice");
Product p = new Product(code, name, price);
product.add(p);
}//end of while
rs.close();
return product;
} catch (SQLException e){
System.err.println(e);
return null;
}
}
}
Category class:
package productsbycategory;
public class Category {
private int categoryID;
public Category(int categoryID) {
this.categoryID = categoryID;
}
public int getCategoryID() {
return categoryID;
}
public void setCategoryID(int categoryID) {
this.categoryID = categoryID;
}
}
Product class:
package productsbycategory;
import java.text.NumberFormat;
public class Product {
private String code;
private String name;
private double price;
public Product() {};
public Product(String code, String name, double price){
this.code = code;
this.name = name;
this.price = price;
}
public String getCode() {
return code;
}
public void setCode(String code){
this.code = code;
}
public String getName(){
return name;
}
public void setName(String name){
this.name = name;
}
public double getPrice(){
NumberFormat formatter = NumberFormat.getCurrencyInstance();
formatter.format(price);
return price;
}
public void setPrice(double price){
this.price = price;
}
}
I looked in my book and other examples and my syntax seems to be correct so I think I am missing something elsewhere.
I tried changing the
public List<Product> getProduct(Category c) {
to
public List<Product> getProduct(int c) {
However, that doesn't work and it doesn't bring the category class in. So the getCategoryID() isn't in there. To correct this I brought it in using Category c = new Category(choice); That still didn't fix the issue. I still received the same error
The try-with-resources must limit itself to declaring those variables:
try (Connection connection = getConnection();
PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setInt(1, c.getCategoryID());
try (ResultSet rs = ps.executeQuery()) {
List<Product> products = new ArrayList<>();
while (rs.next()) {
String code = rs.getString("productCode");
String name = rs.getString("productName");
double price = rs.getDouble("listPrice");
Product p = new Product(code, name, price);
products.add(p);
} //end of while
return products;
} // end of second try
} catch (SQLException e) {
System.getLogger().log(Level.ERROR, "...");
throw new IllegalArgumentException(e);
}
// No return null!
The solution is to nest one try within another try.
Furthermore either do not catch the exception or rethrow it as above. Also avoid null results. Above I use the System.Logger, a facade for any logging library. So using this in a library itself, any using application may chose its own logging library like log4j.
Hey and placeholders ? are filled-in fully quoted and escaped when needed.
WHERE Category.categoryID = ?
So no apostrophes around question marks.