1整体目录
2.代码
CountryMapper
package com.bai.mybatis.mapper;
public interface CountryMapper {
}
MyMapperProxy
package com.bai.mybatis.mapper;
import org.apache.ibatis.session.SqlSession;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.util.List;
/**
* 动态代理实现
* @param <T>
*/
public class MyMapperProxy<T> implements InvocationHandler {
private Class<T> mapperInterface;
private SqlSession sqlSession;
public MyMapperProxy(Class<T> mapperInterface, SqlSession sqlSession) {
this.mapperInterface = mapperInterface;
this.sqlSession = sqlSession;
}
@Override
public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
//针对不同的sql类型,调用sqlSession的不同方法
//接口方法中的参数也有情况,这里只考虑没有参数的情况
List<T> list = sqlSession.selectList(mapperInterface.getCanonicalName()+"."+method.getName());
//返回值也有很多情况,这里不做处理返回
return list;
}
}
SysUserMapper
package com.bai.mybatis.mapper;
import com.bai.mybatis.model.SysRole;
import com.bai.mybatis.model.SysUser;
import org.apache.ibatis.annotations.Param;
import javax.management.relation.Role;
import java.util.List;
public interface SysUserMapper {
/**
* 根据Id查询user
* @param id
* @return
*/
SysUser selectById(Long id);
/**
* 查询全部用户
* @return
*/
List<SysUser> selectAll();
/**
* 查询某用户都是什么角色
* @return
*/
List<Role> selectRoleByUserId(Long id);
/**
* 插入一条对象
*
* @return
*/
int insert(SysUser user);
int insert2(SysUser user);
/**
* 更新用户
* @param user
* @return
*/
int updataById(SysUser user);
/**
* 删除用户根据Id
* @param id
* @return
*/
int deleteById(Long id);
/**
* 多个接口参数的用法
* @param userId
* @param enable
* @return
*/
List<SysRole> selectRolesByUserIDandRoleEnabled(@Param("userId") Long userId,@Param("enable")Integer enable);
}
Country
package com.bai.mybatis.model;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
@Setter
@Getter
@ToString
public class Country {
private Long id;
private String countryname;
private String counttrycode;
}
SysRole
package com.bai.mybatis.model;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
import java.util.Date;
/**
* 角色表
*/
@Getter
@Setter
@ToString
public class SysRole {
private Long id; //角色ID
private String roleName; //角色名
private int enabled; //有效标志
private Long createBy; //创建人
private Date createTime; //创建时间
private SysUser user;
}
SysUser
package com.bai.mybatis.model;
import lombok.Data;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
import java.util.Date;
/**
* 用户表
*/
@Data
@ToString
public class SysUser {
private Long id; //用户ID
private String userName; //用户名
private String userPassword; //密码
private String userEmail; //邮箱
private String userInfo; //简介
private byte[] headImg; //头像
private Date createTime; //创建时间
}
CountryMapper.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">
<!-- xml根元素,属性namespce 定义了当前xml的命名空间 -->
<mapper namespace="com.bai.mybatis.mapper.CountryMapper">
<!--查询全部国家
select 所定义的一个select 查询
id 属性 定义了当前select 查询的唯一一个id
resultType 定义了当前查询的返回值的类型(此处是指实体类的Country,
mybatis-config.xml 的 typeAliases设置的别名用于这里,没有设置别名,则设置com.bai.mybatis.model.Country)
-->
<select id="selectAll" resultType="Country">
select id,countryname,countrycode from country;
</select>
</mapper>
SysUserMapper.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">
<!-- xml根元素,属性namespce 定义了当前xml的命名空间 -->
<mapper namespace="com.bai.mybatis.mapper.SysUserMapper">
<resultMap id="userMap" type="com.bai.mybatis.model.SysUser">
<id property="id" column="id"></id>
<result property="userPassword" column="user_password"></result>
<result property="userEmail" column="user_email"></result>
<result property="userInfo" column="user_info"></result>
<result property="headImg" column="head_img" jdbcType="BLOB"></result>
<result property="createTime" column="create_time" jdbcType="TIMESTAMP"></result>
</resultMap>
<select id="selectAll" resultType="SysUser">
select id,
user_name,
user_password,
user_email,
user_info,
head_img,
create_time
from sys_user;
</select>
<select id="selectRoleByUserId" resultType="SysRole">
select r.id,r.role_name,r.enabled,r.create_by,r.create_time,
u.user_name as "user.userName",u.user_email as "user.userEmail" from sys_user u
INNER JOIN sys_user_role ur on u.id=ur.user_id
INNER JOIN sys_role r on ur.role_id = r.id
where u.id=#{id}
</select>
<!--
<select id="selectAll" resultMap="SysUser">
select id,
user_name userName
user_password userPassword,
user_email userEmail,
user_info.userInfo,
head_img headImg,
create_time createTime
from sys_user;
</select>-->
<!-- <select> 映射查询语句使用的标签
id: 命名空间中唯一标识符,可以用来代表这条语句
resultMap: 用于设置返回值的类型和映射关系-->
<!-- 同一个命名空间下(namespace)不允许有相同id,但是因为方法是可以重载的,
所以同名方法中其中一个方法增加一个RowBound类型的参数 用于实现分页查询
-->
<!-- 定义的id属性值与接口方法名一致 -->
<select id="selectById" resultMap="userMap">
select * from sys_user where id = #{id}
</select>
<!-- 若想返回插入数据的自增的id 值 。设置useGeneratedKeys="true" keyProperty="id" -->
<!--设置useGeneratedKeys设置true后,mybatis会使用jdbc的getGeneratedKeys方法来取出由数据库内部生成的主键。
获得主键后将其赋值给KeyPropert配置的id属性,
当设置多个属性时,使用逗号分开,这种情况下还需要设置keyColumn属性,按顺序指定数据库的列。
这里的列的值和keyProperty配置的属性一一对应,由于要使用数据库返回的主键值,所以SQL上下两部分的列中去掉了id列和对应的#{id}属性-->
<insert id="insert" 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});
-- DATE 2020-06-09(Date)
-- TIME 21:03:17(Time)
-- TIMESTAMP 2020-06-09 21:05:03.474(Timestamp)
</insert>
<insert id="insert2" >
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});
<!-- resuleType设置返回值,order属性的设置与使用的数据库有关。
在musql中 设置AFTER,因为当前记录的之间值在insert语句中执行成功后才会获得到
在oracle 设置 BEFORE 需要先从序列获取值,然后将值作为主键插入到数据库中(需在sql中写出主键,插入、否则会因为缺失主键而报错)-->
<selectKey keyColumn="id" resultType="long" keyProperty="id" order="AFTER">
SELECT LAST_INSERT_ID()
</selectKey>
<!--其他一些支持主键自增的数据库-
mysql SELECT LAST_INSERT_ID(),
oracle select SEQ_ID.nettval from dual
-->
</insert>
<update id="updataById" >
UPDATE `sys_user` SET `user_name`=#{userName},
`user_password`=#{userPassword}, `user_email`=#{userEmail},
`user_info`=#{userInfo}, `head_img`=#{headImg},
`create_time`=#{createTime} WHERE (`id`=#{id});
</update>
<delete id="deleteById">
delete from sys_user where id=#{id}
</delete>
<select id="selectRolesByUserIDandRoleEnabled" resultType="SysRole">
select r.id,r.role_name,r.enabled,r.create_by,r.create_time,
u.user_name as "user.userName",u.user_email as "user.userEmail" from sys_user u
INNER JOIN sys_user_role ur on u.id=ur.user_id
INNER JOIN sys_role r on ur.role_id = r.id
where u.id=#{userId} and r.enabled=#{enable}
/*若没有param修饰 #{0} #{1} #{param1} #{param2} 按照顺序来修饰参数*/
</select>
</mapper>
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>
<!-- 指定使用log4j输出日志-->
<settings>
<setting name="logImpl" value="LOG4J"/>
<!-- 其他配置 全局变量 作用:自动将以下画线方式命名的数据库列映射到java对象的驼峰式命名属性中-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<!-- 配置了包的别名,使用类时,不需要写包名的部分,只写类就可以-->
<typeAliases>
<package name="com.bai.mybatis.model"/>
</typeAliases>
<!-- 配置了数据库的连接-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC">
<property name="" value=""/>
</transactionManager>
<dataSource type="UNPOOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<!-- 配置了一个包含完整类路径的*.xml sql语句和映射配置文件 -->
<mappers>
<!-- <mapper resource="com/bai/mybatis/mapper/CountryMapper.xml"></mapper>-->
<package name="com/bai/mybatis/mapper"/>
</mappers>
</configuration>
BaseMapperTest
package com.bai.mybatis.mapper;
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;
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();
}
}
CountryMapperTest
package com.bai.mybatis.mapper;
import com.bai.mybatis.model.Country;
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.junit.Test;
import java.io.IOException;
import java.io.Reader;
import java.util.List;
public class CountryMapperTest extends BaseMapperTest {
@Test
public void testSelectAll(){
SqlSession sqlSession = getSqlSession();
try{
List<Country> countryList = sqlSession.selectList("com.bai.mybatis.mapper.CountryMapper.selectAll");
printCountryList(countryList);
}finally {
sqlSession.close();
}
}
private void printCountryList(List<Country> countryList){
for (Country country:countryList) {
System.out.println(country.toString());
}
}
}
SysUserMapperTest
package com.bai.mybatis.mapper;
import com.bai.mybatis.model.Country;
import com.bai.mybatis.model.SysUser;
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.Assert;
import org.junit.BeforeClass;
import org.junit.Test;
import javax.management.relation.Role;
import java.io.IOException;
import java.io.Reader;
import java.lang.reflect.Proxy;
import java.util.Date;
import java.util.List;
public class SysUserMapperTest extends BaseMapperTest {
/**
* 根据ID查询单个用户
*/
@Test
public void testSelectById(){
SqlSession sqlSession = getSqlSession();
try{
SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);
//根据Id查询用户
SysUser sysUser = sysUserMapper.selectById(1l);
//user不为null
Assert.assertNotNull(sysUser);
//userName = admin
Assert.assertEquals("admin", sysUser.getUserName());
}finally {
sqlSession.close();
}
}
/**
* 查询全部用户
*/
@Test
public void testSelectAll(){
SqlSession sqlSession = getSqlSession();
try{
// List<SysUser> userList = sqlSession.selectList("com.bai.mybatis.mapper.SysUserMapper.selectAll");
SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);
//调用selectAll 方法查询所有用户
List<SysUser> userList = sysUserMapper.selectAll();
//结果不为null
Assert.assertNotNull(userList);
//用户数量大于0
Assert.assertTrue(userList.size()>0);
printCountryList(userList);
}finally {
sqlSession.close();
}
}
/**
* 根据用户Id查询用户角色集合
*/
@Test
public void selectRoleBuUserId(){
SqlSession sqlSession = getSqlSession();
try{
// List<SysUser> userList = sqlSession.selectList("com.bai.mybatis.mapper.SysUserMapper.selectAll");
SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);
//调用selectAll 方法查询所有用户
List<Role> roleList = sysUserMapper.selectRoleByUserId(2l);
//结果不为null
Assert.assertNotNull(roleList);
//用户数量大于0
Assert.assertTrue(roleList.size()>0);
}finally {
sqlSession.close();
}
}
@Test
public void testinsert(){
SqlSession sqlSession = getSqlSession();
try {
SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);
//创建一个user对象
SysUser user = new SysUser();
user.setUserName("test1");
user.setUserPassword("123456");
user.setUserEmail("test1@123");
user.setUserInfo("test info");
user.setHeadImg(new byte[]{1,2,3});
user.setCreateTime(new Date());
int result = sysUserMapper.insert2(user);
//插入一条记录
Assert.assertEquals(1,result);
//id 为null,没有给id赋值,并且没有配置回写id的值
System.out.println(user.getId());
Assert.assertNotNull(user.getId());
}finally {
//为了不影响其他数据,这里选择回滚
//由于默认的sqlSessionFactory.openSession()是不自动提交的
//因此不手动执行commit 也不会提交到数据库
sqlSession.commit();
//不要忘记关闭sqlSession
sqlSession.close();
}
}
@Test
public void testupdata(){
SqlSession sqlSession = getSqlSession();
try {
SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);
SysUser user = sysUserMapper.selectById(15l);
Assert.assertEquals("test1",user.getUserName());
user.setUserName("test2");
user.setUserEmail("test1@12344");
int resule = sysUserMapper.updataById(user);
Assert.assertEquals(1,resule);
user = sysUserMapper.selectById(15l);
Assert.assertEquals("test2",user.getUserName());
}finally {
//为了不影响其他数据,这里选择回滚
//由于默认的sqlSessionFactory.openSession()是不自动提交的
//因此不手动执行commit 也不会提交到数据库
sqlSession.commit();
//不要忘记关闭sqlSession
sqlSession.close();
}
}
@Test
public void testDeleteById(){
SqlSession sqlSession = getSqlSession();
try{
SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);
int result = sysUserMapper.deleteById(15l);
Assert.assertEquals(1,result);
}finally {
//为了不影响其他数据,这里选择回滚
//由于默认的sqlSessionFactory.openSession()是不自动提交的
//因此不手动执行commit 也不会提交到数据库
sqlSession.commit();
//不要忘记关闭sqlSession
sqlSession.close();
}
}
@Test
public void testselectRoleByUserId(){
SqlSession sqlSession = getSqlSession();
try {
SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);
sysUserMapper.selectRolesByUserIDandRoleEnabled(1l,1);
}finally {
sqlSession.close();
}
}
/**
* 动态代理测试
*/
@Test
public void dynamicAgent(){
SqlSession sqlSession = getSqlSession();
//获取UserMapper接口
MyMapperProxy userMapperProxy = new MyMapperProxy(SysUserMapper.class,sqlSession);
SysUserMapper sysUserMapper = (SysUserMapper) Proxy.newProxyInstance(
Thread.currentThread().getContextClassLoader(),
new Class[]{SysUserMapper.class},userMapperProxy);
//调用selectAllf方法
List<SysUser> user = sysUserMapper.selectAll();
}
private void printCountryList(List<SysUser> userList){
for (SysUser user:userList) {
System.out.println(user.toString());
}
}
}
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.bai.mybatis</groupId>
<artifactId>mybatisconfigfile</artifactId>
<version>1.0-SNAPSHOT</version>
<!-- <properties>-->
<!-- <project.></project.>-->
<!-- </properties>-->
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<!--lombok是一个帮助简化代码的工具 -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.18</version>
</dependency>
<!-- 日志-->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<!-- 测试-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.17</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>1.8</source>
<target>1.8</target>
</configuration>
</plugin>
</plugins>
</build>
</project>
3.数据库
DROP TABLE IF EXISTS `country`;
CREATE TABLE `country` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`countryname` varchar(255) DEFAULT NULL,
`countrycode` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
INSERT INTO `country` VALUES ('1', '中国', 'CN');
INSERT INTO `country` VALUES ('2', '美国', 'US');
INSERT INTO `country` VALUES ('3', '俄罗斯', 'RU');
INSERT INTO `country` VALUES ('4', '英国', 'GB');
INSERT INTO `country` VALUES ('5', '法国', 'FR');
DROP TABLE IF EXISTS `sys_role`;
CREATE TABLE `sys_role` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '角色ID',
`role_name` varchar(50) DEFAULT NULL COMMENT '角色名',
`enabled` int(11) DEFAULT NULL COMMENT '有效标志',
`create_by` bigint(20) DEFAULT NULL COMMENT '创建人',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO `sys_role` VALUES ('1', '管理员', '1', '1', '2020-06-03 20:46:27');
INSERT INTO `sys_role` VALUES ('2', '普通用户', '0', '1', '2020-07-01 20:46:45');
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
`user_name` varchar(50) DEFAULT NULL COMMENT '用户名',
`user_password` varchar(50) DEFAULT NULL COMMENT '密码',
`user_email` varchar(50) DEFAULT NULL COMMENT '邮箱',
`user_info` text COMMENT '简介',
`head_img` blob COMMENT '头像',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
INSERT INTO `sys_user` VALUES ('1', 'admin', '123456', 'admin@email', '管理员', null, '2020-06-03 20:45:14');
INSERT INTO `sys_user` VALUES ('2', 'test', '123456', 'test@email·', '测试用户', null, '2020-06-02 20:46:00');
DROP TABLE IF EXISTS `sys_user_role`;
CREATE TABLE `sys_user_role` (
`user_id` bigint(20) NOT NULL COMMENT '用户ID',
`role_id` bigint(20) DEFAULT NULL COMMENT '角色ID'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `sys_user_role` VALUES ('1', '1');
INSERT INTO `sys_user_role` VALUES ('1', '2');
INSERT INTO `sys_user_role` VALUES ('1001', '2');
4.百度网盘地址:
链接:https://pan.baidu.com/s/1JRptpwi_zK1QYm1XewIc6g
提取码:pg55