如用户表
一、新建本地实体类SysUser.java,生成getter,setter方法
package tk.mybatis.simple.model;
public class SysUserRole {
private Long userId;
private Long roleId;
public Long getUserId() {
return userId;
}
public void setUserId(Long userId) {
this.userId = userId;
}
public Long getRoleId() {
return roleId;
}
public void setRoleId(Long roleId) {
this.roleId = roleId;
}
}
二、编写接口UserMapper.java
package tk.mybatis.simple.mapper;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Param;
import tk.mybatis.simple.model.SysRole;
import tk.mybatis.simple.model.SysUser;
public interface UserMapper {
/**
* 通过id查询用户
* @param id
* @return
*/
SysUser selectById(Long id);
/**
* 查询所有用户
* @return
*/
List<SysUser> selectAll();
/**
* 根据用户id获取角色对象集合
* @param userId
* @return
*/
List<SysRole> selectRolesByUserId1(Long userId);
/**
* 根据用户id获取角色对象集合,
* 还需要其他属性,如用户的一些基本信息,放入role属性为user对象中
* xml中手动映射查询结果的列名——实体类属性
* @param userId
* @return
*/
List<SysRole> selectRolesByUserId2(Long userId);
/**
* 插入一条用户数据
* @param user
* @return
*/
int insert(SysUser user);
/**
* 插入一条用户数据
* 并且返回自增主键值,这里需要数据库自己有主键自增功能
* mysql插入再返回主键值
* @param user
* @return
*/
int insert2(SysUser user);
/**
* 插入一条用户数据
* 并且返回自增主键值,这里通过selectKey方法
* 如oracle没有主键自增功能,先通过序列中获取值,再插入
* 各数据库selectKey获取方式不同
* @param user
* @return
*/
int insert3(SysUser user);
/**
* 根据id更新这条数据
* ①先取id值对应的数据对象
* ②再set需要更新的属性
* ③通过xml映射关系,执行update语句
* @param user
* @return
*/
int updateById(SysUser user);
/**
* 删除id对应这条数据
* @param id
* @return
*/
int deleteById(Long id);
/**
* 删除id对应这条数据
* @param id
* @return
*/
int deleteByUser(SysUser user);
/**
* 根据用户id和角色的enabled状态获取用户的角色结果集合
* 单一参数,不需要考虑其他
* 多参数,xml不能自动对应,需要加注解
*
* xml:参数N个
* xml:参数列表为{未加注解的参数所在数组的索引值,已经加注解的参数的名字,param1,param2,param3,...,param(N-1),param(N)
* 如xml需要取 #{userCode}的参数,接口参数列表中没有注解;
*
* ①此处4个参数,前面3个都有注解,而第4个没有,则标出在参数列表数组索引值中的3(N个参数的索引值为0-【N-1】)
* 第2个参数没加注解,则标出为1; [1, param1, param2, param3, param4, userId, userName]
* 第1个,第3个,第4个 没有注解,则标出0,2,3 [3, 2, enabled, 0, param1, param2, param3, param4]
* ②已加参数名注解的 userId,userName,enabled
* ③所有参数,假设有N个,param1...param(N)
* [3, enabled, param1, param2, param3, param4, userId, userName]
*
* 参数列表自动被封装成Map类型,key来映射xml中sql需要使用的参数值名字,value原来存放参数值
* @param id
* @param enabled
* @return
*/
List<SysRole> selectRolesByUserIdAndRoleEnabled(@Param("userId")Long userId,@Param("enabled")Integer enabled,@Param("userName")String userName,String bb);
List<SysUser> selectByUser(SysUser user);
/**
* 根据用户id集合来获取用户对象集合
* @param idList
* @return
*/
List<SysUser> selectByIdList(List<Long> idList);
/**
* 根据用户id数组来获取用户对象集合
* @param idList
* @return
*/
List<SysUser> selectByIdArray(Long[] idArray);
/**
* 传入参数为map对象
* @param mapObject
* @return
*/
List<SysUser> selectByMap(Map<String,Object> mapObject);
int insertList(List<SysUser> userList);
int updateByMap(Map<String,Object> map);
}
三、映射处理Mapper.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="tk.mybatis.simple.mapper.UserMapper">
<!-- resultMap配置结果映射,id对应上面namespace命名空间 接口mapper ,type:映射实体类-->
<!-- colunm:数据库表 列名 -->
<!-- property:实体类 属性名 ,实际匹配的时候都转成大写格式,这里统一写成一样的-->
<!-- 数据库表的列名一般不区分大小写,以下划线_取名(user_name),而Java类中以驼峰式命名 (userName)-->
<resultMap id="userMap" type="tk.mybatis.simple.model.SysUser" >
<id property="id" column="id" />
<result property="userName" column="user_name"/>
<result property="userPassword" column="user_password"/>
<result property="userEmail" column="user_email"/>
<result property="userInfo" column="user_info"/>
<result property="headImg" column="head_img" jdbcType="BLOB"/>
<result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
</resultMap>
<!-- resultMap指定上面resultMap(id=UserMapper)映射的type:SysUser -->
<select id="selectById" resultMap="userMap">
select * from sys_user where id = #{id}
</select>
<!-- resultMap:对应上面的标签 ,直接根据标签内的列名与属性来映射-->
<!-- resultType:对应全限定名的实体类,需要将查询的列名取别名 来 对应实体类属性 -->
<!-- 此处查询的返回值没有设成上处的resultMap,而是指定全限定名,查询结果列取别名和resultType指定对象的属性名保持一致,从而自动映射 -->
<select id="selectAll" resultType="tk.mybatis.simple.model.SysUser">
select id,
user_name userName,
user_password userPassword,
user_email userEmail,
user_info userInfo,
head_img headImg,
create_time createTime
from sys_user
</select>
<select id="selectRolesByUserId1" resultType="tk.mybatis.simple.model.SysRole">
select
r.id,
r.role_name roleName,
r.enabled,
r.create_by createdBy,
r.create_time createdTime
from sys_user u
inner join sys_user_role ur on u.id=ur.user_id
inner join sys_role r on ur.role_id=r.id
where u.id = #{userId}
</select>
<select id="selectRolesByUserId2" resultType="tk.mybatis.simple.model.SysRole">
select
r.id,
r.role_name roleName,
r.enabled,
r.create_by createdBy,
r.create_time createdTime,
u.user_name as "user.userName",
u.user_email as "user.userEmail"
from sys_user u
inner join sys_user_role ur on u.id=ur.user_id
inner join sys_role r on ur.role_id=r.id
where u.id = #{userId}
</select>
<insert id="insert">
insert into sys_user
(id,user_name,user_password,user_email,user_info,head_img,create_time)
values(
#{id},#{userName},#{userPassword},#{userEmail},#{userInfo},
#{headImg,jdbcType=BLOB},
#{createTime,jdbcType=TIMESTAMP}
)
</insert>
<!-- 需要返回主键自增值,这里设置用Jdbc的useGeneratedKeys方法取出数据库内部生成的主键,然后赋值给keyProperty对应的属性 -->
<insert id="insert2" useGeneratedKeys="true" keyProperty="id">
insert into sys_user
(user_name,user_password,user_email,user_info,head_img,create_time)
values(
#{userName},#{userPassword},#{userEmail},#{userInfo},
#{headImg,jdbcType=BLOB},
#{createTime,jdbcType=TIMESTAMP}
)
</insert>
<!-- mysql插入,获取自增主键值,after-->
<!-- 主键是插入后才获取到的 -->
<insert id="insert3">
insert into sys_user
(user_name,user_password,user_email,user_info,head_img,create_time)
values(
#{userName},#{userPassword},#{userEmail},#{userInfo},
#{headImg,jdbcType=BLOB},
#{createTime,jdbcType=TIMESTAMP}
)
<selectKey keyColumn="id" keyProperty="id" resultType="long" order="AFTER">
select last_insert_id()
</selectKey>
</insert>
<!-- Oracle插入,需要获取自增主键值,before -->
<!-- 先从自增序列中获取,然后将其插入到数据库中 -->
<!-- <insert id="insert3">
<selectKey keyColumn="id" keyProperty="id" resultType="long" order="BEFORE">
select seq_id.nextval from dual
</selectKey>
insert into sys_user
(id,user_name,user_password,user_email,user_info,head_img,create_time)
values(
#{id},#{userName},#{userPassword},#{userEmail},#{userInfo},
#{headImg,jdbcType=BLOB},
#{createTime,jdbcType=TIMESTAMP}
)
</insert> -->
<update id="updateById">
update sys_user
set user_name = #{userName},
user_password = #{userPassword},
user_email = #{userEmail},
user_info = #{userInfo},
head_img = #{headImg,jdbcType=BLOB},
create_time = #{createTime,jdbcType=TIMESTAMP}
where id = #{id}
</update>
<delete id="deleteById">
delete from sys_user where id = #{id}
</delete>
<delete id="deleteByUser">
delete from sys_user where id = #{id}
</delete>
<!-- 多参数情况,需要接口Mapper.java中参数列表设置@Param("") -->
<select id="selectRolesByUserIdAndRoleEnabled" resultType="tk.mybatis.simple.model.SysRole">
select
r.id,
r.role_name roleName,
r.enabled,
r.create_by createdBy,
r.create_time createdTime,
u.user_name as "user.userName",
u.user_email as "user.userEmail"
from sys_user u
inner join sys_user_role ur on u.id=ur.user_id
inner join sys_role r on ur.role_id=r.id
where u.id = #{userId} and r.enabled = #{enabled} and u.user_name = #{userName}
</select>
<!-- 添加if标签判定条件为空,假如传入一个参数,另外一个回默认为空(值为null)也会假如查询条件 -->
<select id="selectByUser" resultType="tk.mybatis.simple.model.SysUser">
select id,
user_name userName,
user_password userPassword,
user_email userEmail,
user_info userInfo,
head_img headImg,
create_time createTime
from sys_user
where 1=1
<if test="userName != null and userName != ''">
and user_name like concat('%',#{userName},'%')
</if>
<if test="userEmail != null and userEmail != ''">
and user_email = #{userEmail}
</if>
</select>
<!--使用where标签 ,条件不满足就没有内容,还会自动去掉开头的and-->
<!--手写sql语句,需要开始添加 where 1=1 -->
<select id="selectByUser2" resultType="tk.mybatis.simple.model.SysUser">
select id,
user_name userName,
user_password userPassword,
user_email userEmail,
user_info userInfo,
head_img headImg,
create_time createTime
from sys_user
<where>
<if test="userName != null and userName != ''">
and user_name like concat('%',#{userName},'%')
</if>
<if test="userEmail != null and userEmail != ''">
and user_email = #{userEmail}
</if>
</where>
</select>
<!-- set标签,会自动去掉结尾的"," ,这里仍需注意set元素中不能为空,所以保留了"id = #{id},"-->
<!-- 手写sql语句update...set..., 最后一个赋值结尾不能有","-->
<update id="updateByIdSelectivev">
update sys_user
<set>
<if test="userName != null and userName != ''">
user_name = #{userName},
</if>
<if test="userPassword != null and userPassword != ''">
user_password = #{userPassword},
</if>
<if test="userEmail != null and userEmail != ''">
user_email = #{userEmail},
</if>
<if test="userInfo != null and userInfo != ''">
user_info = #{userInfo},
</if>
<if test="headImg != null and headImg != ''">
head_img = #{headImg,jdbcType=BLOB},
</if>
<if test="createTime != null and createTime != ''">
create_time = #{createTime,jdbcType=TIMESTAMP},
</if>
id = #{id},
</set>
where id = #{id}
</update>
<!-- foreach批量,只有一个参数,集合:list,数组:array -->
<!-- 多参数使用,需要接口中指定@param ,对应标签内的collection属性值 -->
<select id="selectByIdList" resultType="tk.mybatis.simple.model.SysUser">
select id,
user_name userName,
user_password userPassword,
user_email userEmail,
user_info userInfo,
head_img headImg,
create_time createTime
from sys_user
where id in
<foreach collection="list" open="(" close=")" separator="," item="id" index="i">
#{id}
</foreach>
</select>
<select id="selectByIdArray" resultType="tk.mybatis.simple.model.SysUser">
select id,
user_name userName,
user_password userPassword,
user_email userEmail,
user_info userInfo,
head_img headImg,
create_time createTime
from sys_user
where id in
<foreach collection="array" open="(" close=")" separator="," item="id" index="i">
#{id}
</foreach>
</select>
<!-- 传入Map,collection:map中指定的key值;此时遍历对应的value值 -->
<!-- 需要循环map,@param注解指定名字/使用默认值_parameter -->
<select id="selectByMap" resultType="tk.mybatis.simple.model.SysUser">
select id,
user_name userName,
user_password userPassword,
user_email userEmail,
user_info userInfo,
head_img headImg,
create_time createTime
from sys_user
where id in
<foreach collection="id" open="(" close=")" separator="," item="id" index="i">
#{id}
</foreach>
<!-- <foreach collection="_parameter" open="(" close=")" separator="," item="id" index="i">
#{id}
</foreach> -->
</select>
<!-- 批量插入,mybatis3.3.1及其以上支持返回批量插入主键值 -->
<insert id="insertList">
insert into sys_user(user_name,user_email,user_info,head_img,create_time)
values
<foreach collection="list" item="user" separator=",">
(
#{user.userName},#{user.userEmail},#{user.userInfo},
#{user.headImg,jdbcType=BLOB},
#{user.createTime,jdbcType=TIMESTAMP}
)
</foreach>
</insert>
<update id="updateByMap">
update sys_user
set
<foreach collection="_parameter" item="val" index="key" separator=",">
${key} = #{val}
</foreach>
where id = #{id}
</update>
</mapper>