Java的MyBatis框架中对数据库进行动态SQL查询的教程

其实MyBatis具有的一个强大的特性之一通常是它的动态 SQL 能力。 如果你有使用 JDBC 或其他 相似框架的经验,你就明白要动态的串联 SQL 字符串在一起是十分纠结的,确保不能忘了空格或在列表的最后省略逗号。Mybatis中的动态 SQL 可以彻底处理这种痛苦。对于动态SQL,最通俗简单的方法就是我们自己在硬编码的时候赋予各种动态行为的判断,而在Mybatis中,用一种强大的动态 SQL 语 言来改进这种情形,这种语言可以被用在任意映射的 SQL 语句中。动态 SQL 元素和使用 JSTL 或其他相似的基于 XML 的文本处理器相似。MyBatis 采用功能强大的基于 OGNL 的表达式来消除其他元素。
我们常用的几个节点元素有if,choose(when, otherwise),trim(where, if),foreach。真正使用下来我感觉有点像XSLT(文章后面会顺带提一下~)的用法。
(1)if 的用法

在ViisitMapper的分页配置中,如果pageIndex>-1 and pageSize>-1的时候就加入相应的分页SQL,否则就不添加(默认取全部),如下:

<select id="getListByPagenate" parameterType="PagenateArgs"
  resultType="Visitor">
  select * from (
  <include refid="getListSql" /> <include refid="orderBySql"/>
  ) t <!-- #{}表示参数化输出,${}表示直接输出不进行任何转义操作,自己进行转移 -->
  <if test="pageStart>-1 and pageSize>-1">
    limit #{pageStart}, #{pageSize}
  </if>
</select>
<sql id="getListSql">
  select * from Visitor where
  status>0
</sql>
<sql id="orderBySql">
  order by ${orderFieldStr} ${orderDirectionStr}
</sql>

  因为我们的参数pageIndex与pageSize都是int值所以可以这样直接判断,如果是对象实例我们可以利用null判断来进行一些动态逻辑的控制,具体实际开发中就要看业务需求了。这里我认为要注意的是别十分顺手的吧and写成&&,这个在配置中不会被识别~。

(2)choose (when, otherwise)的用法

  choose when 主要在多个条件的情况下只满足其中一个条件的应用场景中使用,例如这里就构建一个query条件,分别传递id,name与createTime。假设我们查询Visitor表时,如果VisitorId有值则,使用Id查询,如果VisitorName有值则采用VisitName查询,如下,还是在david.mybatis.demo.IVisitorOperation接口类中添加List<Visitor> getListChooseWhenDemo(BasicQueryArgs args)方法。在VisitorMapper中添加相应的的select节点配置:

package david.mybatis.demo;

import java.util.List;

import david.mybatis.model.BasicQueryArgs;
import david.mybatis.model.PagenateArgs;
import david.mybatis.model.Visitor;
import david.mybatis.model.VisitorWithRn;

public interface IVisitorOperation {
  /*
   * 添加访问者
   */
  public int add(Visitor visitor);

  /*
   * 删除访问者
   */
  public int delete(int id);

  /*
   * 更新访问者
   */
  public int update(Visitor visitor);

  /*
   * 查询访问者
   */
  public Visitor query(int id);

  /*
   * 查询List
   */
  public List<Visitor> getList();

  /*
   * 分页查询List
   */
  public List<Visitor> getListByPagenate(PagenateArgs args);

  /*
   * 分页查询List(包含Rownum)
   */
  public List<VisitorWithRn> getListByPagenateWithRn(PagenateArgs args);

  /*
   * 基础查询
   */
  public Visitor basicQuery(int id);

  /*
   * 动态条件查询(choose,when)实例
   */
  public List<Visitor> getListChooseWhenDemo(BasicQueryArgs args);

  /*
   * 动态条件查询(where,if)实例
   */
  public List<Visitor> getListWhereDemo(BasicQueryArgs args);

  /*
   * 动态查询(foreach)实例
   */
  public List<Visitor> getListForeachDemo(List<Integer> ids);

}
<?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="david.mybatis.demo.IVisitorOperation">
  <resultMap type="Visitor" id="visitorRs">
    <id column="Id" property="id" />
    <result column="Name" property="name" />
    <result column="Email" property="email" />
    <result column="Status" property="status" />
    <result column="CreateTime" property="createTime" />
  </resultMap>
  <sql id="getListSqlConditions">
    select * from Visitor
  </sql>
  <!-- 满足其中一个条件时候用choose when操作 -->
  <select id="getListChooseWhenDemo" resultMap="visitorRs"
    parameterType="BasicQueryArgs">
    <include refid="getListSqlConditions" />
    <where>
      <if test="queryStatus>0">
        status=#{queryStatus}
      </if>
      <choose>
        <when test="queryId!=0">
          and id=#{queryId}
        </when>
        <when test="queryName!=null">
          and name like #{queryName}
        </when>
        <otherwise>
          and createTime>= #{queryTime}
        </otherwise>
      </choose>
    </where>
  </select>
