Re: [Springframework-developer] JdbcHelper

[<jue...@we...>]

Great! This is really a convenient way to execute simple SQL statements, =
both for update and for query.=20
=20
Allowing for using PreparedStatements without having to implement a =
PreparedStatementSetter is a significant benefit, as is giving easy =
access to simple results, avoiding the need for implementing a =
RowCallbackHandler. int results are worth special support here, as =
aggregate functions (count etc) all have integer results.
=20
I guess this is as good as it gets in a programmatic fashion. For =
declarative statements declarations and declarative parameter and/or =
result mappings, iBATIS SQL Maps is the way to go. Both approaches have =
clear value; it's good to see them both tap their respective full =
potential.
=20
Juergen
=20

________________________________

Von: spr...@li... im Auftrag =
von tri...@tr...
Gesendet: Di 17.02.2004 20:02
An: spr...@li...
Betreff: RE: [Springframework-developer] JdbcHelper



I like the changes - the new method names fit better with the rest of =
the
JdbcTemplate and Rod got his convenience 'int' method.  Unfortunaltely =
it did
not work to well for Oracle since getObject() maps a NUMERIC to
java.math.BigDecimal.  I added an explicit new Integer(getInt()) when =
the target
is an Integer and the result looks like it could be an integer (NUMERIC,
INTEGER, SMALLINT, TINYINT).

