mysql 复杂的条件语句示例

梦里梦外; 2022-10-24 01:55 160阅读 0赞

## 一个复杂的 mysql 条件语句 ##

### 1. xml ###

<select id="selectByExample" parameterType="com.lenovo.npi.auth.center.domain.ResExample" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List" />
        from ac_res
    
        <if test="_parameter != null">
          <include refid="Example_Where_Clause" />
        </if>
        <if test="orderByClause != null and orderByClause != ''">
          ORDER BY ${@com.lenovo.platform.util.Ognl@replaceOrderByClause(orderByClause)}
        </if>
        <include refid="ibatorgenerated_MysqlLimitSection"></include>
      </select>
    
      ### 1.1 查询列 sql
      <sql id="Base_Column_List">
        id,  rescode,  res_type_code, resuri, parent, ordering,  title,  memo, lastmodified,
        created
      </sql>
      
      ### 1.2 分页sql
      <sql id="ibatorgenerated_MysqlLimitSection">
        <if test="page != null">
          limit #{page.pageSize} offset #{page.begin}
        </if>
      </sql>
    
      ### 1.3 条件对象解析 sql
      <sql id="Update_By_Example_Where_Clause">
        <where>
          <foreach collection="example.oredCriteria" item="criteria" separator="or">
            <if test="criteria.valid">
              <trim prefix="(" prefixOverrides="and" suffix=")">
                <foreach collection="criteria.criteria" item="criterion">
                  <choose>
                    <when test="criterion.noValue">
                      and ${criterion.condition}
                    </when>
                    <when test="criterion.singleValue">
                      and ${criterion.condition} #{criterion.value}
                    </when>
                    <when test="criterion.betweenValue">
                      and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
                    </when>
                    <when test="criterion.listValue">
                      and ${criterion.condition}
                      <foreach close=")" collection="criterion.value" item="listItem" open="(" separator=",">
                        #{listItem}
                      </foreach>
                    </when>
                  </choose>
                </foreach>
              </trim>
            </if>
          </foreach>
        </where>
      </sql>

### 2. 条件对象java ###

getter/setter 略

#### 2.1 ResExample ####

import com.lenovo.platform.common.vo.Page;
    
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.List;
    
    public class ResExample { 
    
        /** * 查询关键字 */
        private String queryKey;
        /** * 排序语句 */
        protected String orderByClause;
    
        protected boolean distinct;
    
        /** * 动态查询字段 */
        protected List<Criteria> oredCriteria;
    
        /** * 分页参数 */
        protected Page page;
    
        public ResExample() { 
            oredCriteria = new ArrayList<Criteria>();
        }
    }

#### 2.2 Criteria ####

对 输入条件的动态拼接，考虑每个参数涉及到的各种情况，下面以id举例：

public Criteria andIdIsNull() { 
                addCriterion("id is null");
                return (Criteria) this;
            }
    
            public Criteria andIdIsNotNull() { 
                addCriterion("id is not null");
                return (Criteria) this;
            }
    
            public Criteria andIdEqualTo(Integer value) { 
                addCriterion("id =", value, "id");
                return (Criteria) this;
            }
    
            public Criteria andIdNotEqualTo(Integer value) { 
                addCriterion("id <>", value, "id");
                return (Criteria) this;
            }
    
            public Criteria andIdGreaterThan(Integer value) { 
                addCriterion("id >", value, "id");
                return (Criteria) this;
            }
    
            public Criteria andIdGreaterThanOrEqualTo(Integer value) { 
                addCriterion("id >=", value, "id");
                return (Criteria) this;
            }
    
            public Criteria andIdLessThan(Integer value) { 
                addCriterion("id <", value, "id");
                return (Criteria) this;
            }
    
            public Criteria andIdLessThanOrEqualTo(Integer value) { 
                addCriterion("id <=", value, "id");
                return (Criteria) this;
            }
    
            public Criteria andIdIn(List<Integer> values) { 
                addCriterion("id in", values, "id");
                return (Criteria) this;
            }
    
            public Criteria andIdNotIn(List<Integer> values) { 
                addCriterion("id not in", values, "id");
                return (Criteria) this;
            }
    
            public Criteria andIdBetween(Integer value1, Integer value2) { 
                addCriterion("id between", value1, value2, "id");
                return (Criteria) this;
            }
    
            public Criteria andIdNotBetween(Integer value1, Integer value2) { 
                addCriterion("id not between", value1, value2, "id");
                return (Criteria) this;
            }

#### 2.3 page ####

public class Page { 
        private static long maxPageSize = 1000;
    
        // 分页查询开始记录位置(Include)*
        private long begin;
        // 分页查看下结束位置(Exclude)
        private long end;
        // 每页显示记录数*
        private long pageSize = 0;
        // 查询结果总记录数
        private long rowTotal = 0;
        // 当前页码*
        private long current = 1;
        // 总共页数
        private long pageTotal = 0;
    
        public Page() { 
        }
     }

### 3. Ognl 对象java ###

xml 中通过下面方式使用外部方法

ORDER BY ${@com.lenovo.platform.util.Ognl@replaceOrderByClause(orderByClause)}

外部方法

import org.apache.commons.lang.StringEscapeUtils;
    import org.apache.commons.lang3.ArrayUtils;
    import org.apache.commons.lang3.StringUtils;
    
    import java.util.Arrays;
    import java.util.stream.Collectors;
    
    public class Ognl { 
        private static final String ORDER_BY_ASC = "ASC";
        private static final String ORDER_BY_DESC = "DESC";
        private static final String REGEX_ORDER_BY = "^[a-zA-z0-9_]+$";
    
        /** * 解析排序字符串 * * @param orderByClause : "field0,field1 asc,field2 des,..." * @return ："field0,field1 asc,field2 des,..." */
        public static String replaceOrderByClause(String orderByClause){ 
            String s = StringEscapeUtils.escapeSql(orderByClause);
            String[] clauses = s.split(",");
            if (ArrayUtils.isEmpty(clauses)) { 
                return null;
            }
    
            return Arrays.stream(clauses).map(c -> { 
                String[] o = c.split(" ");
                if (ArrayUtils.isEmpty(o)) { 
                    return null;
                }
                //check field
                if (!o[0].matches(REGEX_ORDER_BY)) { 
                    return null;
                }
                //check sort
                if (o.length == 2) { 
                    if (StringUtils.equalsIgnoreCase(ORDER_BY_ASC, o[1])
                            || StringUtils.equalsIgnoreCase(ORDER_BY_DESC, o[1])) { 
                        return c;
                    }
                }
                return o[0];
            }).filter(x -> StringUtils.isNotBlank(x))
                    .collect(Collectors.joining(","));
        }
    }