文章目录
引言
经过第一章节【Mybatis】（一）MyBatis入门 & 第一个MyBatis实例 ，我们已经初步搭建了 MyBatis 框架，实现了查询所有记录的功能，并用 JUnit 进行了单元测试，接下来我们将在此基础上使用基于XML的方式对表进行CRUD操作。
1、定义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="cn.kgc.dao.user.UserMapper">
<!--
namespace:名称空间;指定为接口的全类名
id：唯一标识，此时与接口的方法名相对应（getEmpById）
resultType：返回值类型
#{id}：从传递过来的参数中取出id值
public Employee getEmpById(Integer id);
-->
<select id="login" resultType="user" parameterType="string">
select u.*
from smbms_user u
left join smbms_address ad
on u.id=ad.userId
left join smbms_role ro
on u.userRole=ro.id
where userCode=#{userCode}
and userPassword=#{userPassword}
</select>
<!--共用结果集-->
<resultMap id="userDetail" type="user">
<id property="id" column="id"/>
<result property="userCode" column="userCode"/>
<result property="userName" column="userName"/>
<result property="gender" column="gender"/>
<result property="birthday" column="birthday"/>
<result property="phone" column="phone"/>
<result property="address" column="address"/>
<association property="role" javaType="role">
<id property="id" column="id"/>
<result property="roleName" column="roleName"/>
</association>
<association property="addr" javaType="address">
<id property="id" column="id"/>
<result property="tel" column="tel"/>
</association>
</resultMap>
<!--详情页面展示-->
<select id="userList" resultMap="userDetail" >
select u.*,r.*
from smbms_user u
left join
smbms_role r
on u.userRole=r.id
</select>
<!--根据用户查询详情-->
<select id="getUserListByName" resultMap="userDetail" parameterType="string">
select u.*,r.*
from smbms_user u
left join
smbms_role r
on u.userRole=r.id
where u.userName like concat("%",#{uname},"%")
</select>
<!--根据职位名称查询详情-->
<select id="getUserListByRoleName" resultMap="userDetail" parameterType="string">
select u.*,r.*roleName
from smbms_user u
left join
smbms_role r
on u.userRole=r.id
where r.roleName=#{roleName}
</select>
<!--根据用户名和用户角色查询详情-->
<select id="getUserByNameAndRole" resultMap="userDetail" parameterType="string">
select u.*,r.roleName
from smbms_user u
left join
smbms_role r
on u.userRole=r.id
<where>
<if test="roleName !=null">
r.roleName=#{roleName}
</if>
<if test="userName!=null">
and u.userName like concat("%",#{userName},"%")
</if>
</where>
</select>
<!--用户信息修改-->
<update id="modifyUserById" parameterType="user">
update smbms_user set
userName=#{userName},
gender=#{gender},
birthday=#{birthday},
phone=#{phone},address=#{address},
userRole=(
select id from smbms_role
where roleName=#{userRoleName}
)
where id=#{id}
</update>
<!--新增用户-->
<insert id="addUser" parameterType="user">
insert into smbms_user
(userCode,userName,userPassword,gender,
birthday,phone,address,userRole,createdBy,creationDate)
values (
#{userCode},#{userName},#{userPassword}, #{gender}, #{birthday},#{phone}, #{address},
(select id from smbms_role where roleName =#{userRoleName}),
#{createdBy},#{creationDate}
)
</insert>
<!--删除用户-->
<delete id="deleteUserById" parameterType="int">
delete from smbms_user where id=#{id}
</delete>
</mapper>
2、定义UserMapper接口
package cn.kgc.dao.user;
import cn.kgc.entity.User;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface UserMapper {
User login(User user);
List<User> userList();
List<User> getUserListByName(String uame);
List<User> getUserListByRoleName(String roleName);
void modifyUserById(User user);
void addUser(User user);
void deleteUserById(int id);
List<User> getUserByNameAndRole(@Param(value = "userName") String userName,@Param(value = "roleName") String roleName);
}
3、单元测试类
package cn.kgc.dao.user;
import cn.kgc.entity.User;
import cn.kgc.util.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.log4j.Logger;
import org.junit.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.logging.SimpleFormatter;
public class UserMapperTest {
Logger logger=Logger.getLogger(UserMapperTest.class);
@Test
public void test(){
//测试方法
SqlSessionFactory factory=null;
SqlSession session=null;
int count=0;
//1.读取mybatis配置，转成io流
// InputStream is= Resources.getResourceAsStream("mybatis-config.xml");
//2.利用io流对factory对象赋值
// factory= new SqlSessionFactoryBuilder().build(is);
//3.由工厂创建SqlSession对象
session= MyBatisUtil.getSession();
//由session对象进行语句查询
// 调用getMapper(UserMapper.class)执行UserMapper接口方式
count=session.getMapper(UserMapper.class).cnt();
// count=session.selectOne("cn.kgc.dao.user.UserMapper.cnt");
MyBatisUtil.closeSession();
logger.info(count);
}
@Test
public void login(){
SqlSession session=MyBatisUtil.getSession();
User user=new User();
user.setUserCode("yangguo");
user.setUserPassword("0000000");
user=session.getMapper(UserMapper.class).login(user);
logger.info(user);
logger.info(user.getAge());
MyBatisUtil.closeSession();
}
@Test
public void userList(){
SqlSession session=MyBatisUtil.getSession();
List<User> users=session.getMapper(UserMapper.class).userList();
logger.info(users);
MyBatisUtil.closeSession();
}
@Test
public void getUserListByName(){
SqlSession session=MyBatisUtil.getSession();
List<User> users=session.getMapper(UserMapper.class).getUserListByName("赵");
logger.info(users);
MyBatisUtil.closeSession();
}
@Test
public void getUserListByRoleName(){
SqlSession session=MyBatisUtil.getSession();
List<User> list=session.getMapper(UserMapper.class).getUserListByRoleName("经理");
logger.info(list);
MyBatisUtil.closeSession();
}
@Test
public void modifyUserById() {
SqlSession session=MyBatisUtil.getSession();
User user=new User();
user.setId(15);
user.setUserName("赵敏");
user.setGender(1);
try {
user.setBirthday(new SimpleDateFormat("yyyy-mm-dd").parse("1987-12-12"));
} catch (ParseException e) {
e.printStackTrace();
}
user.setPhone("18136249881");
user.setAddress("北京市昌平区");
session.getMapper(UserMapper.class).modifyUserById(user);
MyBatisUtil.closeSession();
}
@Test
public void addUser(){
SqlSession session=MyBatisUtil.getSession();
User user=new User();
user.setUserCode("sun");
user.setUserName("小孙");
user.setUserPassword("1111111");
user.setGender(1);
user.setCreatedBy(1);
user.setUserRoleName("普通员工");
try {
user.setBirthday(new SimpleDateFormat("yyyy-mm-dd").parse("2000-12-12"));
} catch (ParseException e) {
e.printStackTrace();
}
user.setPhone("18136649881");
user.setAddress("北京市昌平区老钢厂");
session.getMapper(UserMapper.class).addUser(user);
MyBatisUtil.closeSession();
}
@Test
public void deleteUserById(){
SqlSession session=MyBatisUtil.getSession();
session.getMapper(UserMapper.class).deleteUserById(16);
MyBatisUtil.closeSession();
}
@Test
public void getUserByNameAndRole(){
SqlSession session=MyBatisUtil.getSession();
List<User> list=session.getMapper(UserMapper.class).getUserByNameAndRole("赵","经理");
logger.info(list);
MyBatisUtil.closeSession();
}
}