These all work for Oracle:
int i1 =3D jt.queryForInt("select count(*) from emp");
Object o2 =3D jt.queryForObject("select sysdate from dual", =
java.util.Date.class);
Object o3 =3D jt.queryForObject("select to_number('12.456', '9999.999') =
from
dual", Number.class);
Object o4 =3D jt.queryForList("select * from emp");


Quoting "j=FCrgen h=F6ller [werk3AT]" <jue...@we...>:

> I've reviewed the code, and I think it's good that we're addressing =
this =3D
> in JdbcTemplate itself. However, on second thought, it seems confusing =
=3D
> to have a runSqlStatement method that executes either a query or =
update, =3D
> with a multitude of possible result semantics. Thus, I've refactored =
=3D
> this into overloaded query/queryForList/queryForObject/queryForInt =3D
> methods.
>
> I've also added corresponding queryXxx and update methods that work =
with =3D
> PreparedStatements, taking an Object[] (and optionally a int[] array =
for =3D
> the SQL types) as arguments that get bound to the PreparedStatement. =
=3D
> This effectively offers some of the conveniences of SqlQuery's find =
=3D
> methods in JdbcTemplate itself; I think that's a valuable addition.
>
> Furthermore, I've put the new methods in JdbcOperations. In the course =
=3D
> of this, I've dropped doWithResultFromXxx methods from the =3D
> JdbcOperations interface: This is not something to be used on a =
regular =3D
> basis, thus I guess it's enough to make them available in JdbcTemplate =
=3D
> itself. This completely avoids the need to refer to the =3D
> ResultSetExtractor interface in the JdbcOperations interface.
>
> As a further minor change, I've allowed ResultSetExtractor to return =
an =3D
> arbitrary result object. The doWithResultFromXxx methods simply pass =
=3D
> this through. The new queryForList/queryForObject/queryForInt methods =
=3D
> are more elegant to implement with this. Finally, I've also moved =3D
> ColumnExtractor and DefaultColumnExtractor to the sandbox: They were =
=3D
> just used by JdbcHelper.
>
> (to be committed in about half an hour)
>
> Juergen
>
>
> -----Original Message-----
> From: spr...@li...
> [mailto:spr...@li...]On =
Behalf
> Of tri...@tr...
> Sent: Monday, February 16, 2004 10:16 PM
> To: spr...@li...
> Subject: Re: [Springframework-developer] JdbcHelper
>
>
>
> I have committed the new method to CVS.  I will add my local tests to =
=3D
> the proper
> test class later today or tomorrow (this is a standalone feature so =
=3D
> little risk
> of breaking any other functionality).  This might actually turn out to =
=3D
> be more
> of a test of MockObjects than real code, but it will at least outline =
=3D
> expected
> functionality.
>
> I ended up implementing (3) as an ArrayList of HashMaps using the =
column =3D
> name as
> key.  We could replace this with a disconnected rowset in the future.
>
> I'll think about the convenience method - is "int" sufficient?
>
> Here is an example:
>
> DriverManagerDataSource ds =3D3D new DriverManagerDataSource();
> ds.setDriverClassName("oracle.jdbc.driver.OracleDriver");
> ds.setUrl("jdbc:oracle:thin:@localhost:1521:ORCL");
> ds.setUsername("scott");
> ds.setPassword("tiger");
> JdbcTemplate jt =3D3D new JdbcTemplate(ds);
> Object o =3D3D jt.runSqlStatement("select * from emp");
> System.out.println(o.getClass().getName());
> System.out.println(o);
>
> java.util.ArrayList
> [{SAL=3D3D800, HIREDATE=3D3D1980-12-17 00:00:00.0, COMM=3D3Dnull, =3D
> EMPNO=3D3D7369, JOB=3D3DCLERK,
> DEPTNO=3D3D20, MGR=3D3D7902, ENAME=3D3DSMITH}, {SAL=3D3D1600, =3D
> HIREDATE=3D3D1981-02-20 00:00:00.0,
> COMM=3D3D300, EMPNO=3D3D7499, JOB=3D3DSALESMAN, DEPTNO=3D3D30, =
MGR=3D3D7698, =3D
> ENAME=3D3DALLEN},
> {SAL=3D3D1250, HIREDATE=3D3D1981-02-22 00:00:00.0, COMM=3D3D500, =
EMPNO=3D3D7521, =3D
> JOB=3D3DSALESMAN,
> DEPTNO=3D3D30, MGR=3D3D7698, ENAME=3D3DWARD}, {SAL=3D3D2975, =3D
> HIREDATE=3D3D1981-04-02 00:00:00.0,
> COMM=3D3Dnull, EMPNO=3D3D7566, JOB=3D3DMANAGER, DEPTNO=3D3D20, =
MGR=3D3D7839, =3D
> ENAME=3D3DJONES},
> {SAL=3D3D1250, HIREDATE=3D3D1981-09-28 00:00:00.0, COMM=3D3D1400, =3D
> EMPNO=3D3D7654, JOB=3D3DSALESMAN,
> DEPTNO=3D3D30, MGR=3D3D7698, ENAME=3D3DMARTIN}, {SAL=3D3D2850, =3D
> HIREDATE=3D3D1981-05-01 00:00:00.0,
> COMM=3D3Dnull, EMPNO=3D3D7698, JOB=3D3DMANAGER, DEPTNO=3D3D30, =
MGR=3D3D7839, =3D
> ENAME=3D3DBLAKE},
> {SAL=3D3D2450, HIREDATE=3D3D1981-06-09 00:00:00.0, COMM=3D3Dnull, =3D
> EMPNO=3D3D7782, JOB=3D3DMANAGER,
> DEPTNO=3D3D10, MGR=3D3D7839, ENAME=3D3DCLARK}, {SAL=3D3D3000, =3D
> HIREDATE=3D3D1987-04-19 00:00:00.0,
> COMM=3D3Dnull, EMPNO=3D3D7788, JOB=3D3DANALYST, DEPTNO=3D3D20, =
MGR=3D3D7566, =3D
> ENAME=3D3DSCOTT},
> {SAL=3D3D5000, HIREDATE=3D3D1981-11-17 00:00:00.0, COMM=3D3Dnull, =3D
> EMPNO=3D3D7839, JOB=3D3DPRESIDENT,
> DEPTNO=3D3D10, MGR=3D3Dnull, ENAME=3D3DKING}, {SAL=3D3D1500, =3D
> HIREDATE=3D3D1981-09-08 00:00:00.0,
> COMM=3D3D0, EMPNO=3D3D7844, JOB=3D3DSALESMAN, DEPTNO=3D3D30, =
MGR=3D3D7698, =3D
> ENAME=3D3DTURNER}, {SAL=3D3D1100,
> HIREDATE=3D3D1987-05-23 00:00:00.0, COMM=3D3Dnull, EMPNO=3D3D7876, =3D
> JOB=3D3DCLERK, DEPTNO=3D3D20,
> MGR=3D3D7788, ENAME=3D3DADAMS}, {SAL=3D3D950, HIREDATE=3D3D1981-12-03 =
=3D
> 00:00:00.0, COMM=3D3Dnull,
> EMPNO=3D3D7900, JOB=3D3DCLERK, DEPTNO=3D3D30, MGR=3D3D7698, =
ENAME=3D3DJAMES}, =3D
> {SAL=3D3D3000,
> HIREDATE=3D3D1981-12-03 00:00:00.0, COMM=3D3Dnull, EMPNO=3D3D7902, =3D
> JOB=3D3DANALYST, DEPTNO=3D3D20,
> MGR=3D3D7566, ENAME=3D3DFORD}, {SAL=3D3D1300, HIREDATE=3D3D1982-01-23 =
=3D
> 00:00:00.0, COMM=3D3Dnull,
> EMPNO=3D3D7934, JOB=3D3DCLERK, DEPTNO=3D3D10, MGR=3D3D7782, =
ENAME=3D3DMILLER}]
>
> Thomas
>
>
> Quoting Rod Johnson <rod...@in...>:
>
> > Thomas,
> >=3D20
> > Sounds great. With this there I'd be glad to get rid of JdbcHelper.
> >=3D20
> > Not sure about (3). I think this needs further thought. For 1.1 we =
=3D
> could add
> > a true disconnected result set: not RowSet as it throws =
SQLException, =3D
> which
> > we want to get away from.
> >=3D20
> > Also a convenience method returning int would be handy, for counts =
and =3D
> the
> > like. Please can I have this, despite Juergen's dislike of =
convenience
> > methods :-)
> >=3D20
> > Regards,
> > Rod
> >=3D20
> > ----- Original Message -----
> > From: <tri...@tr...>
> > To: <spr...@li...>
> > Sent: Monday, February 16, 2004 5:26 PM
> > Subject: RE: [Springframework-developer] JdbcHelper
> >=3D20
> >=3D20
> > > I can see the need to go beyond a single row/value type query.
> > >
> > > How about a new method for the JdbcTemplate:
> > >
> > > Object runSqlStatement(String)
> > >
> > > Based on the type of SQL passed in it would return:
> > >
> > > 1) An Integer containing the number of rows affected if it is an =
=3D
> update
> > statement
> > >
> > > runSqlStatement("update emp set salary =3D3D salary * 1.5") would =
=3D
> return an
> > Integer
> > > with the update count
> > >
> > > 2) A single Object (Integer/Long/String) based on the value =
returned =3D
> from
> > a
> > > single value/single row query
> > >
> > > runSqlStatement("select last_name frmo emp where id =3D3D 2") =
would =3D
> return a
> > String
> > > containing the last name
> > >
> > > 3) An ArrayList of ArrayLists containing a list of rows with a =
list =3D
> of
> > column
> > > values returned by the query
> > >
> > > runSqlStatement("selecy id, last_name from emp") would return an =
=3D
> ArrayList
> > > containing an ArrayList for each row.  The second list would =
contain =3D
> an
> > Integer
> > > with the id and a String with the last_name.
> > >
> > >
> > > Number 3 might be a stretch, but we would still have to check for =
=3D
> this,
> > since we
> > > have no control over the SQL coming in.
> > >
> > > Thomas
> > >
> > >
> > > Quoting rod...@in...:
> > >
> > > > I've actually just (yesterday) introduced into into a whole
> > > > bunch of test cases at a client. Maybe we could put an
> > > > improved runSQLFunction() method on JdbcTemplate? This is a
> > > > very convenient one-liner, and basically the only reason I
> > > > use JdbcTemplate.
> > > >
> > > > Regards,
> > > > Rod
> > > >
> > > >
> > > > -------------------------------------------------------
> > > > SF.Net is sponsored by: Speed Start Your Linux Apps Now.
> > > > Build and deploy apps & Web services for Linux with
> > > > a free DVD software kit from IBM. Click Now!
> > > > =
http://ads.osdn.com/?ad_id=3D3D1356&alloc_id=3D3D3438&op=3D3Dclick
> > > > _______________________________________________
> > > > Springframework-developer mailing list
> > > > Spr...@li...
> > > > =3D
> https://lists.sourceforge.net/lists/listinfo/springframework-developer
> > > >
> > >
> > >
> > >
> > >
> > >
> > > -------------------------------------------------------
> > > SF.Net is sponsored by: Speed Start Your Linux Apps Now.
> > > Build and deploy apps & Web services for Linux with
> > > a free DVD software kit from IBM. Click Now!
> > > http://ads.osdn.com/?ad_id=3D3D1356&alloc_id=3D3D3438&op=3D3Dclick
> > > _______________________________________________
> > > Springframework-developer mailing list
> > > Spr...@li...
> > > =3D
> https://lists.sourceforge.net/lists/listinfo/springframework-developer
> >=3D20
> >=3D20
> >=3D20
> >=3D20
> > -------------------------------------------------------
> > SF.Net is sponsored by: Speed Start Your Linux Apps Now.
> > Build and deploy apps & Web services for Linux with
> > a free DVD software kit from IBM. Click Now!
> > http://ads.osdn.com/?ad_id=3D3D1356&alloc_id=3D3D3438&op=3D3Dclick
> > _______________________________________________
> > Springframework-developer mailing list
> > Spr...@li...
> > =
https://lists.sourceforge.net/lists/listinfo/springframework-developer
> >=3D20
>
>
>
>
>
> -------------------------------------------------------
> SF.Net is sponsored by: Speed Start Your Linux Apps Now.
> Build and deploy apps & Web services for Linux with
> a free DVD software kit from IBM. Click Now!
> http://ads.osdn.com/?ad_id=3D3D1356&alloc_id=3D3D3438&op=3D3Dclick
> _______________________________________________
> Springframework-developer mailing list
> Spr...@li...
> https://lists.sourceforge.net/lists/listinfo/springframework-developer
>
>
> -------------------------------------------------------
> SF.Net is sponsored by: Speed Start Your Linux Apps Now.
> Build and deploy apps & Web services for Linux with
> a free DVD software kit from IBM. Click Now!
> http://ads.osdn.com/?ad_id=3D1356&alloc_id=3D3438&op=3Dclick
> _______________________________________________
> Springframework-developer mailing list
> Spr...@li...
> https://lists.sourceforge.net/lists/listinfo/springframework-developer
>




-------------------------------------------------------
SF.Net is sponsored by: Speed Start Your Linux Apps Now.
Build and deploy apps & Web services for Linux with
a free DVD software kit from IBM. Click Now!
http://ads.osdn.com/?ad_id=3D1356&alloc_id=3D3438&op=3Dclick
_______________________________________________
Springframework-developer mailing list
Spr...@li...
https://lists.sourceforge.net/lists/listinfo/springframework-developer