java - 使用变量创建 SQL 语句

Question

我正在尝试制作一个 sql 查询生成器类型程序，该程序使用用户输入数据来为表构建自定义查询

到目前为止我已经

public int checkBetweenDates() throws SQLException{
        String t1 = "2015-07-08"; //or later some user input variable
        String t2 = "2015-07-09";//or later some user input variable
        String id = "22 03 E7 99";//or later some user input variable
        int rowCount = -1;
        //Statement stmt = null;        

        String dateChoice = "select count(*) " 
                + "from dancers " 
                + "where ts between (t1) and (t2)"
                + "and id = (id)"
                + "values (?)";

        Connection conn = DriverManager.getConnection(host, username, password);
        System.out.println("Connected:");
         PreparedStatement preparedStmt = (PreparedStatement) conn.prepareStatement(dateChoice);
         preparedStmt.setString    (1, t1);
//       preparedStmt.setString    (2, t2);
//       preparedStmt.setString    (3, id);
        // stmt = conn.createStatement();
        ResultSet rs = preparedStmt.executeQuery(dateChoice); 
        try {
                rs = preparedStmt.executeQuery(dateChoice);
                rs.next();
                rowCount = rs.getInt(1);
                System.out.println(rowCount);
            }
         catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        finally {
            rs.close();
            preparedStmt.close();
        }
        return rowCount; 

    }

所以它连接并且一切正常，但它不执行查询，说值的 sql 语法有问题(?,?,?)

任何帮助都会很棒，谢谢大家!!

卡尔

Answer 1

试试这个，查询和设置准备好的语句参数的更改，

public int checkBetweenDates() throws SQLException{
        String t1 = "2015-07-08"; //or later some user input variable
        String t2 = "2015-07-09";//or later some user input variable
        String id = "22 03 E7 99";//or later some user input variable
        int rowCount = -1;
        //Statement stmt = null;        

        String dateChoice = "select count(*) " 
                + "from dancers " 
                + "where ts between ? and ?"
                + "AND id = ?";

        Connection conn = DriverManager.getConnection(host, username, password);
        System.out.println("Connected:");
         PreparedStatement preparedStmt = (PreparedStatement) conn.prepareStatement(dateChoice);
       preparedStmt.setString    (1, t1);
       preparedStmt.setString    (2, t2);
       preparedStmt.setString    (3, id);
        // stmt = conn.createStatement();
        ResultSet rs = preparedStmt.executeQuery(dateChoice); 
        try {
                rs = preparedStmt.executeQuery(dateChoice);
                rs.next();
                rowCount = rs.getInt(1);
                System.out.println(rowCount);
            }
         catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        finally {
            rs.close();
            preparedStmt.close();
        }
        return rowCount; 

    }

如果不适合您，请分享确切的错误。