mybatis(二)使用接口反射实现增删改以及相应的批量操作

sql映射文件的xml

 <!--在crud中,除了查询外其他的类型都无需指定返回的类型-->
    <!--添加操作,不返回id-->
    <insert id="addUser" parameterType="user">
        INSERT INTO user (id, userName, userPwd, realName, nation, cardId) VALUES ( #{id},#{userName},#{userPwd},#{realName},#{nation},#{cardId})
    </insert>
    <!--添加返回id-->
    <!--第一种方法-->
    <insert id="addUserHasKey" parameterType="user">
        <selectKey keyProperty="id" order="AFTER" resultType="int">
            select LAST_INSERT_ID() as id
        </selectKey>
        INSERT INTO user (userName,userPwd,realName,nation,cardId)
        VALUES
        (#{userName},#{userPwd},#{realName},#{nation},#{cardId})
    </insert>
    <!--第二种方法-->
    <insert id="addUserHasKey2" parameterType="user" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO user (userName,userPwd,realName,nation,cardId)
        VALUES
        (#{userName},#{userPwd},#{realName},#{nation},#{cardId})
    </insert>

    <!--批量添加-->
    <insert id="addUserBatch" parameterType="list">
           INSERT INTO user (userName,userPwd,realName,nation,cardId)
        VALUES
        <foreach collection="list" item="item" separator=",">
            (#{item.userName},#{item.userPwd},#{item.realName},#{item.nation},#{item.cardId})
        </foreach>
    </insert>

    <!--单个更新操作-->
    <update id="updateUser" parameterType="user">
      UPDATE user SET userPwd = '10086' WHERE id =#{id}
    </update>
    
    <!--批量的更新操作-->
    <update id="updateUserPwdBatch">
        UPDATE user SET userPwd = '88888' WHERE id IN (
        <foreach collection="array" item="item" separator=",">
            #{item}
        </foreach>
        )
    </update>
    <!--删除操作-->
    <delete id="deleteUser" parameterType="int">
        DELETE FROM user WHERE id=#{id}
    </delete>

    <!--批量删除操作-->
    <delete id="deleteUserBatch" parameterType="list">
        DELETE FROM user WHERE id IN (
        <foreach collection="list" item="item" separator=",">
            #{item}
        </foreach>
        )
    </delete>

接口

    //插入操作
    public Integer addUser(User user);

    //插入返回主键
    public Integer addUserHasKey(User user);

    //批量添加操作
    public Integer addUserBatch(List<User> userList);

    //更新操作
    public Integer updateUser(User user);

    //批量的更新操作
    public Integer updateUserPwdBatch(Integer []ids);

    //删除操作

    public Integer deleteUser(Integer id);
    //批量删除操作
    public Integer deleteUserBatch(List<Integer> id);

测试文件

 @Test
    //插入一条操作
    public void addUser(){
        //Employee employee = new Employee(2,"Tom","1","Tom@qq.com");
        //mapper.addEmp(employee);
        User user1 = new User();
        user1.setId(5);
        user1.setUserName("jdk");
        user1.setUserPwd("123456");
        user1.setRealName("h1");
        user1.setNation("China");
        user1.setCardId(5);
        Integer num = userMapper.addUser(user1);
        System.out.println("num:"+num);

    }

    //插入返回主键
    @Test
    public void addUserHasKey(){
        User user1 = new User();
        user1.setUserName("jdk");
        user1.setUserPwd("123456");
        user1.setRealName("h1");
        user1.setNation("China");
        user1.setCardId(6);
        Integer num = userMapper.addUserHasKey(user1);
        System.out.println("num:"+num);
        System.out.println("id:"+user1.getId());
    }

    //批量添加操作
    @Test
    public void addUserBatch(){
        List<User> userList = new ArrayList<>();
        for (int i=0;i<10;i++){
            User user = new User();
            user.setUserName("jdk_"+i);
            user.setUserPwd("123");
            user.setCardId(10+i);
            user.setNation("china");
            user.setRealName("110");
            userList.add(user);
        }
        Integer num = userMapper.addUserBatch(userList);
        System.out.println("num"+num);
    }

    //更新操作
    @Test
    public void updateUser(){
        User user1 = new User();
        user1.setId(15);
        user1.setUserName("testUpdate");
        user1.setUserPwd("1892");
        Integer num = userMapper.updateUser(user1);
        System.out.println("num:"+num);
    }
    //批量更新操作
    @Test
    public void updateUserPwdBatch(){
        Integer[] ids = new Integer[10];
        for (int i = 0; i < 10; i++) {
            ids[i]=10+i;
        }
        Integer num = userMapper.updateUserPwdBatch(ids);
        System.out.println("num:"+num);
    }
    //删除操作
    @Test
    public void deleteUser(){
        userMapper.deleteUser(10);
    }
    //批量删除操作
    @Test
    public void  deleteUserBatch(){
        List<Integer> ids = new ArrayList<>();
        for (int i=12;i<22;i++){
            ids.add(i);
        }
        userMapper.deleteUserBatch(ids);
    }

注意:当数据库中的字段和编写的po字段不一样的时候,我们常用起别名的方式进行处理,但是现在可以在xml的配置文件中进行声明转换

<resultMap id="userMap" type="user">
        <id column="id" property="id"></id>
        <result column="user_name" property="userName"></result>
        <result column="user_pwd" property="userPwd"></result>
        <result column="real_name" property="realName"></result>
        <result column="card_id" property="cardId"></result>
        <result column="nation" property="nation"></result>
    </resultMap>

sql语句的动态注入:if标签和choose when otherwise
配置文件

  <!-- 根据用户名查询 -->
    <select id="queryUserByName" parameterType="string" resultMap="userMap">
        SELECT * FROM user where 1=1
        <if test="null!=userName and ''!=userName">
            and user_name like concat('%',#{userName},'%')
        </if>
    </select>

    <!-- 根据用户名查询 -->
    <select id="queryUserByNation" parameterType="string" resultMap="userMap">
        SELECT id,
        <choose>
            <when test="null!=nation and ''!=nation">
                real_name, nation
            </when>
            <otherwise>
                user_name
            </otherwise>
        </choose>
         FROM user where 1=1
    </select>

接口的实现,由于放在标签里面,导致无法获取到标签的值,加入注解@Param,声明参数的类型

public interface UserMapper {
    public User queryUserById(Integer id);
    public List<User> queryUserByName(@Param("userName") String userName);
    public List<User> queryUserByNation(@Param("nation") String nation);
}

测试方法

  @Before
    public void init() throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        SqlSession session = sqlSessionFactory.openSession();
        userMapper = session.getMapper(UserMapper.class);
    }

@Test
    public void queryUserByName() throws Exception {
        List<User> userList = userMapper.queryUserByName(" ".trim());
        for (User user : userList){
            System.out.println(user);
        }
    }

    @Test
    public void queryUserByNation() throws Exception {
        List<User> userList = userMapper.queryUserByNation("china");
        for (User user : userList){
            System.out.println(user);
        }
    }