MyBatis框架详解
什么是MyBatis
MyBatis是一款优秀的持久层框架，支持自定义SQL、存储过程和高级映射。
快速入门
实体类
public class User {
private Long id;
private String name;
private String email;
private Integer age;
public User() {}
public User(String name, String email, Integer age) {
this.name = name;
this.email = email;
this.age = age;
}
public Long getId() { return id; }
public void setId(Long id) { this.id = id; }
public String getName() { return name; }
public void setName(String name) { this.name = name; }
public String getEmail() { return email; }
public void setEmail(String email) { this.email = email; }
public Integer getAge() { return age; }
public void setAge(Integer age) { this.age = age; }
}
Mapper接口
import org.apache.ibatis.annotations.*;
import java.util.List;
@Mapper
public interface UserMapper {
@Select("SELECT * FROM users WHERE id = #{id}")
User findById(Long id);
@Select("SELECT * FROM users")
List<User> findAll();
@Insert("INSERT INTO users (name, email, age) VALUES (#{name}, #{email}, #{age})")
@Options(useGeneratedKeys = true, keyProperty = "id")
int insert(User user);
@Update("UPDATE users SET name = #{name}, email = #{email} WHERE id = #{id}")
int update(User user);
@Delete("DELETE FROM users WHERE id = #{id}")
int deleteById(Long id);
}
XML映射文件
<?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="com.example.mapper.UserMapper">
<resultMap id="UserResult" type="User">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="email" column="email"/>
<result property="age" column="age"/>
</resultMap>
<select id="findById" parameterType="Long" resultMap="UserResult">
SELECT * FROM users WHERE id = #{id}
</select>
<select id="findAll" resultMap="UserResult">
SELECT * FROM users
</select>
<insert id="insert" parameterType="User" useGeneratedKeys="true" keyProperty="id">
INSERT INTO users (name, email, age) VALUES (#{name}, #{email}, #{age})
</insert>
<update id="update" parameterType="User">
UPDATE users SET name = #{name}, email = #{email} WHERE id = #{id}
</update>
<delete id="deleteById" parameterType="Long">
DELETE FROM users WHERE id = #{id}
</delete>
</mapper>
动态SQL
<mapper namespace="com.example.mapper.UserMapper">
<select id="findByCondition" parameterType="User" resultMap="UserResult">
SELECT * FROM users
<where>
<if test="name != null and name != ''">
AND name LIKE CONCAT('%', #{name}, '%')
</if>
<if test="age != null">
AND age = #{age}
</if>
<if test="email != null and email != ''">
AND email = #{email}
</if>
</where>
</select>
<insert id="batchInsert" parameterType="list">
INSERT INTO users (name, email, age) VALUES
<foreach collection="list" item="user" separator=",">
(#{user.name}, #{user.email}, #{user.age})
</foreach>
</insert>
<select id="chooseFind" parameterType="User" resultMap="UserResult">
SELECT * FROM users
<where>
<choose>
<when test="name != null">
AND name = #{name}
</when>
<when test="age != null">
AND age = #{age}
</when>
<otherwise>
AND id > 0
</otherwise>
</choose>
</where>
</select>
</mapper>
多表关联
<resultMap id="UserWithOrders" type="User">
<id property="id" column="user_id"/>
<result property="name" column="user_name"/>
<collection property="orders" ofType="Order">
<id property="id" column="order_id"/>
<result property="orderNo" column="order_no"/>
<result property="amount" column="amount"/>
</collection>
</resultMap>
<select id="findUserWithOrders" resultMap="UserWithOrders">
SELECT u.id as user_id, u.name as user_name,
o.id as order_id, o.order_no, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.id = #{id}
</select>
分页查询
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
@Mapper
public interface UserMapper {
List<User> findAll();
}
@Service
public class UserService {
private final UserMapper userMapper;
public PageInfo<User> findPage(int pageNum, int pageSize) {
PageHelper.startPage(pageNum, pageSize);
List<User> users = userMapper.findAll();
return new PageInfo<>(users);
}
}
插件
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.plugin.*;
import java.sql.Connection;
import java.util.Properties;
@Intercepts({
@Signature(type = StatementHandler.class, method = "prepare",
args = {Connection.class, Integer.class})
})
public class SlowSqlPlugin implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
long start = System.currentTimeMillis();
Object result = invocation.proceed();
long elapsed = System.currentTimeMillis() - start;
if (elapsed > 1000) {
System.out.println("慢SQL: " + elapsed + "ms");
}
return result;
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {}
}
MyBatis配置
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<typeAliases>
<package name="com.example.entity"/>
</typeAliases>
<plugins>
<plugin interceptor="com.example.plugin.SlowSqlPlugin"/>
</plugins>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mydb"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<mappers>
<package name="com.example.mapper"/>
</mappers>
</configuration>
MyBatis最佳实践
- 使用注解简化简单SQL
- 复杂SQL使用XML映射文件
- 合理使用动态SQL避免空条件
- 使用分页插件实现分页
- 缓存查询结果提高性能
总结
MyBatis提供了灵活的SQL映射能力。掌握MyBatis的核心特性能帮助你高效地进行数据库操作。