文章目录
引言
1、定义UserMapper.xml
2、定义UserMapper接口
3、单元测试类
引言
经过第一章节【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(); } }
