mybatis综合练习

最新推荐文章于 2023-08-09 08:39:54 发布

虾米大王

最新推荐文章于 2023-08-09 08:39:54 发布

阅读量73

点赞数

分类专栏：感悟 Java 文章标签： mybatis java maven

本文链接：https://blog.csdn.net/modern358/article/details/130685860

版权

Java 同时被 2 个专栏收录

1267 篇文章 4 订阅

订阅专栏

感悟

15 篇文章 0 订阅

订阅专栏

新建一个maven工程，将src目录删除，作为空工程使用。

项目总pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.shrimpking</groupId>
    <artifactId>mybatistest01</artifactId>
    <packaging>pom</packaging>
    <version>1.0-SNAPSHOT</version>
    <modules>
        <module>mybatis-01</module>
        <module>mybatis-02-sys</module>
        <module>mybatis-03-relation</module>
    </modules>

    <dependencies>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.3.0</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.38</version>
        </dependency>

        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.7.12</version>
        </dependency>

        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>1.7.12</version>
        </dependency>

        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
    </dependencies>
</project>

module的pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>mybatistest01</artifactId>
        <groupId>com.shrimpking</groupId>
        <version>1.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>mybatis-02-sys</artifactId>

    <dependencies>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.3.1</version>
        </dependency>
    </dependencies>
</project>

------------------------------------------------------------------------------------------------------------

数据库：

create database mybatis default CHARACTER set utf8 collate utf8_general_ci;

use mybatis;

create table country(
	id int not null auto_increment,
	countryname varchar(255) null,
	countrycode varchar(255) null,
	primary key(id)
);


insert into country(countryname,countrycode) values ('中国','CN');
insert into country(countryname,countrycode) values ('美国','US');
insert into country(countryname,countrycode) values ('俄罗斯','RU');
insert into country(countryname,countrycode) values ('英国','GB');
insert into country(countryname,countrycode) values ('法国','FR');


create table sys_user(
	id bigint not null auto_increment comment '用户id',
	user_name varchar(50) comment '用户名',
	user_password varchar(50) comment '密码',
	user_email varchar(50) comment '邮箱',
	user_info text comment '简介',
	head_img blob comment '头像',
	create_time datetime comment '创建时间',
	primary key(id)
);
alter table sys_user comment '用户表';

create table sys_role(
	id bigint not null auto_increment comment '角色id',
	role_name varchar(50) comment '角色名',
	enabled int comment '有效标志',
	create_by bigint comment '创建人',
	create_time datetime comment '创建时间',
	primary key(id)
);
alter table sys_role comment '角色表';

create table sys_privilege(
	id bigint not null auto_increment comment '权限id',
	privilege_name varchar(50) comment '权限名称',
	privilege_url varchar(50) comment '权限url',
	primary key(id)
);
alter table sys_privilege comment '权限表';

create table sys_user_role(
	user_id bigint comment '用户id',
	role_id bigint comment '角色id'
);
alter table sys_user_role comment '用户角色关联表';

create table sys_role_privilege(
	role_id bigint comment '角色id',
	privilege_id bigint comment '权限id'
);
alter table sys_role_privilege comment '角色权限关联表';

insert into sys_user values (null,'admin','123456','admin@xx.com','管理员',null,'2023-04-01');
insert into sys_user values (null,'test','123456','test@xx.com','测试用户',null,'2023-04-01');

insert into sys_role values (null,'管理员','1','1','2023-04-01');
insert into sys_role values (null,'普通用户','1','1','2023-04-01');


insert into sys_user_role values ('1','1');
insert into sys_user_role values ('1','2');
insert into sys_user_role values ('2','2');

insert into sys_privilege values (null,'用户管理','/users');
insert into sys_privilege values (null,'角色管理','/roles');
insert into sys_privilege values (null,'系统日志','/logs');
insert into sys_privilege values (null,'人员维护','/persons');
insert into sys_privilege values (null,'单位维护','/companies');

insert into sys_role_privilege values ('1','1');
insert into sys_role_privilege values ('1','2');
insert into sys_role_privilege values ('1','3');
insert into sys_role_privilege values ('2','4');
insert into sys_role_privilege values ('2','5');

select * from sys_user where id = 1

select
id,
user_name as userName,
user_password as userPassword,
user_email as userEmail,
user_info as userInfo,
head_img as headImg,
create_time as createTime
from sys_user;

select 
r.*
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 = 2

项目具体情况截图

---------------------------------------------------------------------------------------------------------------------------

下面依次贴出代码

