It further depends on auto increment lock mode. You can have a big gap between "buckets" of id values in the non-default mode, if the bulk insert is large and uses more than one "bucket" of values.
Sent from my iPhone > On Aug 26, 2016, at 12:46 PM, Justin Swanhart <[email protected]> wrote: > > 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 <[email protected]> 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 >>> <[email protected]> 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 : [email protected] >>> Unsubscribe : https://launchpad.net/~maria-discuss >>> More help : https://help.launchpad.net/ListHelp >> >> _______________________________________________ >> Mailing list: https://launchpad.net/~maria-discuss >> Post to : [email protected] >> Unsubscribe : https://launchpad.net/~maria-discuss >> More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
