1

its duplicate question with Get last inserted auto increment id in mysql

I'm creating a group it insert on M_GROUPS.

M_GROUPS table: 

GROUP_ID INT AUTO_INCREMENT, GROUP_CREATOR_ID INT

which from session.

I need to take GROUP_ID and GROUP_CREATOR_ID and insert it on
M_GROUP_MEMBERS table as 

GROUP_ID INT, MEMBER_ID INT.

My problem is I can't take auto increment value GROUP_ID from M_GROUPS 

public void groupCreation(String groupname, int grouptype, int creator_id) { DatabaseService oDatabaseService = new DatabaseService(); Connection connection = oDatabaseService.connect(); try { Statement stmt = null; stmt = connection.createStatement(); String sql; sql = "INSERT INTO M_GROUPS( GROUP_NAME, GROUP_CREATOR_ID, GROUP_TYPE, CREATION_TIME)" + " VALUES ('" + groupname + "','" + creator_id + "','" + grouptype + "',NOW())"; //stmt.executeUpdate(sql); stmt.executeUpdate(sql); } catch (SQLException se) { se.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } finally { try { if (connection != null) connection.close(); } catch (SQLException se) { se.printStackTrace(); } } }
Improve this question

1 Answer 1

Reset to default
4

Use getGeneratedKeys() method from your Statement object to identify the new auto generated values. Iterate the returned ResultSet object to get the newly generated key values in the order of batch statements.

Change: 

stmt.executeUpdate(sql);

To: 

int rowsAffected = stmt.executeUpdate( sql, Statement.RETURN_GENERATED_KEYS ); ResultSet rs = stmt.getGeneratedKeys(); //****************************************************** // in case of batch insert, you can check how many are inserted rs.last(); int rows = rs.getRow(); System.out.println( "Generated keys count: " + rows ); //******************************************************/ int currentRow = 1; rs.beforeFirst(); while( rs.next() ) { System.out.println( /**/( currentRow++ ) + " = " + /**/rs.getInt( 1 ) ); } // while rs

Once you have this auto generated key value in a variable, you can use it with other SQL statements, to store in other tables.

Note: Call to getGeneratedKeys() may throw java.sql.SQLFeatureNotSupportedException, if the JDBC driver, that you are using, does not support this method.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Not working means what? rs.getInt( 1 ) gives you the value of just generated auto incremented primary key value from your insert statement.
it give java.sql.SQLException: Column count doesn't match value count at row 1 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4190) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4122)
It must be on your any other statement, like insert. Check if you are passing same number of values for columns you want to insert into. And show me the statement too.
sir @Ravinder, i do this could u please check the below link stackoverflow.com/questions/22423194/…

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.