配置文件

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<!--configuration核心配置文件-->
<configuration>

    <settings>
        <setting name="logImpl" value="LOG4J"/>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=UTC"/>
                <property name="username" value="root"/>
                <property name="password" value="mysql123"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="com/shrimpking/mapper/UserMapper.xml"/>
        <mapper resource="com/shrimpking/mapper/RoleMapper.xml"/>
        <mapper resource="com/shrimpking/mapper/PrivilegeMapper.xml"/>
        <mapper resource="com/shrimpking/mapper/UserRoleMapper.xml"/>
        <mapper resource="com/shrimpking/mapper/RolePrivilegeMapper.xml"/>
    </mappers>
</configuration>

日志配置文件

log4j.properties

#全局配置
log4j.rootLogger=ERROR, stdout

#Mabatis日志配置
log4j.logger.com.shrimpking = TRACE

#控制台输出配置
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

=====================================================================

基础工具类

BaseMapperTest.java

package com.shrimpking.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 java.io.IOException;
import java.io.Reader;

/**
 * @author user1
 */
public class BaseMapperTest
{
    private static SqlSessionFactory sqlSessionFactory;

    @BeforeClass
    public static void init()
    {
        try
        {
            Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
            reader.close();
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }

    }

    public SqlSession getSqlSession()
    {
        return sqlSessionFactory.openSession(true);
    }
}

==================================================================

实体类

SysUser.java

package com.shrimpking.entity;

import java.util.Arrays;
import java.util.Date;

/**
 * @author user1
 */
public class SysUser
{
    private Long id;
    private String userName;
    private String userPassword;
    private String userEmail;
    private String userInfo;
    private byte[] headImg;
    private Date createTime;

    public SysUser()
    {
    }

    public SysUser(long id, String userName, String userPassword, String userEmail, String userInfo, byte[] headImg, Date createTime)
    {
        this.id = id;
        this.userName = userName;
        this.userPassword = userPassword;
        this.userEmail = userEmail;
        this.userInfo = userInfo;
        this.headImg = headImg;
        this.createTime = createTime;
    }

    public Long getId()
    {
        return id;
    }

    public void setId(Long id)
    {
        this.id = id;
    }

    public String getUserName()
    {
        return userName;
    }

    public void setUserName(String userName)
    {
        this.userName = userName;
    }

    public String getUserPassword()
    {
        return userPassword;
    }

    public void setUserPassword(String userPassword)
    {
        this.userPassword = userPassword;
    }

    public String getUserEmail()
    {
        return userEmail;
    }

    public void setUserEmail(String userEmail)
    {
        this.userEmail = userEmail;
    }

    public String getUserInfo()
    {
        return userInfo;
    }

    public void setUserInfo(String userInfo)
    {
        this.userInfo = userInfo;
    }

    public byte[] getHeadImg()
    {
        return headImg;
    }

    public void setHeadImg(byte[] headImg)
    {
        this.headImg = headImg;
    }

    public Date getCreateTime()
    {
        return createTime;
    }

    public void setCreateTime(Date createTime)
    {
        this.createTime = createTime;
    }

    @Override
    public String toString()
    {
        return "SysUser{" + "id=" + id + ", userName='" + userName + '\'' + ", userPassword='" + userPassword + '\'' + ", userEmail='" + userEmail + '\'' + ", userInfo='" + userInfo + '\'' + ", headImg=" + Arrays.toString(headImg) + ", createTime=" + createTime + '}';
    }
}

SysRole.java

package com.shrimpking.entity;

import java.util.Date;

/**
 * @author user1
 */
public class SysRole
{
    private Long id;
    private String roleName;
    private Integer enabled;
    private Long createBy;
    private Date createTime;

    public SysRole()
    {
    }

    public SysRole(Long id, String roleName, Integer enabled, Long createBy, Date createTime)
    {
        this.id = id;
        this.roleName = roleName;
        this.enabled = enabled;
        this.createBy = createBy;
        this.createTime = createTime;
    }

    public Long getId()
    {
        return id;
    }

    public void setId(Long id)
    {
        this.id = id;
    }

    public String getRoleName()
    {
        return roleName;
    }

    public void setRoleName(String roleName)
    {
        this.roleName = roleName;
    }

    public Integer getEnabled()
    {
        return enabled;
    }

    public void setEnabled(Integer enabled)
    {
        this.enabled = enabled;
    }

    public Long getCreateBy()
    {
        return createBy;
    }

    public void setCreateBy(Long createBy)
    {
        this.createBy = createBy;
    }

