commons-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mikhail Krivoshein <i...@mikkri.com>
Subject Re: [DbUtils] Retrieving Auto Generated Keys
Date Mon, 31 May 2004 15:45:46 GMT
Hello all,

I'd like to present my version of solution for this problem.
=============================================
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

/**
 * Custom query runner. It supports generated keys retrieval
 * for INSERT queries.
 * @author Mikhail Krivoshein <mikhail@mikkri.com>
 * @since 0.1
 */
public class MyQueryRunner extends QueryRunner {
   
   
   
    /**
     * Execute an SQL INSERT, UPDATE, or DELETE query without replacement
     * parameters.
     *
     * @param conn The connection to use to run the query.
     * @param sql The SQL to execute.
     * @return The number of rows updated.
     * @throws SQLException
     */
    public int update(Connection conn, String sql) throws SQLException {
            return this.update(conn, sql, (Object[]) null, null);
    }
   
    /**
     * Execute an SQL INSERT, UPDATE, or DELETE query without replacement
     * parameters.
     *
     * @param conn The connection to use to run the query.
     * @param sql The SQL to execute.
     * @param rsh <code>ResultSetHandler</code> used to process 
generated keys
     * @return The number of rows updated.
     * @throws SQLException
     */
    public int update(Connection conn, String sql, ResultSetHandler rsh) 
throws SQLException {
            return this.update(conn, sql, (Object[]) null, rsh);
    }   

    /**
     * Execute an SQL INSERT, UPDATE, or DELETE query with a single 
replacement
     * parameter.
     *
     * @param conn The connection to use to run the query.
     * @param sql The SQL to execute.
     * @param param The replacement parameter.
     * @return The number of rows updated.
     * @throws SQLException
     */
    public int update(Connection conn, String sql, Object param)
            throws SQLException {

            return this.update(conn, sql, new Object[] { param }, null);
    }
   
    /**
     * Execute an SQL INSERT, UPDATE, or DELETE query with a single 
replacement
     * parameter.
     *
     * @param conn The connection to use to run the query.
     * @param sql The SQL to execute.
     * @param param The replacement parameter.
     * @param rsh <code>ResultSetHandler</code> used to process 
generated keys
     * @return The number of rows updated.
     * @throws SQLException
     */
    public int update(Connection conn, String sql, Object param, 
ResultSetHandler rsh)
            throws SQLException {
            return this.update(conn, sql, new Object[] { param }, rsh);
    }   

    /**
     * Execute an SQL INSERT, UPDATE, or DELETE query.
     *
     * @param conn The connection to use to run the query.
     * @param sql The SQL to execute.
     * @param params The query replacement parameters.
     * @return The number of rows updated.
     * @throws SQLException
     */
    public int update(Connection conn, String sql, Object[] params) 
throws SQLException {
        return this.update(conn, sql, params, null);
    }
   

    /**
     * Execute an SQL INSERT, UPDATE, or DELETE query.
     *
     * @param conn The connection to use to run the query.
     * @param sql The SQL to execute.
     * @param params The query replacement parameters.
     * @param rsh <code>ResultSetHandler</code> used to process 
generated keys
     * @return The number of rows updated.
     * @throws SQLException
     */
    public int update(Connection conn, String sql, Object[] params, 
ResultSetHandler rsh)
            throws SQLException {

            PreparedStatement stmt = null;
            int rows = 0;

            try {
                    stmt = this.prepareStatement(conn, sql);
                    this.fillStatement(stmt, params);

                    rows = stmt.executeUpdate();
                   
                    if(rsh != null) {
                        ResultSet keys = stmt.getGeneratedKeys();
                        rsh.handle(keys);
                    }

            } catch (SQLException e) {
                    this.rethrow(e, sql, params);

            } finally {
                    DbUtils.close(stmt);
            }

            return rows;
    }

    /**
     * Executes the given INSERT, UPDATE, or DELETE SQL statement without
     * any replacement parameters. The <code>Connection</code> is retrieved
     * from the <code>DataSource</code> set in the constructor.  This
     * <code>Connection</code> must be in auto-commit mode or the update 
will
     * not be saved.
     *
     * @param sql The SQL statement to execute.
     * @throws SQLException
     * @return The number of rows updated.
     */
    public int update(String sql) throws SQLException {
            return this.update(sql, (Object[]) null, null);
    }
   
