wood burning stoves 2.0*
The moose likes JDBC and the fly likes A SQL Problem: Help Please Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "A SQL Problem: Help Please" Watch "A SQL Problem: Help Please" New topic
Author

A SQL Problem: Help Please

Mark Lau
Ranch Hand

Joined: Dec 15, 2001
Posts: 120
In my database, there is a Car table, which contains the following columns:
CarID CarMake CarModel CarYear CarMileage CarColor
I want to do a web application where users can search the Car table by entering a keyword such as "Ford".
Usually, we do
SELECT * FROM CAR WHERE CarMake='Ford'
But the problem here is that the user may well enter "Taurus", in which case the SQL query statement should be
SELECT * FROM CAR WHERE CarModel='Taurus'
And he could even enter "1997", in which case the SQL query statement should be
SELECT * FROM CAR WHERE CarYear='1997'
So, how can I have only one SQL query statement to achieve my goal? Thanks a lot.
Gene.
Craig Demyanovich
Ranch Hand

Joined: Sep 25, 2000
Posts: 173
I havne't written any web applications in my work, but I don't think such experience is a requirement to answer your question.
Why not provide three different keyword fields on your web page, one for each criteria by which a user can search the table? You could choose to allow a search based on only one of these fields (your question indicates that this type of search is what you've considered), or you could allow the user to complete all three fields to narrow the search. Choose the one that best meets your current needs, which, I assume, is the first solution.
Have three SQL queries, each one executed from its own method: findByMake(String make), findByModel(String model) and findByYear(int year). Based on the field that contains data, execute the appropriate method. Note that you would want to check for appropriate values for the year, i.e., no negative numbers or numbers greater than the current year + 1, for example. Finally, once you have a working solution, you could refactor the common logic into a method that the above find methods use.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: A SQL Problem: Help Please
 
Similar Threads
How to use the keyword for searching?
Aggregation in Class Diagram
Get data from my DB
generic list
JTextfield