Mybatis xml 常用sql提取

频繁的重复工作会使人厌烦，也不利于提高工作效率。mybatis xml 文件亦如此，虽说在项目中使用了mybatis plus，但有时一些复杂的sql
却很难表示的清楚，于是我想通过提取公共sql语句片段到一个文件中，用时直接引入即可，以提高工作效率。

配置Mybatis 动态占位符${var:'default'}默认值生效

代码配置

@Primary
@Bean(name = "mysqlSqlSessionFactory")
public SqlSessionFactory mysqlSqlSessionFactory(@Qualifier("mysqlDataSource") DataSource dataSource) throws Exception {
    ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
    MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
    bean.setDataSource(dataSource);
    Properties properties = new Properties();
    properties.setProperty("org.apache.ibatis.parsing.PropertyParser.enable-default-value", "true");
    bean.setConfigurationProperties(properties);
    this.setMybatisPlusConfig(bean);
    bean.setTypeAliasesPackage("com.xx.domain.entity.mysql");
    bean.setMapperLocations(resolveMapperLocations());
    this.setSqlSessionFactoryConfiguration(bean);
    this.setSqlSessionFactoryPlugins(bean);
    return bean.getObject();
}

1、循环遍历查询

foreach.png

<!-- 例子 --> 
<sql id="foreach">
    <bind name="column" value="${column:'id'}"/>
    <choose>
        <when test="${collection:ids} != null and ${collection:ids}.size() > 0">
            and ${alias.:}${column} in
            <foreach collection="${collection:ids}" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </when>
        <otherwise>
            and 1 != 1
        </otherwise>
    </choose>
</sql>

2、时间范围查询

date_range.png

<!-- 例子 -->
<sql id="date_range">
    <bind name="column" value="${column:'create_date'}"/>
    <bind name="startDate" value="${startDate:startDate}"/>
    <bind name="endDate" value="${endDate:endDate}"/>
    <choose>
        <when test="startDate != null and endDate != null">
            and ${alias.:}${column} between #{startDate} and #{endDate}
        </when>
        <otherwise>
            <if test="startDate != null">
                and ${alias.:}${column} >= #{startDate}
            </if>
            <if test="endDate != null">
                and ${alias.:}${column} &lt;= #{endDate}
            </if>
        </otherwise>
    </choose>
</sql>

调用示例

<!--Common 为以上xml的namespace-->
<select id="in" resultMap="BaseResultMap">
    select <include refid="Base_Column_List"/> from t_order_order as o
    <where>
        <include refid="Common.foreach">
            <property name="alias." value="o."/>
        </include>
    </where>
</select>

<select id="range" resultMap="BaseResultMap">
    select <include refid="Base_Column_List"/> from t_order_order
    <where>
        <include refid="Common.date_range"/>
    </where>
</select>

由于作者能力有限，有存在不足的地方，欢迎指正。

参考文章

• Mybatis处理动态占位符实现
• MyBatis choose 和 bind