java - Retrieve auto_increment value of multiple rows from MySQL in batch mode -


there mysql table primary key id int auto_increment, need insert multiple rows in batch multiple insert statement, autocommit disabled, following:

set autocommit=0; insert dummy(name, `size`, create_date) values('test', 1, now()); insert dummy(name, `size`, create_date) values('test', 2, now()); commit; 

is possible each generated id, instead of last id.

if yes, when each id generated, , how ids via jdbc?

thx.

if want retrieve auto_increment keys via jdbc need use jdbc features doing (return_generated_keys , .getgeneratedkeys()), this:

try (connection conn = drivermanager.getconnection(myconnectionstring, "root", "beer")) {     try (statement st = conn.createstatement()) {         st.execute(                 "create temporary table dummy (" +                     "`id` int auto_increment primary key, " +                     "`name` varchar(50), " +                     "`size` int, " +                     "`create_date` datetime " +                 ")");     }     conn.setautocommit(false);     system.out.println("autocommit off.");     string sql = "insert dummy(name, `size`, create_date) values('test', ?, now())";     try (preparedstatement ps = conn.preparestatement(             sql,              preparedstatement.return_generated_keys)) {         // first batch         ps.setint(1, 1);  // `size` = 1         ps.addbatch();         ps.setint(1, 2);  // `size` = 2         ps.addbatch();         ps.executebatch();         system.out.println("first batch executed. following auto_increment values created:");         try (resultset rs = ps.getgeneratedkeys()) {             while (rs.next()) {                 system.out.println(rs.getint(1));             }         }         try (statement st = conn.createstatement()) {             sql = "select count(*) n dummy";             try (resultset rs = st.executequery(sql)) {                 rs.next();                 system.out.println(string.format("the table contains %d row(s).", rs.getint(1)));             }         }         conn.rollback();         system.out.print("transaction rolled back. ");         try (statement st = conn.createstatement()) {             sql = "select count(*) n dummy";             try (resultset rs = st.executequery(sql)) {                 rs.next();                 system.out.println(string.format("the table contains %d row(s).", rs.getint(1)));             }         }         // second batch         ps.setint(1, 97);  // `size` = 97         ps.addbatch();         ps.setint(1, 98);  // `size` = 98         ps.addbatch();         ps.setint(1, 99);  // `size` = 99         ps.addbatch();         ps.executebatch();         system.out.println("second batch executed. following auto_increment values created:");         try (resultset rs = ps.getgeneratedkeys()) {             while (rs.next()) {                 system.out.println(rs.getint(1));             }         }     }     try (statement st = conn.createstatement()) {         sql = "select count(*) n dummy";         try (resultset rs = st.executequery(sql)) {             rs.next();             system.out.println(string.format("the table contains %d row(s).", rs.getint(1)));         }     } } 

... produces following console output:

autocommit off. first batch executed. following auto_increment values created: 1 2 table contains 2 row(s). transaction rolled back. table contains 0 row(s). second batch executed. following auto_increment values created: 3 4 5 table contains 3 row(s). 

Comments

Popular posts from this blog

java - UnknownEntityTypeException: Unable to locate persister (Hibernate 5.0) -

python - ValueError: empty vocabulary; perhaps the documents only contain stop words -

ubuntu - collect2: fatal error: ld terminated with signal 9 [Killed] -