mybatis实现动态语句查询

前几天和朋友忽然聊到怎么可以动态查询sql,也就是不管传递过来多少字段,以及字段对应的值,都可以使用这种通用的查询方法;也许很多博客上面都有这种查询的方式,其实我也是参照了其他人的博客之后,使用springboot+ssm自己创建了一个Demo;

1.首先看一下目录结构:

目录结构

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2.创建相应的封装参数的类:

package com.yqwoe.codegenerator.model.param;

import java.io.Serializable;

public class Item implements Serializable {
    final String IN = "in";
    String fieldName;
    String value;
    String type;
    String[] values = null;

    public Item() {
    }

    public Item(String fieldName, String value, String type) {
        this.fieldName = fieldName;
        this.value = value;
        this.type = type;
    }

    public void setFieldName(String fieldName) {
        this.fieldName = fieldName;
    }

    public void setValue(String value) {
        this.value = value;
    }

    public void setType(String type) {
        this.type = type;
    }

    public String getFieldName() {
        return fieldName;

    }

    public String getValue() {
        return value;
    }

    public String getType() {
        return type;
    }

    public String[] getValues() {
        if (IN.equals(this.type)) {
            return value.split(",");
        }
        return values;
    }
}

package com.yqwoe.codegenerator.model.param;

public class Order {
    String fieldName;
    String type="asc";

    public String getFieldName() {
        return fieldName;
    }

    public void setFieldName(String fieldName) {
        this.fieldName = fieldName;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public Order() {
    }

    public Order(String fieldName, String type) {
        this.fieldName = fieldName;
        this.type = type;
    }
}

package com.yqwoe.codegenerator.model.param;

import java.io.Serializable;
import java.util.List;

public class QueryParam implements Serializable {

    List<Item> items = null;
    List<Order> orders = null;

    int pageNum = 1;
    int pageSize = 10;


    public QueryParam() {
    }

    public List<Item> getItems() {
        return items;
    }

    public void setItems(List<Item> items) {
        this.items = items;
    }

    public List<Order> getOrders() {
        return orders;
    }

    public void setOrders(List<Order> orders) {
        this.orders = orders;
    }

    public int getPageNum() {
        return pageNum;
    }

    public void setPageNum(int pageNum) {
        this.pageNum = pageNum;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="BaseMapper">
    <sql id="BaseQuery">
        <if test="items != null ">
            where 1 = 1
            <foreach collection="items" item="item" index="index"
                     separator=" ">
                <if test="item.type == 'eq' ">
                    AND ${item.fieldName} = #{item.value}
                </if>
                <if test="item.type == 'lt' ">
                    <![CDATA[  AND ${item.fieldName} < #{item.value} ]]>
                </if>
                <if test="item.type == 'lte' ">
                    <![CDATA[ AND ${item.fieldName} <= #{item.value} ]]>
                </if>
                <if test="item.type == 'gt' ">
                    <![CDATA[  AND ${item.fieldName} > #{item.value} ]]>
                </if>
                <if test="item.type == 'gte' ">
                    <![CDATA[  AND ${item.fieldName} >= #{item.value} ]]>
                </if>
                <if test="item.type == 'slike' ">
                    <![CDATA[  AND ${item.fieldName} like '${item.value}%' ]]>
                </if>
                <if test="item.type == 'elike' ">
                    <![CDATA[  AND ${item.fieldName} like '%${item.value}' ]]>
                </if>
                <if test="item.type == 'like' ">
                    <![CDATA[  AND ${item.fieldName} like '%${item.value}%' ]]>
                </if>
                <if test="item.type == 'in' ">
                    AND ${item.fieldName} in
                    <foreach collection="item.values" item="value" index="index" open="(" close=")" separator=",">
                        #{value}
                    </foreach>
                </if>
            </foreach>
        </if>
    </sql>
    <sql id="BaseOrder">
        <if test="orders != null ">
            ORDER BY 
            <foreach collection="orders" item="order" index="index" separator=",">
               ${order.fieldName} ${order.type}
            </foreach>
        </if>
    </sql>
</mapper>

<select id="selectAll" resultMap="BaseResultMap" parameterType="com.yqwoe.codegenerator.model.param.QueryParam">
    select
    <include refid="Base_Column_List" />
    from s_user
    <include refid="BaseMapper.BaseQuery" />
    <include refid="BaseMapper.BaseOrder" />
  </select>

好了,现在的代码都给大家看了,so,GG现在要下班了,明天再给你们写上注释!!!!!!!!!