java - 无法将参数添加到准备好的语句中。 JAVA。 Spring MVC

Question

我有一个带有准备好的语句的方法。当我使用 set string/int/etc 方法添加参数时，它显示 java.sql.SQLException: Parameter index out of range (3 > number of parameters, which is 0).在控制台中我收到这样的查询 select*from users where users.enabled = true AND users.weight <= ? AND users.gender LIKE ? 。它不会取代我的?元素。这是我的方法!请帮忙!

        @SuppressWarnings("unchecked")
    @Override
    public List<Users> listUsersSort(int weight, String gender, String place, int ageTo, String currentUser) {
        System.out.println(weight+gender+place+ageTo+currentUser);
        Connection dbConnection = null;
        PreparedStatement preparedStatement = null;
        Session session=null;

        int iterator=0;
        List<Users> usersList =null;
        String selectSQL="select users.username, users.checkusr, users.password, users.name, users.enabled, users.surname, users.email, users.gender, users.age, users.weight, users.height, users.sport, users.place, users.photo from users where users.enabled = true";
        System.out.println(selectSQL);
        Connection con=getConnection();
        try {
            preparedStatement = con.prepareStatement(selectSQL);
        } catch (SQLException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
        //String query = ";
        if (weight<40 == false) {
            String weightParam = " AND users.weight <= ?";
            selectSQL=selectSQL.concat(weightParam);
            System.out.println(selectSQL);
        //  query = query.concat(weightParam);
            try {
                iterator+=1;
                System.out.println(iterator);
                preparedStatement.setInt(iterator, weight);
            } catch (SQLException e) {
                System.out.println("second catch block");
                e.printStackTrace();
            }
            System.out.println(selectSQL);
        }
        if (gender.isEmpty() == false) {
            String genderParam = " AND  users.gender LIKE '?'";
            selectSQL=selectSQL.concat(genderParam);
            try {
                iterator+=1;
                preparedStatement.setString(iterator, gender);
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }

            //String genderParam = " AND  users.gender LIKE " + "'" + gender + "'";
            //query = query.concat(genderParam);
            System.out.println(selectSQL);
        }

        if (place.isEmpty() == false) {
            String placeParam = " AND users.place LIKE '?'";
            selectSQL=selectSQL.concat(placeParam);
            try {
                iterator+=1;
                preparedStatement.setString(iterator, place);
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } 
            //query = query.concat(placeParam);
            System.out.println(selectSQL);
        }
        if (ageTo<40 == false) {
            String age = " AND users.age <= ?";
            selectSQL=selectSQL.concat(age);
            try {
                iterator+=1;
                preparedStatement.setInt(iterator, ageTo);
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            //query = query.concat(age);
            System.out.println(selectSQL);
        }
        String withoutUser=" AND users.username NOT LIKE '?'";
        //query=query.concat(withoutUser);
        selectSQL=selectSQL.concat(withoutUser);
        try {
            iterator+=1;
            preparedStatement.setString(iterator, currentUser);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        ResultSet rs=null;
        try {
            rs = preparedStatement.executeQuery();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        try {
            while (rs.next()) {

                String username = rs.getString("username");

                System.out.println("username : " + username);

            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return usersList;
    }

Answer 1

在通过连接 weightParam 等修改 selectSQL 之前，您正在执行 preparedStatement = con.prepareStatement(selectSQL);。

在将 weightParam 等连接到 selectSQL 后，您应该执行以下准备准备语句的任务:

try {
    preparedStatement = con.prepareStatement(selectSQL);
} catch (SQLException e1) {
    e1.printStackTrace();
}

然后尝试通过执行以下操作为您的准备好的语句设置值:

preparedStatement.setInt(iterator, weight);

或者，您可以执行以下操作:

if (weight<40 == false) {
    String weightParam = " AND users.weight <= ?";
    selectSQL = selectSQL.concat(weightParam);
    System.out.println(selectSQL);

    try {
        ++iterator;
        System.out.println(iterator);
        /* Note : Change Here */
        preparedStatement = con.prepareStatement(selectSQL);
        preparedStatement.setInt(iterator, weight);
    } catch (SQLException e) {
        System.out.println("second catch block");
        e.printStackTrace();
    }
    System.out.println(selectSQL);
}