On Mar 19, 10:20 pm, David Harper <devn...@[EMAIL PROTECTED]
> wrote:
> 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
> > (seehttp://www.dongo.orgto
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
Thanks alot for the solution and advice. :c), BoBi
|