    public Date getCreateTime()
    {
        return createTime;
    }

    public void setCreateTime(Date createTime)
    {
        this.createTime = createTime;
    }

    @Override
    public String toString()
    {
        return "SysRole{" + "id=" + id + ", roleName='" + roleName + '\'' + ", enabled=" + enabled + ", createBy=" + createBy + ", createTime=" + createTime + '}';
    }
}

SysPrivilege.java

package com.shrimpking.entity;

/**
 * @author user1
 */
public class SysPrivilege
{
    private Long id;
    private String privilegeName;
    private String privilegeUrl;

    public SysPrivilege()
    {
    }

    public SysPrivilege(Long id, String privilegeName, String privilegeUrl)
    {
        this.id = id;
        this.privilegeName = privilegeName;
        this.privilegeUrl = privilegeUrl;
    }

    public Long getId()
    {
        return id;
    }

    public void setId(Long id)
    {
        this.id = id;
    }

    public String getPrivilegeName()
    {
        return privilegeName;
    }

    public void setPrivilegeName(String privilegeName)
    {
        this.privilegeName = privilegeName;
    }

    public String getPrivilegeUrl()
    {
        return privilegeUrl;
    }

    public void setPrivilegeUrl(String privilegeUrl)
    {
        this.privilegeUrl = privilegeUrl;
    }

    @Override
    public String toString()
    {
        return "SysPrivilege{" + "id=" + id + ", privilegeName='" + privilegeName + '\'' + ", privilegeUrl='" + privilegeUrl + '\'' + '}';
    }
}

SysRolePrivilege.java

package com.shrimpking.entity;

public class SysRolePrivilege
{
    private Long roleId;
    private Long privilegeId;

    public SysRolePrivilege()
    {
    }

    public SysRolePrivilege(Long roleId, Long privilegeId)
    {
        this.roleId = roleId;
        this.privilegeId = privilegeId;
    }

    public Long getRoleId()
    {
        return roleId;
    }

    public void setRoleId(Long roleId)
    {
        this.roleId = roleId;
    }

    public Long getPrivilegeId()
    {
        return privilegeId;
    }

    public void setPrivilegeId(Long privilegeId)
    {
        this.privilegeId = privilegeId;
    }

    @Override
    public String toString()
    {
        return "SysRolePrivilege{" + "roleId=" + roleId + ", privilegeId=" + privilegeId + '}';
    }
}

SysUserRole.java

package com.shrimpking.entity;

/**
 * @author user1
 */
public class SysUserRole
{
    private Long userId;

    private Long roleId;

    public SysUserRole()
    {
    }

    public SysUserRole(Long userId, Long roleId)
    {
        this.userId = userId;
        this.roleId = 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;
    }

    @Override
    public String toString()
    {
        return "SysUserRole{" + "UserId=" + userId + ", roleId=" + roleId + '}';
    }
}

=====================================================================

Mapper接口

UserMapper.java

package com.shrimpking.mapper;

import com.shrimpking.entity.SysRole;
import com.shrimpking.entity.SysUser;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

/**
 * @author user1
 */
public interface UserMapper
{
    /**
     * 通过id查询用户
     * @param id 用户id
     * @return 返回一个用户
     */
    SysUser selectById(Long id);

    /**
     * 查询全部用户
     * @return 返回一个用户列表
     */
    List<SysUser> selectAll();

    /**
     * 通过用户id查询角色
     * @param userId 用户id
     * @return 返回角色列表
     */
    List<SysRole> selectRolesByUserId(Long userId);

    /**
     * 插入一个用户
     * @param sysUser 用户
     * @return 返回插入的行数
     */
    int insertUser(SysUser sysUser);

    /**
     * 插入一个用户
     * @param sysUser 用户
     * @return 返回插入的行数
     */
    int insertUser2(SysUser sysUser);

    /**
     * 插入一个用户
     * @param sysUser 用户信息
     * @return 返回插入的行数
     */
    int insertUserOracle(SysUser sysUser);

    /**
     * 更新一个用户
     * @param sysUser 用户信息
     * @return 返回
     */
    int updateUser(SysUser sysUser);

    /**
     * 删除一个用户
     * @param id 用户id
     * @return 返回行数
     */
    int deleteUser(Long id);

    /**
     * 多条件，通过用户id和是否启用，查询角色列表
     * @param userId 用户id
     * @param enabled 是否启用
     * @return 返回角色列表
     */
    List<SysRole> selectRolesByUserIdAndRoleEnables(
            @Param("userId") Long userId,
            @Param("enabled") Integer enabled);

