随笔- 100 文章- 0 评论- 3 阅读- 30413

　　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>

posted on 2022-05-15 12:51 曹军阅读(40) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· 在线课堂后端代码梳理

· mybatis-generator生成domain和mapper，以及example的使用

· 【Mybatis】快速入门

· Mybatis的学习

· 0010 MyBatis知识点-黑马教程

阅读排行：
· 分享4款.NET开源、免费、实用的商城系统
· 全程不用写代码，我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了
· 上周热点回顾（2.24-3.2）

公告

昵称：曹军
园龄： 8年5个月
粉丝： 151
关注： 19

+加关注

2025年3月

日

一

二

三

四

五

六

随笔分类

elasticsearch(6)

es(1)

flink(4)

git(1)

graph(1)

hbase(6)

java(20)

kafka(1)

linux(5)

mybatis(2)

mysql(4)

netty(4)

nginx(2)

opentsdb(2)

python(2)

rabbitmq(1)

redis(3)

rocketmq(4)

sharding-jdbc(1)

spring security(1)

SRS流媒体(5)

zookeeper(1)

机器学习(7)

面试(1)

萍(2)

前端学习(2)

算法(8)

项目(1)

随笔档案

2024年4月(1)

2024年3月(3)

2023年9月(8)

2023年8月(1)

2023年7月(4)

2023年6月(1)

2023年5月(1)

2023年4月(5)

2023年3月(4)

2023年2月(4)

2023年1月(1)

2022年12月(2)

2022年11月(2)

2022年10月(1)

2022年9月(6)

2022年8月(11)

2022年7月(10)

2022年6月(13)

2022年5月(11)

2022年4月(3)

2022年3月(7)

2022年2月(1)

阅读排行榜

评论排行榜

1. 回来(2)