maria-discuss team mailing list archive

[Justin Swanhart <greenlion@xxxxxxxxx>]

Generated id values are not guaranteed to be sequential.  The auto_increment lock is shared by transactions.  This code will not always work.

Sent from my iPhone

> On Aug 26, 2016, at 12:33 PM, Diego Dupin <diego.dupin@xxxxxxxxxxx> wrote:
> 
> 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
> 
> _______________________________________________
> 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