build dynamic query

How do I build dynamic query based on user selected multi search fields in JSP ?

Please see the file attached for details.
query.txt
cofactorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gurvinder Pal SinghCommented:
0
cofactorAuthor Commented:
Please see the attached  file in the post for details.  I dont want third party library. I need java solution. . I think a good logic can solve my problem. what say ?
0
for_yanCommented:
String name = request.getParameter("name");

String designation = request.etParameter("designation");

String department = request.getParameter("department");

select n.name, d.designation_name, dt.department_name from
Name_tbl n, Department_link dl, department_tbl dt, Designation_tbl d
where n.user_sl_no = l.user_sl_no and
l.department_sl_no = dt.department_sl_no and
d.designation_sl_no = n.designation_sl_no

0
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

for_yanCommented:
String name = request.getParameter("name");

String designation = request.etParameter("designation");

String department = request.getParameter("department");


ResultSet rs = stmt.executeQuery
("select n.name, d.designation_name, dt.department_name from
Name_tbl n, Department_link dl, department_tbl dt, Designation_tbl d
where n.user_sl_no = l.user_sl_no and
l.department_sl_no = dt.department_sl_no and
d.designation_sl_no = n.designation_sl_no and
n.name = '"  + name + "' and dt.department = '" + department + "' and d.designation = '" + designation + "'")


0
CEHJCommented:
Why not just use a limited version of phpMySql?
0
saleh_neuCommented:
1-after getting the parameter as mentioned in for_yan post you need to a connection to your data base
here is a simple getConnection method to oracle database :
    private Connection getConnection ()
    {
           Connection con=null;
          try {
              
                Class.forName("oracle.jdbc.driver.OracleDriver");
                con=DriverManager.getConnection("jdbc:oracle:thin:@dpe-test-server:1521:orcl","username","password");
             } catch(Exception e)
             {e.printStackTrace();}
            
             return con;
           }
so u need to modify it to accomidate your mysql  DB , keep in mind that you will need  mysql  DB driver in the classpath
2- create another method that returns ResultSet  using the getConnection  method
   

public ResultSet  getMyRS(string  username ,String password)
{
      Connection con=getConnection();
      
      String query="the query as mentioned in for_yan post  ";
      try
      {

            Statement stmt = con.createStatement ();
            
            ResultSet rset = stmt.executeQuery (query);

                                           return rset ;
             
    }
      catch(Exception e)
      {e.printStackTrace();}
      return false;
}

3- in the jsp page u should call getMyRS method to get the resultset then desply its content as follow
<%
ResultSet  rset =YourClassName. getMyRS();

            while (rset.next ())
            
            {
                  out.print(rset.getString(1)+ " it works" ) ;
                  
                  
            }

%>

bear in mind that is way is the simplest way , bcs it use a direct connection to DB and allow sql injection

chears
0
cofactorAuthor Commented:
for_yan,

your solution looks good.  But it has one problem. Let me explain,Assume  a user did not choose any values from  Designation select box. and sohe  default value from Designation box  '-1'   goes to server.

so your  query becomes now
.........................blah blah..........
"' and d.designation = '-1' ;

This is meaningless. Because this part of the query should be removed from the SQL because user really did not select any Designation item value so why to keep this part of the SQL!  ....Likewise user could skip some other item in the jsp and so accordingly your SQL query should vary

So,your query can not give the intended result ..is not it ?
0
CEHJCommented:
If you're set on using Java, use a PreparedStatement and most importantly, ensure that the user account has only read access to the db
0
cofactorAuthor Commented:
>>If you're set on using Java, use a PreparedStatement

Yes. I'm using PreparedStatement.

>>>and most importantly, ensure that the user account has only read access to the db

How does this address the issue I raised in the above post.  my jsp is a search page so user can  only search the db.

CHEJ, could you please look at the file attached in the post. This explain what I'm looking for.  I wish to search DB and return records based on the seledted items by the user in the jsp. Remember user may not input all the search fields.
0
for_yanCommented:
Of course that is true.

In such situation , if some of the vaues can be omityted,
you should create String which will vary
dependeing on which of the fieds
the user selected.
You create first part which depeds on name
String s = ....

if(designation != null) {
s += ....
} else if(department != null) {


} else {

}


I just showed the general framework - in fact you probably would want to select
also something menaingful  about the user, department, etc. not just the same data which theye
entered as in my query


0
for_yanCommented:

And then ins the end
you'kll odf cosues use this combined String and feed it to
executeQuery;

ResultSet rs = stmt.executeQuery(s);

where s was created based on all conditions
0
for_yanCommented:
You'll probably need some validation upfront - say if your user skips name -
your page should probably complain and not even run query.
0
cofactorAuthor Commented:
No .That logic still wont work .

For example,

User may input  any 1 field .  User may input any 2 fields ,  user may input all the 3 fields together.

For input  into any 1 field , there is no SQL join required.

For input  into multiple fields , SQL join is required.
 
So, query will vary depending upon selection of the fields user has inputted.  This looks complicated to me and I find it hard to build the query.
0
cofactorAuthor Commented:
>>>You'll probably need some validation upfront - say if your user skips name -
>>your page should probably complain and not even run query.

No. Thats a valid search criteria !  .

because user may try to search Name based upon say Designation and Departments . so, he will input  Designation and Departments field only . my search results will bring all user Name who has the matching Designation and Departments entered by user .

0
for_yanCommented:

Well, i had in many situations where user had a choice
to input 10-15 fields and I had a method which was going one by one
through each of the field and I was concatentaing the clause of the query.

It is true, you have a little bit excessive  of relationality in your tables which
makes the suituation more complicated, nevertheless it is quite doable - if you have
only three fields, and perhpas name is mandatory - then there would be only four cases -
not difficult to manage.
0
CEHJCommented:
Create some 'valid' statement strings and filter out any invalid queries using JavaScript, which will save useless server-side work
0
for_yanCommented:
Even eight cases is not that a big deal.

In fact I guess it would vbe more simple if you create a view out of your tables
so that each name would have department and designation and then
you query would become equivalent to what I used to do - you'll just concatenate clauses -
 
0
for_yanCommented:
You'll have the beginning:

String s = "select .... from my_view where ";

if(name !- null) s + = " name = '" + name + "'" ;

if(departent != null) s += "and department = '" + department + "' ";

if(designation != null) s += "and designation = '" + designation+ "' ";




You should only be attentive and count correctly "and's" between the clauses.

that would be truly dynamiv query

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CEHJCommented:
Also, don't try to cram all use cases into one implementation : that will  just give you problems. Better to have separate pages for different classes of use case
0
cofactorAuthor Commented:
>>>if you have only three fields,

Yes. I will search by only 3 fields.

>>and perhpas name is mandatory -

ok. I would make this mandatory but user must type at least one character of the Name. I would use a "LIKE" in the query.

>then there would be only four cases -
>not difficult to manage.

How ?

0
for_yanCommented:
Do you have privileges to create view in databse?
0
CEHJCommented:
Write down here your use cases 1 to N
0
for_yanCommented:


create or replace view my_view as
(select n.name, dt.department, d.designation from name_tbl n, department_link dl, department_tbl dt,
designation_tbl d where n.user_sl_no = dl.user_sl_no and dt.department_sl_no = dl.department_sl_no
and n.designation_sl_no = d.designation_sl_no)


after that you have view which has name, designation and department


and then you concatentate the clause part of the query as I showed above. much simpler than
if you have them all in deofferent tables






0
cofactorAuthor Commented:
creating a view and  displaying data from view solved my problem.

Thanks for_yan.  Your solution worked fine.
0
CEHJCommented:
Yes, the view thing is a very good idea
0
for_yanCommented:
Thanks. I'm happy you liked it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.