In fact, one of the powerful features of MyBatis is usually its dynamic SQL capabilities. If you have experience using JDBC or other similar frameworks, you understand that it is very tangled to dynamically concatenate SQL strings together, make sure you don't forget the spaces or omit commas at the end of the list. Dynamic SQL in Mybatis can handle this pain thoroughly. For dynamic SQL, the simplest way is to give us various dynamic behavior judgments when hard-coded. In Mybatis, a powerful dynamic SQL language is used to improve this situation, which can be used in any mapping of SQL statements. Dynamic SQL elements are similar to using JSTL or other similar XML-based text processors. MyBatis uses powerful OGNL-based expressions to eliminate other elements.
Several node elements we commonly use include if, choose(when, otherwise), trim(where, if), and foreach. I feel a bit like the usage of XSLT (I will mention it later in the article ~).
(1) Usage of if
In the pagination configuration of the ViisitMapper, if pageIndex>-1 and pageSize>-1, the corresponding pagination SQL will be added, otherwise it will not be added (the default is all taken), as follows:
<select id="getListByPagenate" parameterType="PagenateArgs" resultType="Visitor"> select * from ( <include refid="getListSql" /> <include refid="orderBySql"/> ) t <!-- #{} means parameterized output, ${} means direct output does not perform any escape operations, transfer it yourself-> <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>Because our parameters pageIndex and pageSize are both int values, we can make direct judgments like this. If it is an object instance, we can use null judgment to control some dynamic logic. The specific development depends on business needs. I think it is important to note here that it is not easy to write &&, this will not be recognized in the configuration~.
(2) The usage of choice (when, otherwise)
Choose when mainly used in application scenarios that only meet one of the conditions under multiple conditions. For example, here is a query condition, passing id, name and createTime respectively. Suppose when we query the Visitor table, if VisitorId has a value, use the Id query, and if VisitorName has a value, use the VisitName query. As follows, add the List<Visitor> getListChooseWhenDemo(BasicQueryArgs args) method to the david.mybatis.demo.IVisitorOperation interface class. Add the corresponding select node configuration in VisitorMapper:
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 { /* * Add visitor*/ public int add(Visitor visitor); /* * Delete visitor*/ public int delete(int id); /* * Update visitor*/ public int update(Visitor Visitor); /* * Query visitor*/ public Visitor query(int id); /* * Query List */ public List<Visitor> getList(); /* * Pagination query List */ public List<Visitor> getListByPagenate(PagenateArgs args); /* * Pagination query List (including Rownum) */ public List<VisitorWithRn> getListByPagenateWithRn(PagenateArgs args); /* * Basic query*/ public Visitor basicQuery(int id); /* * Dynamic condition query (choose, when) instance */ public List<Visitor> getListChooseWhenDemo(BasicQueryArgs args); /* * Dynamic condition query (where,if) instance */ public List<Visitor> getListWhereDemo(BasicQueryArgs args); /* * Dynamic query (foreach) instance */ 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 one of the conditions is met--> <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) Usage of where if (trim)
The advantage of where keywords is that if there are corresponding filter conditions, it knows to insert where keywords at appropriate times. And it also knows when to remove the corresponding AND and OR connectors, mainly dealing with the following scenarios
<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>It will not become a result of the failure of all conditions
<select id="findActiveBlogLike" resultType="Blog"> SELECT * FROM BLOG WHERE</select>
Or because the first condition is not met, the subsequent condition is only met.
<select id="findActiveBlogLike" resultType="Blog"> SELECT * FROM BLOG WHERE AND title like 'someTitle'</select>
Therefore, for this we can create a choice when condition example, and add the corresponding method public List<Visitor> getListWhereDemo(BasicQueryArgs args) to the IVisitorOperation interface class, and add the corresponding configuration in the VisitorMapper configuration file as follows:
<?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> <!-- Add operations if the conditions are met--> <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) Usage of foreach
In commonly used dynamic SQL, we have a business scenario where id in a large string of IDs. In this case, we can use foreach without having to work hard to splice the Id string. The same step is to add the corresponding method to the IVisitorOperation interface class public List<Visitor> getListForeachDemo(List<Integer> ids), and then configure the corresponding node element information in the corresponding Mapper file, as follows:
<?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 Loop Conditions--> <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>Finally, you only need to establish the corresponding test method in DemoRun, and the dynamic SQL in Mybatis will be completed. The DemoRun method used for the test below
/* * Dynamic query foreach instance*/ 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); } } /* * Dynamic query where if instance*/ 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("Check no match!"); else { for (Visitor visitor : ls) { System.out.println(visitor); } } } /* * Dynamic query choose when instance*/ 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("Check no match! "); else { for (Visitor visitor : ls) { System.out.println(visitor); } } }
PS: About OGNL
OGNL is the abbreviation of Object-Graph Navigation Language. From a language perspective: it is a powerful expression language used to obtain and set the properties of java objects. It aims to provide a higher abstraction syntax to navigate java object graphs. OGNL has applications in many places, such as:
Binding language as GUI elements (textfield, combobox, etc.) to model objects.
Database table to Swing's TableModel's data source language.
The binding language for web components and background Model objects (WebOGNL, Tapestry, WebWork, WebObjects).
As a more expressive alternative to Jakarata Commons BeanUtils or JSTL expression language.
In addition, there are many things that can be done in Java, which can also be done using OGNL, such as list mapping and selection. For developers, using OGNL, you can use concise syntax to complete navigation of java objects. Generally speaking, navigation of object information is completed through a "path". This "path" can be an attribute to a java bean, an object indexed in a collection, etc., rather than directly using the get or set method.