基础的mybatis学习

　　git：https://gitee.com/juncaoit/xdmybatis

一：普通的jdbc

1.添加pom

        <!--jdbc-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

2.jdbc代码

package com.jun.basic.controller;

import com.fasterxml.jackson.core.JsonProcessingException;
import org.junit.Test;

import java.sql.*;

public class JdbcTest {
    /**
     * mysql的jdbc测试
     */
    @Test
    public void test1() throws ClassNotFoundException, SQLException, JsonProcessingException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://127.0.0.1:3306/center?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT";
        String useName = "root";
        String pwd = "123456";
        Connection connection = DriverManager.getConnection(url, useName, pwd);
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("select  * from video");
        while (resultSet.next()) {
            System.out.println("resultSet=" + resultSet.getString("title"));
        }
        statement.close();
    }
}

二：简单原理

1.pom

        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.9</version>
        </dependency>

2.工作流程

　　

3.添加mybatis-config.xml

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

　　　　<!--驼峰-->
　　　　<settings>
    　　　　<setting name="mapUnderscoreToCamelCase" value="true"/>
　　　　</settings>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://127.0.0.1:3306/center?useUnicode=true&amp;characterEncoding=utf-8&amp;useSSL=false&amp;serverTimezone=GMT"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="mapper/VideoMapper.xml"/>
    </mappers>
</configuration>

其中：VideoMapper.xml

存在resultType。

<?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.jun.xdmybatis.domain.VideoMapper">


    <!--
    statement sql
    id： 当前mapper下需要唯一
    resultType : sql查询结果集的封装
    -->
    <select id="selectById" resultType="com.jun.xdmybatis.dao.Video">

        select * from video where id = #{id}

    </select>
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 

</mapper>

其中，VideoMapper.class

package com.jun.xdmybatis.domain;

import com.jun.xdmybatis.dao.Video;
import org.apache.ibatis.annotations.Param;

public interface VideoMapper {
    Video selectById(@Param("id") int id);
}

4.使用

