SpringBoot配置Mybatis的yaml文件

1.导入依赖

使用Druid数据源

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.2.0</version>
</dependency>

全部的pom.xml依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.6.0</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example.demo3</groupId>
    <artifactId>Demo3</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>Demo3</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.22</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.0</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.8</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

2.yaml文件

server:
  port: 8080

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/springbootdata?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=false
    username: root
    password: 111111
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      #初始化大小
      initialSize: 5
      #最小值
      minIdle: 5
      #最大值
      maxActive: 20
      #最大等待时间，配置获取连接等待超时，时间单位都是毫秒ms
      maxWait: 60000
      #配置间隔多久才进行一次检测，检测需要关闭的空闲连接
      timeBetweenEvictionRunsMillis: 60000
      #配置一个连接在池中最小生存的时间
      minEvictableIdleTimeMillis: 300000
      validationQuery: SELECT 1 FROM DUAL
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      poolPreparedStatements: true
      # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，
      #'wall'用于防火墙，SpringBoot中没有log4j，我改成了log4j2
      filters: stat,wall,log4j2
      #最大PSCache连接
      maxPoolPreparedStatementPerConnectionSize: 20
      useGlobalDataSourceStat: true
      # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
      connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
      # 配置StatFilter
      web-stat-filter:
        #默认为false，设置为true启动
        enabled: true
        url-pattern: "/*"
        exclusions: "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*"
      #配置StatViewServlet
      stat-view-servlet:
        url-pattern: "/druid/*"
        #允许那些ip
        allow: 127.0.0.1
        login-username: admin
        login-password: 123456
        #禁止那些ip
        deny: 192.168.1.102
        #是否可以重置
        reset-enable: true
        #启用
        enabled: true

# Mybatis配置
mybatis:
  # 配置XML映射文件中指定的实体类别名路径
  type-aliases-package: com.example.demo3.pojo
  # 配置MyBatis的xml配置文件路径
  mapper-locations: classpath:mapper/*.xml
  # 开启驼峰uName自动映射到u_name
  map-underscore-to-camel-case: true


# 打印sql
logging:
  level:
    ssm.app.mapper : debug

3.实体类

文章类

package com.example.demo3.pojo;

import lombok.*;

import java.util.List;

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
@EqualsAndHashCode
public class Article {
    private Integer id;
    private String title;
    private String content;
    private List<Comment> commentList;
}

评论类

package com.example.demo3.pojo;

import lombok.*;

import java.util.List;

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
@EqualsAndHashCode
public class Article {
    private Integer id;
    private String title;
    private String content;
    private List<Comment> commentList;
}

4.Mapper接口

ArticleMapper接口类

package com.example.demo3.mapper;

import com.example.demo3.pojo.Article;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface ArticleMapper {

    public Article selectArticle(Integer id);
    public int updateArticle(Article article);

}

CommentMapper接口类

package com.example.demo3.mapper;


import com.example.demo3.pojo.Comment;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface CommentMapper {

    //查询全部评论数据
    @Select("SELECT * FROM t_comment")
    public List<Comment> selectAll();

    //根据Id查询评论数据
    @Select("SELECT * FROM t_comment WHERE id = #{id}")
    public Comment findById(Integer id);

    //插入数据操作
    @Insert("INSERT INTO t_comment(content,author,aid) " +
            "values (#{content},#{author},#{aId})")
    public int insertComment(Comment comment);

    //更新数据操作
    @Update("UPDATE t_comment SET content=#{content} WHERE id=#{id}")
    public int updateComment(Comment comment);

    //删除数据操作
    @Delete("DELETE FROM t_comment WHERE id=#{id}")
    public int deleteComment(Integer id);
}

xml的映射文件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.example.demo3.mapper.ArticleMapper">

    <resultMap id="articleWithComment" type="Article">
        <id property="id" column="id" />
        <result property="title" column="title" />
        <result property="content" column="content" />
        <collection property="commentList" ofType="Comment">
            <id property="id" column="c_id" />
            <result property="content" column="c_content" />
            <result property="author" column="author" />
        </collection>
    </resultMap>

    <select id="selectArticle" resultMap="articleWithComment">
        SELECT a.*,c.id c_id,c.content c_content,c.author
        FROM t_article a,t_comment c
        WHERE a.id=c.aid AND a.id = #{id}
    </select>
    <update id="updateArticle" parameterType="Article" >
        UPDATE t_article
        <set>
            <if test="title !=null and title !=''">
                title=#{title},
            </if>
            <if test="content !=null and content !=''">
                content=#{content}
            </if>
        </set>
        WHERE id=#{id}
    </update>
</mapper>

5.测试类

package com.example.demo3;

import com.example.demo3.mapper.ArticleMapper;
import com.example.demo3.mapper.CommentMapper;
import com.example.demo3.pojo.Article;
import com.example.demo3.pojo.Comment;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;


@SpringBootTest
class Demo3ApplicationTests {

    @Autowired
    private CommentMapper commentMapper;
    @Autowired
    private ArticleMapper articleMapper;

    //评论接口测试
    @Test
    public void selectAllComment(){
        List<Comment> comment = commentMapper.selectAll();
        System.out.println(comment);
    }

    //根据id查询评论
    @Test
    public void selectComment(){
        Comment comment = commentMapper.findById(1);
        System.out.println(comment);
    }

    //插入评论
    @Test
    public void insertComment(){
        Comment comment = new Comment("老人与海","海明威",2);
        commentMapper.insertComment(comment);
    }


    //文章接口测试
    @Test
    public void selectArticle(){
        Article article = articleMapper.selectArticle(1);
        System.out.println(article);
    }

    @Test
    void contextLoads() {
    }

}