MyBatis高级特性详解
MyBatis会话管理工具
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
public class DatabaseConnectionManager {
private static SqlSessionFactory sqlSessionFactory;
static {
try {
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
sqlSessionFactory = builder.build(Resources.getResourceAsStream("mybatis-config.xml"));
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSession openSession() {
return sqlSessionFactory.openSession(true);
}
public static SqlSession openSession(boolean autoCommit) {
return sqlSessionFactory.openSession(autoCommit);
}
public static void commitAndClose(SqlSession session) {
if (session != null) {
session.commit();
session.close();
}
}
public static void rollbackAndClose(SqlSession session) {
if (session != null) {
session.rollback();
session.close();
}
}
}参数传递机制
| 输入参数类型 | 输出参数类型 |
|---|---|
| HashMap | HashMap |
| String、Integer | String、Integer |
| POJO | POJO |
单参数传递
// 接口方法定义
User findUserById(Integer userId);<select id="findUserById" resultType="user">
SELECT * FROM user WHERE id = #{userId}
</select>多参数传递
方式一：使用@Param注解（推荐）
User findByUsernameAndGender(@Param("username") String username,
@Param("gender") String gender);<select id="findByUsernameAndGender" resultType="user">
SELECT * FROM user WHERE user_name = #{username} AND sex = #{gender}
</select>方式二：使用POJO对象
void insertUser(User user);<insert id="insertUser">
INSERT INTO user VALUES (null, #{username}, #{birthday}, #{sex}, #{address})
</insert>方式三：使用Map集合
List<User> findByConditions(Map<String, String> conditions);<select id="findByConditions" resultType="user">
SELECT * FROM user WHERE sex = #{sex} AND address = #{address}
</select>主键自动生成
使用insert标签的属性获取自动生成的主键值：
| 属性 | 说明 |
|---|---|
| useGeneratedKeys | 设置为true以获取自动生成的主键 |
| keyColumn | 数据库表中主键列名 |
| keyProperty | 实体类中主键属性名 |
<insert id="addUser" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
INSERT INTO user VALUES (null, #{username}, #{birthday}, #{sex}, #{address})
</insert>参数绑定方式比较
#{}与${}的区别：
- #{}采用预编译方式，可以防止SQL注入
- ${}直接拼接SQL语句，存在SQL注入风险
- ${}通常用于动态表名、列名等场景
结果映射
MyBatis提供两种结果映射方式：
resultType
- 适用于简单类型（int、String等）
- 适用于POJO对象
- 适用于List集合
- 适用于Map对象
resultMap
解决数据库字段与实体类属性不一致问题，支持复杂映射：
<resultMap id="userResultMap" type="user">
<id column="id" property="id"/>
<result column="user_name" property="username"/>
</resultMap>
<select id="getUserById" resultMap="userResultMap">
SELECT * FROM user WHERE id = #{id}
</select>动态SQL
if标签
List<User> findMaleUsers(@Param("username") String username);<select id="findMaleUsers" resultType="user">
SELECT * FROM user WHERE sex = '男'
<if test="username != null and username.trim() != ''">
AND user_name LIKE CONCAT('%', #{username}, '%')
</if>
</select>choose、when、otherwise标签
List<User> searchUsers(@Param("username") String username,
@Param("address") String address);<select id="searchUsers" resultType="user">
SELECT * FROM user WHERE sex = '男'
<choose>
<when test="username != null and username.trim() != ''">
AND user_name LIKE CONCAT('%', #{username}, '%')
</when>
<when test="address != null and address.trim() != ''">
AND address = #{address}
</when>
<otherwise>
AND user_name = '孙悟空'
</otherwise>
</choose>
</select>where标签
List<User> queryByConditions(@Param("username") String username,
@Param("address") String address);<select id="queryByConditions" resultType="user">
SELECT * FROM user
<where>
<if test="username != null and username.trim() != ''">
user_name LIKE CONCAT('%', #{username}, '%')
</if>
<if test="address != null and address.trim() != ''">
AND address = #{address}
</if>
</where>
</select>set标签
void updateUser(User user);<update id="updateUser">
UPDATE user
<set>
<if test="username != null">
user_name = #{username},
</if>
<if test="birthday != null">
birthday = #{birthday},
</if>
<if test="sex != null">
sex = #{sex},
</if>
<if test="address != null">
address = #{address},
</if>
</set>
WHERE id = #{id}
</update>foreach标签
List<User> findByIds(@Param("ids") List<Integer> ids);<select id="findByIds" resultType="user">
SELECT * FROM user WHERE id IN
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</select>多表查询
一对一查询
Order findOrderByNumber(@Param("orderNumber") String orderNumber);<resultMap id="orderWithUserResultMap" type="Order" autoMapping="true">
<id column="id" property="id"/>
<result column="order_number" property="orderNumber"/>
<association property="user" javaType="User" autoMapping="true">
<id column="user_id" property="id"/>
<result column="user_name" property="username"/>
</association>
</resultMap>
<select id="findOrderByNumber" resultMap="orderWithUserResultMap">
SELECT o.*, u.id AS user_id, u.user_name
FROM tb_order o
INNER JOIN tb_user u ON o.user_id = u.id
WHERE o.order_number = #{orderNumber}
</select>一对多查询
User findUserWithOrders(@Param("id") Integer id);<resultMap id="userWithOrdersResultMap" type="User" autoMapping="true">
<id column="id" property="id"/>
<collection property="orders" javaType="List" ofType="Order" autoMapping="true">
<id column="order_id" property="id"/>
<result column="order_number" property="orderNumber"/>
</collection>
</resultMap>
<select id="findUserWithOrders" resultMap="userWithOrdersResultMap">
SELECT u.*, o.id AS order_id, o.order_number
FROM tb_user u
LEFT JOIN tb_order o ON u.id = o.user_id
WHERE u.id = #{id}
</select>注解开发
MyBatis支持使用注解简化配置：
@Insert("INSERT INTO tb_user VALUES (null, #{username}, #{password}, #{name}, #{age}, #{sex})")
@Options(useGeneratedKeys = true, keyProperty = "id")
void addUser(User user);
@Select("SELECT * FROM tb_user WHERE id = #{id}")
User getUserById(Integer id);
@Update("UPDATE tb_user SET username = #{username} WHERE id = #{id}")
void updateUser(User user);
@Delete("DELETE FROM tb_user WHERE id = #{id}")
void deleteUser(Integer id);