Spring Boot 第三章数据访问

2024-02-25 19:02:15

上一篇: Spring Boot 第二章核心配置与注解

数据访问

新建一格数据库spring_boot_homework
![QQ截图20210318215510.png][2]
新建文章和评论表格
代码：

# Host: 39.106.42.58  (Version: 5.7.32-log)
# Date: 2021-03-19 12:32:09
# Generator: MySQL-Front 5.3  (Build 4.234)

/*!40101 SET NAMES utf8 */;

#
# Structure for table "t_article"
#

CREATE TABLE `t_article` (
  `Id` int(2) NOT NULL AUTO_INCREMENT COMMENT '文章id',
  `title` varchar(255) DEFAULT '' COMMENT '文章标题',
  `content` longtext COMMENT '文章内容',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

#
# Data for table "t_article"
#

INSERT INTO `t_article` VALUES (1,'Spring Boot 基础入门','从入门到精通讲解......'),(2,'Spring Cloud 基础入门','从入门到放弃......');

#
# Structure for table "t_comment"
#

CREATE TABLE `t_comment` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `content` longtext COMMENT '评论内容',
  `author` varchar(255) DEFAULT NULL COMMENT '评论作者',
  `a_id` int(2) DEFAULT NULL COMMENT '关联文章id',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

#
# Data for table "t_comment"
#

INSERT INTO `t_comment` VALUES (1,'很全、很详细 。\t','狂奔的蜗牛',1),(2,'赞一个','张三',1),(3,'写的不错','Tom',1),(4,'欢迎私信','Kitty',2),(5,'很好','文轩',2);

数据访问 Mybatis
先导入一些依赖
再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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>textbook</artifactId>
    <version>1.0-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.3.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <!-- Spring Boot提供的配置处理器依赖，代码提示 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>

<!--        web 开发场景-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

<!--        测试依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

<!--        热部署-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
        </dependency>

        <!--    静态模板依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>

<!--        导入MyBadis开发团队自己适配的，实现数据访问操作。-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.4</version>
        </dependency>

<!--        阿里巴巴 适配的druid数据源启动器 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>

<!--        数据库连接-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>


    </dependencies>

<!--    maven 打包工具等插件-->
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <version>2.1.3.RELEASE</version>

            </plugin>
        </plugins>
    </build>
</project>

再application.properties全局配置文件中添加

# 允许数据库驼峰匹配
mybatis.configuration.map-underscore-to-camel-case=true

# MYsql 连接 配置
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/spring_boot_homework?serverTimezone=UTC

# 添加并配置第三方数据源 Druid
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.druid.initial-size=20
spring.datasource.druid.max-active=100
spring.datasource.druid.min-idle=10

新建文章和评论的实体类：
文章：model/ArticleMain.java

package wx0725.top.model;

import java.util.List;

public class ArticleMain {
    private Integer id;

    private String title;

    private String content;

    private List<CommentMain> commentMainList;

    @Override
    public String toString() {
        return "ArticleMain{" +
                "id=" + id +
                ", title='" + title + '\'' +
                ", content='" + content + '\'' +
                ", commentMainList=" + commentMainList +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getContent() {
        return content;
    }

    public void setContent(String content) {
        this.content = content;
    }

    public List<CommentMain> getCommentMainList() {
        return commentMainList;
    }

    public void setCommentMainList(List<CommentMain> commentMainList) {
        this.commentMainList = commentMainList;
    }
}

评论：model/CommentMain.java

package wx0725.top.model;

public class CommentMain {
    private Integer id;

    private String content;

    private String author;

    private Integer aId;


    @Override
    public String toString() {
        return "CommentMain{" +
                "id=" + id +
                ", content='" + content + '\'' +
                ", author='" + author + '\'' +
                ", aId=" + aId +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getContent() {
        return content;
    }

    public void setContent(String content) {
        this.content = content;
    }

    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }

    public Integer getaId() {
        return aId;
    }

    public void setaId(Integer aId) {
        this.aId = aId;
    }
}

自定义一个配置类，将第三方Druid数据源的属性值注入：config/DataSourceConfig.java

package wx0725.top.config;

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Configurable;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import javax.sql.DataSource;

/**
 * 实现第三方数据源注入
 */
@Configurable
public class DataSourceConfig {

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource getDruid(){
        return new DruidDataSource();
    }
}

接下来是两种MyBatis的整合方法

评论使用过注解
文章使用配置文件

使用注解对评论操作

@Mapper定义一个Mybatis接口文件。这里使用@MapperScan,将mapper目录作为固定接口包，方便分类管理

启动类添加：

@MapperScan({"wx0725.top.mapper"})

新建一个评论操作接口：
mapper/CommentMapper.java

package wx0725.top.mapper;

import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Component;
import wx0725.top.model.CommentMain;

public interface CommentMapper {

    //    查询指定id评论
    @Select("SELECT * FROM t_comment WHERE id=#{id}")
    public CommentMain findById(Integer id);

    //    插入评论
    @Insert(
            "INSERT INTO t_comment(content,author,a_id) " +
                    "VALUES(#{content}, #{author}, #{aId})"
    )
    public int insertComment(CommentMain commentMain);

    //    更新评论 
    @Update("UPDATE t_comment SET content=#{content} WHERE id=#{id}")
    public int updateComment(CommentMain commentMain);

    //    删除指定评论 
    @Delete("DELETE FROM t_comment WHERE id=#{id}")
    public int delectComment(Integer id);
}

测试：

    @Autowired
    private CommentMapper commentMapper;

    @Test
    public void selectComment(){
        CommentMain commentMain = commentMapper.findById(1);
        System.out.println(commentMain);
    }

CommentMain{id=1, content='很全、很详细 。	', author='狂奔的蜗牛', aId=1}

使用xml配置文件对文章内容整合

mapper/ArticleMapper.java

package wx0725.top.mapper;

import wx0725.top.model.ArticleMain;

public interface ArticleMapper {
    public ArticleMain selectArticle(Integer id);
    public int updateArticle(ArticleMain articleMain);
}

配置文件：mappper/ArticleMapper.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="wx0725.top.mapper.ArticleMapper">
    <!--    文章查询-->
    <select id="selectArticle" resultMap="articleWithComment">
        SELECT a.*, c.id c_id, c.content c_content, c.author c_author
        FROM t_article a, t_comment c
        WHERE a.id=c.a_id AND a.id=#{id}
    </select>
    <!--articleWithComment 文章查询结果-->
    <resultMap id="articleWithComment" type="ArticleMain">
        <id property="id" column="id"/>
        <result property="title" column="title"/>
        <result property="content" column="content"/>
        <collection property="commentMainList" ofType="CommentMain">
            <id property="id" column="c_id"/>
            <result property="content" column="c_content"/>
            <result property="author" column="c_author"/>
        </collection>
    </resultMap>

    <!--  更新文章信息  -->
    <update id="updateArticle" parameterType="ArticleMain">
        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>

测试


    /**
     * XML 文件 操作 MyBatis
     */
    @Autowired
    private ArticleMapper articleMapper;

    @Test
    public void selectArticle(){
        ArticleMain articleMain = articleMapper.selectArticle(1);
        System.out.println(articleMain);
    }

打印：

ArticleMain{id=1, title='Spring Boot 基础入门', content='从入门到精通讲解......', commentMainList=[CommentMain{id=1, content='很全、很详细 。	', author='狂奔的蜗牛', aId=null}, CommentMain{id=2, content='赞一个', author='张三', aId=null}, CommentMain{id=3, content='写的不错', author='Tom', aId=null}]}

码农公寓

数据访问

接下来是两种MyBatis的整合方法

相关文章