【SpringBoot】三：访问数据库

1.DataSource

2.JdbcTemplate

2.1 准备环境

(1）新建数据库

（2）创建项目



启动lombok

2.2 准备表和数据

2.3 配置数据源

【application.properties】

运行springboot,初始化数据库脚本。

之后不再初始化数据库脚本。

2.4 JdbcTemplate访问mysql

JdbcTemplate的方法
（1）execute 方法：可以用于执行任何 SQL 语句，常用来执行 DDL 语句。
（2）update、batchUpdate 方法：用于执行新增、修改与删除等语句。
（3）query 和 queryForXXX 方法：用于执行查询相关的语句。
（4）call 方法：用于执行数据库存储过程和函数相关的语句。

2.5 创建实体类 ArticlePO

2.6 测试

2.6.1 测试聚合函数

3.mybatis

3.1 单表CRUMD

3.1.1 创建模块

3.1.2 查询

PO【ArticlePO.java】

package com.sdnu.mybatis.po;

import lombok.Data;

import java.time.LocalDateTime;

@Data
public class ArticlePO {
    private Integer id;
    private Integer userId;
    private String title;
    private String summary;
    private Integer readCount;
    private LocalDateTime createTime;
    private LocalDateTime updateTime;
}

mapper【ArticleMapper.java】

package com.sdnu.mybatis.mapper;

import com.sdnu.mybatis.po.ArticlePO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

public interface ArticleMapper {
    //按主键查询
    @Select("""
        select id, user_id, title, summary, read_count, create_time, update_time
        from article where id = #{articleId}
        """)
    @Results(id = "BaseArticleMap", value = {
         @Result(id = true, column = "id", property = "id"),
             @Result(column = "user_id", property = "userId"),
             @Result(column = "summary", property = "title"),
             @Result(column = "summary", property = "summary"),
             @Result(column = "read_count", property = "readCount"),
             @Result(column = "create_time", property = "createTime"),
             @Result(column = "update_time", property = "updateTime")
    })
    ArticlePO selectById(@Param("articleId") Integer id);
}

3.1.3 插入

mapper【ArticleMapper.java】

public interface ArticleMapper {
     //插入
    @Insert("""
            insert into article(id, user_id, title, summary, read_count, create_time, update_time)
            value (#{id}, #{userId}, #{title}, #{summary}, #{readCount}, #{createTime}, #{updateTime})
            """)
    int insertArticle(ArticlePO articlePO);
}

3.1.4 更新

mapper【ArticleMapper.java】

package com.sdnu.mybatis.mapper;

import com.sdnu.mybatis.po.ArticlePO;
import org.apache.ibatis.annotations.*;

public interface ArticleMapper {
    @Update("""
            update article set title = #{title} where id = #{id}
            """)
    int updateArticle(@Param("title") String title, @Param("id") Integer id);
}

3.1.5 删除

package com.sdnu.mybatis.mapper;

import com.sdnu.mybatis.po.ArticlePO;
import org.apache.ibatis.annotations.*;

public interface ArticleMapper {
    @Delete("""
            delete from article where id = #{id}
            """)
    int deleteArticle(@Param("id") Integer id);
}

3.2 ResultMap

mapper【ArticleMapper.java】

package com.sdnu.mybatis.mapper;

import com.sdnu.mybatis.po.ArticlePO;
import org.apache.ibatis.annotations.*;

public interface ArticleDao {
    //根据id查询文章
    @Select("""
        select id, user_id, title, summary, read_count, create_time, update_time
        from article where id = #{articleId}
        """)
    @Results(id = "BaseArticleMap", value = {
            @Result(id = true, column = "id", property = "id"),
            @Result(column = "user_id", property = "userId"),
            @Result(column = "summary", property = "title"),
            @Result(column = "summary", property = "summary"),
            @Result(column = "read_count", property = "readCount"),
            @Result(column = "create_time", property = "createTime"),
            @Result(column = "update_time", property = "updateTime")
    })
    ArticlePO selectById(@Param("articleId") Integer id);
    //根据user_id查询文章
    @Select("""
        select id, user_id, title, summary, read_count, create_time, update_time
        from article where user_id = #{userId}
        """)
    //引用结果集映射，value值是@Results中的id
    @ResultMap("BaseArticleMap")
    ArticlePO selectByUserId(@Param("userId") Integer userId);
}

@ResultMap使用两种方式：

• 通过@Results定义列的映射关系，@ResultMap(value = “@Result的id”)
• 在xml中定义, 在代码中使用@ResultMap(value = “xml的id”)
  xml方式：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.sdnu.mybatis.mapper.ArticleDao">
    <resultMap id="ArticleMapper" type="com.sdnu.mybatis.po.ArticlePO">
        <id column="id" property="id"/>
        <result column="user_id" property="userId" />
        <result column="read_count" property="readCount" />
        <result column="create_time" property="createTime" />
        <result column="update_time" property="updateTime" />
    </resultMap>
</mapper>

3.3 SQL 提供者

【SqlProvider.java】

package com.sdnu.mybatis.provider;

public class SqlProvider {
    public static String selectArticle(){
        return """
            select * from article where id = #{id}
           """;
    }
    public static String updateTime(){
        return """
             update article set update_time = #{newTime} where id = #{id}
            """;
    }
}

【ArticleRepository.java】

package com.sdnu.mybatis.mapper;

import com.sdnu.mybatis.po.ArticlePO;
import com.sdnu.mybatis.provider.SqlProvider;
import org.apache.ibatis.annotations.*;

import java.time.LocalDateTime;

public interface ArticleRepository {
    @Select("")
    @Results(id = "NewBaseArticleMap", value = {
        @Result(id = true, column = "id", property = "id"),
        @Result(column = "user_id", property = "userId"),
        @Result(column = "title", property = "title"),
        @Result(column = "summary", property = "summary"),
        @Result(column = "read_count", property = "readCount"),
        @Result(column = "create_time", property = "createTime"),
        @Result(column = "update_time", property = "updateTime")
    })
    ArticlePO selectMapper();

    @ResultMap("NewBaseArticleMap")
    @SelectProvider(type = SqlProvider.class, method = "selectArticle")
    ArticlePO selectByPrimary(Integer id);

    @UpdateProvider(type = SqlProvider.class, method = "updateTime")
    int updateTime(@Param("id") Integer id, @Param("newTime") LocalDateTime newTime);
}

【RepositoryTest.java】

package com.sdnu.mybatis;

import com.sdnu.mybatis.mapper.ArticleRepository;
import com.sdnu.mybatis.po.ArticlePO;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import javax.annotation.Resource;
import java.time.LocalDateTime;

@SpringBootTest
public class RepositoryTest {
    @Resource
    private ArticleRepository articleRepository;
    @Test
    void testRepository(){
        ArticlePO articlePO = articleRepository.selectByPrimary(2);
        System.out.println(articlePO);
    }
    @Test
    void testRepository2(){
        int rows = articleRepository.updateTime(1, LocalDateTime.now());
        System.out.println(rows);
    }
}

3.4 @One一对一查询

3.5 @Many 一对多查

3.6 常用配置参数

https://mybatis.org/mybatis-3/zh/configuration.html#settings

3.7 MybatisAutoConfiguration

3.8 合适的连接池

4. 声明式事务