Retrieving the Insert ID

In Chapter 6, "MySQL Functions," I cover the LAST_INSERT_ID() function, which is a MySQL-specific tool for retrieving the value inserted into an auto-incremented field. You can do this with Java, too; it's just not as simple. I'll show the syntax and then explain it.

stmt.executeUpdate("INSERT INTO tablename (table_id, column) VALUES (NULL, 'value') ");
ResultSet rs = stmt.getGeneratedKeys();
rs.next();
int id = rs.getInt(1);

First some sort of INSERT query is run that invokes MySQL's AUTO_INCREMENT feature. Then a result set is assigned a value by calling the getGeneratedKeys() method. To fetch the first "row" in the result set, next() must be called. From that point, the actual value can be assigned to an integer by calling the getInt() function.

This technique will work as of Java 1.4 and is the preferred method for retrieving the insert ID. With this in mind, I'll create a new script, similar to Insert.java, that lets a user create an invoice and reports the new invoice number back to them.

To retrieve the insert ID:

Create a new Java application in your text editor or Java development tool (Script 9.4).

import java.sql.*;
public class AddInvoice {
   public static void main(String args[]) throws Exception {

This class will be called AddInvoice.

Script 9.4. This program works like `Insert` but reports back the generated invoice ID.

[View full width]
1 import java.sql.*; 2 3 // Script 9.4 'AddInvoice.java' 4 5 public class AddInvoice { 6 7 public static void main(String args[]) throws Exception { 8 9 // Initialize variables. 10 Connection con = null; 11 Statement stmt = null; 12 ResultSet rs = null; 13 int affected = 0; 14 int id = 0; 15 16 try { 17 18 // Connect to MySQL. 19 String url = "jdbc:mysql:///accounting"; 20 Class.forName("com.mysql.jdbc.Driver").newInstance(); 21 con = DriverManager.getConnection(url, "username", "password"); 22 23 // Run the query. 24 stmt = con.createStatement(); 25 affected = stmt.executeUpdate("INSERT INTO invoices (client_id, invoice_amount, invoice_description) VALUES ('" + args[0] + "', '" + args[1] + "', '" + args[2] + "')"); 26 27 // Confirm that the insert worked. 28 if (affected == 1) { 29 rs = stmt.getGeneratedKeys(); 30 rs.next(); 31 id = rs.getInt(1); 32 System.out.println("Invoice number " + id + " has been created!"); 33 } else { 34 System.out.println("The invoice could not be added to the database!"); 35 } 36 } 37 38 // Catch exceptions. 39 catch (SQLException e) { 40 System.out.println("Problem: " + e.toString()); 41 } 42 43 // Clean up. 44 finally { 45 if (rs != null) { 46 try { 47 rs.close(); 48 } catch (SQLException e) { 49 // Do nothing with exception. 50 } 51 rs = null; 52 } 53 if (stmt != null) { 54 try { 55 stmt.close(); 56 } catch (SQLException e) { 57 // Do nothing with exception. 58 } 59 stmt = null; 60 } 61 if (con != null) { 62 try { 63 con.close(); 64 } catch (SQLException e) { 65 // Do nothing with exception. 66 } 67 } 68 } 69 70 } // End of main(). 71 72 } // End of class AddInvoice.

Initialize the variables.

Connection con = null;
Statement stmt = null;
ResultSet rs = null;
int affected = 0;
int id = 0;

Along with the three standard JDBC variables of type Connection, Statement, and ResultSet, this program needs two integers.

Establish a connection to the accounting database.

try {
   String url = "jdbc:mysql:///accounting";
   Class.forName("com.mysql.jdbc.Driver").newInstance();
   con = DriverManager.getConnection(url, "username", "password");

Execute an INSERT query.

[View full width]stmt = con.createStatement();
affected = stmt.executeUpdate("INSERT INTO invoices (client_id, invoice_amount,
 invoice_description) VALUES ('" + args[0] + "', '" + args[1] + "', '" + args[2] + "')");

As with the Insert.java example, these values will be retrieved as command-line arguments typed when the application is run (Figure 9.10). To access their values, I refer to args[0] through args[2].

Figure 9.10. Once again the `INSERT` query will rely upon command-line arguments.

Report on the success of the INSERT.

if (affected == 1) {
   rs = stmt.getGeneratedKeys();
   rs.next();
   id = rs.getInt(1);
   System.out.println("Invoice number" + id + " has been created!");
} else {
   System.out.println("The invoice could not be added to the database!");
}

Again, most of this code is exactly like that in the other INSERT example. The technique outlined earlier for retrieving the insert ID is incorporated, and that value will be printed.

Complete the try clause and catch any errors that might have occurred.

}
catch
(SQLException e) {
   System.out.println("Problem: " + e.toString());
}

Wrap up the class.

 finally {
    if (rs != null) {
        try {
            rs.close();
        } catch (SQLException e) {
        }
        rs = null;
    }
    if (stmt != null) {
        try {
            stmt.close();
        } catch (SQLException e) {
        }
        stmt = null;
    }
    if (con != null) {
        try {
           con.close();
        } catch (SQLException e) {
        }
    }
}
} // End of main().
} // End of class Insert.

Save the file as AddInvoice.java and compile it.

Run AddInvoice (Figure 9.11).

Figure 9.11. The invoice ID will be displayed for the user if it was successfully added to the database.

When running the program, add the three requisite arguments (for the client's ID, invoice amount, and invoice description):

java AddInvoice 2 4599.26 'Invoice Description'

Tips

You can also retrieve the incremented ID by running a SELECT LAST_INSERT_ID() query and fetching that result.