    //======================动态SQL========================

    /**
     * 根据动态sql查询用户
     * @param sysUser
     * @return
     */
    List<SysUser> selectByUser(SysUser sysUser);


    /**
     * 根据主键更新，动态sql的
     * @param sysUser
     * @return
     */
    int updateByIdSelective(SysUser sysUser);

    /**
     * 插入时，使用动态sql
     * @param sysUser
     * @return
     */
    int insertUserIf(SysUser sysUser);

    /**
     * 根据id或用户名查询
     * @param sysUser
     * @return
     */
    SysUser selectByIdOrUserName(SysUser sysUser);


    /**
     * 查询，使用动态sql的where标签
     * @param sysUser
     * @return
     */
    List<SysUser> selectByUserWhere(SysUser sysUser);

    /**
     * 更新，通过动态sql的set标签
     * @param sysUser
     * @return
     */
    int updateByIdSet(SysUser sysUser);


    /**
     * 查询，使用动态sql的foreach标签
     * @param idList
     * @return
     */
    List<SysUser> selectUserListByIdForeach(List<Long> idList);

    /**
     * 更新，通过map方式，使用foreach，
     * @param map
     * @return
     */
    int updateByMap(Map<String,Object> map);
}

RoleMapper.java

package com.shrimpking.mapper;

import com.shrimpking.entity.SysRole;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectKey;
import org.apache.ibatis.annotations.Update;

import java.util.List;

public interface RoleMapper
{
    /**
     * 使用注解方式，查询角色
     * @param id 角色id
     * @return 返回一个角色
     */
    @Select("select id,role_name,enabled,create_by,create_time from sys_role where id=#{id}")
    SysRole selectById(Long id);


    /**
     * 与xml方式中resultmap对应的注解方式，
     * 在mybatis3.3.1版本后，新增id，可以重复利用results给其他，作为注解
     *
     * @param id 新增id，可以赋值后，其他方法可以重复利用
     * @return 返回角色
     *
     */
    @Results(id="roleResultMap",value = {
            @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")
    })
    @Select("select id,role_name,enabled,create_by,create_time from sys_role where id=#{id}")
    SysRole selectById2(Long id);


    /**
     * mybatis3.3.1之后，可以重复利用id标识后的results，
     * @return 返回全部角色
     */
    @ResultMap("roleResultMap")
    @Select("select * from sys_role")
    List<SysRole> selectAll();


    /**
     * 注解方式，不返回数据库自增id的插入
     * @param sysRole 角色
     * @return 返回行数
     */
    @Insert({"insert into sys_role(id,role_name,enabled,create_by,create_time)",
            "values(#{id},#{roleName},#{enabled},#{createBy},#{createTime,jdbcType=TIMESTAMP})"
    })
    int insertRole(SysRole sysRole);


    /**
     * 注解方式，返回插入数据库的自增id，mysql数据库的
     * @param sysRole
     * @return
     */
    @Insert({"insert into sys_role(role_name,enabled,create_by,create_time)",
            "values (#{roleName},#{enabled},#{createBy},#{createTime,jdbcType=TIMESTAMP})"
    })
    @Options(useGeneratedKeys = true,keyProperty = "id",keyColumn = "id")
    int insertRole2(SysRole sysRole);


    /**
     * 注解方式，另一种返回插入数据库的自增id，mysql的
     * @param sysRole
     * @return
     */
    @Insert({"insert into sys_role(role_name,enabled,create_by,create_time)",
            "values (#{roleName},#{enabled},#{createBy},#{createTime,jdbcType=TIMESTAMP})"
    })
    @SelectKey(statement = "SELECT LAST_INSERT_ID()",keyProperty = "id",keyColumn = "id",resultType = Long.class,before = false)
    int insertRole3(SysRole sysRole);

    /**
     * 更新
     * @param sysRole
     * @return
     */
    @Update({"update sys_role",
             "set role_name = #{roleName},",
            "enabled = #{enabled},",
             "create_by = #{createBy},",
             "create_time = #{createTime,jdbcType=TIMESTAMP}",
             "where id=#{id}"
    })
    int updateById(SysRole sysRole);


    @Delete("delete from sys_role where id=#{id}")
    int deleteRole(Long id);
}

PrivilegeMapper.java

package com.shrimpking.mapper;

import com.shrimpking.entity.SysPrivilege;
import com.shrimpking.provider.PrivilegeProvider;
import org.apache.ibatis.annotations.SelectProvider;

/**
 * @author user1
 */
