[Maria-discuss] When using the getGeneratedKeys() method the keys are returned, but in which order?
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
Hi, You can not get all of the generated keys from a bulk insert, only the last one, because the id value is made available through the last_insert_id() function, and the client API value to return the last insert id works the same way. You will either have to do single value (not bulk) inserts, or you will have to lock the table, select the maximum id, insert the values, select the new maximum value, and then unlock the table. This will give you the complete set of values for the block of id values assigned to the bulk insert. --Justin On Thu, Aug 25, 2016 at 10:38 AM, Péter Éberhardt <eberhardt.peter@gmail.com
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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
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@gmail.com> 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
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@mariadb.com> 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@gmail.com> 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
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 <greenlion@gmail.com> 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 <diego.dupin@mariadb.com> 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@gmail.com> 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
participants (3)
-
Diego Dupin
-
Justin Swanhart
-
Péter Éberhardt