You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@commons.apache.org by Mikhail Krivoshein <in...@mikkri.com> on 2004/05/29 21:01:34 UTC
[DbUtils] Retrieving Auto Generated Keys
Hello all,
Looks like there is no way to retrieve auto generated keys when using
DbUtils QueryRunner.
I guess it is good idea to extend QueryRunner in way that it will
support something like
Object getGeneratedKeys(ResultSetHandler rsh)
method to convet generated keys data into Object.
Really I need this code for my project and ready to implement this by
myself.
Looking forward to hear comments.
Best regards,
Mikhail Krivoshein
P.s. I see one problem. getGeneratedKeys() method is part of JDBC 3.0.
Perhaps, it may be issue.
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-dev-help@jakarta.apache.org
Re: [DbUtils] Retrieving Auto Generated Keys
Posted by David Graham <gr...@yahoo.com>.
--- Mikhail Krivoshein <in...@mikkri.com> wrote:
> >
> >
> >The best thing to do is open a bugzilla enhancement ticket with a cvs
> diff
> >-u formatted patch of the changes plus test cases. Before adding this
> we
> >need to be sure it will still run on Java 1.3.
> >
> >David
> >
> >
> OK. I will prepare this later on this week.
> David, is it enough to test code with JRE 1.3 on my PC?
Yes, it would be good to test out the theory that you could compile the
code on 1.4 using javac's -source and/or -target parameters and run it
successfully on 1.3. Of course, when you call
Statement.getGeneratedKeys() on 1.3 it should throw an exception.
David
>
> Best regards,
> Mikhail Krivoshein
>
__________________________________
Do you Yahoo!?
Friends. Fun. Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-dev-help@jakarta.apache.org
Re: [DbUtils] Retrieving Auto Generated Keys
Posted by Mikhail Krivoshein <in...@mikkri.com>.
>
>
>The best thing to do is open a bugzilla enhancement ticket with a cvs diff
>-u formatted patch of the changes plus test cases. Before adding this we
>need to be sure it will still run on Java 1.3.
>
>David
>
>
OK. I will prepare this later on this week.
David, is it enough to test code with JRE 1.3 on my PC?
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
Re: [DbUtils] Retrieving Auto Generated Keys
Posted by David Graham <gr...@yahoo.com>.
--- Mikhail Krivoshein <in...@mikkri.com> wrote:
>
>
> >>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.
> >>
> >>
> >
> >Passing a ResultSetHandler into the update() methods to deal with
> primary
> >keys seems reasonable.
> >
> So what id your decision? Do you plan to update code with my changes?
The best thing to do is open a bugzilla enhancement ticket with a cvs diff
-u formatted patch of the changes plus test cases. Before adding this we
need to be sure it will still run on Java 1.3.
David
>
> >>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 features specifically do you need support for?
> >
> I just want to know you opinion about how to deal with them. At this
> moment I need getGeneratedKeys() only.
> But who knows that happens later?
>
> >I want to keep the releases and builds as simple as possible. That's
> why
> >we use Proxies for the JDBC interfaces (which are incompatible between
> >versions) instead of concrete implementations:
>
>http://jakarta.apache.org/commons/dbutils/xref/org/apache/commons/dbutils/ProxyFactory.html
> >
> Looks like I misunderstand you here.
>
> 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
>
__________________________________
Do you Yahoo!?
Friends. Fun. Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-dev-help@jakarta.apache.org
Re: [DbUtils] Retrieving Auto Generated Keys
Posted by Mikhail Krivoshein <in...@mikkri.com>.
>>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.
>>
>>
>
>Passing a ResultSetHandler into the update() methods to deal with primary
>keys seems reasonable.
>
So what id your decision? Do you plan to update code with my changes?
>>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 features specifically do you need support for?
>
I just want to know you opinion about how to deal with them. At this
moment I need getGeneratedKeys() only.
But who knows that happens later?
>I want to keep the releases and builds as simple as possible. That's why
>we use Proxies for the JDBC interfaces (which are incompatible between
>versions) instead of concrete implementations:
>http://jakarta.apache.org/commons/dbutils/xref/org/apache/commons/dbutils/ProxyFactory.html
>
Looks like I misunderstand you here.
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
Re: [DbUtils] Retrieving Auto Generated Keys
Posted by David Graham <gr...@yahoo.com>.
--- Mikhail Krivoshein <in...@mikkri.com> wrote:
<snip/>
>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.
Passing a ResultSetHandler into the update() methods to deal with primary
keys seems reasonable.
>
>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 features specifically do you need support for?
I haven't confirmed this but I believe we can include a call to
Statement.getGeneratedKeys() and still have DbUtils run on 1.3 JVMs. You
would have to compile the code on 1.4 but I think as long as you don't try
to invoke getGeneratedKeys() on a 1.3 JVM you'll be ok.
Note that the protected QueryRunner.prepareStatement() method is already
the hook for creating PreparedStatements that will return generated keys
(by passing Statement.RETURN_GENERATED_KEYS into
Connection.prepareStatement()).
>What's about DbUtils Option Pack :-) that will include only them?
I want to keep the releases and builds as simple as possible. That's why
we use Proxies for the JDBC interfaces (which are incompatible between
versions) instead of concrete implementations:
http://jakarta.apache.org/commons/dbutils/xref/org/apache/commons/dbutils/ProxyFactory.html
David
>Best regards,
>Mikhail Krivoshein
__________________________________
Do you Yahoo!?
Friends. Fun. Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-dev-help@jakarta.apache.org
Re: [DbUtils] Retrieving Auto Generated Keys
Posted by Mikhail Krivoshein <in...@mikkri.com>.
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 <mi...@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
Re: [DbUtils] Retrieving Auto Generated Keys
Posted by David Graham <gr...@yahoo.com>.
--- Mikhail Krivoshein <in...@mikkri.com> wrote:
> Hello all,
>
> Looks like there is no way to retrieve auto generated keys when using
> DbUtils QueryRunner.
> I guess it is good idea to extend QueryRunner in way that it will
> support something like
>
> Object getGeneratedKeys(ResultSetHandler rsh)
>
> method to convet generated keys data into Object.
>
> Really I need this code for my project and ready to implement this by
> myself.
> Looking forward to hear comments.
>
> Best regards,
> Mikhail Krivoshein
>
> P.s. I see one problem. getGeneratedKeys() method is part of JDBC 3.0.
> Perhaps, it may be issue.
That's exactly why DbUtils doesn't support it yet. I'd be interested in
any solutions you come up with.
David
__________________________________
Do you Yahoo!?
Friends. Fun. Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-dev-help@jakarta.apache.org