Menu
▾
▴
RE: [Springframework-developer] JdbcHelper
|
From: <tri...@tr...> - 2004-02-17 19:06:28
|
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 = jt.queryForInt("select count(*) from emp");
Object o2 = jt.queryForObject("select sysdate from dual", java.util.Date.class);
Object o3 = jt.queryForObject("select to_number('12.456', '9999.999') from
dual", Number.class);
Object o4 = jt.queryForList("select * from emp");
Quoting "jürgen höller [werk3AT]" <jue...@we...>:
> 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
>
>
> -------------------------------------------------------
> 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=1356&alloc_id=3438&op=click
> _______________________________________________
> Springframework-developer mailing list
> Spr...@li...
> https://lists.sourceforge.net/lists/listinfo/springframework-developer
>
|
