使用PreparedStatement占位符查询SQLite时的Try-with-resources错误

我在开发基于SQLite的商品分类查询程序时，遇到了两个核心问题：

1. 无法用用户选择的类别ID替换SQL占位符，报错：

the try-with-resources must either be a variable declaration or an expression denoting a reference to a final or effectively final variable

2. 即使调整方法参数，依然无法正确传递参数到SQL语句中

错误原因分析 #

1. Try-With-Resources语法误用：try的括号内只能放置资源声明语句，不能插入ps.setInt()这类方法调用，你把参数绑定代码放在了try括号里，违反语法规则。
2. SQL占位符被当成字符串字面量：SQL语句中?被单引号包裹（'?'），导致SQL将其识别为普通字符串，而非参数占位符，无法完成参数绑定。
3. 方法调用参数不匹配：Main类中调用getProduct时传入的是int类型，但方法定义要求传入Category对象，类型不匹配导致错误。

修复后的代码 #

1. ProductDB类修正

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 {
        String dbURL = "jdbc:sqlite:guitar_shop.sqlite";
        Connection connection = DriverManager.getConnection(dbURL);
        return connection;
    }

    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-with-resources结构，仅在括号内声明资源
        try (Connection connection = getConnection();
             PreparedStatement ps = connection.prepareStatement(sql)) {
            // 将参数绑定代码移到try块内部
            ps.setInt(1, c.getCategoryID());
            // 嵌套try-with-resources管理ResultSet
            try (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);
                }
                return product;
            }
        } catch (SQLException e){
            System.err.println(e);
            return null;
        }
    }
}

2. Main类修正

package productsbycategory;

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);
        System.out.println("Products by Category");
        System.out.println();
        
        int choice = 0;
        while (choice != 999) {
            System.out.print("""
                           CATEGORIES
                           1 - Guitars
                           2 - Basses
                           3 - Drums
                            
                           Enter a category id (999 to exit):  """);
        
            choice = Integer.parseInt(sc.nextLine());
            
            switch(choice) {
                case 1: 
                    // 创建Category对象并传入getProduct方法
                    Category guitarCategory = new Category(choice);
                    List<Product> guitarProducts = pDB.getProduct(guitarCategory);
                   
                    if (guitarProducts == null || guitarProducts.isEmpty()) {
                        System.out.println("There are no products");
                    } else {
                        for (Product p : guitarProducts) {
                            System.out.println(p.getCode() + "\t" + p.getName() + "\t" + p.getPrice());
                        }
                    }
                    System.out.println();
                    break;
                case 2: 
                    Category bassCategory = new Category(choice);
                    List<Product> bassProducts = pDB.getProduct(bassCategory);
                    if (bassProducts == null || bassProducts.isEmpty()) {
                        System.out.println("There are no products");
                    } else {
                        for (Product p : bassProducts) {
                            System.out.println(p.getCode() + "\t" + p.getName() + "\t" + p.getPrice());
                        }
                    }
                    System.out.println();
                    break;
                case 3: 
                    Category drumCategory = new Category(choice);
                    List<Product> drumProducts = pDB.getProduct(drumCategory);
                    if (drumProducts == null || drumProducts.isEmpty()) {
                        System.out.println("There are no products");
                    } else {
                        for (Product p : drumProducts) {
                            System.out.println(p.getCode() + "\t" + p.getName() + "\t" + p.getPrice());
                        }
                    }
                    System.out.println();
                    break;
                default: 
                    System.out.println("Bye!\n");
            }  
        }
        sc.close();
    }
}

3. Product类优化（可选）

原getPrice方法中格式化货币后未返回结果，修正后可同时支持原始价格和格式化价格：

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 String getFormattedPrice(){
        NumberFormat formatter = NumberFormat.getCurrencyInstance();
        return formatter.format(price);
    }
    // 返回原始价格数值
    public double getPrice(){
        return price;
    }
    public void setPrice(double price){
        this.price = price;
    }
}

若需显示格式化价格，在Main类中调用p.getFormattedPrice()即可。

关键修复点总结 #

• 严格遵循Try-With-Resources语法：仅在括号内声明资源对象，业务代码放在块内
• SQL占位符?不能添加单引号，否则会被识别为普通字符串
• 确保方法调用的参数类型与方法定义完全匹配

内容的提问来源于stack exchange，提问作者Zapherail