← Back to team overview

maria-discuss team mailing list archive

Re: When using the getGeneratedKeys() method the keys are returned, but in which order?

 

Hi peter,

Basically, server will return different informations to driver. In those
informations, there is a "last insert id" that correspond to the first
created id of the query, and the number of affected rows.
So for example :

try (Statement stmt = sharedConnection.createStatement()) {
    stmt.executeUpdate("INSERT INTO demo (name) VALUES
('Jeff'),('Jones'),('Agnes'),('Wolfgang')",
Statement.RETURN_GENERATED_KEYS);
    try (ResultSet rs = stmt.getGeneratedKeys()) {
        while (rs.next()) {
            System.out.println(rs.getString(1));
        }
    }
}

server will return for example "last insert id" 1 and 4 new rows.

Driver will reconstruct the inserted Id according to
@@auto_increment_increment.
will return
1
2 (= 1 + @@auto_increment_increment)
3 (= 1 + 2 * @@auto_increment_increment)
4 (= 1 + 3 * @@auto_increment_increment)

In JDBC, standard way is to use PrepareStatement (and it's better to avoid
injection than created a String like the previous one) and batch methods.
Example :

String[] names = new String[] {"Jeff", "Jones", "Agnes", "Wolfgang"};
try (PreparedStatement pstmt =
sharedConnection.prepareStatement("INSERT INTO demo (name) VALUES
(?)", Statement.RETURN_GENERATED_KEYS)) {
    for (String name : names) {
        pstmt.setString(1, name);
        pstmt.addBatch();
    }
    pstmt.executeBatch();

    try (ResultSet rs = pstmt.getGeneratedKeys()) {
        while (rs.next()) {
            System.out.println(rs.getString(1));
        }
    }
}

Server will send to driver informations for each insert in the order
corresponding to the addBatch() methods.
There is no reconstruction this way.

I'll add this to documentation next week

diego



On Thu, Aug 25, 2016 at 4:38 PM, Péter Éberhardt <eberhardt.peter@xxxxxxxxx>
wrote:

> Hi,
>
> I want to insert multiple records into a table using one statement
> like this through jdbc:
>
>   INSERT INTO demo (name) VALUES ('Jeff'),('Jones'),('Agnes'),.
> ..,('Wolfgang');
>
> Then I want to get the generated ids with the getGeneratedKeys()
> method and match them to the inserted records but how can I be sure
> the method gives the ids in the same order as I listed the records in
> the statement? I can't find any related information in the
> documentation just the fact the method gives back those ids.
>
> DDL:
>
> CREATE TABLE demo (id bigint primary key auto_increment , name text);
>
> If this information would put into the document also then I can go
> with my implementation on this way.
>
> Thanks,
> Peter
>
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>

Follow ups

References