</mapper>

(3)where if (trim)的用法

where关键词的好处是在于,如果有相应的过滤条件的话,它知道在适当的时候插入where关键词。而且它也知道在何时该去掉相应的AND与OR的连接符,主要应对如下情景

<select id="findActiveBlogLike"
   resultType="Blog">
 SELECT * FROM BLOG
 WHERE
 <if test="state != null">
  state = #{state}
 </if>
 <if test="title != null">
  AND title like #{title}
 </if>
 <if test="author != null and author.name != null">
  AND author_name like #{author.name}
 </if>
</select>

不会因为所有条件不满足变为

<select id="findActiveBlogLike"
   resultType="Blog">
 SELECT * FROM BLOG
 WHERE
</select>

或者因为没有满足第一个条件,单单满足后面的条件变成

<select id="findActiveBlogLike"
   resultType="Blog">
  SELECT * FROM BLOG
  WHERE
  AND title like ‘someTitle'
</select>

所以针对这种我们可以在建立choose when条件示例,同样在IVisitorOperation接口类中加入相应的方法public List<Visitor> getListWhereDemo(BasicQueryArgs args),把VisitorMapper配置文件中的相对应配置加上去如下:

<?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="david.mybatis.demo.IVisitorOperation">
  <sql id="getListSqlConditions">
    select * from Visitor
  </sql>
  <!-- 满足条件的都加上去操作 -->
  <select id="getListWhereDemo" resultMap="visitorRs"
    parameterType="BasicQueryArgs">
    <include refid="getListSqlConditions" />

    <where>
      <if test="queryStatus>0">
        status>0
      </if>
      <if test="queryId>0">
        and id=#{queryId}
      </if>
      <if test="queryName!=null">
        and name like=#{queryName}
      </if>
      <if test="queryTime!=null">
        and createTime>=#{queryTime}
      </if>
    </where>
    <!--
    <trim prefix="WHERE" prefixOverrides="AND |OR ">
      <if test="queryStatus>0">
        status>0
      </if>
      <if test="queryId>0">
        and id=#{queryId}
      </if>
      <if test="queryName!=null">
        and name like=#{queryName}
      </if>
      <if test="queryTime!=null">
        and createTime>=#{queryTime}
      </if>
    </trim>
    -->
  </select>
</mapper>

(4)foreach的用法

在常用的动态SQL中我们有个业务场景是要where id in 一大串的ID,像这种情况我们就可以用到foreach啦,不必自己辛辛苦苦去拼接Id字符串啦。同样的步骤还是在IVisitorOperation接口类中加入相应的方法public List<Visitor> getListForeachDemo(List<Integer> ids),然后再对应的Mapper文件中配置上相应的节点元素信息,如下:

<?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="david.mybatis.demo.IVisitorOperation">
  <sql id="getListSqlConditions">
    select * from Visitor
  </sql>
  <!-- Foreach循环条件 -->
  <select id="getListForeachDemo" resultMap="visitorRs">
    <include refid="getListSqlConditions"/>
    where status>0 and id in
    <foreach collection="list" item="item" index="index" open="(" separator="," close=")">
      ${item}
    </foreach>
  </select>
</mapper>

最后你只需要在DemoRun中建立相应的测试方法,Mybatis里面的动态SQL也就完成啦,下面测试用的DemoRun方法

