Re: [Springframework-developer] JdbcHelper

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

Agreed. I can kill some convenience methods that do essentially the same 
thing...

jürgen höller [werk3AT] wrote:

>Great! This is really a convenient way to execute simple SQL statements, both for update and for query. 
> 
>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.
> 
>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.
> 
>Juergen
> 
>
>________________________________
>
>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 = 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
>>    
>>
>>>>        
>>>>