MyBatis-Kits

条件构造器

强大的条件构造器是 Mybatis-Kits 的核心功能，有了它，你几乎可以实现单表查询的所有功能，而不用去创建 XML 文件。

条件构造相关说明

  • Conditions : 条件查询主类，查询条件封装实体，封装了部分 Restrictions 类的便捷操作。
  • Restrictions : 封装条件表达式的类，组合查询条件的主要功能都是在这个类中实现。
  • Conjunction : SQL 条件连接符, AND 和 OR。

Restrictions

Restrictions 是条件构造的主要工具类，封装了所有的条件查询构造工具方法。

Conditions 类中封装了 Restrictions 的一些常用方法。包括：

eq, ne, like, gt, lt, le, ge, between, in, isNull, isNotNull, sqlRestriction, conjunction, disjunction

eq

Restrictions.eq("name", "rock")
// name = 'rock'

ne

Restrictions.ne("name", "rock")
// name <> 'rock'

like

Restrictions.like("name", "rock", MatchMode.EXACT);
// name like 'rock'
Restrictions.like("name", "rock", MatchMode.START);
// name like '%rock'
Restrictions.like("name", "rock", MatchMode.END);
// name like 'rock%'
Restrictions.like("name", "rock", MatchMode.ANYWHERE);
// name like '%rock%'

gt && lt

Restrictions.gt("id", 100);
// id > 100
Restrictions.lt("id", 200);
// id < 200

ge && le

Restrictions.ge("id", 100);
// id >= 100
Restrictions.le("id", 200);
// id <= 200

between

Restrictions.between("age",20,30)
// age between 20 and 30

in

// 两种调用方式
in(String propertyName, Object[] values);
in(String propertyName, Collection values);

Integer[] ages = {10, 20};
Restrictions.in("age", ages);
// age in ( 10 , 20 )

isNull && isNotNull

Restrictions.isNull("name");
// name is null
Restrictions.isNotNull("name");
// name is not null

and && or

Restrictions.and(Restrictions.eq("age",18), Restrictions.eq("name","rock");
// age = 18 and name = 'rock'
Restrictions.or(Restrictions.eq("age",18), Restrictions.eq("age",29));
// age = 18 or age = 29

sqlRestriction

自定义 SQL 查询条件

Restrictions.sqlRestriction("LENGTH(name) > ", 10);
// LENGTH(name) > 10

conjunction && disjunction

conjunction 是 SQL 连接符，主要包括三种 and, or, ().

conditions.add(Restrictions.conjunction(Restrictions.eq("age",1), Restrictions.eq("age", 2)));
conditions.add(Restrictions.disjunction(Restrictions.gt("id",100), Restrictions.lt("id", 200)));
// ((age=1 and age=2 ) and (id>100 or id<200 ))
conditions.add(Restrictions.conjunction());
conditions.add(Restrictions.eq("name","rock"));
// (1=1 and name='rock')

测试 Sample

Conditions conditions = new Conditions();
conditions.add(Restrictions.and(Restrictions.eq("age",18),Restrictions.eq("name","zhangsan")));
conditions.add(Restrictions.or(Restrictions.eq("count",18),Restrictions.eq("count",29), Restrictions.eq("name","rock")));
// output: ((age=18 AND name='zhangsan') AND (count=18 OR count=29 OR name='rock'))

总结

Mybatis-Kits 的条件构造器已经可以满足你的绝大多数的单表查询需求，如果你需要增加联合查询功能，只需增加相应的 UserMapper.xml， 在里面实现就好了。 完全兼容原生 MyBatis 的所有功能。

<?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="org.rockyang.mybatis.boot.demo.mapper.UserMapper">

    <resultMap id="baseResult" type="org.rockyang.mybatis.boot.demo.model.User" />
    
    <select id="getAmountSum" parameterType="date" resultType="decimal">
      SELECT SUM(age) as age FROM user WHERE update_time &gt;= #{date};
    </select>
    
	<!-- 安全增减用户的余额，实现原子性操作 -->
    <update id="mathOpt" parameterType="org.rockyang.mybatis.plus.support.MathOptVo">
        update user
        <set>
            <if test="opt == 'add'">
                ${field} = ${field} + #{offset} WHERE id = #{id}
            </if>
            <if test="opt == 'subtract'">
                ${field} = ${field} - #{offset} WHERE id = #{id} AND ${field} > #{offset}
            </if>
            <if test="opt == 'multiply'">
                ${field} = ${field} * #{offset} WHERE id = #{id} AND ${offset} != 0
            </if>
            <if test="opt == 'divide'">
                ${field} = ${field} / #{offset} WHERE id = #{id} AND ${offset} != 0
            </if>
        </set>
    </update>
</mapper>

← 分页插件 →