MySQLSyntaxErrorException报错求助：PreparedStatement条件查询语法问题

报错信息 #

com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''1'' at line 1

代码片段 #

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

    String signString ="";
    if (type == 1)//大于
    {
        signString  = ">=" ;
        System.out.print("1 selected \n");
    }
    if (type == 2)//小于
    {
        signString  = "<=" ;
        System.out.print("2 selected \n");
    }
    if (type == 3)//等于
    {
        signString  = "=" ;
        System.out.print("3 selected \n");
    }

    String sql = "SELECT * FROM total WHERE totals ? ?;";

    PreparedStatement stmt = con.prepareStatement(sql);
    stmt.setString(1,signString);
    stmt.setString(2,amount);

    ResultSet rs = stmt.executeQuery();

问题原因 #

你试图用PreparedStatement的参数替换SQL中的比较运算符（>=/<=/=），但PreparedStatement的参数仅支持替换SQL中的值，不能替换语法关键字或运算符。执行时数据库收到的SQL会变成类似SELECT * FROM total WHERE totals '>=' '1';的非法格式，触发语法错误。

解决方案 #

直接在构建SQL字符串时拼接合法的运算符，仅用PreparedStatement绑定数值参数即可：

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

    String signString = "";
    // 校验type合法性，避免非法值生成无效SQL
    if (type == 1) {
        signString = ">=";
        System.out.println("1 selected");
    } else if (type == 2) {
        signString = "<=";
        System.out.println("2 selected");
    } else if (type == 3) {
        signString = "=";
        System.out.println("3 selected");
    } else {
        throw new IllegalArgumentException("Invalid type value: " + type);
    }

    // 拼接合法运算符，仅绑定数值参数
    String sql = "SELECT * FROM total WHERE totals " + signString + " ?;";

    PreparedStatement stmt = con.prepareStatement(sql);
    // 如果amount是数值类型，建议用对应类型的set方法，比如setDouble/setInt
    stmt.setString(1, amount);

    ResultSet rs = stmt.executeQuery();
    // 后续处理ResultSet存入二维数组的逻辑...
} catch (Exception e) {
    e.printStackTrace();
}

注意事项 #

• 运算符通过固定分支生成，不存在SQL注入风险，直接拼接安全。
• 若amount是数值类型，优先使用setInt/setDouble等类型匹配的方法，避免字符串转数值的潜在问题。
• 必须处理type为非法值的情况，防止生成无效SQL。

内容的提问来源于stack exchange，提问作者Semi Ravitisai Vainitoba