RE: is there a better way of creating SQL queries from form objec ts ?

["Benedict, Paul C" <pa...@merck.com>]

Sean,

Have you thought about constructing a DAO/Adapater object that receives your
input, and then does the notorious SQL-string building on the inside? If you
cannot get the elegant solution, you should at least make it appear elegant
by wrapping it inside of the DAO layer.

-----Original Message-----
From: Sean Burlington [mailto:sean@uncertainty.org.uk] 
Sent: Monday, May 09, 2005 10:38 AM
To: Struts Users Mailing List
Subject: 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





------------------------------------------------------------------------------
Notice:  This e-mail message, together with any attachments, contains information of Merck & Co., Inc. (One Merck Drive, Whitehouse Station, New Jersey, USA 08889), and/or its affiliates (which may be known outside the United States as Merck Frosst, Merck Sharp & Dohme or MSD and in Japan, as Banyu) that may be confidential, proprietary copyrighted and/or legally privileged. It is intended solely for the use of the individual or entity named on this message.  If you are not the intended recipient, and have received this message in error, please notify us immediately by reply e-mail and then delete it from your system.
------------------------------------------------------------------------------

---------------------------------------------------------------------
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 objec ts ?

[Sean Burlington <se...@uncertainty.org.uk>]

Benedict, Paul C wrote:
> Sean,
> 
> Have you thought about constructing a DAO/Adapater object that receives your
> input, and then does the notorious SQL-string building on the inside? If you
> cannot get the elegant solution, you should at least make it appear elegant
> by wrapping it inside of the DAO layer.


yes - I've done that bit :-)

I'm a solo in-house programmer and sometimes I worry that I'm doing 
things like this badly.

your comment "notorious SQL-string building" makes me think maybe I'm 
not doing so bad and that this is just a pain....

Cheers for the advice

-- 

Sean



---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
For additional commands, e-mail: user-help@struts.apache.org