mybatis中的mapper

  插入后返回主键、关联查询、多参

xml格式

插入后返回主键

“keyProperty”表示返回的 id 要保存到对象的那个属性中,“useGeneratedKeys”表示主键 id 为自增长

1
2
<insert id="insertUser" parameterType="com.hfy.db.bean.User" useGeneratedKeys="true" keyProperty="id">
</insert>

数据库为 Oracle 时

1
2
3
4
5
6
7
8
<insert id="insert" parameterType="com.test.User">
<selectKey resultType="INTEGER" order="BEFORE" keyProperty="userId">
SELECT SEQ_USER.NEXTVAL as userId from DUAL
</selectKey>
insert into user (user_id, user_name, modified, state)
values (#{userId,jdbcType=INTEGER}, #{userName,jdbcType=VARCHAR},
#{modified,jdbcType=TIMESTAMP}, #{state,jdbcType=INTEGER})
</insert>

由于 Oracle 没有自增长一说法,只有序列这种模仿自增的形式,所以不能再使用“useGeneratedKeys”属性。而是使用将 ID 获取并赋值到对象的属性中,insert 插入操作时正常插入 id

多个参数

  对于接口中多个请求参数,没有封装成对象

1
2
3
4
5
6
7
8
9
10
11
12
13
// dao中:
OrderDetail selectByTypeAndDate(@Param("type")Integer type, @Param("date")String date);
// mapper中:
<select id="selectByTypeAndDate" resultMap="orderDetailResult" >
SELECT * FROM order_detail
WHERE type = #{type}
<c:if test="type == 1">
and minPlayTime > #{date}
</c:if>
<c:if test="type == 2">
and maxPlayTime < #{date}
</c:if>
</select>

关联查询  

1
2
3
4
5
6
<resultMap type="com.hfy.db.bean.Order" id="OrderResult">
<result property="id" column="id" />
<!-- 配置关联关系 -->
<association property="user" column="userId"
select="com.hfy.dao.UserDao.selectById"></association>
</resultMap>

MyBatis参数传入集合之foreach动态sql http://blog.csdn.net/small____fish/article/details/8029030/

注解格式

插入后返回主键

1
2
@Insert("")
@SelectKey(statement = "SELECT LAST_INSERT_ID()", keyProperty = "user.id", resultType = int.class, before = false)

MyBatis注解select in参数

1
2
3
4
5
6
7
/**
*
* @param ids '1,2,3'
* @return
*/
@Select("select * from user where id in (${ids})")
List<UserInfo> getUserbyIds(@Param("ids")String ids);

MyBatis注解list参数

注解中想使用xml中的<if>标签,需要用<script>标签将其包起来

1
2
3
4
5
6
7
8
9
10
11
12
<script>
insert into tb_image_temp(
<foreach collection='keys' item='item' open='(' separator=',' close=')'>
${item}
</foreach>
) values(
<foreach collection='values' item='item' open='(' separator=',' close=')'>
#{item}
</foreach>
)
</script>

MyBatis注解map参数

1
2
3
4
5
6
7
8
@Update("<script>n" +
" update ${tableName} set " +
" <foreach collection='map' index='key' item='item' open='' separator=',' close='' >n" +
" ${key} = #{item}n" +
" </foreach>n" +
" where id = #{id}n" +
"</script>")
void updateImage(@Param("tableName") String tableName, @Param("map") Map<String, Object> map, @Param("id") Integer id);

结果映射

1
2
3
4
5
6
7
8
9
10
@Select("")
@Results({
@Result(property ="groupId",column = "groupId"),
//查询关联对象
@Result(property = "adminGroup",
column = "groupId",
one = @One(select = "com.cyf.db.mapper.AdminGroupMapper.getGroupById")
)
})
List<ManageAccount> getManageAccount(@Param("manageAccount") ManageAccount manageAccount);