public class XdMybatis {
    public static void main(String[] args) throws IOException {
        String resource = "config/mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
            final VideoMapper mapper = sqlSession.getMapper(VideoMapper.class);
            final Video video = mapper.selectById(30);
            log.info("video={}", JsonUtils.toJsonString(video));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

5.入参的说明

　　paramterType的参数类型

　　　　可以是基本类型，如果是一个参数，可以不写，如果是两个，则不好写，也可以不写

　　　　可以是java集合

　　　　可以是对象

　　　　

三：增删改查

1.表结构

CREATE TABLE `video` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(524) DEFAULT NULL COMMENT '视频标题',
  `summary` varchar(1026) DEFAULT NULL COMMENT '概述',
  `cover_img` varchar(524) DEFAULT NULL COMMENT '封面图',
  `price` int(11) DEFAULT NULL COMMENT '价格,分',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `point` double(11,2) DEFAULT '8.70' COMMENT '默认8.7，最高10分',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=48 DEFAULT CHARSET=utf8;

2.java

package com.jun.xdmybatis;

import com.jun.xdmybatis.dao.Video;
import com.jun.xdmybatis.domain.VideoMapper;
import com.jun.xdmybatis.utils.JsonUtils;
import lombok.extern.slf4j.Slf4j;
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.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;

import java.io.IOException;
import java.io.InputStream;
import java.util.*;

/**
 * 主类
 */
//@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
@Slf4j
public class XdMybatis {
    public static void main(String[] args) throws IOException {
        String resource = "config/mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
            final VideoMapper mapper = sqlSession.getMapper(VideoMapper.class);
            // 查询
            selectById(mapper);
            // 插入
            insert(mapper);
            // 批量插入
            batchInsert(mapper);
            // 更新
            update(mapper);
            // 动态更新
            updateVideoSelective(mapper);
            // 删除
            delete(mapper);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 查询
     */
    private static void selectById(VideoMapper mapper) throws com.fasterxml.jackson.core.JsonProcessingException {
        final Video video = mapper.selectById(30);
        log.info("video={}", JsonUtils.toJsonString(video));
    }

    /**
     * 插入
     */
    private static void insert(VideoMapper mapper) {
        //新增一条记录
        Video video = new Video();
        video.setTitle("课堂面试专题900道");
        video.setCoverImg("xdclass.net/aaa.png");
        video.setPoint(9.4);
        video.setCreateTime(new Date());
        video.setPrice(9900);
        video.setSummary("这个是面试专题概要");

        int rows = mapper.add(video);
        System.out.println(rows);
    }

    /**
     * 批量插入
     */
    private static void batchInsert(VideoMapper mapper) {
        //新增一条记录
        Video video1 = new Video();
        video1.setTitle("小滴课堂面试专题900道1111");
        video1.setCoverImg("xdclass.net/aaa.png111");
        video1.setPoint(9.41);
        video1.setCreateTime(new Date());
        video1.setPrice(9911);
        video1.setSummary("这个是面试专题概要11");

        //新增一条记录
        Video video2 = new Video();
        video2.setTitle("小滴课堂面试专题900道2");
        video2.setCoverImg("xdclass.net/aaa.png2");
        video2.setPoint(9.2);
        video2.setCreateTime(new Date());
        video2.setPrice(9922);
        video2.setSummary("这个是面试专题概要22");

        List<Video> list = new ArrayList<>();
        list.add(video1);
        list.add(video2);

        int rows = mapper.addBatch(list);
        System.out.println(rows);
        System.out.println(list.toString());
    }

    /**
     * 更新
     */
    private static void update(VideoMapper mapper) {
        Video video = new Video();
        video.setId(59);
        video.setTitle("小滴课堂面试专题900道 2021年新版");
        video.setCoverImg("xdclass.net/6666.png");
        mapper.updateVideo(video);
    }

    /**
     * 动态更新
     */
    private static void updateVideoSelective(VideoMapper mapper) {
        Video video = new Video();
        video.setId(57);
        video.setTitle("小滴课堂面试专题900道 2021年新版");
        video.setCoverImg("xdclass.net/6666.png");

        mapper.updateVideoSelective(video);
    }

    /**
     * 删除
     */
    private static void delete(VideoMapper mapper) {
        Map<String, Object> map = new HashMap<>();
        map.put("createTime", "2021-01-11 09:33:20");
        map.put("price", 9000);

        int rows = mapper.deleteByCreateTimeAndPrice(map);
        System.out.println(rows);
    }

}

3.sql

<?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.jun.xdmybatis.domain.VideoMapper">
    <select id="selectById" resultType="com.jun.xdmybatis.dao.Video">
        select * from video where id = #{id}
    </select>


    <insert id="add" parameterType="com.jun.xdmybatis.dao.Video" useGeneratedKeys="true" keyProperty="id"
            keyColumn="id">
        INSERT INTO `video` ( `title`, `summary`, `cover_img`, `price`, `create_time`, `point`)
        VALUES
        (#{title,jdbcType=VARCHAR},#{summary,jdbcType=VARCHAR},#{coverImg,jdbcType=VARCHAR},#{price,jdbcType=INTEGER},
         #{createTime,jdbcType=TIMESTAMP},#{point,jdbcType=DOUBLE});
    </insert>


    <!--批量插入-->
    <insert id="addBatch" parameterType="com.jun.xdmybatis.dao.Video">
        INSERT INTO `video` ( `title`, `summary`, `cover_img`, `price`, `create_time`, `point`)
        VALUES
        <foreach collection="list" item="video" separator=",">
            (#{video.title,jdbcType=VARCHAR},#{video.summary,jdbcType=VARCHAR},#{video.coverImg,jdbcType=VARCHAR},
            #{video.price,jdbcType=INTEGER},
            #{video.createTime,jdbcType=TIMESTAMP},#{video.point,jdbcType=DOUBLE})
        </foreach>
    </insert>

    <update id="updateVideo" parameterType="com.jun.xdmybatis.dao.Video">
        update video
        set
        title = #{title,jdbcType=VARCHAR},
        summary = #{summary,jdbcType=VARCHAR},
        cover_img = #{coverImg,jdbcType=VARCHAR},
        price = #{price,jdbcType=INTEGER},
        create_time = #{createTime,jdbcType=TIMESTAMP},
        point = #{point,jdbcType=DOUBLE}
        where
        id = #{id}
    </update>

    <update id="updateVideoSelective" parameterType="com.jun.xdmybatis.dao.Video">
        update video
        <trim prefix="set" suffixOverrides=",">
            <if test="title != null "> title = #{title,jdbcType=VARCHAR},</if>
            <if test="summary != null "> summary = #{summary,jdbcType=VARCHAR},</if>
            <if test="coverImg != null "> cover_img = #{coverImg,jdbcType=VARCHAR},</if>
            <if test="price != 0 "> price = #{price,jdbcType=INTEGER},</if>
            <if test="createTime !=null "> create_time = #{createTime,jdbcType=TIMESTAMP},</if>
            <!--一定要看pojo类里面的是基本数据类型，还是包装数据类型-->
            <if test="point != null "> point = #{point,jdbcType=DOUBLE},</if>
        </trim>
        where
        id = #{id}

    </update>


    <!--<delete id="deleteByCreateTimeAndPrice" parameterType="java.util.Map">-->
    <delete id="deleteByCreateTimeAndPrice" parameterType="Map">
        delete from video where create_time <![CDATA[ > ]]> #{createTime} and price <![CDATA[ >= ]]> #{price}
    </delete>


</mapper>