mybatis的一些基本用法
2017-11-04 本文已影响0人
xiaofudeng
输出SQL代码等调试信息
在configuration里的settings部分加入logImpl即可, 使用STDOUT_LOGGING可不必再配置其他日志库, 开箱即用比较方便.
<settings>
<!--输出SQL语句等信息到STDOUT中-->
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
引用变量的值
- #{name} 会进行安全性处理
- ${name} 直接引用原来的值, 可能遭受注入攻击
往SQL语句中传入多个参数
- 直接使用POJO类参数
直接使用#{property}即可引用对象的property属性. Mybatis通过getter()来获取值.
<!--插入用户-->
<insert id="insertUser" useGeneratedKeys="true" parameterType="me.xiaofud.entity.User" keyColumn="id" keyProperty="id">
INSERT INTO users (account, nickname, profile, birthday, token, image)
VALUES (#{account},
#{nickname},
#{profile},
#{birthday},
#{token},
#{image}
);
</insert>
接口文件
public interface SyllabusDao {
public void insertUser(User user);
}
- 直接设置parameterType为map即可
<select id="..." parameterType="map>
#{key}
</select>
- 在代码中使用@Param注解
public List<Job> queryMultiple(@Param("afterID") int afterID, @Param("count") int count);
获取插入数据的id
配置useGeneratedKeys="true", 同时用keyProperty指定在POJO中key对应的属性, keyColumn指定在数据库中key的列名.
<insert id="insertUser" useGeneratedKeys="true" parameterType="me.xiaofud.entity.User" keyColumn="id" keyProperty="id">
INSERT INTO users (account, nickname, profile, birthday, token, image)
VALUES (#{account},
#{nickname},
#{profile},
#{birthday},
#{token},
#{image}
);
</insert>
mapper的one to many
官方文档
简单的XML示例
比较关键的地方是如果SQL中出现了JOIN操作, 那一定要注意将结果的列名用别名处理一下, 否则会有潜在的很多冲突.
<?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="me.xiaofud.dao.SyllabusDao">
<resultMap id="userWithPosts" type="me.xiaofud.entity.User">
<id property="id" column="user_id"/>
<result column="user_accout" property="account"/>
<result column="user_nickname" property="nickname"/>
<result column="user_avator" property="image"/>
<collection property="postList" javaType="List" ofType="me.xiaofud.entity.Post">
<id column="post_id" property="id"/>
<result column="post_description" property="description"/>
<result column="post_user_id" property="uid"/>
</collection>
</resultMap>
<!--注意在JOIN情况下, 一定要对列名重新指定别名-->
<!--否则很多冲突-->
<select id="queryUserByAccount" resultMap="userWithPosts">
SELECT
users.id AS user_id,
account AS user_accout,
nickname AS user_nickname,
image AS user_avator,
posts.id AS post_id,
posts.uid AS post_user_id,
description AS post_description
FROM users JOIN posts ON users.id = posts.uid
WHERE account = #{account} ORDER BY posts.post_time; # LIMIT 20;
</select>
</mapper>
上面的collection中的内容也可以分开写
<resultMap id="userWithPosts" type="me.xiaofud.entity.User">
<id property="id" column="user_id"/>
<result column="user_accout" property="account"/>
<result column="user_nickname" property="nickname"/>
<result column="user_avator" property="image"/>
<collection property="postList" resultMap="postMap"/>
</resultMap>
<resultMap id="postMap" type="me.xiaofud.entity.Post">
<id column="post_id" property="id"/>
<result column="post_description" property="description"/>
<result column="post_user_id" property="uid"/>
</resultMap>
</resultMap>
association的columnPrefix属性
原文同文章开头
适用情景, 某个表可能有多个字段指向相同的另外一张表, 此时的association中的column可以适用columnPrefix来减少一些重复性输入.
What if the blog has a co-author? The select statement would look like:
<select id="selectBlog" resultMap="blogResult">
select
B.id as blog_id,
B.title as blog_title,
A.id as author_id,
A.username as author_username,
A.password as author_password,
A.email as author_email,
A.bio as author_bio,
CA.id as co_author_id,
CA.username as co_author_username,
CA.password as co_author_password,
CA.email as co_author_email,
CA.bio as co_author_bio
from Blog B
left outer join Author A on B.author_id = A.id
left outer join Author CA on B.co_author_id = CA.id
where B.id = #{id}
</select>
Recall that the resultMap for Author is defined as follows.
<resultMap id="authorResult" type="Author">
<id property="id" column="author_id"/>
<result property="username" column="author_username"/>
<result property="password" column="author_password"/>
<result property="email" column="author_email"/>
<result property="bio" column="author_bio"/>
</resultMap>
Because the column names in the results differ from the columns defined in the resultMap, you need to specify columnPrefix to reuse the resultMap for mapping co-author results.
<resultMap id="blogResult" type="Blog">
<id property="id" column="blog_id" />
<result property="title" column="blog_title"/>
<association property="author"
resultMap="authorResult" />
<association property="coAuthor"
resultMap="authorResult"
columnPrefix="co_" />
</resultMap>
Dynamic SQL
if条件
只有if的条件成立, 在if块部分的字符才会加入到SQL语句之中. 不过如果仅使用<if>不会有trim功能, 比如说在WHERE后面有两个<if>, 如果第一个不成立, 第二个成立, 那么 AND 就会直接出现在WHERE后导致语法错误. 后面介绍的trim可以避免这个问题.
<select id="findActiveBlogWithTitleLike"
resultType="Blog">
SELECT * FROM BLOG
WHERE state = ‘ACTIVE’
<if test="title != null">
AND title like #{title}
</if>
</select>
choose, when, otherwise
<select id="findActiveBlogLike"
resultType="Blog">
SELECT * FROM BLOG WHERE state = ‘ACTIVE’
<choose>
<when test="title != null">
AND title like #{title}
</when>
<when test="author != null and author.name != null">
AND author_name like #{author.name}
</when>
<otherwise>
AND featured = 1
</otherwise>
</choose>
</select>
trim, where, set
使用<where>可以避免去考虑可能AND会直接出现在WHERE后面的情况.
<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>
</where>
</select>
上面的<where>等价于下面的定义
<trim prefix="WHERE" prefixOverrides="AND |OR ">
...
</trim>
<set>的例子
<update id="updateAuthorIfNecessary">
update Author
<set>
<if test="username != null">username=#{username},</if>
<if test="password != null">password=#{password},</if>
<if test="email != null">email=#{email},</if>
<if test="bio != null">bio=#{bio}</if>
</set>
where id=#{id}
</update>
与之相等的<trim>
<trim prefix="SET" suffixOverrides=",">
...
</trim>
foreach
<select id="selectPostIn" resultType="domain.blog.Post">
SELECT *
FROM POST P
WHERE ID in
<foreach item="item" index="index" collection="list"
open="(" separator="," close=")">
#{item}
</foreach>
</select>
bind
<select id="selectBlogsLike" resultType="Blog">
<bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />
SELECT * FROM BLOG
WHERE title LIKE #{pattern}
</select>
多数据库支持
<insert id="insert">
<selectKey keyProperty="id" resultType="int" order="BEFORE">
<if test="_databaseId == 'oracle'">
select seq_users.nextval from dual
</if>
<if test="_databaseId == 'db2'">
select nextval for seq_users from sysibm.sysdummy1"
</if>
</selectKey>
insert into users values (#{id}, #{name})
</insert>
