Mybatis用户添加，删除，更新，查询

目录

1.UserMapper.xml

2.UserMapper接口

3.测试类

---

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="com.igeek.ch01.UserMapper">

    <!--
        需求1：根据id查询用户信息
        Mapped Statement语句 select标签
        1.id属性：当前namespace下的唯一标识，同一个namespace下不可以重复
        2.parameterType属性：输入参数映射
            2.1 简单数据类型：基本数据类型、String类型、包装器类型
                ${key}
                    1.类似于Statement语句中拼接SQL
                    2.带来SQL注入问题，引发数据不安全
                    3.${key}的key，建议使用value，即${value}
                #{key}
                    1.类似于PreparedStatement语句中的?占位符
                    2.避免SQL注入问题
                    3.#{key}中的key，建议字段名称一致，即#{id}
            2.2 HashMap类型：hashmap<KEY,VALUE>
            2.3 pojo类型：类似于User、UserQueryVO等
        3.resultType属性：输出结果映射
            3.1 简单数据类型：基本数据类型、String类型、包装器类型
            3.2 pojo类型：类似于User、UserQueryVO等
    -->
    <select id="selectUserById" parameterType="int" resultType="User">
        select * from user where id = #{id}
    </select>

    <!--
        需求2：根据名称模糊查询用户列表
        ${key}  select * from user where name like ${value}
        1.类似于Statement语句中拼接SQL
        2.带来SQL注入问题，引发数据不安全
        3.${key}的key，建议使用value，即${value}

        #{key}  select * from user where name like concat('%',#{name},'%')
        1.类似于PreparedStatement语句中的?占位符
        2.避免SQL注入问题
        3.#{key}中的key，建议字段名称一致，即#{id}
    -->
    <select id="selectUsersByLikeName" parameterType="string" resultType="User">
        select * from user where name like concat('%',#{name},'%')
    </select>


    <!--
        需求3：插入用户信息
        parameterType输入参数映射
            2.3 pojo类型：类似于User、UserQueryVO等
            #{Key} 此时Key，必须与parameterType指定的数据类型中的属性名称一致
    -->
    <insert id="insertUser" parameterType="User">
        <selectKey order="AFTER" keyColumn="id" keyProperty="id" resultType="int">
            select last_insert_id()
        </selectKey>
        insert into user values(null,#{name} , #{age} , #{gender} , #{birthday} , null , #{pwd} , '0')
    </insert>

    <!--
        作业：
        1.更新用户信息
        2.删除用户信息
    -->

    <update id="updateUser" parameterType="User" >
        update user set name=#{name},age=#{age}
        where id = #{id};
    </update>

    <delete id="deleteUser" parameterType="int">
        delete from user where id=#{id}
    </delete>
</mapper>

2.UserMapper接口

package com.igeek.ch01;

import com.igeek.pojo.User;

import java.util.List;

/**
 * XxxMapper接口与XxxMapper.xml映射文件  生成接口的代理对象
 * 1.XxxMapper接口名称与XxxMapper.xml名称一致
 * 2.XxxMapper.xml中namespace属性，必须是XxxMapper接口的全类名
 * 3.XxxMapper接口中的方法的方法名，必须与XxxMapper.xml中SQL的id标识一致
 * 4.XxxMapper接口中的方法的形参类型，必须与XxxMapper.xml中SQL的parameterType类型一致
 * 5.XxxMapper接口中的方法的返回值类型，必须与XxxMapper.xml中SQL的resultType类型一致
 * 6.XxxMapper接口与XxxMapper.xml映射文件在同包下  （不重要）
 * 7.在SqlMapConfig.xml核心配置文件中，添加扫描
 */
public interface UserMapper {

    //根据id查询用户信息
    public User selectUserById(int id);

    //根据名称模糊查询用户列表
    public List<User> selectUsersByLikeName(String name);

    //插入用户信息
    public int insertUser(User user);

    //更新用户信息
    public int updateUser(User user);

    public int deleteUser(int id);
}

3.测试类

package com.igeek.ch01;

import com.igeek.pojo.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.time.LocalDate;
import java.util.List;

import static org.junit.Assert.*;

public class UserMapperTest {

    //会话工厂
    private SqlSessionFactory sqlSessionFactory;
    //不可以定义为成员变量，引发线程安全的问题
    //private SqlSession sqlSession;

    @Before
    public void up() throws IOException {
        //1.加载MyBatis核心配置文件
        InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
        //2.创建会话工厂对象
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
    }

    @Test
    public void selectUserById() {
        //3.创建会话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //4.获取UserMapper接口的代理对象
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        System.out.println(mapper.getClass().getName());
        //5.执行查询
        User user = mapper.selectUserById(1);
        System.out.println(user);
        //6.关闭资源
        sqlSession.close();
    }


    @Test
    public void selectUsersByLikeName(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        //测试#{Key}
        //List<User> users = mapper.selectUsersByLikeName("张");

        //测试${Key}
        //List<User> users = mapper.selectUsersByLikeName("'%张%'");
        //测试SQL注入问题
        List<User> users = mapper.selectUsersByLikeName("'' or 1=1 -- '");

        System.out.println(users.size());
        System.out.println(users);
        sqlSession.close();
    }


    @Test
    public void insertUser(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = new User("张三哈",18 ,"男" , LocalDate.of(2005 , 6 , 8) , "123");
        int i = mapper.insertUser(user);
        System.out.println(i>0?"插入成功":"插入失败");
        System.out.println("用户编号："+user.getId());
        //提交事务
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void updateUser() {

        //1. 会话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //2.代理duixiang
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        //3.执行语句

        User user = new User();
        user.setId(1);
        user.setName("李国勇");
        user.setAge(23);
        int i = mapper.updateUser(user);
        System.out.println(i);


        sqlSession.commit();
        sqlSession.close();



    }

    @Test
    public void deleteUser() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int i = mapper.deleteUser(10);
        System.out.println(i);
        sqlSession.commit();
        sqlSession.close();
    }
}