You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@struts.apache.org by Sean Burlington <se...@uncertainty.org.uk> on 2005/05/09 16:37:47 UTC
is there a better way of creating SQL queries from form objects ?
Hi all,
this is such a common problem that I'm sure there must be loads of
advice on how to deal with it - I just can't find it.
I have a database containing many records
Searches can be performed on several text fields - and refined by
boolean fields.
The html form thus consist of several text inputs and several checkboxes.
Only one text box should ever be filled in.
I can work out the resultant sql query by using lots of if statements as
below but it just doesn't seem very elegant ...
I could also break the search down into several forms and so different
actions - but this seems overly complex.
String query = "select order from po where";
String searchTerm = "";
if (!"".equals(search.getCode())){
query += " code=?";
searchTerm = search.getCode();
} else if (!"".equals(search.getParish())){
query += " parish=?";
searchTerm = search.getParish();
} else if (!"".equals(search.getDetails())){
query += " details=?";
searchTerm = search.getDetails();
}
if (search.isActiveMods()){
query += " and active";
}
if (search.isActiveReclass()){
query += " and reclass";
}
PreparedStatement statement = conn.prepareStatement(query);
statement.setString(1, searchTerm);
--
Sean
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
For additional commands, e-mail: user-help@struts.apache.org
Re: is there a better way of creating SQL queries from form objects
?
Posted by Aladin Alaily <st...@aladin.ca>.
Hi Sean,
iBatis works like a charm for what you're describing. You don't have to
learn a new language. Just use SQLMaps and you're done.
Good Luck.
Aladin
Sean Burlington wrote:
> Hi all,
> this is such a common problem that I'm sure there must be loads of
> advice on how to deal with it - I just can't find it.
>
> I have a database containing many records
>
> Searches can be performed on several text fields - and refined by
> boolean fields.
>
> The html form thus consist of several text inputs and several checkboxes.
>
> Only one text box should ever be filled in.
>
> I can work out the resultant sql query by using lots of if statements as
> below but it just doesn't seem very elegant ...
>
> I could also break the search down into several forms and so different
> actions - but this seems overly complex.
>
> String query = "select order from po where";
> String searchTerm = "";
>
> if (!"".equals(search.getCode())){
> query += " code=?";
> searchTerm = search.getCode();
> } else if (!"".equals(search.getParish())){
> query += " parish=?";
> searchTerm = search.getParish();
> } else if (!"".equals(search.getDetails())){
> query += " details=?";
> searchTerm = search.getDetails();
> }
>
> if (search.isActiveMods()){
> query += " and active";
> }
> if (search.isActiveReclass()){
> query += " and reclass";
> }
>
> PreparedStatement statement = conn.prepareStatement(query);
> statement.setString(1, searchTerm);
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
For additional commands, e-mail: user-help@struts.apache.org
Re: is there a better way of creating SQL queries from form objects
?
Posted by Sean Burlington <se...@uncertainty.org.uk>.
Rick Reumann wrote:
> Look into using iBATIS http://www.ibatis.com. It's easy to use and will
> handle this kind of dynamic SQL very nicel (the PDF instruction manual
> is nice. Look at the SQL Maps documentatoin under Dynamic Mapped
> Statements.)
that's a bigger change in coding than I was thinking of ...
but interesting - what advantages does this have over hibernate ?
--
Sean
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
For additional commands, e-mail: user-help@struts.apache.org
Re: is there a better way of creating SQL queries from form objects
?
Posted by Rick Reumann <st...@reumann.net>.
Look into using iBATIS http://www.ibatis.com. It's easy to use and will
handle this kind of dynamic SQL very nicel (the PDF instruction manual
is nice. Look at the SQL Maps documentatoin under Dynamic Mapped
Statements.)
Sean Burlington wrote the following on 5/9/2005 10:37 AM:
> Hi all,
> this is such a common problem that I'm sure there must be loads of
> advice on how to deal with it - I just can't find it.
>
> I have a database containing many records
>
> Searches can be performed on several text fields - and refined by
> boolean fields.
>
> The html form thus consist of several text inputs and several checkboxes.
>
> Only one text box should ever be filled in.
>
> I can work out the resultant sql query by using lots of if statements as
> below but it just doesn't seem very elegant ...
>
> I could also break the search down into several forms and so different
> actions - but this seems overly complex.
>
> String query = "select order from po where";
> String searchTerm = "";
>
> if (!"".equals(search.getCode())){
> query += " code=?";
> searchTerm = search.getCode();
> } else if (!"".equals(search.getParish())){
> query += " parish=?";
> searchTerm = search.getParish();
> } else if (!"".equals(search.getDetails())){
> query += " details=?";
> searchTerm = search.getDetails();
> }
>
> if (search.isActiveMods()){
> query += " and active";
> }
> if (search.isActiveReclass()){
> query += " and reclass";
> }
>
> PreparedStatement statement = conn.prepareStatement(query);
> statement.setString(1, searchTerm);
>
--
Rick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
For additional commands, e-mail: user-help@struts.apache.org
Re: is there a better way of creating SQL queries from form objects
?
Posted by Sean Burlington <se...@uncertainty.org.uk>.
Erik Weber wrote:
> As far as the implementation goes, there is no reason to be scared of
> switches and loops. (But favor polymorphism/virtual functions over a
> giant loop.) However, you should get rid of all those concat operators
> and use StringBuffer.append instead. The performance will improve
> significantly.
>
thanks Eric
polymorphism - that was the word I needed :-)
now google can help me ...
--
Sean
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
For additional commands, e-mail: user-help@struts.apache.org
Re: is there a better way of creating SQL queries from form objects
?
Posted by Erik Weber <er...@mindspring.com>.
Erik Weber wrote:
> As far as the implementation goes, there is no reason to be scared of
> switches and loops. (But favor polymorphism/virtual functions over a
> giant loop.)
Sorry, I meant over a giant switch, not loop!
> However, you should get rid of all those concat operators and use
> StringBuffer.append instead. The performance will improve significantly.
>
> Erik
>
>
> Sean Burlington wrote:
>
>> Hi all,
>> this is such a common problem that I'm sure there must be loads
>> of advice on how to deal with it - I just can't find it.
>>
>> I have a database containing many records
>>
>> Searches can be performed on several text fields - and refined by
>> boolean fields.
>>
>> The html form thus consist of several text inputs and several
>> checkboxes.
>>
>> Only one text box should ever be filled in.
>>
>> I can work out the resultant sql query by using lots of if statements
>> as below but it just doesn't seem very elegant ...
>>
>> I could also break the search down into several forms and so
>> different actions - but this seems overly complex.
>>
>> String query = "select order from po where";
>> String searchTerm = "";
>>
>> if (!"".equals(search.getCode())){
>> query += " code=?";
>> searchTerm = search.getCode();
>> } else if (!"".equals(search.getParish())){
>> query += " parish=?";
>> searchTerm = search.getParish();
>> } else if (!"".equals(search.getDetails())){
>> query += " details=?";
>> searchTerm = search.getDetails();
>> }
>>
>> if (search.isActiveMods()){
>> query += " and active";
>> }
>> if (search.isActiveReclass()){
>> query += " and reclass";
>> }
>>
>> PreparedStatement statement = conn.prepareStatement(query);
>> statement.setString(1, searchTerm);
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
> For additional commands, e-mail: user-help@struts.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
For additional commands, e-mail: user-help@struts.apache.org
Re: is there a better way of creating SQL queries from form objects
?
Posted by Erik Weber <er...@mindspring.com>.
As far as the implementation goes, there is no reason to be scared of
switches and loops. (But favor polymorphism/virtual functions over a
giant loop.) However, you should get rid of all those concat operators
and use StringBuffer.append instead. The performance will improve
significantly.
Erik
Sean Burlington wrote:
> Hi all,
> this is such a common problem that I'm sure there must be loads of
> advice on how to deal with it - I just can't find it.
>
> I have a database containing many records
>
> Searches can be performed on several text fields - and refined by
> boolean fields.
>
> The html form thus consist of several text inputs and several checkboxes.
>
> Only one text box should ever be filled in.
>
> I can work out the resultant sql query by using lots of if statements
> as below but it just doesn't seem very elegant ...
>
> I could also break the search down into several forms and so different
> actions - but this seems overly complex.
>
> String query = "select order from po where";
> String searchTerm = "";
>
> if (!"".equals(search.getCode())){
> query += " code=?";
> searchTerm = search.getCode();
> } else if (!"".equals(search.getParish())){
> query += " parish=?";
> searchTerm = search.getParish();
> } else if (!"".equals(search.getDetails())){
> query += " details=?";
> searchTerm = search.getDetails();
> }
>
> if (search.isActiveMods()){
> query += " and active";
> }
> if (search.isActiveReclass()){
> query += " and reclass";
> }
>
> PreparedStatement statement = conn.prepareStatement(query);
> statement.setString(1, searchTerm);
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
For additional commands, e-mail: user-help@struts.apache.org