03 增删改查入门

1 全局配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="db.properties"></properties>

    <settings>
        <!--开启驼峰命名-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    <!--配置别名-->
    <typeAliases>
        <package name="study.wyy.mybatis.helloworld.model"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <!--指定事务管理器类型-->
            <transactionManager type="JDBC"/>
            <!--配置datasource-->
            <dataSource type="POOLED">
                <!--配置数据库链接驱动-->
                <property name="driver" value="${jdbc.driver}"/>
                <!--配置数据库链接地址-->
                <property name="url" value="${jdbc.url}"/>
                <!--配置数据库链用户名-->
                <property name="username" value="${jdbc.username}"/>
                <!--配置数据库链接密码-->
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>

    <!--多数据库支持-->
    <databaseIdProvider type="DB_VENDOR">
        <property name="SQL Server" value="sqlserver"/>
        <property name="DB2" value="db2"/>
        <property name="Oracle" value="oracle" />
        <property name="MySQL" value="mysql" />
    </databaseIdProvider>
    <!--注册映射文件-->
    <mappers>
        <mapper resource="mapper/TbUserMapper.xml"/>
    </mappers>
</configuration>

db.properties文件：

jdbc.url=jdbc:mysql://localhost:3306/ms
jdbc.driver=com.mysql.jdbc.Driver
jdbc.username=root
jdbc.password=rootroot

2 Mapper接口和sql映射

package study.wyy.mybatis.helloworld.mapper;

import study.wyy.mybatis.helloworld.model.TbUser;

import java.util.Map;

/**
 * @author by wyaoyao
 * @Description
 * @Date 2021/1/30 9:58 下午
 */
public interface TbUserMapper {

    /**
        根据id查询
     */
    TbUser selectById(Long id);

    /****
     * 插入数据
     * @param user
     * @return 返回值是影响的行数
     */
    Integer insert(TbUser user);

    /****
     * 根据ID更新
     * @param user
     * @return 返回值是影响的行数
     */
    Integer updateById(TbUser user);

    /****
     * 根据ID删除
     * @param id
     * @return 返回值是影响的行数
     */
    Integer deleteById(Long id);


    /****
     * 统计 统计指定条件的数量
     * @param map: 查询条件
     * @return 返回值是数据总量
     */
    Long count(Map map);
    
}

<?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">

<!--
    namespace: 名称空间
    id: 唯一标识
    resultType： 返回值类型，返回数据库的数据的数据呗，而我们已经构建了一个实体来对应数据库表，所以返回值类型，就是对应实体的全类名
