<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.simple.mapper.SysUserMapper">
<resultMap id="userMap" type="org.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"/>
<result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
</resultMap>
<select id="selectById" resultMap="userMap">
select * from sys_user where id = #{id}
</select>
<select id="selectUserAndRoleById" resultType="org.simple.model.SysUser">
SELECT t1.id,t1.user_name,t1.user_email,t1.user_password,t1.user_info,t1.head_img,t1.create_time,
t3.id,t3.role_name,t3.enabled,t3.create_by,t3.create_time
FROM sys_user t1
LEFT JOIN sys_user_role t2 ON t1.id=t2.user_id
LEFT JOIN sys_role t3 ON t2.role_id=t3.id WHERE t1.id=#{id}
</select>
<select id="selectAll" resultType="org.simple.model.SysUser">
select id,user_name,user_password,user_email,user_info,head_img,create_time from sys_user
</select>
<select id="selectRoleByUserId" resultType="org.simple.model.SysRole">
SELECT t3.id,t3.role_name,t3.enabled,t3.create_by,t3.create_time,
t1.user_name,t1.user_email,t1.user_password
FROM sys_user t1
LEFT JOIN sys_user_role t2 ON t1.id=t2.user_id
LEFT JOIN sys_role t3 ON t2.role_id=t3.id WHERE t1.id=#{userId}
</select>
<select id="selectRolesByUserIdAndRoleEnabled" resultType="org.simple.model.SysRole">
SELECT t3.id,t3.role_name,t3.enabled,t3.create_by,t3.create_time
FROM sys_user t1
LEFT JOIN sys_user_role t2 ON t1.id=t2.user_id
LEFT JOIN sys_role t3 ON t2.role_id=t3.id WHERE t1.id=#{userId} and t3.enabled=#{enabled}
</select>
<select id="selectRolesByUserAndRole" resultType="org.simple.model.SysRole">
SELECT t3.id,t3.role_name,t3.enabled,t3.create_by,t3.create_time
FROM sys_user t1
LEFT JOIN sys_user_role t2 ON t1.id=t2.user_id
LEFT JOIN sys_role t3 ON t2.role_id=t3.id WHERE t1.id=#{user.id} and t3.enabled=#{role.enabled}
</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})<!--jdbcType:DATE jdbcType:TIME-->
</insert>
<insert id="insert2" useGeneratedKeys="true" keyProperty="id">
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})<!--jdbcType:DATE jdbcType:TIME-->
</insert>
<insert id="insert3">
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})<!--jdbcType:DATE jdbcType:TIME-->
<selectKey keyColumn="id" resultType="long" keyProperty="id" order="AFTER">
SELECT LAST_INSERT_ID()
</selectKey>
</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>
</mapper>
<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.simple.mapper.UserMapper">
<resultMap id="usersMap" type="org.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>
<!--MyBatis动态SQL-->
<!--模糊查询-->
<select id="selectByUser" resultType="org.simple.model.SysUser">
select id,user_name,user_password,user_email,
user_info,head_img,create_time
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 用法-->
<select id="selectByUsers" resultType="org.simple.model.SysUser">
select id,
user_name,
user_password,
user_email,
user_info,
head_img,
create_time
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>
<!--if用法-->
<insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
insert into sys_user(
<if test="userName!=null and userName!=''">user_name,</if>
<if test="userPassword!=null and userPassword!=''">user_password,</if>
<if test="userEmail!=null and userEmail!=''">user_email,</if>
<if test="userInfo!=null and userInfo!=''">user_info,</if>
<if test="headImg!=null">head_img,</if>
<if test="createTime!=null">create_time</if>
) values(
<if test="userName!=null and userName!=''">#{userName},</if>
<if test="userPassword!=null and userPassword!=''">#{userPassword},</if>
<if test="userEmail!=null and userEmail!=''">#{userEmail},</if>
<if test="userInfo!=null and userInfo!=''">#{userInfo},</if>
<if test="headImg!=null">#{headImg,jdbcType=BLOB},</if>
<if test="createTime!=null">#{createTime,jdbcType=TIMESTAMP}</if>
)
</insert>
<!--choose 用法-->
<select id="selectByIdOrUserName" resultType="org.simple.model.SysUser">
select id,
user_name,
user_password,
user_email,
user_info,
head_img,
create_time
from sys_user
where 1=1
<choose>
<when test="id!=null">
and id=#{id}
</when>
<when test="userName!=null and userName!=''">
and user_name=#{userName}
</when>
<otherwise>
and 1=2
</otherwise>
</choose>
</select>
<update id="updateByIdSelective">
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 ">
head_img=#{headImg,jdbcType=BLOB},
</if>
<if test="createTime!=null">
create_time=#{createTime,jdbcType=TIMESTAMP},
</if>
id=#{id}
where id=#{id}
</update>
<!--set 用法-->
<update id="updateByIdSelectives">
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 ">
head_img=#{headImg,jdbcType=BLOB},
</if>
<if test="createTime!=null">
create_time=#{createTime,jdbcType=TIMESTAMP},
</if>
id=#{id},
</set>
where id=#{id}
</update>
<!--trim 用法-->
<!--foreach 用法-->
<select id="selectByIdList" resultType="org.simple.model.SysUser">
select id,
user_name,
user_password,
user_email,
user_info,
head_img,
create_time
from sys_user
where id in
<foreach collection="list" open="(" close=")" separator=","
item="id" index="i">
#{id}
</foreach>
</select>
<!--批量插入-->
<insert id="insertList">
insert into sys_user(user_name,
user_password,
user_email,
user_info,
head_img,
create_time)
values
<foreach collection="list" item="user" separator=",">
(
#{user.userName},#{user.userPassword},#{user.userEmail},
#{user.userInfo}, #{user.headImg,jdbcType=BLOB},
#{user.createTime,jdbcType=TIMESTAMP}
)
</foreach>
</insert>
<!--批量删除-->
<delete id="delList">
delete from sys_user
<where>
id in
<foreach collection="list" open="(" close=")" separator="," item="id" index="i">
#{id}
</foreach>
</where>
</delete>
<!--foreach 实现动态UPDATE-->
<update id="updateByMap">
update sys_user
set
<foreach collection="_parameter" item="val" index="key" separator=",">
${key}=#{val}
</foreach>
where id=#{id}
</update>
</mapper>
package org.simple.mapper;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;
import org.simple.model.SysRole;
import java.util.List;
public interface SysRoleMapper {
@Select({"select id,role_name,enabled,create_by,create_time",
"from sys_role",
"where id=#{id}"})
SysRole selectById(Long id);
@Results({
@Result(property = "id", column = "id", id = true),
@Result(property = "roleName", column = "role_name"),
@Result(property = "enabled", column = "enabled"),
@Result(property = "createBy", column = "create_by"),
@Result(property = "createTime", column = "create_time", jdbcType = JdbcType.TIMESTAMP)
})
@Select({"select id,role_name,enabled,create_by,create_time",
"from sys_role",
"where id=#{id}"})
SysRole selectById2(Long id);
@Select("select *from sys_role")
List<SysRole> selectAll();
//不需要返回主键添加
@Insert({"insert into sys role (id, role_name, enabled, create_by, create_time )",
"values(#{id}, #{roleName}, #{enabled}, #{createBy },",
"#{ createTime , jdbcType=TIMESTAMP })"})
int insert(SysRole role);
//返回自增主键添加
@Insert({"insert into sys role (id, role_name, enabled, create_by, create_time )",
"values(#{id}, #{roleName}, #{enabled}, #{createBy },",
"#{ createTime,jdbcType=TIMESTAMP })"})
@Options(useGeneratedKeys = true, keyProperty = "id")
int insert1(SysRole role);
@Update({"update sys_user",
" set role_name=#{roleName},enabled=#{enabled}，create_by=#{createBy}，",
"create_time=#{createTime,jdbcType=TIMESTAMP}",
"where id=#{id}"})
int updateById(SysRole role);
@Delete({"delete from sys_role where id=#{id}"})
int deleteById(Long id);
}
//BaseMapper
package org.simple.utils;
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 org.junit.BeforeClass;
import org.simple.model.SysUser;
import java.io.IOException;
import java.io.Reader;
import java.util.List;
public class BaseMapper
{
private static SqlSessionFactory sqlSessionFactory;
@BeforeClass
public static void init() throws IOException {
// 读取mybatis-config.xml配置文件
Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
sqlSessionFactory=new SqlSessionFactoryBuilder().build(reader);
reader.close();
}
public SqlSession getSqlSession(){
return sqlSessionFactory.openSession();
}
}
