Java tutorial
package org.apache.commons.dbutils; import java.sql.Connection; import java.sql.ParameterMetaData; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import javax.sql.DataSource; /** * Extends {@link QueryRunner} with support for autogenerated key retrieval. * <p> * Note that, unlike its super class {@link QueryRunner}, this class is * <strong>not</strong> thread-safe * <p> * An example code to use this: * * <pre> * GenKeyQueryRunner<Object> runner = new GenKeyQueryRunner(ds, new ScalarHandler(), "id"); * // The key column to use for auto-generated key retrieval will be the column named "id" * * int updates = runner.update("INSERT INTO person(name, height) VALUES (?, ?)", "John Doe", 1.80); * // This will insert a John Doe into the Person table, * // and the RDBMS will generate an auto-incremented id * * Long key = (Long) runner.getGeneratedKeys(); * // Use the key here * ... * </pre> * * @author eugen p. (eugen.plischke at gmx.net) * @author Julien Aym (julien.ayme at gmail.com) * @version $Id: $ * * created on 04.11.2009 */ public class GenKeyQueryRunner<T> extends QueryRunner { /** * The indexes of the key columns which are used to auto-generated key * retrieval. May be empty if JDBC should decide on its own which columns * suit best. * <p> * If this field is not null, then keyColsByName must be null */ private final int[] keyColsByIndex; /** * The names of the key columns which are used to auto-generated key * retrieval. May be empty if JDBC should decide on its own which columns * suit best. * <p> * If this field is not null, then keyColsByIndex must be null */ private final String[] keyColsByName; /** * The ResultSetHandler used to transform the generated keys into a Java * Object */ private final ResultSetHandler<T> keyHandler; /** * The generated keys (available only after a sucessfull call to update()) */ private T generatedKeys; /** * Private constructor called to set fields appropriatly * * @param ds * The <code>DataSource</code> to retrieve connections from. * @param pmdKnownBroken * Oracle drivers don't support * {@link ParameterMetaData#getParameterType(int) }; if * <code>pmdKnownBroken</code> is set to true, we won't even try * it; if false, we'll try it, and if it breaks, we'll remember * not to use it again. See * {@link QueryRunner#QueryRunner(boolean)} * @param keyColsByIndex * The indexes of the key columns which are used to * auto-generated key retrieval. May be empty if JDBC should * decide on its own which columns suit best. * @param keyColsByName * The names of the key columns which are used to auto-generated * key retrieval. May be empty if JDBC should decide on its own * which columns suit best. * @param keyHandler * The ResultSetHandler used to transform the generated keys into * a Java Object */ private GenKeyQueryRunner(DataSource ds, boolean pmdKnowBroken, int[] keyColsByIndex, String[] keyColsByName, ResultSetHandler<T> keyHandler) { super(ds, pmdKnowBroken); this.keyColsByIndex = keyColsByIndex; this.keyColsByName = keyColsByName; this.keyHandler = keyHandler; } /** * Construct a new GenKeyQueryRunner using the given handler, which will let * JDBC decide on its own the key columns to use for auto-generated keys * retrieval * * @param keyHandler * The ResultSetHandler used to transform the generated keys into * a Java Object */ public GenKeyQueryRunner(ResultSetHandler<T> keyHandler) { this(null, false, null, null, keyHandler); } /** * Construct a new GenKeyQueryRunner using the given handler, which will use * the given column indexes for auto-generated keys retrieval * * @param keyHandler * The ResultSetHandler used to transform the generated keys into * a Java Object * @param keyColsByIndex * The indexes of the key columns which are used to * auto-generated key retrieval. May be empty if JDBC should * decide on its own which columns suit best. */ public GenKeyQueryRunner(ResultSetHandler<T> keyHandler, int... keyColsByIndex) { this(null, false, keyColsByIndex, null, keyHandler); } /** * Construct a new GenKeyQueryRunner using the given handler, which will use * the given column indexes for auto-generated keys retrieval * * @param keyHandler * The ResultSetHandler used to transform the generated keys into * a Java Object * @param keyColsByName * The names of the key columns which are used to auto-generated * key retrieval. May be empty if JDBC should decide on its own * which columns suit best. */ public GenKeyQueryRunner(ResultSetHandler<T> keyHandler, String... keyColsByName) { this(null, false, null, keyColsByName, keyHandler); } /** * Construct a new GenKeyQueryRunner using the given handler, which will let * JDBC decide on its own the key columns to use for auto-generated keys * retrieval * * @param ds * The <code>DataSource</code> to retrieve connections from. * @param keyHandler * The ResultSetHandler used to transform the generated keys into * a Java Object */ public GenKeyQueryRunner(DataSource ds, ResultSetHandler<T> keyHandler) { this(ds, false, null, null, keyHandler); } /** * Construct a new GenKeyQueryRunner using the given handler, which will use * the given column indexes for auto-generated keys retrieval * * @param ds * The <code>DataSource</code> to retrieve connections from. * @param keyHandler * The ResultSetHandler used to transform the generated keys into * a Java Object * @param keyColsByIndex * The indexes of the key columns which are used to * auto-generated key retrieval. May be empty if JDBC should * decide on its own which columns suit best. */ public GenKeyQueryRunner(DataSource ds, ResultSetHandler<T> keyHandler, int... keyColsByIndex) { this(ds, false, keyColsByIndex, null, keyHandler); } /** * Construct a new GenKeyQueryRunner using the given handler, which will use * the given column indexes for auto-generated keys retrieval * * @param ds * The <code>DataSource</code> to retrieve connections from. * @param keyHandler * The ResultSetHandler used to transform the generated keys into * a Java Object * @param keyColsByName * The names of the key columns which are used to auto-generated * key retrieval. May be empty if JDBC should decide on its own * which columns suit best. */ public GenKeyQueryRunner(DataSource ds, ResultSetHandler<T> keyHandler, String... keyColsByName) { this(ds, false, null, keyColsByName, keyHandler); } /** * Construct a new GenKeyQueryRunner using the given handler, which will let * JDBC decide on its own the key columns to use for auto-generated keys * retrieval * * @param pmdKnownBroken * see {@link QueryRunner#QueryRunner(boolean)} * @param keyHandler * The ResultSetHandler used to transform the generated keys into * a Java Object */ public GenKeyQueryRunner(boolean pmdKnownBroken, ResultSetHandler<T> keyHandler) { this(null, pmdKnownBroken, null, null, keyHandler); } /** * Construct a new GenKeyQueryRunner using the given handler, which will use * the given column indexes for auto-generated keys retrieval * * @param pmdKnownBroken * see {@link QueryRunner#QueryRunner(boolean)} * @param keyHandler * The ResultSetHandler used to transform the generated keys into * a Java Object * @param keyColsByIndex * The indexes of the key columns which are used to * auto-generated key retrieval. May be empty if JDBC should * decide on its own which columns suit best. */ public GenKeyQueryRunner(boolean pmdKnownBroken, ResultSetHandler<T> keyHandler, int... keyColsByIndex) { this(null, pmdKnownBroken, keyColsByIndex, null, keyHandler); } /** * Construct a new GenKeyQueryRunner using the given handler, which will use * the given column indexes for auto-generated keys retrieval * * @param pmdKnownBroken * see {@link QueryRunner#QueryRunner(boolean)} * @param keyHandler * The ResultSetHandler used to transform the generated keys into * a Java Object * @param keyColsByName * The names of the key columns which are used to auto-generated * key retrieval. May be empty if JDBC should decide on its own * which columns suit best. */ public GenKeyQueryRunner(boolean pmdKnownBroken, ResultSetHandler<T> keyHandler, String... keyColsByName) { this(null, pmdKnownBroken, null, keyColsByName, keyHandler); } /** * Construct a new GenKeyQueryRunner using the given handler, which will let * JDBC decide on its own the key columns to use for auto-generated keys * retrieval * * @param ds * The <code>DataSource</code> to retrieve connections from. * @param pmdKnownBroken * see {@link QueryRunner#QueryRunner(boolean)} * @param keyHandler * The ResultSetHandler used to transform the generated keys into * a Java Object */ public GenKeyQueryRunner(DataSource ds, boolean pmdKnownBroken, ResultSetHandler<T> keyHandler) { this(ds, pmdKnownBroken, null, null, keyHandler); } /** * Construct a new GenKeyQueryRunner using the given handler, which will use * the given column indexes for auto-generated keys retrieval * * @param ds * The <code>DataSource</code> to retrieve connections from. * @param pmdKnownBroken * see {@link QueryRunner#QueryRunner(boolean)} * @param keyHandler * The ResultSetHandler used to transform the generated keys into * a Java Object * @param keyColsByIndex * The indexes of the key columns which are used to * auto-generated key retrieval. May be empty if JDBC should * decide on its own which columns suit best. */ public GenKeyQueryRunner(DataSource ds, boolean pmdKnownBroken, ResultSetHandler<T> keyHandler, int... keyColsByIndex) { this(ds, pmdKnownBroken, keyColsByIndex, null, keyHandler); } /** * Construct a new GenKeyQueryRunner using the given handler, which will use * the given column indexes for auto-generated keys retrieval * * @param ds * The <code>DataSource</code> to retrieve connections from. * @param pmdKnownBroken * see {@link QueryRunner#QueryRunner(boolean)} * @param keyHandler * The ResultSetHandler used to transform the generated keys into * a Java Object * @param keyColsByName * The names of the key columns which are used to auto-generated * key retrieval. May be empty if JDBC should decide on its own * which columns suit best. */ public GenKeyQueryRunner(DataSource ds, boolean pmdKnownBroken, ResultSetHandler<T> keyHandler, String... keyColsByName) { this(ds, pmdKnownBroken, null, keyColsByName, keyHandler); } /** * Factory method that creates and initializes a * <code>PreparedStatement</code> object for the given SQL. * <code>QueryRunner</code> methods always call this method to prepare * statements for them. Subclasses can override this method to provide * special PreparedStatement configuration if needed. This implementation * calls the appropriate <code>conn.prepareStatement(sql, ...)</code> method * according to this GenKeyQueryRunner config. * * @param conn * The <code>Connection</code> used to create the * <code>PreparedStatement</code> * @param sql * The SQL statement to prepare. * @return An initialized <code>PreparedStatement</code>. * @throws SQLException * if a database access error occurs */ @Override protected PreparedStatement prepareStatement(Connection conn, String sql) throws SQLException { PreparedStatement ps; if (keyColsByIndex != null && keyColsByIndex.length > 0) { ps = conn.prepareStatement(sql, keyColsByIndex); } else if (keyColsByName != null && keyColsByName.length > 0) { ps = conn.prepareStatement(sql, keyColsByName); } else { ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); } return ps; } @Override public int update(Connection conn, String sql, Object... params) throws SQLException { PreparedStatement stmt = null; int rows = 0; ResultSet autoKeyRs = null; // Clear generatedKeys first, in case an exception is thrown this.generatedKeys = null; try { stmt = this.prepareStatement(conn, sql); this.fillStatement(stmt, params); rows = stmt.executeUpdate(); autoKeyRs = stmt.getGeneratedKeys(); this.generatedKeys = keyHandler.handle(autoKeyRs); // Store the generated keys here, they will be available for // retrieval via the getGeneratedKeys method } catch (SQLException e) { this.rethrow(e, sql, params); } finally { try { close(autoKeyRs); } finally { close(stmt); } } return rows; } /** * Returns the generated keys, generated within the last update call * * @return the generated keys, may be <code>null</code> if no key was * generated or if the update method was not invoked before. */ public T getGeneratedKeys() { return generatedKeys; } }