SpringBoot数据访问篇

原创

一把杀猪刀 2022-12-01 17:24:13 博主文章分类：SpringBoot ©著作权

文章标签 mybatis 数据库 java spring mysql 文章分类 OpenStack 云计算

©著作权归作者所有：来自51CTO博客作者一把杀猪刀的原创作品，请联系作者获取转载授权，否则将追究法律责任

SpringBoot整合数据访问层框架，常见的JDBC、Mybatis、MabatisPlus、JPA等，这里主要整合Mybatis框架
准备工作
使用SpringBoot项目初始化器创建SpringBoot项目骨架，可以在创建时直接导入MySQL&JDBC
或者创建基本的SpringBoot后在pom中导入

<dependency><!--导入JDBC依赖-->
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId><!--导入Mysql驱动-->
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

填写配置文件

spring:
  datasource:
    username: root
    password: "07603306"
    url: jdbc:mysql://xxx.xx.xx.xx:3306/xxx
    driver-class-name: com.mysql.cj.jdbc.Driver//这是使用Tomcat底层默认的数据源

编写测试JDBC连接代码

@Autowired
    DataSource dataSource;
    @Test
    public void mysqlTest() throws SQLException {
        System.out.println(dataSource.getClass());
        Connection connection=dataSource.getConnection();
        System.out.println(connection);
        connection.close();
    }

编写查询数据测试代码

@Autowired
    JdbcTemplate jdbcTemplate;//这是原生JDBC的写法
    @Test
    public void jdbcTemplate() throws SQLException {
        List<Map<String,Object>> list=jdbcTemplate.queryForList("select * from activity_category");
        System.out.println(list);
    }

使用Druid数据源
Druid，基本上公司开发都会使用Druid作为数据源，因为Druid提供成套的服务，数据监控，安全等功能；
先导入Druid的pom依赖

<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.8</version>
        </dependency>

改写yml配置文件

spring:
  datasource:
    username: root
    password: "07603306"
    url: jdbc:mysql://120.27.16.240:3306/quanquan
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    #type可以切换数据源类型，默认使用tomcat的数据源，这里我们切换成druid

SpringBoot数据访问篇_java

这里可以看到数据源已经切换成druid了

下面我列举基本常用的配置

spring:
  datasource:
    username: root
    password: "07603306"
    url: jdbc:mysql://120.27.16.240:3306/quanquan
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
  #一下配置需要我们手动编写代码才能注入到容器否则不生效，而且这些配置会报黄色背景提示
    initialSize: 5
    minIdle: 5
    maxActive: 20
    maxWait: 60000
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: SELECT 1 FROM DUAL
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    #   配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
    filters: stat,wall,log4j
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

//创建配置类DruidConfig

