MySQL PreparedStatement动态操作符使用指南：规避语法错误

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DynamicQueryExample {

    public static ResultSet executeDynamicQuery(int type, String amount) throws SQLException, ClassNotFoundException {
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jsupermarket", "root", "");

            String signString = "";
            if (type == 1) { // greater than or equal
                signString = ">=";
                System.out.print("Type 1 selected (>=)\n");
            } else if (type == 2) { // less than or equal
                signString = "<=";
                System.out.print("Type 2 selected (<=)\n");
            } else if (type == 3) { // equal
                signString = "=";
                System.out.print("Type 3 selected (=)\n");
            } else {
                // 处理无效类型或提供默认值
                throw new IllegalArgumentException("Invalid query type provided.");
            }

            // 正确的做法：将操作符直接拼接进SQL字符串，而值使用占位符
            // 注意：SQL语句末尾不应有分号，JDBC驱动会自动处理
            String sql = "SELECT * FROM total WHERE totals " + signString + " ?";

            stmt = con.prepareStatement(sql);
            stmt.setString(1, amount); // 绑定实际的比较值

            rs = stmt.executeQuery();
            return rs;

        } finally {
            // 在生产环境中，务必确保资源（ResultSet, PreparedStatement, Connection）得到正确关闭
            // if (rs != null) { try { rs.close(); } catch (SQLException ignore) {} }
            // if (stmt != null) { try { stmt.close(); } catch (SQLException ignore) {} }
            // if (con != null) { try { con.close(); } catch (SQLException ignore) {} }
        }
    }

    public static void main(String[] args) {
        try {
            // 示例调用
            System.out.println("Querying for totals >= 100:");
            ResultSet result1 = executeDynamicQuery(1, "100"); // totals >= 100
            while (result1.next()) {
                System.out.println("Total: " + result1.getString("totals"));
            }
            // 实际应用中，处理完ResultSet后应立即关闭
            if (result1 != null) result1.close();

            System.out.println("\nQuerying for totals <= 50:");
            ResultSet result2 = executeDynamicQuery(2, "50"); // totals <= 50
            while (result2.next()) {
                System.out.println("Total: " + result2.getString("totals"));
            }
            if (result2 != null) result2.close();

        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
}