sql multi-condition query

To implement multi-condition query, when the value is 0, it is unrestricted. How to use sql to achieve it

For example: when querying a certain type of store in a certain area, it is possible that the area is unlimited and the type is Chinese food. When querying, there is no need for where region = value, but only where type = Chinese food. When selecting the area as Beijing, the type is Chinese food. When querying, it is where region = Beijing. AND type=Chinese food.

Then there are many other conditions. It is too cumbersome to use if else to judge on the backend and then use different sql statements to query. I don’t know much about sql, but I think sql should have corresponding solutions. Please give me some advice.

Reply content:

To implement multi-condition query, when the value is 0, it is unrestricted. How to use sql to achieve it

For example: when querying a certain type of store in a certain area, it is possible that the area is unlimited and the type is Chinese food. When querying, there is no need for where region = value, but only where type = Chinese food. When selecting the area as Beijing, the type is Chinese food. When querying, it is where region = Beijing. AND type=Chinese food.

Then there are many other conditions. It is too cumbersome to use if else to judge on the backend and then use different sql statements to query. I don’t know much about sql, but I think sql should have corresponding solutions. Please give me some advice.

You can judge if and then spell in the corresponding where
ex: $sql = select * from xxx where 1=1;

<code>if(类型!=0){
    $sql .= 'and where 类型 = 中餐';
}</code>

Use the query condition as a Map params; and then use a method to splice additional query statements;

<code>public String getWhere(Map<String, Object> params) {
    String str = "";
    
    if(params.size() > 0) {
        boolean and = false;
        for(String param : params.keySet()) {
            Object value = params.get(param);
            if(value == null || value.equals(0)) {
                continue;
            } else {
                if(and) {
                    str = str + " and " + param + " = " + " " + value;
                } else {
                    and = true;
                    str = str + param + " = " + " " + value;
                }
                
            }
        }
    }
    
    if(str != "") { 
        str = " where " + str;
    }
    
    return str;
}</code>

mybatis spells SQL
JPA spells creteria

Link conditions and or

Querying Mysql uses strings to transmit Mysql query commands, so it is completely feasible to modify the query conditions behind where. You can first create a string array to store these commands, and each time the user modifies the conditions, such as checking Address, modify the corresponding value of the array, or after clicking query, check which components in the interface, such as meal category or address are checked, get their values, add them to the array, and then traverse them when starting the query. If it is empty, it will not be processed. If it is not empty, it will be added to the unified condition string, and finally added to the query command string.
string cmd = "select * from table name where unified condition"
Newbie 1 Only, it may not be well thought out, but there shouldn’t be any big problem with this approach