N
nico
I actually posted this at the javaranch forums already. I usually
don't 'cross-post' but I haven't figured this out and I HAVE to start
coding this tomorrow! Please help. Thanks all. Here's my question:
How to implement Advanced Searching via PreparedStatement. Just to be
on the same page so you understand what I'm after, please look at the
advanced google search form here:
http://www.google.com/advanced_search?hl=en
Notice that the user can input whatever fields they want (one, a few,
many, all, it doesn't matter to in respect to what google is requiring
of the user). So I have something similar where I have about a dozen
fields and I don't want to restrict the user on which combinations of
fields they use.
As far as I know, I have to provide the column names and the values
can be dynamic using the IN parameters like: name=? AND age=?, etc.,
but I can't do: ?=? AND ?=?. I'm correct on this aren't I? This is
from looking at several examples and having always done it this way
myself. However, if I'm wrong and there's an easier way to do this
that would be great.
So given my problem statement how would I deal with the multiple
combinations of 'chosen' fields by the user without doing this
dynamically? (I'm thinking that I WILL have to do this dynamically) So
for example with only three fields WITHOUT doing it dynamically I'm
conceiving that I'd have to go through this mess:
code:
col1=?
col1=? AND col2=?
col1=? AND col3=?
col1=? AND col2=? AND col3=?
col2=?
col2=? AND col3=?
col3=?
...etc...
So for 10 fields it's going to get ridiculous unless I do it
dynamically, or use my first idea of having one PreparedStatement and
setting empty fields to sql wildcard '%' so I could just do:
col1=? AND col2=? AND col3=?.
However, this would not be the most efficient way as the extra search
fields, so I am quite keen to find another solution. I have already
done a lot of dynamic building of sql so it doesn't bother me to do so
if I have to, but I'm just wondering if there's a better way before I
commit to that solution. I have to start coding tomorrow so I'm eager
to figure this out ASAP. Thanks all!
don't 'cross-post' but I haven't figured this out and I HAVE to start
coding this tomorrow! Please help. Thanks all. Here's my question:
How to implement Advanced Searching via PreparedStatement. Just to be
on the same page so you understand what I'm after, please look at the
advanced google search form here:
http://www.google.com/advanced_search?hl=en
Notice that the user can input whatever fields they want (one, a few,
many, all, it doesn't matter to in respect to what google is requiring
of the user). So I have something similar where I have about a dozen
fields and I don't want to restrict the user on which combinations of
fields they use.
As far as I know, I have to provide the column names and the values
can be dynamic using the IN parameters like: name=? AND age=?, etc.,
but I can't do: ?=? AND ?=?. I'm correct on this aren't I? This is
from looking at several examples and having always done it this way
myself. However, if I'm wrong and there's an easier way to do this
that would be great.
So given my problem statement how would I deal with the multiple
combinations of 'chosen' fields by the user without doing this
dynamically? (I'm thinking that I WILL have to do this dynamically) So
for example with only three fields WITHOUT doing it dynamically I'm
conceiving that I'd have to go through this mess:
code:
col1=?
col1=? AND col2=?
col1=? AND col3=?
col1=? AND col2=? AND col3=?
col2=?
col2=? AND col3=?
col3=?
...etc...
So for 10 fields it's going to get ridiculous unless I do it
dynamically, or use my first idea of having one PreparedStatement and
setting empty fields to sql wildcard '%' so I could just do:
col1=? AND col2=? AND col3=?.
However, this would not be the most efficient way as the extra search
fields, so I am quite keen to find another solution. I have already
done a lot of dynamic building of sql so it doesn't bother me to do so
if I have to, but I'm just wondering if there's a better way before I
commit to that solution. I have to start coding tomorrow so I'm eager
to figure this out ASAP. Thanks all!