springframework-developer

[Springframework-developer] New JDBC feature - named parameters

[Thomas R. <tho...@tr...>]

I added support for named parameters some days ago.  Here is an example:

    public List getPreferredBeer() {
     Map params = new HashMap(2);
     params.put("unwantedBrand", "Heineken");
     params.put("maxPrice", new BigDecimal(25.00));
     List l = getJdbcTemplate().queryForList(
       "select id, price, brand from beers " +
       "where price < :maxPrice and brand <> :unwantedBrand",
       params);
     return l;
   }

I put together a PDF document that highlights additional usage -  
http://www.springdeveloper.com/documents/spring-1_3-jdbc-example.pdf

Take a recent snapshot for a spin and let me know if something is  
missing or not working.  I'm still working on adding some additional  
tests.

Thomas

Re: [Springframework-developer] New JDBC feature - named parameters

[James E. <jam...@gm...>]

http://opensource2.atlassian.com/projects/spring/browse/SPR-303

Hey Thomas, I'm glad to see this getting some traction.  I have a few
questions though.

Will the implementation support pulling the values from a bean (where
the names in the query match property names in the bean)?  The
original argument against was to just use ibatis...but with what you
have it is so close.  I'd probably need to (for the most part) map my
bean to and from a hashmap for the execute calls.

I'm going to be looking at the code soon, but my first reaction to the
auto-expanding list was one of fear.  I'm sure it has been implemented
optimally, but just to voice my fear:  does passing in a list as a
value simply trigger a replacement of that value/marker with a comma
separated list of other markers (?s)?  Or is it one that will replace
the marker with a comma-separated list of the values.  If it is the
former, the hit is not too bad...assuming most use cases are such that
only a set number or combination winds up getting used (like between 3
and 10).  And (in the case of Oracle) only that many statements are
cached and all is well.  But if its the latter (replaced with
comma-separated values) then every single call is unique...and the db=20
(oracle anyway) will die (especially if this call is made frequently)
because the statement cache will constantly be pushing out statements
for these new ones that appear to be unique.

Anyway I hope I'm not insulting you...I'm sure you know this, like I
said, just voicing my initial gut-fear.

Thanks again Thomas,
James

On 9/7/05, Thomas Risberg <tho...@tr...> wrote:
> I added support for named parameters some days ago.  Here is an example: =
  =20
>                                                    =20
>=20
>    public List getPreferredBeer() {
>     Map params =3D new HashMap(2);
>     params.put("unwantedBrand", "Heineken");
>     params.put("maxPrice", new BigDecimal(25.00));
>     List l =3D getJdbcTemplate().queryForList(
>       "select id, price, brand from beers " +
>       "where price < :maxPrice and brand <> :unwantedBrand",
>       params);
>     return l;
>   }
>=20
> I put together a PDF document that highlights additional usage -
> http://www.springdeveloper.com/documents/spring-1_3-jdbc-example.pdf
>=20
> Take a recent snapshot for a spin and let me know if something is missing=
 or
> not working.  I'm still working on adding some additional tests.
>=20
> Thomas
>=20
>

Re: [Springframework-developer] New JDBC feature - named parameters

[Thomas R. <tho...@tr...>]

James,

I've been off for a few days so little progress on this feature  
recently - just pulled the code out of the main tree.  The code will  
make it's way back after 1.2.5 is out - might add it to the sandbox  
in the meantime.

I did not think of adding support for pulling data directly from a  
bean - but the more I think about it the more attractive this option  
seems.  There has to be some parts of Spring that can be reused for  
pulling property values from a bean :)

For the expanded list - I do use the placeholder/bind variable  
approach so no need to worry.  The primary issue will be problems  
with very long lists - different drivers/databases have different  
limits on how long the list can be.

Thomas

PS - I'm not insulted :)  It's always better to bring up issues while  
something is still being worked instead of afterwards.

On Sep 14, 2005, at 9:53 AM, James Estes wrote:

> http://opensource2.atlassian.com/projects/spring/browse/SPR-303
>
> Hey Thomas, I'm glad to see this getting some traction.  I have a few
> questions though.
>
> Will the implementation support pulling the values from a bean (where
> the names in the query match property names in the bean)?  The
> original argument against was to just use ibatis...but with what you
> have it is so close.  I'd probably need to (for the most part) map my
> bean to and from a hashmap for the execute calls.
>
> I'm going to be looking at the code soon, but my first reaction to the
> auto-expanding list was one of fear.  I'm sure it has been implemented
> optimally, but just to voice my fear:  does passing in a list as a
> value simply trigger a replacement of that value/marker with a comma
> separated list of other markers (?s)?  Or is it one that will replace
> the marker with a comma-separated list of the values.  If it is the
> former, the hit is not too bad...assuming most use cases are such that
> only a set number or combination winds up getting used (like between 3
> and 10).  And (in the case of Oracle) only that many statements are
> cached and all is well.  But if its the latter (replaced with
> comma-separated values) then every single call is unique...and the db
> (oracle anyway) will die (especially if this call is made frequently)
> because the statement cache will constantly be pushing out statements
> for these new ones that appear to be unique.
>
> Anyway I hope I'm not insulting you...I'm sure you know this, like I
> said, just voicing my initial gut-fear.
>
> Thanks again Thomas,
> James
>
> On 9/7/05, Thomas Risberg <tho...@tr...> wrote:
>
>> I added support for named parameters some days ago.  Here is an  
>> example:
>>
>>
>>    public List getPreferredBeer() {
>>     Map params = new HashMap(2);
>>     params.put("unwantedBrand", "Heineken");
>>     params.put("maxPrice", new BigDecimal(25.00));
>>     List l = getJdbcTemplate().queryForList(
>>       "select id, price, brand from beers " +
>>       "where price < :maxPrice and brand <> :unwantedBrand",
>>       params);
>>     return l;
>>   }
>>
>> I put together a PDF document that highlights additional usage -
>> http://www.springdeveloper.com/documents/spring-1_3-jdbc-example.pdf
>>
>> Take a recent snapshot for a spin and let me know if something is  
>> missing or
>> not working.  I'm still working on adding some additional tests.
>>
>> Thomas
>>
>>
>>
>
>
> -------------------------------------------------------
> SF.Net email is sponsored by:
> Tame your development challenges with Apache's Geronimo App Server.  
> Download
> it for free - -and be entered to win a 42" plasma tv or your very own
> Sony(tm)PSP.  Click here to play: http://sourceforge.net/geronimo.php
> _______________________________________________
> Springframework-developer mailing list
> Spr...@li...
> https://lists.sourceforge.net/lists/listinfo/springframework-developer
>
>
>