mybatis对数据库的常用操作

注意

重点是比如UsersMapper.xml映射文件的内容。

查询

    <select id="selectById" parameterType="int" resultType="com.me.pojo.Users">
        select * from users where id=#{id}
    </select>

    <!--${}字符串拼接，不能防止sql注入-->
    <select id="selectByUserName" parameterType="String" resultType="com.me.pojo.Users">
        /*传参为‘李%’*/
        select * from users where username like #{value}
        /*传参为‘李’*/
        /*select * from users where username like '${value}%'*/
    </select>

测试方法

    @Test
    public void selectByUserName(){
        SqlSession sqlSession=sqlSessionFactory.openSession();
        List<Users> usersList=sqlSession.selectList("UsersMapper.selectByUserName","李%");
        /*List<Users> usersList=sqlSession.selectList("UsersMapper.selectByUserName","李");*/
        System.err.println(usersList);
        /*System.out.println(usersList.get(0).getUsername());
        System.out.println(usersList.get(1).getUsername());*/
    }

新增

<!--添加用户信息-->
    <insert id="addUser" parameterType="com.me.pojo.Users">
        insert into users(id,username,password,address,birthday,sex) values(#{id},#{username},
        # {password},#{address},#{birthday},#{sex})
    </insert>

    <insert id="addUser2" parameterType="com.me.pojo.Users" keyProperty="id" useGeneratedKeys="true">
        insert into users(id,username,password,address,birthday,sex) values(#{id},#{username},# 
        {password},#{address},#{birthday},#{sex})
    </insert>

测试方法

    @Test
    public void addUser(){
        SqlSession sqlSession=sqlSessionFactory.openSession();
        Users users=new Users(4,"王五","123",new Date(),"男","北京");
        int res=sqlSession.insert("UsersMapper.addUser",users);
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void addUser2(){
        SqlSession sqlSession=sqlSessionFactory.openSession();
        Users user=new Users(5,"王五","123",new Date(),"男","北京");
        int res=sqlSession.insert("UsersMapper.addUser",user);
        System.err.println("res:"+res);
        System.err.println("users.getId:"+user.getId());
        sqlSession.commit();
        sqlSession.close();
    }

修改

    <update id="updateUser" parameterType="com.me.pojo.Users">
        update users set password=#{password},address=#{address} where id=#{id}
    </update>

测试方法

    @Test
    public void updateUser(){
        SqlSession sqlSession=sqlSessionFactory.openSession();
        Users user=new Users();
        user.setPassword("newPass123456");
        user.setAddress("济南");
        user.setId(4);
        int res=sqlSession.update("UsersMapper.updateUser",user);
        sqlSession.commit();
        sqlSession.close();
    }

删除

    <delete id="deleteUser" parameterType="int">
        /*这里是简单类型，# { value } 中的value可以任意写*/
        delete from users where id=#{id}
    </delete>

测试方法

    @Test
    public void deleteUser(){
        SqlSession sqlSession=sqlSessionFactory.openSession();
        int res=sqlSession.delete("UsersMapper.deleteUser",5);
        System.err.println("res:"+res);
        sqlSession.commit();
        sqlSession.close();
    }