java - 如何修复ORA-01008 : Not all variables bound

Question

我对这段代码有疑问。当我传递用户并将参数传递给 isLogin 函数时，它会抛出 ORA-01008 错误。我使用 jdbc 连接到 Oracle 数据库。

 public boolean isLogin(Connection conn, String user, String pass) throws SQLException{
   String sql = "SELECT * FROM PRACOWNIK WHERE imie =? AND nazwisko =? ";
        PreparedStatement stmt;
        ResultSet rs;

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, user);
            stmt.setString(2, pass);
            rs = stmt.executeQuery(sql);

            if(rs.next()){
                return true;
            }
            else {
                return false;
            }


        } catch (SQLException e){
            Alert alert = new Alert(Alert.AlertType.ERROR);
            alert.setTitle("Error ");
            alert.setContentText(e.getMessage());
            alert.showAndWait();
            return false;
        }

    }

我在Controller类中使用这个函数

public class Controller implements Initializable{

    public Pracownik pracownik = new Pracownik();

    @FXML
    private Label isConnected;

    @FXML
    private TextField txtUsername;

    @FXML
    private TextField txtPass;

    private Connection conn;

   // private ObservableList<Pracownik> lista = FXCollections.observableArrayList();

    public void initialize(URL url, ResourceBundle rb){
        conn = DBConnection.getConnection();

     //   lista = new Pracownik().getAll(conn);
    }

    public void login(ActionEvent event){
        try {
            if(pracownik.isLogin(conn, txtUsername.getText(), txtPass.getText())){
            isConnected.setText("Correct");
            }
            else{
             isConnected.setText("False");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}

这是一条错误消息

Caused by: Error : 1008, Position : 0, Sql = SELECT pesel FROM PRACOWNIK WHERE imie =:1  AND nazwisko =:2  , OriginalSql = SELECT pesel FROM PRACOWNIK WHERE imie =? AND nazwisko =? , Error Msg = ORA-01008: not all variables bound

当我使用普通的 Select 查询只是为了打印表格时，一切都很好。

Answer 1

您不应再次指定 SQL 查询。已经指定了。更改行:

rs = stmt.executeQuery(sql); // method from java.sql.Statement

至:

rs = stmt.executeQuery(); // method from java.sql.PreparedStatement

第一种方法不考虑参数并“按原样”运行 SQL...因此您会收到您提到的错误。