时间戳

首页 > 技术文章 > 基础的mybatis学习

基础的mybatis学习

juncaoit 2022-05-15 12:51 原文

　　mybatis稍微梳理了下，对于mybatis，将会写基础使用，快速开发，源码分析。

　　本文档写简单的基础使用。

　　下面的代码都是粘贴的片段，详细代码，建议参考git，可完整运行。

　　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);
    }

}

 

.mapper

package com.jun.xdmybatis.domain;

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

import java.util.List;
import java.util.Map;

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

    /**
     * 新增一条视频记录
     */
    int add(Video video);

    /**
     * 批量插入
     */
    int addBatch(List<Video> list);

    /**
     * 更新视频
     */
    int updateVideo(Video video);

    /**
     * 动态选择更新
     */
    int updateVideoSelective(Video video);


    /**
     * 根据时间和价格删除
     */
    int deleteByCreateTimeAndPrice(Map<String,Object> map);
}

　　

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>

 

四：一对一映射

1.说明

　　查询订单信息，包含对应的用户信息

 

2.程序

    /**
     * 一对一查询
     */
    private static void oneToOne(SqlSession sqlSession) throws JsonProcessingException {
        final VideoOrderMapper mapper = sqlSession.getMapper(VideoOrderMapper.class);
        List<VideoOrder> videoOrders = mapper.queryVideoOrderList();
        log.info("videoOrders={}", JsonUtils.toJsonString(videoOrders));
    }

 

mapper

package com.jun.xdmybatis.dao;


import com.jun.xdmybatis.domain.VideoOrder;

import java.util.List;

public interface VideoOrderMapper {

    /**
     * 查询全部订单，关联用户信息
     * @return
     */
    List<VideoOrder> queryVideoOrderList();

}

　　

domain：

package com.jun.xdmybatis.domain;

import lombok.Data;

import java.util.Date;

@Data
public class VideoOrder {
    private int id;

    private String outTradeNo;

    private int state;

    private Date createTime;

    private int totalFee;

    private int videoId;

    private String videoTitle;

    private String videoImg;

    private int userId;

    private User user;


}

　　

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.jun.xdmybatis.dao.VideoOrderMapper">


    <resultMap id="VideoOrderResultMap" type="com.jun.xdmybatis.domain.VideoOrder">
        <id column="id" property="id"/>

        <result column="user_id" property="userId"/>
        <result column="out_trade_no" property="outTradeNo"/>
        <result column="create_time" property="createTime"/>
        <result column="state" property="state"/>
        <result column="total_fee" property="totalFee"/>
        <result column="video_id" property="videoId"/>
        <result column="video_title" property="videoTitle"/>
        <result column="video_img" property="videoImg"/>
        
        <!--
         association 配置属性一对一
         property 对应videoOrder里面的user属性名
         javType 这个属性的类型
         -->
        <association property="user" javaType="com.jun.xdmybatis.domain.User">
            <id property="id"  column="user_id"/>
            <result property="name" column="name"/>
            <result property="headImg" column="head_img"/>
            <result property="createTime" column="create_time"/>
            <result property="phone" column="phone"/>
        </association>

    </resultMap>

    <!--一对一管理查询订单， 订单内部包含用户属性-->
    <select id="queryVideoOrderList" resultMap="VideoOrderResultMap">

        select

         o.id id,
         o.user_id ,
         o.out_trade_no,
         o.create_time,
         o.state,
         o.total_fee,
         o.video_id,
         o.video_title,
         o.video_img,
         u.name,
         u.head_img,
         u.create_time,
         u.phone
         from video_order o left join user u on o.user_id = u.id

    </select>


</mapper>

　　

 

五：一对多映射

1.说明

　　一个用户对应多个订单

 

2.程序

    /**
     * 一对多查询
     */
    private static void ontToMany(SqlSession sqlSession) throws JsonProcessingException {
        final VideoOrderMapper mapper = sqlSession.getMapper(VideoOrderMapper.class);
        List<UserOrder> userOrders = mapper.queryUserOrder();
        log.info("userOrders={}", JsonUtils.toJsonString(userOrders));
    }

　　

mapper

public interface VideoOrderMapper {

    /**
     * 查询全部订单，关联用户信息
     * @return
     */
    List<VideoOrder> queryVideoOrderList();


    /**
     * 查询全部用户的全部订单
     * @return
     */
    List<UserOrder> queryUserOrder();

}

　　

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.jun.xdmybatis.dao.VideoOrderMapper">
    <resultMap id="VideoOrderResultMap" type="com.jun.xdmybatis.domain.VideoOrder">
        <id column="id" property="id"/>
        <result column="user_id" property="userId"/>
        <result column="out_trade_no" property="outTradeNo"/>
        <result column="create_time" property="createTime"/>
        <result column="state" property="state"/>
        <result column="total_fee" property="totalFee"/>
        <result column="video_id" property="videoId"/>
        <result column="video_title" property="videoTitle"/>
        <result column="video_img" property="videoImg"/>
        <!--
         association 配置属性一对一
         property 对应videoOrder里面的user属性名
         javType 这个属性的类型
         -->
        <association property="user" javaType="com.jun.xdmybatis.domain.User">
            <id property="id"  column="user_id"/>
            <result column="name" property="name"/>
            <result column="head_img" property="headImg"/>
            <result column="create_time" property="createTime"/>
            <result column="phone" property="phone"/>
        </association>
    </resultMap>

    <!--一对一管理查询订单， 订单内部包含用户属性-->
    <select id="queryVideoOrderList" resultMap="VideoOrderResultMap">
        select
         o.id id,
         o.user_id ,
         o.out_trade_no,
         o.create_time,
         o.state,
         o.total_fee,
         o.video_id,
         o.video_title,
         o.video_img,
         u.name,
         u.head_img,
         u.create_time,
         u.phone
         from video_order o left join user u on o.user_id = u.id
    </select>

    <resultMap id="UserOrderResultMap" type="com.jun.xdmybatis.domain.UserOrder">
        <id property="id"  column="id"/>
        <result property="name" column="name"/>
        <result property="headImg" column="head_img"/>
        <result property="createTime" column="create_time"/>
        <result property="phone" column="phone"/>

        <collection property="videoOrderList" ofType="com.jun.xdmybatis.domain.VideoOrder">
            <id column="order_id" property="id"/>
            <result column="user_id" property="userId"/>
            <result column="out_trade_no" property="outTradeNo"/>
            <result column="create_time" property="createTime"/>
            <result column="state" property="state"/>
            <result column="total_fee" property="totalFee"/>
            <result column="video_id" property="videoId"/>
            <result column="video_title" property="videoTitle"/>
            <result column="video_img" property="videoImg"/>
        </collection>
    </resultMap>

    <select id="queryUserOrder" resultMap="UserOrderResultMap">
        select
        u.id,
        u.name,
        u.head_img,
        u.create_time,
        u.phone,
        o.id order_id,
        o.out_trade_no,
        o.user_id,
        o.create_time,
        o.state,
        o.total_fee,
        o.video_id,
        o.video_title,
        o.video_img
        from user u left join video_order o on u.id = o.user_id
    </select>

</mapper>

　　

 

推荐阅读