【Mybatis进行增删改查】

编码流程

• 编写接口
• 编写对应的mapper中的sql语句
• 测试使用
  

编写接口

package com.cao.Dao;

import com.cao.pojo.User;

import java.util.List;

public interface UserMapper {
    //select All user
    List<User> getUserList();
    // select user by id
    User getUserById(int id);
    //insert a user
    int addUser(User user);
    //update a user
    int updateUser(User user);
    //万能map更新数据
    int updateUser2(Map<String,Object> map);
    //delete user
    int deleteUser(int id);
}

编写对应的mapper中的sql语句

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cao.Dao.UserMapper">

    <select id="getUserList" resultType="com.cao.pojo.User">
        select * from mybatis.user
    </select>
    <select id="getUserById" resultType="com.cao.pojo.User" parameterType="int">
        select * from mybatis.user where id = #{id}
    </select>
<!--    对象中的属性可以直接取出-->
    <insert id="addUser" parameterType="com.cao.pojo.User">
        insert into mybatis.user (id,name,pwd) values (#{id},#{name},#{pwd})
    </insert>
    <update id="updateUser" parameterType="com.cao.pojo.User">
        update mybatis.user set name=#{name},pwd =#{pwd} where id =#{id}
    </update>
<!--    传递map的key-->
    <update id="updateUser2" parameterType="map">
        update mybatis.user set name =#{username} where id = #{userid}
    <delete id="deleteUser" parameterType="int">
        delete from mybatis.user where id=#{id}
    </delete>
</mapper>

@Test测试使用

package com.cao.Dao;

import com.cao.pojo.User;
import org.apache.ibatis.session.SqlSession;
import com.cao.utils.mybatisUtils;
import org.junit.jupiter.api.Test;

import java.util.List;

public class UserDaoTest {
    @Test
    public void test1(){
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = userMapper.getUserList();
        for (User user:userList){
            System.out.println(user);
        }
        sqlSession.close();
    }

    @Test
    public void test2(){
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = userMapper.getUserById(3);
        System.out.println(user);
        sqlSession.close();
    }
    //增删改需要提交事务
    @Test
    public void test3(){
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        userMapper.addUser(new User(5,"cao","123456"));
        //提交事务
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    public void test4(){
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        userMapper.updateUser(new User(5,"chang","112233"));
        //提交事务
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    public void test5(){
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        userMapper.deleteUser(1);
        //提交事务
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    public void test6(){
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        HashMap<String, Object> map = new HashMap<String, Object>();
        map.put("userid",5);
        map.put("username","cao");
        userMapper.updateUser2(map);
        //提交事务
        sqlSession.commit();
        sqlSession.close();
    }
}

万能Map

在实体类或者数据库中的表，字段和参数过多时，利用map的键值对，更新某一条数据的时候不需要new一个完整的User，只需要往map中put需要修改的键值对即可。
mapper文件中的sql语句也不需要强制变量名与数据库中的变量名一致。

模糊查询

• java代码执行的时候，传递通配符%

List<User> userList = userMapper.getUserLike("%c%");

• 在sql拼接中使用通配符

select * from mybatis.user where name like "%"#{value}"%"

总结

增删改需要提交事务，sql语句才会生效

        //提交事务
        sqlSession.commit();

多个参数用Map，或者注解。
Map传递参数，直接在sql中取出key即可【parameterType=“map”】

    <update id="updateUser2" parameterType="map">
        update mybatis.user set name =#{username} where id = #{userid}
    </update>

对象传递参数，直接在sql中取出对象的属性即可【parameterType=“com.cao.pojo.User”】

    <update id="updateUser" parameterType="com.cao.pojo.User">
        update mybatis.user set name=#{name},pwd =#{pwd} where id =#{id}
    </update>

只有一个基本类型参数情况下，可以省略【parameterType=“int”】，直接取到

            parameterType="int"可以不写
    <delete id="deleteUser" parameterType="int">
        delete from mybatis.user where id=#{id}
    </delete>```