The moose likes JDBC and Relational Databases 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
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "A SQL Problem: Help Please" Watch "A SQL Problem: Help Please" New topic

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
But the problem here is that the user may well enter "Taurus", in which case the SQL query statement should be
And he could even enter "1997", in which case the SQL query statement should be
So, how can I have only one SQL query statement to achieve my goal? Thanks a lot.
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.
I agree. Here's the link:
subject: A SQL Problem: Help Please
It's not a secret anymore!