How to get the primary key when inserting a record with a duplicate unique key

Question

I have a DAO method for inserting and updating email records into a table with a unique key for the email address. The method returns the id of the record, but I'm struggling to get the id of the original, existing record when a duplicate email is inserted. I consulted MYSQL: Getting existing primary key when inserting record with duplicate unique key?, but I'm using JdbcTemplate. I tried using 'ON DUPLICATE KEY UPDATE', but it keeps throwing a NullPointerException.

Here is the code:

    @Override
    public Integer insertOrUpdate(final Email e) {
        if (e == null) throw new IllegalArgumentException();

        if (e.getIdEmail() != null) {
            String sql = "UPDATE Email SET email=? WHERE idEmail=?";
            jdbcTemplate.update(sql, e.getEmail(), e.getIdEmail());
            return e.getIdEmail();
        }
        else {
            final String sql = "INSERT IGNORE INTO Email (email) VALUES (?) ON DUPLICATE KEY UPDATE email=email";
            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator() {
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, e.getEmail());
                    return ps;
                }
            }, keyHolder);
            return ((Long) keyHolder.getKey()).intValue();
        }
    }

Before I tried using 'ON DUPLICATE KEY UPDATE', it threw a MySQLIntegrityViolationException for the duplicate key, but now the keyHolder keeps throwing a NullPointerException.

Answer 1

I solved this by performing a separate query for the id if the keyHolder is null.

Here is the updated code:

    @Override
    public Integer insertOrUpdate(final Email e) {
        if (e == null) throw new IllegalArgumentException();

        if (e.getIdEmail() != null) {
            String sql = "UPDATE Email SET email=? WHERE idEmail=?";
            jdbcTemplate.update(sql, e.getEmail(), e.getIdEmail());
            return e.getIdEmail();
        }
        else {
            final String sql = "INSERT IGNORE INTO Email (email) VALUES (?)";
            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator() {
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, e.getEmail());
                    return ps;
                }
            }, keyHolder);
            if (keyHolder.getKey() != null)
                return ((Long) keyHolder.getKey()).intValue();
            else {
                String sql2 = "SELECT idEmail FROM Email WHERE email=?";
                return jdbcTemplate.queryForObject(sql2, new Object[] {e.getEmail()}, Integer.class);
            }
        }
    }