    /**
     * Executes the given INSERT, UPDATE, or DELETE SQL statement without
     * any replacement parameters. The <code>Connection</code> is retrieved
     * from the <code>DataSource</code> set in the constructor.  This
     * <code>Connection</code> must be in auto-commit mode or the update 
will
     * not be saved.
     *
     * @param sql The SQL statement to execute.
     * @param rs <code>ResultSetHandler</code> used to process generated 
keys
     * @throws SQLException
     * @return The number of rows updated.
     */
    public int update(String sql, ResultSetHandler rsh) throws 
SQLException {
            return this.update(sql, (Object[]) null, rsh);
    }   

    /**
     * Executes the given INSERT, UPDATE, or DELETE SQL statement with
     * a single replacement parameter.  The <code>Connection</code> is
     * retrieved from the <code>DataSource</code> set in the constructor.
     * This <code>Connection</code> must be in auto-commit mode or the
     * update will not be saved.
     *
     * @param sql The SQL statement to execute.
     * @param param The replacement parameter.
     * @throws SQLException
     * @return The number of rows updated.
     */
    public int update(String sql, Object param) throws SQLException {
            return this.update(sql, new Object[] { param });
    }
   
    /**
     * Executes the given INSERT, UPDATE, or DELETE SQL statement with
     * a single replacement parameter.  The <code>Connection</code> is
     * retrieved from the <code>DataSource</code> set in the constructor.
     * This <code>Connection</code> must be in auto-commit mode or the
     * update will not be saved.
     *
     * @param sql The SQL statement to execute.
     * @param param The replacement parameter.
     * @param rsh <code>ResultSetHandler</code> used to process 
generated keys
     * @throws SQLException
     * @return The number of rows updated.
     */
    public int update(String sql, Object param, ResultSetHandler rsh) 
throws SQLException {
            return this.update(sql, new Object[] { param }, rsh);
    }   

    /**
     * Executes the given INSERT, UPDATE, or DELETE SQL statement.  The
     * <code>Connection</code> is retrieved from the 
<code>DataSource</code>
     * set in the constructor.  This <code>Connection</code> must be in
     * auto-commit mode or the update will not be saved.
     *
     * @param sql The SQL statement to execute.
     * @param params Initializes the PreparedStatement's IN (i.e. '?')
     * parameters.
     * @throws SQLException
     * @return The number of rows updated.
     */
    public int update(String sql, Object[] params) throws SQLException {
            Connection conn = this.ds.getConnection();

            try {
                    return this.update(conn, sql, params);
            } finally {
                    DbUtils.close(conn);
            }
    }
   
    /**
     * Executes the given INSERT, UPDATE, or DELETE SQL statement.  The
     * <code>Connection</code> is retrieved from the 
<code>DataSource</code>
     * set in the constructor.  This <code>Connection</code> must be in
     * auto-commit mode or the update will not be saved.
     *
     * @param sql The SQL statement to execute.
     * @param params Initializes the PreparedStatement's IN (i.e. '?')
     * parameters.
     * @param rsh <code>ResultSetHandler</code> used to process 
generated keys
     * @throws SQLException
     * @return The number of rows updated.
     */
    public int update(String sql, Object[] params, ResultSetHandler rsh) 
throws SQLException {
            Connection conn = this.ds.getConnection();

            try {
                    return this.update(conn, sql, params, rsh);
            } finally {
                    DbUtils.close(conn);
            }
    }    

    /**
     * Default class constructor.
     */
    public MyQueryRunner() {
        super();
    }

    /**
     * Class constructor.  Methods that do not take a
     * <code>Connection</code> parameter will retrieve connections from this
     * <code>DataSource</code>.
     *
     * @param ds The <code>DataSource</code> to retrieve connections from.
     */
    public MyQueryRunner(DataSource ds) {
        super(ds);
    }
}
=============================================

I dislike idea to implement independant set of query methods named 
insert because JDBC doesn't do that.
And unfornutatly there is no way to implement Object 
getGeneratedKeys(ResultSetHandler rsh) because
update closes statement object.

Looking forward for comments.
Also I'd like to ask David about JDBC 3.0 support. How do you plan to 
support JDBC 3.0 features in DbUtils?
What's about DbUtils Option Pack :-) that will include only them?

Best regards,
Mikhail Krivoshein



---------------------------------------------------------------------
To unsubscribe, e-mail: commons-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-dev-help@jakarta.apache.org


Mime
View raw message