【Mybatis】（五）执行CRUD操作

<?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>

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);
}

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();
    }
}