public interface PrivilegeMapper
{
    @SelectProvider(type = PrivilegeProvider.class,method = "select")
    SysPrivilege selectById(Long id);


}

RolePrivilegeMapper.java

package com.shrimpking.mapper;

public interface RolePrivilegeMapper
{
}

UserRoleMapper.java

package com.shrimpking.mapper;

public interface UserRoleMapper
{
}

PrivilegeProvider.java

package com.shrimpking.provider;

import org.apache.ibatis.jdbc.SQL;

public class PrivilegeProvider
{
    public String select(final Long id)
    {
        return  new SQL()
        {
            {
                SELECT("id,privilege_name,privilege_url");
                FROM("sys_privilege");
                WHERE("id = #{id}");
            }
        }.toString();
    }

}

====================================================================

接口实现类

UserMapper.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="com.shrimpking.mapper.UserMapper">

    <!--  具体映射方式  -->
    <resultMap id="userMap" type="com.shrimpking.entity.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>
    <!-- 查询，通过resultmap方式，映射字段  -->
    <select id="selectById" resultMap="userMap">
        select * from sys_user where id = #{id}
    </select>

    <!--
    配置文件开启自动下划线转换驼峰后，可以直接使用这个，返回正常
    <select id="selectById" resultType="com.shrimpking.entity.SysUser">
        select * from sys_user where id = #{id}
    </select>
    -->

    <!--  查询全部用户，通过sql起别名的方式，映射字段  -->
    <select id="selectAll" resultType="com.shrimpking.entity.SysUser">
        select
        id,
        user_name as userName,
        user_password as userPassword,
        user_email as userEmail,
        user_info as userInfo,
        head_img as headImg,
        create_time as createTime
        from sys_user;
    </select>

    <!--  多表关联，但是仅需要返回其中一张表的内容，可以使用此方式  -->
    <select id="selectRolesByUserId" resultType="com.shrimpking.entity.SysRole">
        select
        r.*
        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>

    <!-- 插入，无需返回数据库自增的id值   -->
    <insert id="insertUser" parameterType="com.shrimpking.entity.SysUser">
        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>

    <!-- 插入，mysql数据库方式下，通过id获取插入数据库后的id值   -->
    <insert id="insertUser2" parameterType="com.shrimpking.entity.SysUser" 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数据库方式下，获取返回的id值的另一种方式，order=after，为mysql。order=before，为oracle数据库  -->
    <insert id="insertUserOracle" parameterType="com.shrimpking.entity.SysUser">
        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" resultType="long" keyProperty="id" order="AFTER">
            SELECT LAST_INSERT_ID()
        </selectKey>
    </insert>

    <!--  更新，数据库的值  -->
    <update id="updateUser" parameterType="com.shrimpking.entity.SysUser">
        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>

    <!-- 通过id，删除用户   -->
    <delete id="deleteUser">
        delete from sys_user where id = #{id}
    </delete>

    <!--  多条件，查询角色列表  -->
    <select id="selectRolesByUserIdAndRoleEnables" resultType="com.shrimpking.entity.SysRole">
        select
        r.*
        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}
    </select>

    <!--根据动态sql查询用户-->
    <select id="selectByUser" resultType="com.shrimpking.entity.SysUser">
        select * 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>
    
    <!--动态sql，选择性更新-->
    <update id="updateByIdSelective" parameterType="com.shrimpking.entity.SysUser">
        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>

    <!-- 插入，根据if判断，动态sql   -->
    <insert id="insertUserIf" useGeneratedKeys="true" keyProperty="id">
        insert into sys_user (
        user_name,user_password,
        <if test="userEmail != null and userEmail != ''">
            user_email,
        </if>
        user_info,head_img,create_time)
        values (
        #{userName},#{userPassword},
        <if test="userEmail != null and userEmail != ''">
            #{userEmail},
        </if>
        #{userInfo},#{headImg,jdbcType=BLOB},
        #{createTime,jdbcType=TIMESTAMP}
        )
    </insert>

    <select id="selectByIdOrUserName" resultType="com.shrimpking.entity.SysUser">
        select
        id,
        user_name as userName,
        user_password as userPassword,
        user_email as userEmail,
        user_info as userInfo,
        head_img as headImg,
        create_time as createTime
        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>

    <!-- 查询，使用动态sql的where标签   -->
    <select id="selectByUserWhere" resultType="com.shrimpking.entity.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>

    <!-- 更新，通过动态sql的set标签    -->
    <update id="updateByIdSet">
        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>

    <!--  查询，通过动态sql的foreach标签  -->
    <select id="selectUserListByIdForeach" resultType="com.shrimpking.entity.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>

    <!--更新，通过map方式，foreach    -->
    <update id="updateByMap">
        update sys_user
        set
        <foreach collection="_parameter" index="key" item="val" separator=",">
            ${key} = #{val}
        </foreach>
        where id = #{id}
    </update>
 </mapper>

