• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Devaka Cooray
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Knute Snortum
  • Bear Bibeault
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • Piet Souris
  • Ganesh Patekar
Bartenders:
  • Frits Walraven
  • Carey Brown
  • Tim Holloway

How should the query be for multiple criteria ?

 
Ranch Hand
Posts: 518
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am now trying to use a servlet to query a joined table where there is tutor id and subjectNames.

However, I do not know if my mind is overworked or what I just can't quite get the idea how to write the right query.

So, a user will search thru a list of subjects and then my aim is to obtain the tutor_id with the subjectNames the user want.

So, I thought of using HashMap<String, ArrayList<String>>tutorSubject{

And then I create the query

String[] subjectNames = getParameterValues



I wonder if I am in the right direction.

My mind is now very blank and eyes very pain.

And if I am going to add in more criteria like the location, how should I do it, if I have a table with the tutor details including the location.

I am like very lost now.

Hope to get some tips and helps here.

Many thanks.
 
Sheriff
Posts: 6029
157
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I would think you're trying to build an SQL query with a WHERE clause something like this:

But you need to change the "something" and "somethingElse" into the indices of your array subjectNames, so

But this has two problems: you does know how long the array subjectNames is, and building an SQL query like this (concatenating strings) leaves you open to SQL injection attacks.  So I think you need to build the WHERE clause something like this:

Then using a prepared statement, add the subjectNames indices to the statement.

Does this make sense to you?  I'll try to add code in another reply if I have time.
 
Rancher
Posts: 4116
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Wouldn't that be:
subjectName = ? OR subjectName = ? etc

Which would be better done as:
subjectName IN (?,?,? etc)

Though you would need to build that (?,?,? etc) up based on the number of things in the list.
 
tangara goh
Ranch Hand
Posts: 518
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:Wouldn't that be:
subjectName = ? OR subjectName = ? etc

Which would be better done as:
subjectName IN (?,?,? etc)

Though you would need to build that (?,?,? etc) up based on the number of things in the list.



I can’t do that since each time the subjectNames will change. Right?
 
Dave Tolls
Rancher
Posts: 4116
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why should the subjectNames changing be a problem?

I expect the numbero f subjects to vary, but that's why I said that you would have to build up the set of '?'s in the query:

 
Knute Snortum
Sheriff
Posts: 6029
157
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:Wouldn't that be:
subjectName = ? OR subjectName = ? etc


You're quite right.
 
tangara goh
Ranch Hand
Posts: 518
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:Why should the subjectNames changing be a problem?

I expect the numbero f subjects to vary, but that's why I said that you would have to build up the set of '?'s in the query:



OK. Dave.  You are right.

But, my problem now is that after getting the resultSets out I need to further 'filter' to say find the tutor address so I may need to use inner join.

In this case, do I make 2 separate sql or is it possible to combine everything into 1 sql ?
 
Knute Snortum
Sheriff
Posts: 6029
157
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You should be able to do it all in one query, but it could get complex.  It would look something like this:
(I believe I have my ANDs and ORs correct this time.)
 
Dave Tolls
Rancher
Posts: 4116
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Try and do it as one query.
In general it's best to get the database to do the filtering as that's the sort of thing it's designed to do.
It's likely to be better at it than code you would write.

Not that finding the tutor address is filtering.
As you said, it's joining.
 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
SELECT tutor_id FROM tutor_subject WHERE subjectName =' " + subjectparameter+"'";
 
tangara goh
Ranch Hand
Posts: 518
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Sagar Jaybhay wrote:SELECT tutor_id FROM tutor_subject WHERE subjectName =' " + subjectparameter+"'";



That's not what I am looking for.

Why you are not using IN clause ?
 
Knute Snortum
Sheriff
Posts: 6029
157
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Sagar Jaybhay wrote:SELECT tutor_id FROM tutor_subject WHERE subjectName =' " + subjectparameter+"'";


Concatenating strings to create a query like that is a bad idea in general.  It leaves you open to SQL injection attacks.  Use a prepared statement instead.
 
Consider Paul's rocket mass heater.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!