JDBC Return generated key or existing key

Question

I have a table with unique index to eliminate duplicates (simplified example)

CREATE TABLE `domain` (
      `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
      `subdomain` VARCHAR(255) NOT NULL,
      `domain` VARCHAR(63) NOT NULL,
      `zone` VARCHAR(63) NOT NULL,
      PRIMARY KEY (`id`),
      UNIQUE INDEX `UNIQUE` (`subdomain` ASC, `domain` ASC, `zone` ASC),
    ENGINE = InnoDB;

I insert a lot of rows and i need to get primary keys returned (for other one-to-many inserts).

My problem is, that I insert a lot of duplicates and I need those keys returned too.

This is my solution which works, but isn't there more simple solution? With this I cannot use batch inserts and I want this to be most efficient.

PreparedStatement selectDomain = connection.prepareStatement("SELECT id FROM domain WHERE subdomain = ? AND domain = ? AND zone = ?");
PreparedStatement insertDomain = connection.prepareStatement("INSERT INTO domain(subdomain, domain, zone) VALUES (?,?,?)", Statement.RETURN_GENERATED_KEYS);

public int insertDomain(String subdomain, String domain, String zone) throws SQLException {
        int domainId = 0;
        selectDomain.setString(1, subdomain);
        selectDomain.setString(2, domain);
        selectDomain.setString(3, zone);
        ResultSet resultSet = selectDomain.executeQuery();
        if (resultSet.next()) {
            domainId = resultSet.getInt(1);
        } else {
            insertDomain.setString(1, subdomain);
            insertDomain.setString(2, subdomain);
            insertDomain.setString(3, subdomain);
            insertDomain.executeUpdate();
            resultSet = insertDomain.getGeneratedKeys();
            if (resultSet.next()) {
                domainId = resultSet.getInt(1);
            }
        }
        selectDomain.clearParameters();
        insertDomain.clearParameters();
}

score 0 · Accepted Answer · edited May 23 '17 at 11:44

As I understand its not so easy approach for using batch execution. Your apporach is the best way to get the auto generated keys. There are few limitations of JDBC driver and it varies version to version, where getGeneratedKeys() works for single entry.

Please look into below links, it may help you :-

How to get generated keys from JDBC batch insert in Oracle?

http://docs.oracle.com/database/121/JJDBC/jdbcvers.htm#JJDBC28099

score 0 · Answer 2 · answered Feb 10 '15 at 13:14

You could modify your INSERT to be something like this:

INSERT INTO domain (subdomain, domain, zone)
SELECT $subdomain, $domain, $zone
FROM domain 
WHERE NOT EXISTS(
    SELECT subdomain, domain, zone
    FROM domain d
    WHERE d.subdomain= $subdomain and d.domain=$domain and d.zone=$zone
)
LIMIT 1

Where $subdomain, $domain, $zone are the tag (properly quoted or as a placeholder of course) that you want to add if it isn't already there. This approach won't even trigger an INSERT (and the subsequent autoincrement wastage) if the tag is already there. You could probably come up with nicer SQL than that but the above should do the trick.

If your table is properly indexed then the extra SELECT for the existence check will be fast and the database is going to have to perform that check anyway.

Thanks for your answer, but i don't get it :-) It gives me "unsafe update warning" and it doesnt solve my problem - i need to get the id returned (whether the entry already exists or not). As I understend you query, it checks for duplicate and then inserts. I can do this safely using INSERT IGNORE because i use UNIQUE INDEX on (subdomain, domain, zone)...but the result is same - it doesn't return the key on duplicate. — GuirNab, Feb 10 '15 at 13:49

JDBC Return generated key or existing key

2 Answers2