RoleMapper.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="com.shrimpking.mapper.RoleMapper">

</mapper>

PrivilegeMapper.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="com.shrimpking.mapper.PrivilegeMapper">

</mapper>

RolePrivilegeMapper.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="com.shrimpking.mapper.RolePrivilegeMapper">

</mapper>

UserRoleMapper.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="com.shrimpking.mapper.UserRoleMapper">

</mapper>

=================================================================

测试类代码

UserMapperTest.java

package com.shrimpking;

import com.shrimpking.entity.SysRole;
import com.shrimpking.entity.SysUser;
import com.shrimpking.mapper.UserMapper;
import com.shrimpking.utils.BaseMapperTest;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.ListIterator;
import java.util.Map;

public class UserMapperTest extends BaseMapperTest
{
    @Test
    public void testSelectById()
    {
        SqlSession session = getSqlSession();
        try
        {
            UserMapper mapper = session.getMapper(UserMapper.class);
            SysUser sysUser = mapper.selectById(1L);
            System.out.println(sysUser);
        }
        finally
        {
            session.close();
        }
    }

    @Test
    public void testSelectAll()
    {
        SqlSession sqlSession = getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<SysUser> sysUsers = mapper.selectAll();
        for (SysUser sysUser : sysUsers)
        {
            System.out.println(sysUser);
        }
        sqlSession.close();
    }

    @Test
    public void testSelectRolesByUserId()
    {
        SqlSession sqlSession = getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<SysRole> sysRoles = mapper.selectRolesByUserId(1L);
        for (SysRole sysRole : sysRoles)
        {
            System.out.println(sysRole);
        }
        sqlSession.close();
    }

    @Test
    public void testInsert()
    {
        SqlSession sqlSession = getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        SysUser sysUser = new SysUser();
        sysUser.setId(4L);
        sysUser.setUserName("admin");
        sysUser.setUserPassword("123456");
        sysUser.setUserEmail("xx@xx.com");
        sysUser.setUserInfo("普通用户");
        sysUser.setHeadImg(null);
        sysUser.setCreateTime(new Date());
        int i = mapper.insertUser(sysUser);
        if(i > 0)
        {
            System.out.println("插入成功");
        }
        sqlSession.close();
    }

    @Test
    public void testInsert2()
    {
        SqlSession sqlSession = getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        SysUser sysUser = new SysUser();
        sysUser.setUserName("test");
        sysUser.setUserPassword("123456");
        sysUser.setUserEmail("xx@xx.com");
        sysUser.setUserInfo("普通用户");
        sysUser.setHeadImg(null);
        sysUser.setCreateTime(new Date());
        int i = mapper.insertUser2(sysUser);
        if(i > 0)
        {
            System.out.println("插入成功");
            System.out.println(sysUser.getId());
        }
        sqlSession.close();
    }

    @Test
    public void testInsert3()
    {
        SqlSession sqlSession = getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        SysUser sysUser = new SysUser();
        sysUser.setUserName("auto");
        sysUser.setUserPassword("123456");
        sysUser.setUserEmail("xx@xx.com");
        sysUser.setUserInfo("普通用户");
        sysUser.setHeadImg(null);
        sysUser.setCreateTime(new Date());
        int i = mapper.insertUserOracle(sysUser);
        if(i > 0)
        {
            System.out.println("插入成功");
            System.out.println(sysUser.getId());
        }
        sqlSession.close();
    }

    @Test
    public void testUpdate()
    {
        SqlSession sqlSession = getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        SysUser sysUser = mapper.selectById(8L);
        sysUser.setUserName("update");
        int i = mapper.updateUser(sysUser);
        if(i>0)
        {
            System.out.println("更新成功");
        }
        sqlSession.close();
    }

    @Test
    public void testDelete()
    {
        SqlSession sqlSession = getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int i = mapper.deleteUser(8L);
        if(i > 0)
        {
            System.out.println("删除成功");
        }
        sqlSession.close();
    }

    @Test
    public void testSelectRolesByUserIdAndRoleEnabled()
    {
        SqlSession sqlSession = getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<SysRole> sysRoles = mapper.selectRolesByUserIdAndRoleEnables(1L, 1);
        for (SysRole sysRole : sysRoles)
        {
            System.out.println(sysRole);
        }
        sqlSession.close();
    }


