java jdbc check if value exists before inserting into table

Refresh

April 2019

Views

186 time

1

I'm working on a Java project and I've hit a wall regarding inserting values into my table. I wanted to have a check before inserting into this table to see if there were any duplicates.

I've tried an executeUpdate with a resultset for executequery afterwards, but I'm getting "java.sql.SQLException: Can not issue data manipulation statements with executeQuery()."

Here is the code I'm working with, thank you in advanced!

    public void registernow(ActionEvent actionEvent) throws IOException, SQLException {
    ConnectionClass registerClass = new ConnectionClass();
    Connection register = registerClass.getConnection();

    try {
        Statement registerStatement = register.createStatement();
        String sql = "INSERT INTO userAuth (username, password) VALUES ('" + txt_username.getText() + "' , '" + txt_password.getText() + "')";
        registerStatement.executeUpdate(sql);

        ResultSet resultSet = registerStatement.executeQuery(sql);

        if (resultSet.next()) {
            FXMLLoader fxmlLoader = new FXMLLoader(getClass().getResource("register.fxml"));
            Parent fxmlregister = (Parent) fxmlLoader.load();
            Stage stage = new Stage();
            stage.setTitle("COMP228 Project Registration Complete!");
            stage.setScene(new Scene(fxmlregister));
            stage.show();
        } else {
            FXMLLoader fxmlLoader = new FXMLLoader(getClass().getResource("registerfailed.fxml"));
            Parent fxmlregisterfailed = (Parent) fxmlLoader.load();
            Stage stage = new Stage();
            stage.setTitle("COMP228 Project Registration Failed!");
            stage.setScene(new Scene(fxmlregisterfailed));
            stage.show();
        }

2 answers

2

As the error and @JBNizet already told you, you are trying to execute the same update statement but with the ExecuteQuery.

So you are trying to execute an update statement with a executeQuery, you should try your second command with a query

sql = "SELECT COUNT(1) FROM yourTable WHERE yourCondition = true";
ResultSet resultSet = registerStatement.executeQuery(sql);

And you sould really take notes of the comment of @JBNizet, is bad to concat strings to create queries.

2

The Answer by rekiem87 is correct, as are the two comments on the Question.

UPSERT

Besides those points, if you wish to update an existing record or otherwise insert a new one, that behavior is commonly known as an UPSERT (meaning, UPdate or else inSERT) or a “merge”.

As I recall, such a feature is not defined in the SQL standard. But many databases have implemented one.

For example, in Postgres, use INSERT ON CONFLICT. See this first look, this tutorial, this posting, feature description for Postgres 9.5, and this presentation on YouTube.

INSERT INTO user_ ( username_ , password_ )
VALUES ( 'Alice' , 'pw123' ) 
ON CONFLICT ( username_ )
DO UPDATE SET ( password_ ) = ( 'pw123' )
;

Note that at least in the Postgres implementation, you can choose to do nothing if a row already exists.

INSERT INTO user_ ( username_ , password_ )
VALUES ( 'Alice' , 'pw123' ) 
ON CONFLICT ( username_ )
DO NOTHING
;

Of course I am ignoring the entire topic of properly storing passwords for users. I am sure this example in the Question is quite contrived and that you would never just write passwords.