Search Engine Queries

[Nic Werner <we...@sonoma.edu>]

I know this might not be a pure JSTL thing, but if anyone could offer 
some pointers, I'd appreciate it:


I've got a search page thing going on in JSP (JSTL), and I want to be 
able to search on any of the fields that are entered, basically an AND 
filter. So as to not create dynamic SQL queries, I use the below SQL code:

<query part here.......>
and nvl(d.cable,' ') like nvl(?,'%')  and
nvl( d.pair , ' ') like nvl(?,'%')  and
nvl( to_char(d.prefix) , ' ') like nvl(?,'%')  and
nvl( upper(d.lname) , ' ') like upper(nvl(?,'%')) and
nvl( upper(d.fname) , ' ') like upper(nvl(?,'%')) and
nvl( to_char(d.DN) , ' ') like nvl(?,'%')   and
nvl( to_char(d.TN) , ' ') like nvl(?,'%')    and
nvl( upper(d.DEPT_CD) , ' ') like  nvl(?,'%')
<JSTL PARAMS>
sql:param value="${param.cable}"/>
<sql:param value="%${param.pair}%"/>
<sql:param value="${param.prefix}"/>
<sql:param value="%${param.lname}%"/>
<sql:param value="%${param.fname}%"/>
<sql:param value="%${param.dn}%"/>
<sql:param value="%${param.tn1}%${param.tn2}%${param.tn3}%${param.tn4}%"/> 
<sql:param value="${param.dept_cd}"/>


This works well, except all the 'like' statements really bog down the 
Oracle system. Can anyone suggest a way to dynamically create the above 
query portion, only putting in the statements that actually have data?

     I don't know much Java, but this seems like an applicable language 
to create the text, but PL/SQL seems viable also. JSTL might be 
inappropriate for this. Any thoughts? Am I reinventing the wheel?

Thanks,

- Nic.

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

Re: Search Engine Queries

[Keith <pd...@pdragon.net>]

Might be a lot of work depending how many AND statments you have, but you can nest <c:if> 
statements inside <sql:query> tags. Using one of your fields as an example:

<sql:query>
...
<c:if test="${!empty param.cable}" /> 
  and nvl(d.cable,' ') like nvl(?,'%')  and
</c:if>
...
<c:if test="${!empty param.cable}" />
  <sql:param value="${param.cable}"/>
</c:if>
...
</sql:query>

This way, the above field will only be added as part of the query if the 'cable' 
parameter has data entered into it (tests to see if it's "not empty"). Just have to make 
sure you put the <c:if> statements in both the query and params part.

Keith

---------- Original Message -----------
From: Nic Werner <we...@sonoma.edu>
To: tag Libraries Users List <ta...@jakarta.apache.org>
Sent: Thu, 03 Jun 2004 09:29:50 -0700
Subject: Search Engine Queries

> I know this might not be a pure JSTL thing, but if anyone could offer 
> some pointers, I'd appreciate it:
> 
> I've got a search page thing going on in JSP (JSTL), and I want to be 
> able to search on any of the fields that are entered, basically an AND 
> filter. So as to not create dynamic SQL queries, I use the below SQL code:
> 
> <query part here.......>
> and nvl(d.cable,' ') like nvl(?,'%')  and
> nvl( d.pair , ' ') like nvl(?,'%')  and
> nvl( to_char(d.prefix) , ' ') like nvl(?,'%')  and
> nvl( upper(d.lname) , ' ') like upper(nvl(?,'%')) and
> nvl( upper(d.fname) , ' ') like upper(nvl(?,'%')) and
> nvl( to_char(d.DN) , ' ') like nvl(?,'%')   and
> nvl( to_char(d.TN) , ' ') like nvl(?,'%')    and
> nvl( upper(d.DEPT_CD) , ' ') like  nvl(?,'%')
> <JSTL PARAMS>
> sql:param value="${param.cable}"/>
> <sql:param value="%${param.pair}%"/>
> <sql:param value="${param.prefix}"/>
> <sql:param value="%${param.lname}%"/>
> <sql:param value="%${param.fname}%"/>
> <sql:param value="%${param.dn}%"/>
> <sql:param value="%${param.tn1}%${param.tn2}%${param.tn3}%${param.tn4}%"/> 
> <sql:param value="${param.dept_cd}"/>
> 
> This works well, except all the 'like' statements really bog down the 
> Oracle system. Can anyone suggest a way to dynamically create the above 
> query portion, only putting in the statements that actually have data?
> 
>      I don't know much Java, but this seems like an applicable language 
> to create the text, but PL/SQL seems viable also. JSTL might be 
> inappropriate for this. Any thoughts? Am I reinventing the wheel?
> 
> Thanks,
> 
> - Nic.
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
------- End of Original Message -------


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