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();
        }
java jdbc
Eddie

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.

rekiem87
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.

Basil Bourque

Related Issues

Last questions

Hot tags

javascript java c# python android php jquery c++ html ios css sql mysql .net c r asp.net ruby-on-rails objective-c arrays node.js sql-server iphone regex ruby angularjs json swift django linux asp.net-mvc xml wpf angular spring string ajax python-3.x git excel windows xcode multithreading pandas database reactjs bash scala algorithm eclipse html5 vba performance macos postgresql mongodb laravel oracle visual-studio list vb.net wordpress linq entity-framework winforms unit-testing matlab typescript image python-2.7 perl twitter-bootstrap numpy css3 qt swing hibernate c++11 shell apache amazon-web-services haskell maven forms azure facebook powershell rest function excel-vba delphi tsql ruby-on-rails-3 symfony google-chrome firebase api docker dataframe oop