Java/Groovy and MySQL: Checking if row exists in table

Question

I am trying to check if a specific row exists in a table that includes two given parameters: record_id and modifiedDate. So far my code does not work.

public void doSomething(int RECORD_ID) {
    DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
    Date date = new Date();
    String modifiedDate = dateFormat.format(date);

    Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db", "user", "pass");
    Statement stmt = connection.createStatement();
    String checkIfInDB = "select exists(select * from table where reference = ${RECORD_ID} and creation_date = '${modifiedDate}');"
    ResultSet rs = stmt.executeQuery(checkIfInDB);

    if(rs.next()) {
        println "Query in db"
        stmt.close();
        connection.close();
        return;
    }

    else {
        String command = "INSERT INTO table(reference, creation_date) VALUES (${RECORD_ID}, '${modifiedDate}');"
        stmt.executeUpdate(command)
        println "Success"
        stmt.close();
        connection.close();
        return;
    }
}

If the user inserts a RECORD_ID and date that already exists, the program adds it to the table anyway, when it should print 'Query in db'. I would appreciate any help to solve this issue.

@ScaryWombat Which sql are u referring to? the select.. and insert into..? Those work as normal — swing1234, Aug 07 '19 at 02:36
Sorry I was meaning the `select` - but anyway, I was meaning that `exists` is going to return a row (either true or false) in all cases. But there again shouldn't `if(!rs.next()) {` be `if(rs.next()) {` meaning that if there is a row. Sorry your code confuses me. — Scary Wombat, Aug 07 '19 at 02:42
`1.` change select statement to `select 1 from table where ...` without exists & subquery and change the `if(!rs.next())` to `if(rs.next())` `2.` in groovy your code could be minimum twice shorter. — daggett, Aug 07 '19 at 04:20
I think it can help you [Insert if not exists](https://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql). Also read and use [Try-with-resources](https://stackoverflow.com/questions/8066501/how-should-i-use-try-with-resources-with-jdbc) — SURU, Aug 07 '19 at 08:10
@ScaryWombat I tried that and the opposite happened -- When I insert a row that is not already in the db, it will go through the if statement. — swing1234, Aug 07 '19 at 17:20
That does not make sense. There is no `if` after the `insert` — Scary Wombat, Aug 08 '19 at 00:08

score 0 · Answer 1 · answered Aug 08 '19 at 01:46

Rather than listing what was wrong with the provided code I offer an example of a working example that could be used to help you along your journey...

public static void main(String[] args) {
    int recordId = 1;

    String jdbcSource = "jdbc:mysql://localhost:####/";
    String user = "****";
    String password = "****";

    String checkIfInDB = "select count(*) as cnt from example_schema.example_table where example_table.reference = ? and example_table.creation_date = ?";

    try (Connection connection = DriverManager.getConnection(jdbcSource, user, password)) {

        PreparedStatement stmt = connection.prepareStatement(checkIfInDB);
        stmt.setInt(1, recordId);
        stmt.setDate(2, java.sql.Date.valueOf(LocalDate.now()));

        ResultSet rs = stmt.executeQuery();

        if (rs.next()) {
            System.out.println("at least one row matched");
            return;
        } else {
            // to-do implement insert statement
            return;
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

When I insert a record_id that does not exist in the database, it prints out 'at least one row matched', when it is supposed to insert. — swing1234, Aug 08 '19 at 16:32
I'm sorry, but I don't understand your comment. When you say "I insert a record_id that does not exist" it means to me that you inserted a record by some other means than the sample code above. Also, I understand that a row with a matching record_id now exists. So, the next time it is queried then it will return a count of at least one. — Jeff Stewart, Aug 09 '19 at 01:43

Java/Groovy and MySQL: Checking if row exists in table

1 Answers1