新建一个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&useUnicode=true&characterEncoding=UTF-8&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();
}
}
以上是全部。