RE: [Springframework-developer] JdbcHelper

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

I've reviewed the code, and I think it's good that we're addressing this =
in JdbcTemplate itself. However, on second thought, it seems confusing =
to have a runSqlStatement method that executes either a query or update, =
with a multitude of possible result semantics. Thus, I've refactored =
this into overloaded query/queryForList/queryForObject/queryForInt =
methods.

I've also added corresponding queryXxx and update methods that work with =
PreparedStatements, taking an Object[] (and optionally a int[] array for =
the SQL types) as arguments that get bound to the PreparedStatement. =
This effectively offers some of the conveniences of SqlQuery's find =
methods in JdbcTemplate itself; I think that's a valuable addition.

Furthermore, I've put the new methods in JdbcOperations. In the course =
of this, I've dropped doWithResultFromXxx methods from the =
JdbcOperations interface: This is not something to be used on a regular =
basis, thus I guess it's enough to make them available in JdbcTemplate =
itself. This completely avoids the need to refer to the =
ResultSetExtractor interface in the JdbcOperations interface.

As a further minor change, I've allowed ResultSetExtractor to return an =
arbitrary result object. The doWithResultFromXxx methods simply pass =
this through. The new queryForList/queryForObject/queryForInt methods =
are more elegant to implement with this. Finally, I've also moved =
ColumnExtractor and DefaultColumnExtractor to the sandbox: They were =
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 =
the proper
test class later today or tomorrow (this is a standalone feature so =
little risk
of breaking any other functionality).  This might actually turn out to =
be more
of a test of MockObjects than real code, but it will at least outline =
expected
functionality.

I ended up implementing (3) as an ArrayList of HashMaps using the column =
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 =3D new DriverManagerDataSource();
ds.setDriverClassName("oracle.jdbc.driver.OracleDriver");
ds.setUrl("jdbc:oracle:thin:@localhost:1521:ORCL");
ds.setUsername("scott");
ds.setPassword("tiger");
JdbcTemplate jt =3D new JdbcTemplate(ds);
Object o =3D jt.runSqlStatement("select * from emp");
System.out.println(o.getClass().getName());
System.out.println(o);

java.util.ArrayList
[{SAL=3D800, HIREDATE=3D1980-12-17 00:00:00.0, COMM=3Dnull, =
EMPNO=3D7369, JOB=3DCLERK,
DEPTNO=3D20, MGR=3D7902, ENAME=3DSMITH}, {SAL=3D1600, =
HIREDATE=3D1981-02-20 00:00:00.0,
COMM=3D300, EMPNO=3D7499, JOB=3DSALESMAN, DEPTNO=3D30, MGR=3D7698, =
ENAME=3DALLEN},
{SAL=3D1250, HIREDATE=3D1981-02-22 00:00:00.0, COMM=3D500, EMPNO=3D7521, =
JOB=3DSALESMAN,
DEPTNO=3D30, MGR=3D7698, ENAME=3DWARD}, {SAL=3D2975, =
HIREDATE=3D1981-04-02 00:00:00.0,
COMM=3Dnull, EMPNO=3D7566, JOB=3DMANAGER, DEPTNO=3D20, MGR=3D7839, =
ENAME=3DJONES},
{SAL=3D1250, HIREDATE=3D1981-09-28 00:00:00.0, COMM=3D1400, =
EMPNO=3D7654, JOB=3DSALESMAN,
DEPTNO=3D30, MGR=3D7698, ENAME=3DMARTIN}, {SAL=3D2850, =
HIREDATE=3D1981-05-01 00:00:00.0,
COMM=3Dnull, EMPNO=3D7698, JOB=3DMANAGER, DEPTNO=3D30, MGR=3D7839, =
ENAME=3DBLAKE},
{SAL=3D2450, HIREDATE=3D1981-06-09 00:00:00.0, COMM=3Dnull, =
EMPNO=3D7782, JOB=3DMANAGER,
DEPTNO=3D10, MGR=3D7839, ENAME=3DCLARK}, {SAL=3D3000, =
HIREDATE=3D1987-04-19 00:00:00.0,
COMM=3Dnull, EMPNO=3D7788, JOB=3DANALYST, DEPTNO=3D20, MGR=3D7566, =
ENAME=3DSCOTT},
{SAL=3D5000, HIREDATE=3D1981-11-17 00:00:00.0, COMM=3Dnull, =
EMPNO=3D7839, JOB=3DPRESIDENT,
DEPTNO=3D10, MGR=3Dnull, ENAME=3DKING}, {SAL=3D1500, =
HIREDATE=3D1981-09-08 00:00:00.0,
COMM=3D0, EMPNO=3D7844, JOB=3DSALESMAN, DEPTNO=3D30, MGR=3D7698, =
ENAME=3DTURNER}, {SAL=3D1100,
HIREDATE=3D1987-05-23 00:00:00.0, COMM=3Dnull, EMPNO=3D7876, =
JOB=3DCLERK, DEPTNO=3D20,
MGR=3D7788, ENAME=3DADAMS}, {SAL=3D950, HIREDATE=3D1981-12-03 =
00:00:00.0, COMM=3Dnull,
EMPNO=3D7900, JOB=3DCLERK, DEPTNO=3D30, MGR=3D7698, ENAME=3DJAMES}, =
{SAL=3D3000,
HIREDATE=3D1981-12-03 00:00:00.0, COMM=3Dnull, EMPNO=3D7902, =
JOB=3DANALYST, DEPTNO=3D20,
MGR=3D7566, ENAME=3DFORD}, {SAL=3D1300, HIREDATE=3D1982-01-23 =
00:00:00.0, COMM=3Dnull,
EMPNO=3D7934, JOB=3DCLERK, DEPTNO=3D10, MGR=3D7782, ENAME=3DMILLER}]

Thomas

Quoting Rod Johnson <rod...@in...>:

> Thomas,
>=20
> Sounds great. With this there I'd be glad to get rid of JdbcHelper.
>=20
> Not sure about (3). I think this needs further thought. For 1.1 we =
could add
> a true disconnected result set: not RowSet as it throws SQLException, =
which
> we want to get away from.
>=20
> Also a convenience method returning int would be handy, for counts and =
the
> like. Please can I have this, despite Juergen's dislike of convenience
> methods :-)
>=20
> Regards,
> Rod
>=20
> ----- Original Message -----
> From: <tri...@tr...>
> To: <spr...@li...>
> Sent: Monday, February 16, 2004 5:26 PM
> Subject: RE: [Springframework-developer] JdbcHelper
>=20
>=20
> > 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 =
update
> statement
> >
> > runSqlStatement("update emp set salary =3D salary * 1.5") would =
return an
> Integer
> > with the update count
> >
> > 2) A single Object (Integer/Long/String) based on the value returned =
from
> a
> > single value/single row query
> >
> > runSqlStatement("select last_name frmo emp where id =3D 2") would =
return a
> String
> > containing the last name
> >
> > 3) An ArrayList of ArrayLists containing a list of rows with a list =
of
> column
> > values returned by the query
> >
> > runSqlStatement("selecy id, last_name from emp") would return an =
ArrayList
> > containing an ArrayList for each row.  The second list would contain =
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 =
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=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
>=20
>=20
>=20
>=20
> -------------------------------------------------------
> 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
>=20

-------------------------------------------------------
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