1. Batch operation optimization
Batch operations can significantly improve the processing efficiency of the database, MyBatis passes<foreach>Tags support batch insertion, update and delete.
Batch Insert：
<insert parameterType="">
INSERT INTO user (username, email, create_time) VALUES
<foreach collection="list" item="item" separator=",">
(#{}, #{}, #{})
</foreach>
</insert>This example shows how to insert user data in batches. pass<foreach>Iterate through the incoming user list and insert the information of each user into the database.
Batch updates：
<update parameterType="">
<foreach collection="list" item="item" separator=";">
UPDATE user
SET username = #{}, email = #{}
WHERE id = #{}
</foreach>
</update>This example demonstrates how to batch update user information. For each user, update the username and email address according to their ID.
Batch Delete：
<delete parameterType="">
DELETE FROM user WHERE id IN
<foreach collection="list" item="id" open="(" separator="," close=")">
#{id}
</foreach>
</delete>This example shows how to delete users in batches. pass<foreach>Generate an ID list and delete the users corresponding to these IDs.
2. Dynamic SQL
Dynamic SQL allows dynamic construction of SQL statements based on conditions, MyBatis passes<if>Tags implement this function.
Dynamic query：
<select resultType="User">
SELECT * FROM user
WHERE 1=1
<if test="username != null and username != ''">
AND username LIKE CONCAT('%', #{username}, '%')
</if>
<if test="email != null and email != ''">
AND email = #{email}
</if>
<if test="status != null">
AND status = #{status}
</if>
</select>This example shows how to dynamically build a query statement based on the passed in conditions. If a condition is empty, the corresponding query part will not be added to SQL.
3. Multi-condition branch query
For more complex query logic, MyBatis provides<choose>、<when>and<otherwise>Label.
Multi-condition branch query：
<select resultType="User">
SELECT * FROM user
WHERE 1=1
<choose>
<when test="searchType == 'username'">
AND username LIKE CONCAT('%', #{keyword}, '%')
</when>
<when test="searchType == 'email'">
AND email LIKE CONCAT('%', #{keyword}, '%')
</when>
<otherwise>
AND (username LIKE CONCAT('%', #{keyword}, '%') OR email LIKE CONCAT('%', #{keyword}, '%'))
</otherwise>
</choose>
</select>This example shows how to select different query criteria based on different search types. If no search type is specified, the username and email address are searched by default.
4. SQL statement optimization
use<trim>Tags can optimize generated SQL statements to avoid redundantANDorORKeywords.
SQL statement optimization：
<select resultType="User">
SELECT * FROM user
<trim prefix="WHERE" prefixOverrides="AND |OR ">
<if test="username != null and username != ''">
AND username LIKE CONCAT('%', #{username}, '%')
</if>
<if test="email != null and email != ''">
AND email = #{email}
</if>
<if test="status != null">
AND status = #{status}
</if>
</trim>
</select>This example shows how to use<trim>Tags to remove excessANDorOR, and add it when there is a query conditionWHEREKeywords.
5. Automatically generate primary keys
In the insertion operation, it is often necessary to obtain the primary key automatically generated by the database. MyBatis provides<selectKey>Tags anduseGeneratedKeysProperties to implement this function.
Automatically generate primary keys：
<insert parameterType="User" useGeneratedKeys="true" keyProperty="id">
INSERT INTO user (username, email, create_time)
VALUES (#{username}, #{email}, #{createTime})
</insert>(Note: here<selectKey>Labels are not usually required becauseuseGeneratedKeys=trueandkeyPropertyIt's enough for MyBatis to automatically handle primary key generation. )
This example shows how to automatically get the generated primary key after inserting the user and assign it to the id attribute of the incoming User object.
6. Use MyBatis in annotation method
In addition to XML configuration, MyBatis also supports the use of annotations to define SQL operations, making the code more concise.
Annotation method example：
public interface UserMapper {
@Select("SELECT * FROM user WHERE id = #{id}")
User getUserById(Long id);
@Insert("INSERT INTO user (username, email, create_time) VALUES (#{username}, #{email}, #{createTime})")
@Options(useGeneratedKeys = true, keyProperty = "id")
int insertUser(User user);
@Update("UPDATE user SET username = #{username}, email = #{email} WHERE id = #{id}")
int updateUser(User user);
@Delete("DELETE FROM user WHERE id = #{id}")
int deleteUser(Long id);
}This example shows how to use annotations to define simple CRUD operations. For complex SQL statements, it is still recommended to use XML configuration.
7. Advanced Mapping
MyBatis provides powerful object relationship mapping capabilities that can handle complex table relationships.
One-to-many mapping：
<resultMap type="User">
<id property="id" column="user_id"/>
<result property="username" column="username"/>
<collection property="orders" ofType="Order">
<id property="id" column="order_id"/>
<result property="orderNumber" column="order_number"/>
<result property="createTime" column="order_create_time"/>
</collection>
</resultMap>
<select resultMap="userWithOrdersMap">
SELECT as user_id, , as order_id, o.order_number, o.create_time as order_create_time
FROM user u
LEFT JOIN orders o ON = o.user_id
WHERE = #{userId}
</select>This example shows how to map user and order information into a complex object structure to implement one-to-many mapping.
8. MyBatis-Plus integration
MyBatis-Plus is an enhancement tool for MyBatis, providing convenient CRUD operation and powerful condition constructor.
MyBatis-Plus usage example：
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
public List<User> findUsersByCondition(String username, String email) {
return (new QueryWrapper<User>()
.like((username), "username", username)
.eq((email), "email", email));
}
}This is the end of this article about the implementation of several methods of general SQL writing in MyBatis. For more related content on general SQL writing in MyBatis, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!