/*
   * 动态查询foreach实例
   */
  public static void getListForeachDemo(List<Integer> ids) {
    SqlSession session = MybatisUtils.getSqlSession();
    IVisitorOperation vOperation = session.getMapper(IVisitorOperation.class);
    List<Visitor> ls = vOperation.getListForeachDemo(ids);
    for (Visitor visitor : ls) {
      System.out.println(visitor);
    }
  }

  /*
   * 动态查询where if实例
   */
  public static void getListWhereCondition(int id, String name, Date createTime) {
    name = name == "" ? null : name;
    SqlSession session = MybatisUtils.getSqlSession();
    BasicQueryArgs args = new BasicQueryArgs(id, name, createTime);
    IVisitorOperation vOperation = session.getMapper(IVisitorOperation.class);
    List<Visitor> ls = vOperation.getListWhereDemo(args);
    if (ls.size() == 0)
      System.out.println("查无匹配!");
    else {
      for (Visitor visitor : ls) {
        System.out.println(visitor);
      }
    }
  }

  /*
   * 动态查询choose when实例
   */
  public static void getListChooseWhenDemo(int id, String name, Date createTime) {
    name = name == "" ? null : name;
    SqlSession session = MybatisUtils.getSqlSession();
    BasicQueryArgs args = new BasicQueryArgs(id, name, createTime);
    IVisitorOperation vOperation = session.getMapper(IVisitorOperation.class);
    List<Visitor> ls = vOperation.getListChooseWhenDemo(args);
    if (ls.size() == 0)
      System.out.println("查无匹配!");
    else {
      for (Visitor visitor : ls) {
        System.out.println(visitor);
      }
    }
  }

   
PS:关于OGNL
OGNL 是 Object-Graph Navigation Language 的缩写,从语言角度来说:它是一个功能强大的表达式语言,用来获取和设置 java 对象的属性 , 它旨在提供一个更高抽象度语法来对 java 对象图进行导航,OGNL 在许多的地方都有应用,例如:
作为 GUI 元素(textfield,combobox, 等)到模型对象的绑定语言。
数据库表到 Swing 的 TableModel 的数据源语言。
web 组件和后台 Model 对象的绑定语言 (WebOGNL,Tapestry,WebWork,WebObjects) 。
作为 Jakarata Commons BeanUtils 或者 JSTL 的表达式语言的一个更具表达力的替代语言。
另外,java 中很多可以做的事情,也可以使用 OGNL 来完成,例如:列表映射和选择。 对于开发者来说,使用 OGNL,可以用简洁的语法来完成对 java 对象的导航。通常来说: 通过一个“路径”来完成对象信息的导航,这个“路径”可以是到 java bean 的某个属性,或者集合中的某个索引的对象,等等,而不是直接使用 get 或者 set 方法来完成。

时间: 2016-04-03

Mybatis动态SQL之if、choose、where、set、trim、foreach标记实例详解

动态SQL就是动态的生成SQL. if标记 假设有这样一种需求:查询用户,当用户名不等于"admin"的时候,我们还需要密码为123456. 数据库中的数据为: MyBatisConfig.xml <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"

MyBatis实践之动态SQL及关联查询

序言 MyBatis,大家都知道,半自动的ORM框架,原来叫ibatis,后来好像是10年apache软件基金组织把它托管给了goole code,就重新命名了MyBatis,功能相对以前更强大了.它相对全自动的持久层框架Hibernate,更加灵活,更轻量级,这点我还是深有体会的. MyBatis的一个强大特性之一就是动态SQL能力了,能省去我们很多串联判断拼接SQL的痛苦,根据项目而定,在一定的场合下使用,能大大减少程序的代码量和复杂程度,不过还是不是过度太过复杂的使用,以免不利于后期的维护

详解Java的MyBatis框架中动态SQL的基本用法

有些时候,sql语句where条件中,需要一些安全判断,例如按某一条件查询时如果传入的参数是空,此时查询出的结果很可能是空的,也许我们需要参数为空时,是查出全部的信息.使用Oracle的序列.mysql的函数生成Id.这时我们可以使用动态sql.下文均采用mysql语法和函数(例如字符串链接函数CONCAT). selectKey 标签 在insert语句中,在Oracle经常使用序列.在MySQL中使用函数来自动生成插入表的主键,而且需要方法能返回这个生成主键.使用myBatis的select

Mybatis入门教程(四)之mybatis动态sql

推荐阅读: MyBatis入门学习教程(一)-MyBatis快速入门  什么是动态SQL? 动态SQL有什么作用? 传统的使用JDBC的方法,相信大家在组合复杂的的SQL语句的时候,需要去拼接,稍不注意哪怕少了个空格,都会导致错误.Mybatis的动态SQL功能正是为了解决这种问题, 其通过 if, choose, when, otherwise, trim, where, set, foreach标签,可组合成非常灵活的SQL语句,从而提高开发人员的效率. 下面就去感受Mybatis动态SQL

oracle+mybatis 使用动态Sql当插入字段不确定的情况下实现批量insert