```java
package com.tao.springbootconfig02.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;

import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;


@Configuration
public class DruidConfig {

    //这个就是注入我们自己的配置代码
    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource druid(){
        return  new DruidDataSource();
    }
    

    //配置一个Druid的监控后台
    @Bean
    public ServletRegistrationBean<StatViewServlet> druidStatViewServlet() {
        ServletRegistrationBean<StatViewServlet> registrationBean = new ServletRegistrationBean<>(new StatViewServlet(),  "/druid/*");
        registrationBean.addInitParameter("allow", "");// IP白名单 (没有配置或者为空，则允许所有访问)
        registrationBean.addInitParameter("deny", "");// IP黑名单 (存在共同时，deny优先于allow)
        registrationBean.addInitParameter("loginUsername", "root");
        registrationBean.addInitParameter("loginPassword", "1234");
        registrationBean.addInitParameter("resetEnable", "false");
        return registrationBean;
    }
 //2、配置一个web监控的filter
    @Bean
    public FilterRegistrationBean webStatFilter(){
        FilterRegistrationBean bean = new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());

        Map<String,String> initParams = new HashMap<>();
        initParams.put("exclusions","*.js,*.css,/druid/*");

        bean.setInitParameters(initParams);

        bean.setUrlPatterns(Arrays.asList("/*"));

        return  bean;
    }

}

这里可以测试一下不写public DataSource druid()这个会怎么样，可以使用第一个测试链接类型的测试代码打打上断点调试一下，下面这张图是写了这个注入配置代码的

SpringBoot数据访问篇_mybatis_02

可以看到我们自己的配置生效了，这里如果调试报错的话在pom中填写

<dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>

上面提到的后台界面可以在浏览器上输入http://localhost:8080/druid/index.html访问

整合Mybatis

注解版
导入pom依赖

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

编写代码
创建一个映射表的实体类

package com.tao.springbootconfig02.pojo;

import java.util.Date;

public class ActivityParticipate {

    private int id;
    private int act_id;
    private int s_id;
    private Date create_time;
    private Date update_time;
    private String delete_flag;

    public int getId() {
        return id;
    }

    public int getAct_id() {
        return act_id;
    }

    public void setAct_id(int act_id) {
        this.act_id = act_id;
    }

    public int getS_id() {
        return s_id;
    }

    public void setS_id(int s_id) {
        this.s_id = s_id;
    }

    public Date getCreate_time() {
        return create_time;
    }

    public void setCreate_time(Date create_time) {
        this.create_time = create_time;
    }

    public Date getUpdate_time() {
        return update_time;
    }

    public void setUpdate_time(Date update_time) {
        this.update_time = update_time;
    }

    public String getDelete_flag() {
        return delete_flag;
    }

    public void setDelete_flag(String delete_flag) {
        this.delete_flag = delete_flag;
    }

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

}

创建一个Mapper使用注解方式

package com.tao.springbootconfig02.mappr;

import com.tao.springbootconfig02.pojo.ActivityParticipate;
import org.apache.ibatis.annotations.*;

@Mapper
public interface ActivityParticipateMapper {

    @Select("select * from activity_participate where id=#{id}")
    public ActivityParticipate getActivityParticipateId(Integer id);

    @Delete("delete from activity_participate where id=#{id}")
    public int deleteActivityParticipateById(Integer id);

    @Insert("insert into activity_participate(`act_id`,`s_id`,`create_time`,`update_time`,`delete_flag`) values(#{activityParticipate})")
    public int insertActivityParticipate(ActivityParticipate activityParticipate);

    @Update("update activity_participate set `delete_flag`=#{deleteFlag} where id=#{id}")
    public int updateActivityParticipate(ActivityParticipate activityParticipate);
}

创建controller

@Autowired
    ActivityParticipateMapper activityParticipateMapper;

    @RequestMapping("/select")
    public ActivityParticipate selectTest(Integer id){
        System.out.println("111");
        return activityParticipateMapper.getActivityParticipateId(id);
    }

    @RequestMapping("/insert")
    public ActivityParticipate insertTest(ActivityParticipate activityParticipate){
        activityParticipateMapper.insertActivityParticipate(activityParticipate);
        return activityParticipate;
    }

这个controller需要注意返回的是V还是M所以这两个api是返回M那么需要在类上添加注解@RestController

请求结果

SpringBoot数据访问篇_数据库_03

这里还有一点需要注意，如果数据库里面的字段是这样的

`id``act_id``s_id``create_time``update_time``delete_flag``activity_participate`

代码里不想使用_的话那么需要编写Mybatis的配置类

@Configuration
public class MybatisConfig {

    @Bean
    public ConfigurationCustomizer configurationCustomizer(){
        return new ConfigurationCustomizer() {
            @Override
            public void customize(org.apache.ibatis.session.Configuration configuration) {
                configuration.setMapUnderscoreToCamelCase(true);//开启驼峰命名法
            }
        };
    }
}

实体类就改成

package com.tao.springbootconfig02.pojo;

import java.util.Date;

public class ActivityParticipate {

    private int id;
    private int actId;
    private int sId;
    private Date createTime;
    private Date updateTime;
    private String deleteFlag;

    public int getId() {
        return id;
    }

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

    public int getActId() {
        return actId;
    }

    public void setActId(int actId) {
        this.actId = actId;
    }

    public int getsId() {
        return sId;
    }

    public void setsId(int sId) {
        this.sId = sId;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public Date getUpdateTime() {
        return updateTime;
    }

    public void setUpdateTime(Date updateTime) {
        this.updateTime = updateTime;
    }

    public String getDeleteFlag() {
        return deleteFlag;
    }

    public void setDeleteFlag(String deleteFlag) {
        this.deleteFlag = deleteFlag;
    }
}

那么请求的结果是这样的

SpringBoot数据访问篇_java_04

这里的Mapper这个注解一定不要忘写了，如果忘写启动直接报错，每个Mapper类都需要写@Mapper这个注解有点多余，那么我们直接去掉，在主启动类上写

@MapperScan(value = "com.tao.springbootconfig02.mapper")
@SpringBootApplication
public class SpringbootConfig02Application {

    public static void main(String[] args) {
        SpringApplication.run(SpringbootConfig02Application.class, args);
    }

}

后面的路径就是mapper文件夹的类路径，这样也是可以的，那么注解版的就告一段落了，这里注解版只针对简单SQL还好，复杂的就有点不好写了；

XML版SQL映射文件
1.编写mybatis-config.xml配置文件
这些mybatis-config.xml、mapper映射文件可以参考mybatis在github代码托管官方配置

<?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>//这个设置是否开启驼峰命名法，由于这里使用mybatis-config.xml的方式所以之前SpringBoot里面代码配置的失效
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
</configuration>

编写mapper映射文件

<?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.tao.springbootconfig02.mapper.ActivityParticipateMapper"><!--绑定映射的接口-->
    //id是映射接口名 返回值是返回对象的全类名
    <select id="getActivityParticipateId" resultType="com.tao.springbootconfig02.pojo.ActivityParticipate">
        select * from activity_participate where id=#{id}
    </select>

    <insert id="insertActivityParticipate">
        insert into activity_participate(`act_id`,`s_id`,`create_time`,`update_time`,`delete_flag`) values(#{actId},#{sId},#{createTime},#{updateTime},#{updateTime})
    </insert>
</mapper>

映射接口就是把注解删除

package com.tao.springbootconfig02.mapper;

import com.tao.springbootconfig02.pojo.ActivityParticipate;
import org.apache.ibatis.annotations.*;

public interface ActivityParticipateMapper {

    //@Select("select * from activity_participate where id=#{id}")
    public ActivityParticipate getActivityParticipateId(Integer id);

    @Delete("delete from activity_participate where id=#{id}")
    public int deleteActivityParticipateById(Integer id);

    //@Insert("insert into activity_participate(`act_id`,`s_id`,`create_time`,`update_time`,`delete_flag`) values(#{activityParticipate})")
    public int insertActivityParticipate(ActivityParticipate activityParticipate);

    @Update("update activity_participate set `delete_flag`=#{deleteFlag} where id=#{id}")
    public int updateActivityParticipate(ActivityParticipate activityParticipate);
}

然后在yml配置文件中配置mybatis相关配置

mybatis:
  config-location: classpath:mybatis/mybatis-config.xml//这个是指定mybatis路径
  mapper-locations: classpath:mybatis/mapper/*.xml//这是指定mapper映射文件路径，这里直接指向mapper包

这是整个项目的目录

SpringBoot数据访问篇_mysql_05

启动项目访问

SpringBoot数据访问篇_mybatis_06

结果一样，这里的注解版和xml映射文件版可以一起使用，都是生效的只不过是注解版和xml映射文件版的配置都需要单独配置，当然也不要忘了写主启动类上的MapperScan

SpringData JPA整合

先介绍这是个啥玩意

SpringBoot数据访问篇_java_07

SpringData是SpringBoot底层默认的数据库访问技术，是Spring官方用来简化数据库访问的技术，上面图中的子项目就是JPA简化的项目，SpringData就是用来统一数据库访问的API

SpringBoot数据访问篇_spring_08

SpringData是提供统一的Repository的接口，这个接口具有基本的CRUD功能，基本分页功能，乐观锁机制功能，来实现统一数据访问的功能，我们编写代码只需要编写一个自己的接口来继承CrudRepository接口，那么我们自己的接口就具有了基本的CRUD操作，我们自己的接口是不需要关心任何的实现

SpringBoot数据访问篇_mysql_09

并且SpringData还提供各种模板，比如操作MongoDB、Redis的操作模板工具类

SpringBoot数据访问篇_java_10

这里说一下SpringData JPA底层是使用Hibernate进行封装的

开始编写简单的测试代码

导入JPA核心依赖这里还要导入Mysql驱动、JDBC连接我这里就只写核心依赖

<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

编写实体类

package com.tao.springbootconfig02.pojo;

import com.fasterxml.jackson.annotation.JsonIgnoreProperties;

import javax.persistence.*;
@JsonIgnoreProperties({"hibernateLazyInitializer","handler"})//序列化
@Entity//告诉JPA不是一个普通的实体类，这个是和数据库表映射的类
@Table(name = "test")//指定和那个数据库表对应，如果不写就默认这个实体类对应的表就是activity_participate
public class Test {

    @Id//这是一个主键
    @GeneratedValue(strategy = GenerationType.IDENTITY)//主键自增
    private int id;
    @Column(name = "name" ,length = 30)//这是和数据库表对应的列
    private String name;
}

编写Repository接口

package com.tao.springbootconfig02.repository;


import com.tao.springbootconfig02.pojo.Test;
import org.springframework.data.jpa.repository.JpaRepository;

public interface TestRepository extends JpaRepository<Test,Integer> {
}

如果需要打印SQL日志yml中配置这个

spring:
  datasource:
    username: root
    password: "07603306"
    url: jdbc:mysql://120.27.16.240:3306/quanquan
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource


    initialSize: 5
    minIdle: 5
    maxActive: 20
    maxWait: 60000
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: SELECT 1 FROM DUAL
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    #   配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
    filters: stat,wall,log4j
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

  jpa:
    hibernate:
    show-sql: true

编写controller

@Autowired
    TestRepository testRepository;

    @RequestMapping("/select")
    public List<Test>selectTest(Integer id){
        return testRepository.findAll();
    }

那么基本的数据库访问的就整合到这里，后期会专门针对常用的Mybatis和mybatisPlus、SpringDataJPA详细整合