I need some simple utils for JDBC (o/r mappings do not solve all of my
problems).
I use callbacks at this time, it is good for performance and no problems
with "cose":
//connection is "ThreadLocal"
db.executeQuery(
"SELECT * FROM Boing b WHERE b.name=?",
new Object[] { name },
new Handler(){
public void handle(ResutSet rs )throws SQLException{
//my
code........................................................................
....................
}
}
);
It is possible to have predefined handlers to convert result sets to data
structure like List, if max resultset size is known.
It can be used to implement something like this :
int count = db.executeIntQuery( "SELECT count(*) FROM Boing b WHERE
b.name=?", new Object[] { name });
All open/close, try/finally, setObject/setNull stuff in single
executeQuery(String sql,Object params[], Callback c);
User can define "adapter" like this himself :
abstract class MyHandler inplements Handler{
final public void handle(ResutSet rs )throws SQLException{
ResultSetMetadata metadata = rs.getMetaData();
Object[] row = new Object[ metadata.getColumnCount() ];
while(rs.next()){
rowToArray(rs, row );
handleNext(row);
}
}
abstract void handleNext( Object[] row )throws SQLException;
}
I think this stuff can be usefull for JDBC developers.
----- Original Message -----
From: "Henri Yandell" <[EMAIL PROTECTED]>
To: "Jakarta Commons Developers List" <[EMAIL PROTECTED]>
Sent: Sunday, November 24, 2002 11:11 AM
Subject: Re: Re: [DBUtils][patch] executeQuery method
>
> Yes. Plus the signature you provide as an example will not work due to
> closing issues.
>
> This highlights why an Object[] should be passed in, it can be a literal.
> So my code would look like:
>
> thing = DbUtils.executeQuery( conn,
> "SELECT * FROM Boing b WHERE b.name=?",
> new Object[] { name }
> );
>
> This removes a chunk of user code and replaces it with a simpler, yet
> equally meaningful piece of code. The problem however is that it does not
> scope beyond memory as 'thing' is some form of memory construct. Failure
> to scope may be bad, or the name may simply need changing to
> DbUtils.executeQueryIntoList(.... and a List returned with the more
> obvious semantics that a memory structure is in place than an Iterator.
>
> The other option is to pass in the PreparedStatement, in which case all
> that is hidden is the loop over the prepared statement, assuming that
> still remained.
>
> However, just providing that functionality and making the user's code
> clearer can still be pratical, and dependency on DbUtils is a one-time
> cost, so many other parts of the code could be using DbUtils.
>
> The biggest worry for me with DbUtils.executeQuery [over simple things
> like whether List or Iterator is returned] is whether a utility method
> must scope fully in all directions. Should they seek to never have
> caveats, should they simply make it obvious through the name or
> documentation. Shame we can't throw runtime warnings to some JVM handler
> without interrupting the flow of the thread.
>
> Hen
>
> On Sat, 23 Nov 2002, Juozas Baliuka wrote:
>
> >
> >
> > I do not think this can solve some problem:
> > "ResultSet executeQuery(Connnection c, String sql, List params);"
> > It just replaces "PreparetStatement.executeQuery" in users code, Is it
> > something practical in this replacement?.
> > It will be the same JDBC in users code, but with dependancy on DBUtils.
> >
> > ----- Original Message -----
> > From: "Henri Yandell" <[EMAIL PROTECTED]>
> > To: "Jakarta Commons Developers List" <[EMAIL PROTECTED]>
> > Sent: Saturday, November 23, 2002 9:28 PM
> > Subject: Re: Re: [DBUtils][patch] executeQuery method
> >
> >
> > >
> > > Which launches us into the beginnnigs of a religion/framework and away
> >
> > Callback is not religion/framework, it is way to eleminate "copy/paste"
and
> > "close" problems.
> >
> > > from simply JDBC, but not that much. It seems like a good idea
[Connection
> > > needs pulling out, no pool], and we could then have an MemoryIterator
> > > which pulls all the values into memory and other implementations.
> > >
> > > Hen
> > >
> > > On Sat, 23 Nov 2002, Juozas Baliuka wrote:
> > >
> > > >
> > > > It is very trival way to handle JDBC stuff:
> > > >
> > > > interface ResultSetHandler{
> > > >
> > > > void handle( java.sql.ResultSet rs );
> > > >
> > > > }
> > > > //executes single query
> > > > public void executeQuery( String sql, Object params[],
> > ResultSetHandler
> > > > handler )throws SQLException{
> > > > Connection connection = pool.getConnection();
> > > > Statement statement = null;
> > > > ResultSet rs = null;
> > > > try{
> > > >
> > > > statement = connection.prepareStatement(sql);
> > > >
> > > > for( int i =0; i< params.length; i++){
> > > > if(params[i] == null ){
> > > > statement.setNull( i + 1, Types.OTHER );
> > > > }else{
> > > > statement.setObject( i + 1, params[i], Types.OTHER );
> > > > }
> > > >
> > > > }
> > > > rs = statement.executeQuery();
> > > > handler.handle(rs);
> > > >
> > > > }finally{
> > > >
> > > > close(rs);
> > > > close(statement);
> > > > close(connection);
> > > >
> > > > }
> > > > }
> > > >
> > > >
> > > > This can be modified to support updates and transactions.
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > ----- Original Message -----
> > > > From: <[EMAIL PROTECTED]>
> > > > To: "Jakarta Commons Developers List"
<[EMAIL PROTECTED]>
> > > > Sent: Saturday, November 23, 2002 12:47 PM
> > > > Subject: RE: Re: [DBUtils][patch] executeQuery method
> > > >
> > > >
> > > > > I haven't been following this thread, but this is sounding very
> > crossdb
> > > > like?
> > > > >
> > > > > In any case, after reading this message, executeQuery should
> > definitely
> > > > return a ResultSet, not an iterator as Ken said. That is ridiculous
> > even
> > > > thinking to return an iterator and pulling the whole resultset into
> > memory.
> > > > Would be almost unusable in any large data situation.
> > > > >
> > > > > Travis
> > > > >
> > > > > ---- Original Message ----
> > > > > From: Henri Yandell <[EMAIL PROTECTED]>
> > > > > Sent: 2002-11-22
> > > > > To: Jakarta Commons Developers List
<[EMAIL PROTECTED]>
> > > > > Subject: Re: [DBUtils][patch] executeQuery method
> > > > >
> > > > >
> > > > >
> > > > > On Fri, 22 Nov 2002, Ken Horn wrote:
> > > > >
> > > > > >
> > > > > > Personally this executeQuery call is almost identical to ones I
> > write
> > > > > > everywhere I go... I don't think the executeQuery, should return
any
> > > > > > type of Iterator, but the ResultSet itself. This avoids the
whole
> > memory
> > > > > > issue for large result sets. If you're passing a Connection
you're
> > not
> > > > > > really hiding jdbc use anyway.
> > > > > >
> > > > > > I normally make the interface:
> > > > > > ResultSet executeQuery(Connnection c, String sql, List params);
> > > > > > ResultSet executeQuery(Connnection c, String sql, List params,
int[]
> > > > types);
> > > > >
> > > > > Does this work? Does JDBC say that you can close a Statement and
the
> > > > > ResultSet will not close and still be usable? That's the reason
for
> > not
> > > > > returning ResultSet. It seems there are two options, where
> > > > > resource-management is either fully hidden, or not hidden at all:
> > > > >
> > > > > 1) ResultSet executeQuery(Connection, PreparedStatement,
<params>)
> > > > > 2) <memory> executeQuery(Connection, String, <params>)
> > > > >
> > > > > Where <memory> is Iterator/List/Object[], and <params> is
> > > > > Object[]/List/Iterator.
> > > > >
> > > > > I'm actually happy to offer both versions. The current one would
reuse
> > > > > this one.
> > > > >
> > > > > > with versions for update and also allowing to pass a
> > PreparedStatement
> > > > > > in too (incase it needs special preparation). The types int[] is
so
> > you
> > > > > > can correctly set NULL values with a null reference, on certain
> > drivers
> > > > > > (Sybase and (i think) Oracle).
> > > > >
> > > > > The int[] is a good thing. It's ugly, but the only real way around
the
> > > > > null irritation as knowing which type = which column is db
dependent
> > and
> > > > > sql-statement dependant. [wtf is it with Oracle always returning
> > > > > BigDecimal :)]
> > > > >
> > > > > > If your looking to wrap the returned fields into a grid style
> > object,
> > > > > > then do so
> > > > >
> > > > > This goes against the concept of DbUtils I think. It's not meant
to
> > invent
> > > > > a new API to hide JDBC, but to only use the existing stuff in JDK.
> > Else it
> > > > > becomes a pointless component, there are lots of higher level JDBC
> > hiding
> > > > > things out there. Commons.sql does this for one I think.
> > > > >
> > > > > I imagine this will be a broken record on DbUtils. It is
definitely a
> > > > > project with a limit on its scope. It must be db-independent, and
it
> > > > > mustn't force people to adopt a new religion [framework]. My view
> > anyway
> > > > > :)
> > > > >
> > > > > Hen
> > > > >
> > > > > --
> > > > > To unsubscribe, e-mail:
> > > > <mailto:[EMAIL PROTECTED]>
> > > > > For additional commands, e-mail:
> > > > <mailto:[EMAIL PROTECTED]>
> > > > >
> > > > >
> > > > >
> > > > > --
> > > > > To unsubscribe, e-mail:
> > > > <mailto:[EMAIL PROTECTED]>
> > > > > For additional commands, e-mail:
> > > > <mailto:[EMAIL PROTECTED]>
> > > > >
> > > > >
> > > >
> > > >
> > > > --
> > > > To unsubscribe, e-mail:
> > <mailto:[EMAIL PROTECTED]>
> > > > For additional commands, e-mail:
> > <mailto:[EMAIL PROTECTED]>
> > > >
> > > >
> > >
> > >
> > > --
> > > To unsubscribe, e-mail:
> > <mailto:[EMAIL PROTECTED]>
> > > For additional commands, e-mail:
> > <mailto:[EMAIL PROTECTED]>
> > >
> >
> >
> > --
> > To unsubscribe, e-mail:
<mailto:[EMAIL PROTECTED]>
> > For additional commands, e-mail:
<mailto:[EMAIL PROTECTED]>
> >
> >
>
>
> --
> To unsubscribe, e-mail:
<mailto:[EMAIL PROTECTED]>
> For additional commands, e-mail:
<mailto:[EMAIL PROTECTED]>
>
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
