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
