Re: Getting a Mysql auto increment value back into my Java client

BoBi wrote:
> Hello,
> 
> I'm busy developing a Java GUI client/server database application with
> Netbeans IDE 6.0 and
> Mysql to automate the data management and other tasks for my Dongo
> website
> (see http://www.dongo.org
to get an idea what is the targeted result
> of all
> my programming). I have a question as described below.
> 
> To insert a country in my mysql country table I use the code below
> which is working fine:
> 
>   private static void countryInsert(Country country) {
>     if (country.isValid())
>     try {
>       String sqlString = "INSERT INTO country VALUES(0, '" +
> country.getCode();
>       sqlString += "', '" + country.getName() + "')";
>       sqlStatement.executeUpdate(sqlString);
>     } catch (SQLException ex) {
>       Logger.getLogger(Objectbase.class.getName()).log(Level.SEVERE,
> null, ex);
>     }
>   }
> 
> The first column sid of the country table (as for all my tables) is
> auto increment:
> 
> | sid   | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment
> |
> 
> I would like to get the value assigned to sid by Mysql back into my
> client Java application
> preferably with the one sql statement I'm already using to insert.
> Only if really necessary
> with a second sql statement. What is the best way to programm this?

The best solution is to use Connection.prepareStatement(String sql, int 
autoGeneratedKeys) to create a prepared statement:

   String sqlString = "INSERT INTO country(countryCode,countryName)
    VALUES(?,?)";

   PreparedStatement pstmt = conn.prepareStatement(sqlString,
       Statement.RETURN_GENERATED_KEYS);

assuming that your table has columns named countryCode and countryName 
to store the country code and name respectively.

Then insert a row using code such as

   pstmt.setString(1, country.getCode());
   pstmt.setString(2, country.getName());

   int rc = pstmt.executeUpdate();

   // rc is the number of rows actually inserted

and retrieve the auto_increment value generated by the server like this:

   ResultSet rs = pstmt.getGeneratedKeys();

   int sid = rs.next() ? rs.getInt(1) : -1;

   rs.close();

The use of a prepared statement is also preferable to hard-coding quotes 
into an SQL query string as you did in your code snippet.

You can also re-use the prepared statement to insert further rows, for 
as long as the parent connection remains open.  All in all, prepared 
statements are a more elegant, flexible and secure approach, as well as 
providing a convenient way to get auto_increment values.

David Harper
Cambridge, England