-->
<mapper namespace="study.wyy.mybatis.helloworld.mapper.TbUserMapper">
    <select id="selectById" resultType="study.wyy.mybatis.helloworld.model.TbUser">
        select `id`, `username`, `password`, `phone`, `create_time` as `createTime`, `update_time` as `updateTime`
        from tb_user where id = #{id}
    </select>

    <!--
           parameterType: 指定查询参数类型，已经配置了别名，所以可以直接使用别名，而不需要全类名
     -->
    <insert id="insert" parameterType="tbUser">
        insert into 
            tb_user(`username`, `password`, `phone`,`create_time`,`update_time`)
        values (#{username},#{password},#{phone},now(),now())
    </insert>

    <!--
        parameterType: 指定查询参数类型，已经配置了别名，所以可以直接使用别名，而不需要全类名
    -->
    <update id="updateById" parameterType="tbUser">
        update tb_user
        set
            `username` = #{username},
            `password` = #{password},
            `phone` = #{phone},
            `update_time` = now()
        where
            id = #{id}
    </update>
    
    <delete id="deleteById" parameterType="long">
        delete from
            tb_user
        where
            `id` = #{id}
    </delete>
    <!--
           parameterType: 指定查询参数类型，
            mybatis为java常用类型都内置了别名，比如map,list
       -->
    <select id="count" parameterType="map" resultType="long">
        select
            count(1)
        from
            tb_user
        where
            `id` = #{id} and
            `username` = #{username} and
            `phone` = #{phone}
    </select>
</mapper>

测试

package study.wyy.mybatis.helloworld.test;

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.Assert;
import org.junit.Test;
import study.wyy.mybatis.helloworld.mapper.TbUserMapper;
import study.wyy.mybatis.helloworld.model.TbUser;

import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Map;


public class CrudTest {
    @Test
    public  void testInsert() throws IOException {
        String resource = "mybatis-config.xml";
        // 1 加载配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream(resource);
        // 2 构建SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        // 3 获取session
        SqlSession sqlSession = sqlSessionFactory.openSession();

        // 4 获取mapper接口的代理对象
        TbUserMapper tbUserMapper = sqlSession.getMapper(TbUserMapper.class);
        // 插入
        TbUser insert = new TbUser();
        insert.setUsername("t-mac");
        insert.setPassword("123456");
        insert.setPhone("13011112222");
        Integer row = tbUserMapper.insert(insert);
        System.out.println("row======>" + row);
        // 不要忘记提交
        sqlSession.commit();
        // 查询
        TbUser selectResult = tbUserMapper.selectById(2L);
        System.out.println(selectResult);
    }

    @Test
    public  void testUpdate() throws IOException {
        String resource = "mybatis-config.xml";
        // 1 加载配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream(resource);
        // 2 构建SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        // 3 获取session
        SqlSession sqlSession = sqlSessionFactory.openSession();

        // 4 获取mapper接口的代理对象
        TbUserMapper tbUserMapper = sqlSession.getMapper(TbUserMapper.class);
        // 查询
        TbUser selectResult = tbUserMapper.selectById(2L);
        System.out.println(selectResult);
        // 修改这条记录的username
        selectResult.setUsername("wade");
        Integer row = tbUserMapper.updateById(selectResult);
        System.out.println("row======>" + row);
        // 不要忘记提交
        sqlSession.commit();
        // 查询
        selectResult = tbUserMapper.selectById(2L);
        Assert.assertEquals("wade",selectResult.getUsername());
    }

    @Test
    public  void testCount() throws IOException {
        String resource = "mybatis-config.xml";
        // 1 加载配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream(resource);
        // 2 构建SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        // 3 获取session
        SqlSession sqlSession = sqlSessionFactory.openSession();

        // 4 获取mapper接口的代理对象
        TbUserMapper tbUserMapper = sqlSession.getMapper(TbUserMapper.class);
        // 查询
        TbUser selectResult = tbUserMapper.selectById(2L);
        System.out.println(selectResult);
        Map<String,Object> map = new HashMap();
        map.put("id",selectResult.getId());
        map.put("username",selectResult.getUsername());
        map.put("phone",selectResult.getPhone());
        Long count = tbUserMapper.count(map);
        Assert.assertEquals(Long.valueOf(1L),count);
    }

    @Test
    public  void testDel() throws IOException {
        String resource = "mybatis-config.xml";
        // 1 加载配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream(resource);
        // 2 构建SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        // 3 获取session
        SqlSession sqlSession = sqlSessionFactory.openSession();

        // 4 获取mapper接口的代理对象
        TbUserMapper tbUserMapper = sqlSession.getMapper(TbUserMapper.class);
        // 查询
        TbUser selectResult = tbUserMapper.selectById(2L);
        // 删除
        tbUserMapper.deleteById(selectResult.getId());
        sqlSession.commit();
        // 统计
        Map<String,Object> map = new HashMap();
        map.put("id",selectResult.getId());
        map.put("username",selectResult.getUsername());
        map.put("phone",selectResult.getPhone());
        Long count = tbUserMapper.count(map);
        Assert.assertEquals(Long.valueOf(0L),count);
    }
}

3 注意

1. SqlSession不会自动提交，需要手动提交：

sqlSession.commit();

2. 可以开启自动提交

 // 3 获取session
SqlSession sqlSession = sqlSessionFactory.openSession(true);