Mybatis学习总结（二）映射器对数据库的操作

mapper映射器对数据库的增删改查

首先，需要添加单元测试的依赖包

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
    </dependency>

接下来，分别在Test.java和User.xml中写如下代码
1）查询数据

//查询数据
    @Test
    public  void find() {
        List<User> objects = sqlSession.selectList("com.njdf.mapper.User.find");
        System.out.println(objects);
    }
   

    <select id="find" resultType="com.njdf.pojo.User">
        select user_id,username from user;
    </select>

2）添加数据

//添加数据
	@Test
    public void add(){
        User user = new User("sy", "123");
        int a = sqlSession.insert("com.njdf.mapper.User.add",user);
        System.out.println(a+"行受影响");
    }

    <select id="add" parameterType="com.njdf.pojo.User">
        insert into user (username,password) values (#{username},#{password});
    </select>

3）在xml中使用like模糊查询

    @Test
    public void selectLike(){
        List<User> list = sqlSession.selectList("com.njdf.mapper.User.selectLike","g");
        for (User u:list) {
            System.out.println(u.toString());
        }
    }

    <select id="selectLike" parameterType="string" resultType="user">
        select username,password from user where password like "%"#{key}"%"
    </select>

4)使用数组来删除数据

    @Test
    public void deleteById(){
        int [] ids = {23,24,25};
        sqlSession.delete("com.njdf.mapper.User.deleteById",ids);
    }

    <select id="deleteById" parameterType="int">
        delete from user where user_id in
        <foreach collection="array" item="ids" open="(" close=")" separator=",">
            #{ids}
        </foreach>
    </select>

5）使用map来删除数据

    @Test
    public void deleteByNAP(){
        Map<String,Object> map = new HashMap<>();
        map.put("key1","sy");
        map.put("key2","ss");
        int a = sqlSession.delete("com.njdf.mapper.User.deleteByNAP",map);
        System.out.println(a+"行受影响");
    }

    <select id="deleteByNAP" parameterType="map">
        delete from user where username=#{key1} and password=#{key2};
    </select>

6）使用动态sql语句查询，if 判断

如果数据库字段为int类型并且值有可能为0，则需要使用Integer类型
如果都是基本数据类型，需要转换成引用类型

    <select id="dyfind" parameterType="user" resultType="user">
        select * from user
        <where>
            <if test="user_name!=null">user_name = #{user_name}</if>
            <if test="password!=null">and password = #{password}</if>
            <!--如果数据库字段为int类型并且值有可能为0，则需要使用Integer类型-->
            <!-- 如果都是基本数据类型，需要转换成引用类型-->
            <if test="dj!=null">and dj = #{dj}</if>
            <if test="bit1!=null">and bit1 = #{bit1}</if>
        </where>
    </select>

7)update更改

    <update id="dyUpdate" parameterType="user">
        update user
        <set>
            <if test="user_name!=null"> user_name = #{user_name}</if>
            <if test="password!=null">,password = #{password}</if>
        </set>
        where id = #{id}
    </update>

8)switch选择

    <!--只需要使用一个标签，如果说这个标签为null，就使用下个标签-->
    <select id="selectByChoose" parameterType="user" resultType="user">
        select * from user
        <where>
            <choose>
                <when test="user_name!=null">user_name = #{user_name}</when>
                <when test="password!=null">and password = #{password}</when>
                <otherwise>and dj = #{dj} </otherwise>
            </choose>
        </where>
    </select>