Named Bind Variables

[james.estes]

I'm going to post an issue in JIRA, but it appears down. Thought I'd post here to get some feelers.

When I first started using the SqlQuery objects, I noticed that the
SqlParameter object has a name property. This made me think that
maybe spring supports named bind variables. So I tried it:


select *
from employees
where (firstName=:name
OR lastname=:name)
and :refDate between startdate and enddate

...no dice. So, since the support was not there, I added it (I will post the diff to JIRA).
Following is a description of the changes I made.

To start out with, I added a SqlParameterValue class that basically
wraps an object and gives it a name. This is the name of the declared
parameter that the object corresponds to. I then modified
PreparedStatementCreatorFactory to check for values of this type and
upon seeing them, re-orders the input parameter list to match the
order their names appear in the declared parameter list. At this
point, I could create a sql query that declares parameters with names
and then calls execute with SqlParameterValues with names:



super(ds, "select * "+
"from employees "+
"where (firstname=? "+
" or lastname=?) "+
" and ? between startdate and enddate"
);
declareParameter(new SqlParameter("firstname", SqlTypes.VARCHAR));
declareParameter(new SqlParameter("lastname", SqlTypes.VARCHAR));
declareParameter(new SqlParameter("refdate", SqlTypes.DATE));
compile();

and execute it with:


query.execute(new Object[]{
new SqlParameterValue("lastname", commandBean.getLastName()),
new SqlParameterValue("firstname", commandBean.getFirstName()),
new SqlParameterValue("refdate", commandBean.getRefDate())
);


...notice I can pass the parameters in any order...they will be
reorganized by the PreparedStatementCreatorFactory.

Then, stepping back and looking at the result, I thought it would be
really nice if i could just pass my bean to the execute method and
have spring pull out the parameters from the properties of the bean.
I added this support by adding a method to the RdbmsOperation class
that pulls the properties from the bean, then added "*fromBean"
methods (updateFromBean, executeFromBean, and findObjectFromBean) to
the SqlUpdate and SqlQuery classes. Now I can execute my query with:


query.executeFromBean( commandBean );


I still wanted to be able to have named
bind variables within the actual sql string and not have to be sure
and declare the parameters in the order they appear in the sql. I
also wanted to be able to reference a declared parameter multiple
times in the same sql (in my query the firstname and lastname on the
command bean are actually the same value....the query is finding an
employee who was known to be employed on a given date and first or
last name was the given value).

So I modified PreparedStatementCreatorFactory to have a
usingNamedBinds property that would be set to true when using a sql
query that contained named bind variables. When this 'mode' is on,
the factory will pull the placeholders from the sql and reorder
the declared parameters and the input parameters (when called) to
match the order they appear in the sql. I then modified SqlOperation
to also have a usingNamedBinds property that would be passed through
to the PreparedStatementCreatorFactory. Once finished, this allowed
me to declare and execute my query as:



super(ds, "select * "+
"from employees "+
"where (firstname=:name "+
" or lastname=:name) "+
" and :refDate between startdate and enddate"
);
setUsingNamedBinds(true);
declareParameter(new SqlParameter("refDate", SqlTypes.DATE));
declareParameter(new SqlParameter("name", SqlTypes.VARCHAR));
compile();

and execute it with:


//order MUST match declared order
query.execute(new Object[]{
commandBean.getRefDate(),
commandBean.getName()
);

OR


//order need not match declared order
query.execute(new Object[]{
new SqlParameterValue("name", commandBean.getName()),
new SqlParameterValue("refDate", commandBean.getRefDate())
);

OR


query.executeFromBean(commandBean);

...and we have what I originally wanted.

So, I hope you'll find this useful enough to include into spring. I
know it has greatly simplified my daos. My next logical step is to
externalize the queries entirely and introduce a NamedSqlFactory that
creates SqlOperations by reading some external source. The implementation
I'm thinking about would have a .sql file that it reads from a file similar
to:



--findEmployeesByNameAndDate(String name, Date refDate):com.foo.Employee{
select *
from employees
where (firstName=:name
OR lastname=:name)
and :refDate between startdate and enddate
--}

My dao could then create and call it by:


List employees = namedSqlFactory.executeFromBean(
"findEmployeesByNameAndDate", commandBean );

[Colin Sampaleanu]

James,

This is potentially useful and you should put it into Jira. One thing to keep in mind though is that by the last step (externalizing the queries to an external file), you are essentially getting into a very weak version of the iBatis SQL Maps functionality. iBatis works great, and we support it out of the box, mixing and matching with the other data-access strategies, so I would suggest that you just use iBatis for externalized queries/updates.

[james.estes]

Thanks, I'll check out iBatis...I've heard so much about it I'm not sure why I haven't had a look already. I'll be posting the Jira issue later today.

James

[james.estes]

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

[Tom Turelinckx]

James,

On our project, we've been using simple extensions adding named parameter and dynamic sql support to the Spring jdbc framework for well over a year now, in several different applications, all running in production.

As there have been requests for named parameter support on the mailing lists before, I've described (http://article.gmane.org/gmane.comp.java.springframework.user/1721) our approach, and sent (in private mail) a revised, simplified and polished version of our code, complete with javadocs and unit tests, to a few key Spring developers for review, about 3 months ago.

Though I haven't heard from them since, a couple of changes have been made to the jdbc framework afterwards, that simplified our code. I gues there are good reasons for not adding this kind of functionality to Spring, probably to avoid re-implementing functionality iBatis already provides...

Nevertheless, I like our code better than iBatis, because it fits nicely within the Spring jdbc framework, allows different dynamic sql parser implementations, and because the code is very, very simple. Also, there's no need to patch any Spring classes, just add a few additional ones.

To recapitulate what's described in the posting mentioned above, there's a NamedParameterSqlQuery class that works exactly like SqlQuery, but allows ":name" notation in the sql string. The declared SqlParameters must have a name, and the execute method takes a Map instead of an array of parameters. NamedParameterSqlQuery is supposed to be configured as a singleton bean.
There's also DynamicSqlQuery that allows the sql string to be dynamic, e.g. VTL. It's configured as a prototype bean, and there's a corresponding singleton DynamicSqlQueryFactory bean, that you can pass a context Map to receive a specific prototype instance. The context Map contains the information needed to execute the dynamic sql string and turn it into a static sql string. The DynamicSqlQuery instances are cached by the Factory (based on the context Map), and once you've gotten an instance from the Factory, it works just like a NamedParameterSqlQuery.

I've attached the most important source files as a zip file to your JIRA issue (apparently, attachments to forum posts are not possible) so you can have a look. Though these files can be compiled, the archive is not complete. You'll have to add a few jar files, and there's no build script, javadoc or unit tests included. Most importantly, a concrete SqlParser implementation is missing, but it's extremely straightforward to implement using Velocity as a parser. I didn't like Velocity's whitespace handling, however, so we're using a custom implementation that is too ugly to publish ;-)

Kind regards,
Tom.