    @Test
    public void testSelectByUser()
    {
        SqlSession sqlSession = getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        //只查询用户名
        SysUser sysUser1 = new SysUser();
        sysUser1.setUserName("ad");
        List<SysUser> sysUsers1 = mapper.selectByUser(sysUser1);
        for (SysUser user1 : sysUsers1)
        {
            System.out.println(user1);
        }
        //只查询邮箱
        SysUser sysUser2 = new SysUser();
        sysUser2.setUserEmail("test@xx.com");
        List<SysUser> sysUsers2 = mapper.selectByUser(sysUser2);
        for (SysUser user2 : sysUsers2)
        {
            System.out.println(user2);
        }
        //用户名和邮箱都查询
        SysUser sysUser3 = new SysUser();
        sysUser3.setUserName("ad");
        sysUser3.setUserEmail("admin@xx.com");
        List<SysUser> sysUsers3 = mapper.selectByUser(sysUser3);
        for (SysUser user3 : sysUsers3)
        {
            System.out.println(user3);
        }
        sqlSession.close();
    }

    @Test
    public void testUpdateByIdSelective()
    {
        SqlSession sqlSession = getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        SysUser sysUser = new SysUser();
        sysUser.setId(7L);
        sysUser.setUserEmail("77@xx.com");
        int i = mapper.updateByIdSelective(sysUser);
        if(i > 0)
        {
            System.out.println("更新成功");
        }
        sqlSession.close();
    }

    @Test
    public void testInsertUserIf()
    {
        SqlSession sqlSession = getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        SysUser sysUser = new SysUser();
        sysUser.setUserName("test-selective");
        sysUser.setUserPassword("123");
        sysUser.setUserInfo("test info");
        sysUser.setCreateTime(new Date());
        int i = mapper.insertUserIf(sysUser);
        if(i > 0)
        {
            System.out.println("插入成功");
        }
        sqlSession.close();
    }

    @Test
    public void testSelectByIdOrUserName()
    {
        SqlSession sqlSession = getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        //只查询id
        SysUser sysUser = new SysUser();
        sysUser.setId(9L);
        SysUser out1 = mapper.selectByIdOrUserName(sysUser);
        System.out.println(out1);
        //只查询用户名
        SysUser sysUser2 = new SysUser();
        sysUser2.setUserName("test-selective");
        SysUser out2 = mapper.selectByIdOrUserName(sysUser2);
        System.out.println(out2);
        //没有id，没有用户名
        SysUser sysUser3 = new SysUser();
        SysUser out3 = mapper.selectByIdOrUserName(sysUser3);
        System.out.println(out3);
        sqlSession.close();
    }

    @Test
    public void testSelectByUserWhere()
    {
        SqlSession sqlSession = getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        //用户名模糊查询
        SysUser sysUser1 = new SysUser();
        sysUser1.setUserName("admin");
        List<SysUser> sysUsers1 = mapper.selectByUserWhere(sysUser1);
        for (SysUser user1 : sysUsers1)
        {
            System.out.println(user1);
        }
        //只查询邮箱
        SysUser sysUser2 = new SysUser();
        sysUser2.setUserEmail("xx@xx.com");
        List<SysUser> sysUsers2 = mapper.selectByUserWhere(sysUser2);
        for (SysUser user2 : sysUsers2)
        {
            System.out.println(user2);
        }
        sqlSession.close();
    }

    @Test
    public void testUpdateByIdSet()
    {
        SqlSession sqlSession = getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        SysUser sysUser = new SysUser();
        sysUser.setId(7L);
        sysUser.setUserInfo("set标签");
        int i = mapper.updateByIdSet(sysUser);
        if(i > 0)
        {
            System.out.println("更新成功");
        }
        sqlSession.close();
    }

    @Test
    public void testSelectUserListByIdForeach()
    {
        SqlSession sqlSession = getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<Long> idList = new ArrayList<Long>();
        idList.add(1L);
        idList.add(3L);
        idList.add(7L);
        List<SysUser> sysUsers = mapper.selectUserListByIdForeach(idList);
        for (SysUser sysUser : sysUsers)
        {
            System.out.println(sysUser);
        }
        sqlSession.close();

    }

