Dynamic SQL Statements in Mybatis

Posted by guttyguppy on Sun, 18 Aug 2019 11:34:11 +0200

The Foundation of MyBatis Series (10) Dynamic SQL Statements in Mybatis

1. Overview of Dynamic SQL

Referring to the official documents, it is described as follows:

They are:

  • if
  • choose (when, otherwise)
  • trim (where, set)
  • foreach

2. Label of Dynamic SQL

We use different SQL statements to query according to different values of entity classes. For example, if the ID is not empty, it can be queried according to id, and if the username is different, the user name should be added as a condition. This situation is often encountered in our multi-conditional combination queries.

2.1 Persistence Layer Interface

/**
* Query the user list based on user information
* @param user
* @return
*/
List<User> findByUser(User user);

2.2 Persistence Layer Dao Mapping Configuration

<select id="findByUser" resultType="user" parameterType="user">
	select * from user where 1 = 1
	<if test="username != null and username != ''">
		and username like #{username}
	</if>
	<if test="address != null">
		and address like #{address}
	</if>
</select>

Note: The tag's test attribute writes the object's attribute name, and if it's an object of wrapping class, use the OGNL expression. Also note the role of where 1=1

2.3 Test

@Test
public void testFindByUser() {
	User u = new User();
	u.setUsername("%week%");
	u.setAddress("%Gulf Province%");
	//6. Perform operations
	List<User> users = userDao.findByUser(u);
	for(User user : users) {
		System.out.println(user);
	}
}

3. Label of Dynamic SQL

Sometimes we don't want to apply all conditional statements, but just choose one of them. In this case, MyBatis provides the choice element, which is somewhat like the switch statement in Java.

<select id="findByUser" resultType="user">
  select * from user where 1 = 1
  <choose>
    <when test="username != null and username != ''">
      and username like #{username}
    </when>
    <when test="address != null">
      and address like #{address}
    </when>
    <otherwise>
      and sex = 'male'
    </otherwise>
  </choose>
</select>

3. Label of Dynamic SQL

To simplify the assembly of where 1=1 above, we can use the < where > tag to simplify development.

3.1 Modify persistence layer Dao mapping configuration

<!-- Query according to user information -->
<select id="findByUser" resultType="user" parameterType="user">
	select * from user
	<where>
		<if test="username!=null and username != '' ">
			and username like #{username}
		</if>
		<if test="address != null">
			and address like #{address}
		</if>
	</where>
</select>

4. Label of Dynamic Label

Another common operational requirement of dynamic SQL is to traverse a collection, usually when constructing IN conditional statements.

Input multiple IDs to query user information, using the following two sql implementations:

  • SELECT * FROM USERS WHERE username LIKE'% Zhang%'AND (id =10 OR id =89 OR id=16)
  • SELECT * FROM USERS WHERE username LIKE'% Zhang%'AND id IN (10,89,16)

So when we do range query, we need to add values in a set as parameters dynamically.

4.1 Add a List collection to QueryVo to encapsulate parameters

public class QueryVo implements Serializable {
	private List<Integer> ids;
	public List<Integer> getIds() {
		return ids;
	}
	public void setIds(List<Integer> ids) {
		this.ids = ids;
	}
}

4.2 Modify the Persistence Layer Dao Interface

/**
* Query users based on id sets
* @param vo
* @return
*/
List<User> findInIds(QueryVo vo);

4.3 Modify persistence layer Dao mapping configuration

SQL statement: select field from user where id in (?)

<!-- Query all users in id Among the collections of ___________ -->
<select id="findInIds" resultType="user" parameterType="queryvo">
	select * from user
	<where>
		<if test="ids != null and ids.size() > 0">
			<foreach collection="ids" open="id in ( " close=")" item="uid" separator=",">
				#{uid}
			</foreach>
		</if>
	</where>
</select>

Labels are used to traverse collections with properties:

  • collection: Represents the set elements to be traversed. Be careful not to write {} when writing.
  • open: The beginning of a representative statement
  • close: Represents the closing part
  • item: Represents each element of a traversal collection, and the variable name generated
  • sperator: Representation separator

4.4 Writing test methods

@Test
public void testFindInIds() {
	QueryVo vo = new QueryVo();
	List<Integer> ids = new ArrayList<Integer>();
	ids.add(41);
	ids.add(42);
	ids.add(43);
	ids.add(46);
	ids.add(57);
	vo.setIds(ids);
	//6. Perform operations
	List<User> users = userDao.findInIds(vo);
	for(User user : users) {
		System.out.println(user);
	}
}

5. Simplified SQL fragments in Mybatis

sql can extract duplicate sql and use include reference to achieve sql reuse.

5.1 Define Code Fragments

<sql id="defaultSql">
	select * from user
</sql>

5.2 Reference Code Fragment

<!-- Configure all operations of query -->
<select id="findAll" resultType="user">
	<include refid="defaultSql"></include>
</select>
<!-- according to id query -->
<select id="findById" resultType="UsEr" parameterType="int">
	<include refid="defaultSql"></include>
	where id = #{uid}
</select>

Topics: SQL Mybatis Attribute Java

Hot Topics

©2024 Programmer Think