最近做项目遇到一个挺纠结的问题,由于业务的关系,DB的数据表无法确定,在使用过程中字段可能会增加,这样在insert时给我造成了很大的困扰. 先来看一下最终我是怎么实现的: <insert id="batchInsertLine" parameterType="HashMap"> <![CDATA[ INSERT INTO tg_fcst_lines(${lineColumn}) select result.*,sq_fcst_lines.next

MyBatis动态SQL标签用法实例详解

1.动态SQL片段 通过SQL片段达到代码复用 <!-- 动态条件分页查询 --> <sql id="sql_count"> select count(*) </sql> <sql id="sql_select"> select * </sql> <sql id="sql_where"> from icp <dynamic prepend="where&quo

mybatis的动态sql详解(精)

MyBatis 的一个强大的特性之一通常是它的动态 SQL 能力.如果你有使用 JDBC 或其他 相似框架的经验,你就明白条件地串联 SQL 字符串在一起是多么的痛苦,确保不能忘了空 格或在列表的最后省略逗号.动态 SQL 可以彻底处理这种痛苦. 通常使用动态SQL不可能是独立的一部分,MyBatis当然使用一种强大的动态SQL语言来改进这种情形,这种语言可以被用在任意映射的SQL语句中. 动态SQL元素和使用 JSTL或其他相似的基于XML的文本处理器相似.在MyBatis之前的版本中,有很多

MyBatis使用动态SQL标签的小陷阱

MyBatis是一个支持普通SQL查询,存储过程和高级映射的优秀持久层框架.MyBatis消除了几乎所有的JDBC代码和参数的手工设置以及对结果集的检索封装.MyBatis可以使用简单的XML或注解用于配置和原始映射,将接口和Java的POJO(Plain Old Java Objects,普通的Java对象)映射成数据库中的记录. 现在MyBatis越来越受大家的喜爱了,它的优势大家都知道,我就不多说了,直接说重点. MyBatis中提供动态SQL功能,我们可以使用<if><when&

Mybatis之动态sql标签的使用

1.Mybatis动态sql MyBatis 的强大特性之一便是它的动态 SQL.如果你有使用 JDBC 或其它类似框架的经验,你就能体会到根据不同条件拼接 SQL 语句的痛苦.例如拼接时要确保不能忘记添加必要的空格,还要注意去掉列表最后一个列名的逗号.利用动态 SQL 这一特性可以彻底摆脱这种痛苦. 虽然在以前使用动态 SQL 并非一件易事,但正是 MyBatis 提供了可以被用在任意 SQL 映射语句中的强大的动态 SQL 语言得以改进这种情形. 动态 SQL 元素和 JSTL 或基于类似

MyBatis 执行动态 SQL语句详解

大家基本上都知道如何使用 MyBatis 执行任意 SQL,使用方法很简单,例如在一个 XXMapper.xml 中: <select id="executeSql" resultType="map"> ${_parameter} </select> 你可以如下调用: sqlSession.selectList("executeSql", "select * from sysuser where enabled

Mybatis中动态SQL,if,where,foreach的使用教程详解

MyBatis的动态SQL是基于OGNL表达式的,它可以帮助我们方便的在SQL语句中实现某些逻辑. MyBatis中用于实现动态SQL的元素主要有: if choose(when,otherwise) trim where set foreach mybatis核心 对sql语句进行灵活操作,通过表达式进行判断,对sql进行灵活拼接.组装. 1.statement中直接定义使用动态SQL: 在statement中利用if 和 where 条件组合达到我们的需求,通过一个例子来说明: 原SQL语句

MyBatis执行动态SQL的方法

大家基本上都知道如何使用 MyBatis 执行任意 SQL,使用方法很简单,例如在一个 XXMapper.xml 中: <select id="executeSql" resultType="map"> ${_parameter} </select> 你可以如下调用: sqlSession.selectList("executeSql", "select * from sysuser where enabled

MyBatis中动态sql的实现方法示例

1. 动态sql 动态sql是mybatis中的一个核心,什么是动态sql? 动态sql即对sql语句进行灵活操作,通过表达式进行判断,对sql进行灵活拼接.组装. MyBatis的强大特性之一便是它的动态 SQL.如果你有使用 JDBC 或其他类似框架的经验,你就能体会到根据不同条件拼接 SQL 语句有多么痛苦.拼接的时候要确保不能忘了必要的空格,还要注意省掉列名列表最后的逗号.有些时候,SQL语句where条件中,需要一些安全判断,例如按某一条件查询时如果传入的参数是空,此时查询出的结果很可

MyBatis的动态SQL语句实现

1. 动态SQL之<if>标签 我们根据实体类的不同取值,使用不同的SQL语句来进行查询.比如在id如果不为空时可以根据id查询,如果username不为空时还要加入用户名作为条件,这种情况在我们的多条件组合查询中经常会碰到. <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN&qu