    @Test
    public void testUpdateByMap()
    {
        SqlSession sqlSession = getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        Map<String,Object> map = new HashMap<String,Object>();
        map.put("id",9L);
        map.put("user_name","map");
        map.put("user_password","111");
        map.put("user_email","map@xx.com");
        map.put("user_info","用户");
        int i = mapper.updateByMap(map);
        if(i > 0)
        {
            System.out.println("修改成功");
        }
        sqlSession.close();
    }
}

RoleTest.java

package com.shrimpking;

import com.shrimpking.entity.SysRole;
import com.shrimpking.mapper.RoleMapper;
import com.shrimpking.utils.BaseMapperTest;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.Date;
import java.util.List;

public class RoleTest extends BaseMapperTest
{
    @Test
    public void testSelectById()
    {
        SqlSession sqlSession = getSqlSession();
        RoleMapper mapper = sqlSession.getMapper(RoleMapper.class);
        SysRole sysRole = mapper.selectById(1L);
        System.out.println(sysRole);
        sqlSession.close();
    }

    @Test
    public void testSelectById2()
    {
        SqlSession sqlSession = getSqlSession();
        RoleMapper mapper = sqlSession.getMapper(RoleMapper.class);
        SysRole sysRole = mapper.selectById2(1L);
        System.out.println(sysRole);
        sqlSession.close();
    }


    @Test
    public void testSelectAll()
    {
        SqlSession sqlSession = getSqlSession();
        RoleMapper mapper = sqlSession.getMapper(RoleMapper.class);
        List<SysRole> sysRoles = mapper.selectAll();
        for (SysRole sysRole : sysRoles)
        {
            System.out.println(sysRole);
        }
        sqlSession.close();
    }


    @Test
    public void testInsertRole()
    {
        SqlSession sqlSession = getSqlSession();
        RoleMapper mapper = sqlSession.getMapper(RoleMapper.class);
        SysRole sysRole = new SysRole();
        sysRole.setId(3L);
        sysRole.setRoleName("角色");
        sysRole.setEnabled(1);
        sysRole.setCreateBy(1L);
        sysRole.setCreateTime(new Date());
        int i = mapper.insertRole(sysRole);
        if(i > 0)
        {
            System.out.println("插入成功");
        }
        sqlSession.close();
    }

    @Test
    public void testInsertRole2()
    {
        SqlSession sqlSession = getSqlSession();
        RoleMapper mapper = sqlSession.getMapper(RoleMapper.class);
        SysRole sysRole = new SysRole();
        sysRole.setRoleName("返回id");
        sysRole.setEnabled(1);
        sysRole.setCreateBy(1L);
        sysRole.setCreateTime(new Date());
        int i = mapper.insertRole2(sysRole);
        if(i > 0)
        {
            System.out.println("插入成功");
            System.out.println(sysRole.getId());
        }
        sqlSession.close();
    }

    @Test
    public void testInsertRole3()
    {
        SqlSession sqlSession = getSqlSession();
        RoleMapper mapper = sqlSession.getMapper(RoleMapper.class);
        SysRole sysRole = new SysRole();
        sysRole.setRoleName("返回");
        sysRole.setEnabled(1);
        sysRole.setCreateBy(1L);
        sysRole.setCreateTime(new Date());
        mapper.insertRole3(sysRole);
        sqlSession.close();
    }

    @Test
    public void testUpdateById()
    {
        SqlSession sqlSession = getSqlSession();
        RoleMapper mapper = sqlSession.getMapper(RoleMapper.class);
        SysRole sysRole = mapper.selectById(3L);
        sysRole.setRoleName("更新3");
        int i = mapper.updateById(sysRole);
        if(i > 0)
        {
            System.out.println("修改成功");
        }
        sqlSession.close();
    }

    @Test
    public void testDeleteRole()
    {
        SqlSession sqlSession = getSqlSession();
        RoleMapper mapper = sqlSession.getMapper(RoleMapper.class);
        int i = mapper.deleteRole(7L);
        if(i > 0)
        {
            System.out.println("删除成功");
        }
        sqlSession.close();
    }
}

PrivilegeTest.java

package com.shrimpking;

import com.shrimpking.entity.SysPrivilege;
import com.shrimpking.mapper.PrivilegeMapper;
import com.shrimpking.utils.BaseMapperTest;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

public class PrivilegeTest extends BaseMapperTest
{
    @Test
    public void testSelect()
    {
        SqlSession sqlSession = getSqlSession();
        PrivilegeMapper mapper = sqlSession.getMapper(PrivilegeMapper.class);
        SysPrivilege sysPrivilege = mapper.selectById(1L);
        System.out.println(sysPrivilege);
        sqlSession.close();
    }

}

以上是全部。