问题描述:

I'm creating a GUI using java to let a user search from a table of cars. There are 7 fields to search from: VIN, brand, model, year, mileage, price, and color. The user can choose any combination from one to all the fields to search for a car.

Depending on which combination the user leaves blank I'm using:

select * from Cars where color = '"+color+"'"

or

select * from Cars where mileage = '"+mileage1+"' and color = '"+color+"'

But given 7 fields there are 128 total combinations. Do I have to implement all possible combinations? Or is there a way that SQL can ignore when the user leaves one or more fields blank?

网友答案:
public class Filter {
    private final Set<String> fields;
    private final Map<String, String> search;

    public Filter(String ... fields) {
        this.fields = new HashSet<>(Arrays.asList(fields));
    }

    public void addSearch(String field, String value) {
        if (field != null && value != null && fields.contains(field)) {
            search.put(field, value);
        }
    }

    public String toSQL() {
        StringBuilder builder = new StringBuilder();
        for (Map.Entry<String, String> entry : search) {
            String field = entry.getKey(), value = entry.getValue();
            if (builder.length() == 0) {
                builder.append("WHERE ");
            } else {
                builder.append("AND ");
            }
            builder.append(field).append(" = '").append(value).append("'")
        }
        return builder.toString();
    }
}

This is a very simple solution which you surely can improve on. Use it like this:

Filter filter = new Filter("color", "mileage" /* and other columns */);
filter.addSearch("color", color);

String sql = "SELECT * FROM Cars " + filter.toSQL();

You should probably check the user input to protect against SQL injection and other possible misuses.

Another more professional solution is "Object Relational Mapping", in Java with JPA. Google "JPA", "Criteria API", "Hibernate" or "ORM".

网友答案:

just contruct your sql statement, when field is filled you add to sql string

    sql = "select * from Cars where"
    if(field1.text.toString().trim().compareTo("") == 0) sql = sql + field1;

etc
Dont forget the "and"
* Edit : never trust user input (sql injection) *

相关阅读:
Top