javasqlitetry-with-resources

Try-with-resources error when trying to pull information with a prepared statement placeholder


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


Solution

  • 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.