[Java知识库] SpringBoot数据库操作

开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> Java知识库 -> SpringBoot数据库操作 -> 正文阅读

[Java知识库]SpringBoot数据库操作

集成JdbcTemplate?

1导入依赖：

<!--jdbc starter-->       
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
<--引入数据库驱动-->

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

2：配置文件

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=root
###指定数据源类型，默认为HikariDataSource
#spring.datasource.type=com.zaxxer.hikari.HikariDataSource
#spring.datasource.type=org.apache.commons.dbcp2.BasicDataSource

DataSource自动装配原理

1：SpringBoot会将以spring.datasource开头的配置属性通过@ConfigurationProperties注解绑定到

DataSourceProperties 类上。

@ConfigurationProperties(
    prefix = "spring.datasource"
)
public class DataSourceProperties implements BeanClassLoaderAware, InitializingBean {

2：springboot利用装配原理：org\springframework\boot\spring-boot-autoconfigure\2.5.4\spring-boot-autoconfigure-2.5.4.jar!\org\springframework\boot\autoconfigure\jdbc包下的DataSourceAutoConfiguration 类自动配置数据源

@Configuration(
    proxyBeanMethods = false
)
@ConditionalOnClass({DataSource.class, EmbeddedDatabaseType.class})
@ConditionalOnMissingBean(
    type = {"io.r2dbc.spi.ConnectionFactory"}
)
//将配置文件的属性绑定到DataSourceProperties类中并将其放入容器
@EnableConfigurationProperties({DataSourceProperties.class})
@Import({DataSourcePoolMetadataProvidersConfiguration.class, InitializationSpecificCredentialsDataSourceInitializationConfiguration.class, SharedCredentialsDataSourceInitializationConfiguration.class})
public class DataSourceAutoConfiguration {


//当我们自己没有配置DateSource时，会为我们配置连接池以及引入DataSourceConfiguration数据源配置类
    @Configuration(
        proxyBeanMethods = false
    )
    @Conditional({DataSourceAutoConfiguration.PooledDataSourceCondition.class})
    @ConditionalOnMissingBean({DataSource.class, XADataSource.class})
    @Import({Hikari.class, Tomcat.class, Dbcp2.class, OracleUcp.class, Generic.class, DataSourceJmxConfiguration.class})
    protected static class PooledDataSourceConfiguration {
        protected PooledDataSourceConfiguration() {
        }
    }

3：数据源配置类DataSourceConfiguration

说白了就是判断我们是否有自定义配置数据源，如果没有配置则根据我们的type指定的数据源类型进行数据源配置，SpringBoot2版本默认使用HikariCP数据源，替换SpringBoot1使用的tomcat数据源

Hikari的优点：关于springboot 的默认数据源_qq78827534的博客-CSDN博客_springboot默认数据源

abstract class DataSourceConfiguration {
    DataSourceConfiguration() {
    }

    protected static <T> T createDataSource(DataSourceProperties properties, Class<? extends DataSource> type) {
        return properties.initializeDataSourceBuilder().type(type).build();
    }

    @Configuration(
        proxyBeanMethods = false
    )
    @ConditionalOnMissingBean({DataSource.class})
    @ConditionalOnProperty(
        name = {"spring.datasource.type"}
    )
    static class Generic {
        Generic() {
        }

        @Bean
        DataSource dataSource(DataSourceProperties properties) {
            return properties.initializeDataSourceBuilder().build();
        }
    }

    @Configuration(
        proxyBeanMethods = false
    )
    @ConditionalOnClass({PoolDataSourceImpl.class, OracleConnection.class})
    @ConditionalOnMissingBean({DataSource.class})
    @ConditionalOnProperty(
        name = {"spring.datasource.type"},
        havingValue = "oracle.ucp.jdbc.PoolDataSource",
        matchIfMissing = true
    )
    static class OracleUcp {
        OracleUcp() {
        }

        @Bean
        @ConfigurationProperties(
            prefix = "spring.datasource.oracleucp"
        )
        PoolDataSourceImpl dataSource(DataSourceProperties properties) throws SQLException {
            PoolDataSourceImpl dataSource = (PoolDataSourceImpl)DataSourceConfiguration.createDataSource(properties, PoolDataSourceImpl.class);
            dataSource.setValidateConnectionOnBorrow(true);
            if (StringUtils.hasText(properties.getName())) {
                dataSource.setConnectionPoolName(properties.getName());
            }

            return dataSource;
        }
    }

    @Configuration(
        proxyBeanMethods = false
    )
    @ConditionalOnClass({BasicDataSource.class})
    @ConditionalOnMissingBean({DataSource.class})
    @ConditionalOnProperty(
        name = {"spring.datasource.type"},
        havingValue = "org.apache.commons.dbcp2.BasicDataSource",
        matchIfMissing = true
    )
    static class Dbcp2 {
        Dbcp2() {
        }

        @Bean
        @ConfigurationProperties(
            prefix = "spring.datasource.dbcp2"
        )
        BasicDataSource dataSource(DataSourceProperties properties) {
            return (BasicDataSource)DataSourceConfiguration.createDataSource(properties, BasicDataSource.class);
        }
    }

    @Configuration(
        proxyBeanMethods = false
    )
    @ConditionalOnClass({HikariDataSource.class})
    @ConditionalOnMissingBean({DataSource.class})
    @ConditionalOnProperty(
        name = {"spring.datasource.type"},
        havingValue = "com.zaxxer.hikari.HikariDataSource",
        matchIfMissing = true
    )
    static class Hikari {
        Hikari() {
        }

        @Bean
        @ConfigurationProperties(
            prefix = "spring.datasource.hikari"
        )
        HikariDataSource dataSource(DataSourceProperties properties) {
            HikariDataSource dataSource = (HikariDataSource)DataSourceConfiguration.createDataSource(properties, HikariDataSource.class);
            if (StringUtils.hasText(properties.getName())) {
                dataSource.setPoolName(properties.getName());
            }

            return dataSource;
        }
    }

    @Configuration(
        proxyBeanMethods = false
    )
    @ConditionalOnClass({org.apache.tomcat.jdbc.pool.DataSource.class})
    @ConditionalOnMissingBean({DataSource.class})
    @ConditionalOnProperty(
        name = {"spring.datasource.type"},
        havingValue = "org.apache.tomcat.jdbc.pool.DataSource",
        matchIfMissing = true
    )
    static class Tomcat {
        Tomcat() {
        }

        @Bean
        @ConfigurationProperties(
            prefix = "spring.datasource.tomcat"
        )
        org.apache.tomcat.jdbc.pool.DataSource dataSource(DataSourceProperties properties) {
            org.apache.tomcat.jdbc.pool.DataSource dataSource = (org.apache.tomcat.jdbc.pool.DataSource)DataSourceConfiguration.createDataSource(properties, org.apache.tomcat.jdbc.pool.DataSource.class);
            DatabaseDriver databaseDriver = DatabaseDriver.fromJdbcUrl(properties.determineUrl());
            String validationQuery = databaseDriver.getValidationQuery();
            if (validationQuery != null) {
                dataSource.setTestOnBorrow(true);
                dataSource.setValidationQuery(validationQuery);
            }

            return dataSource;
        }
    }
}

3：运行结果：

a:默认数据源类型：com.zaxxer.hikari.HikariDataSource，并且自动容器中自动装配了JdbcTemplate。

?b:如果我们使用：spring.datasource.type=org.apache.commons.dbcp2.BasicDataSource指定数据源类型，并且导入依赖

依赖：

        <!-- dbcp数据源 -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-dbcp2</artifactId>
        </dependency>
        <!-- tomcat数据源-->
        <!--<dependency>-->
            <!--<groupId>org.apache.tomcat</groupId>-->
            <!--<artifactId>tomcat-jdbc</artifactId>-->
            <!--<version>8.5.20</version>-->
        <!--</dependency>-->

配置：?

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=root
###指定数据源类型，默认为HikariDataSource
#spring.datasource.type=com.zaxxer.hikari.HikariDataSource
#指定配置数据源类型
spring.datasource.type=org.apache.commons.dbcp2.BasicDataSource

?查看运行结果：

?JdbcTemplate原理是：

自动配置包D:\Maven\repository\org\springframework\boot\spring-boot-autoconfigure\2.5.4\spring-boot-autoconfigure-2.5.4.jar!\org\springframework\boot\autoconfigure\jdbc下的JdbcTemplateConfiguration类为我们自动配置了JdbcTemplate,我们可以通过在配置文件中以spring.jdbc为前缀配置查询超时时间（query-timeout属性）等，这些属性都会绑定在JdbcProperties类上。

例如：spring.jdbc.template.query-timeout=10? ?超时时间为10s

JdbcTemplateConfiguration源码：

package org.springframework.boot.autoconfigure.jdbc;

import javax.sql.DataSource;
import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
import org.springframework.boot.autoconfigure.jdbc.JdbcProperties.Template;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcOperations;
import org.springframework.jdbc.core.JdbcTemplate;

@Configuration(
    proxyBeanMethods = false
)
@ConditionalOnMissingBean({JdbcOperations.class})
class JdbcTemplateConfiguration {
    JdbcTemplateConfiguration() {
    }

    @Bean
    @Primary
    JdbcTemplate jdbcTemplate(DataSource dataSource, JdbcProperties properties) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        Template template = properties.getTemplate();
        jdbcTemplate.setFetchSize(template.getFetchSize());
        jdbcTemplate.setMaxRows(template.getMaxRows());
        if (template.getQueryTimeout() != null) {
            jdbcTemplate.setQueryTimeout((int)template.getQueryTimeout().getSeconds());
        }

        return jdbcTemplate;
    }
}

集成JPA

1:引入依赖

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

2：查看jar包依赖

?引入spring-boot-starter-data-jpa自动会为我们引入spring-boot-starter-jdbc以及hibernate

配置文件：通过 prefix = "spring.jpa.hibernate"自定义配置属性

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=root
###指定数据源类型，默认为HikariDataSource
#spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.type=org.apache.commons.dbcp2.BasicDataSource
spring.jdbc.template.query-timeout=10
##配置数据定义语言，即表的Create,drop,Alert
spring.jpa.hibernate.ddl-auto=update
##sql显示
spring.jpa.show-sql=true

DDL和DML:

DDL:数据定义语言，适用范围：数据库中的某些对象（database,table）进行管理，create,drop,alter

DML:数据操作语言，对数据库中的数据进行简单操作，CRUD(增删改查)

spring.jpa.hibernate.ddl-auto 默认有四个值：

ddl-auto:create----每次运行该程序，没有表格会新建表格，表内有数据会清空
ddl-auto:create-drop----每次程序结束的时候会清空表
ddl-auto:update----每次运行程序，没有表格会新建表格，表内有数据不会清空，只会更新
ddl-auto:validate----运行程序会校验数据与数据库的字段类型是否相同，不同会报错

最常用的是update

例如：

我先创建一个实体User

package com.example.springboot.entry;


import lombok.Data;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="USER")
@ToString
@Getter
@Setter
public class User {

  @Id
  private long id;
  private String name;
  private long age;
  private int phone;
//新加一个属性，会自动的给USER表中添加一列
  private String address;
}

创建Jpa

package com.example.springboot.jpa;

import com.example.springboot.entry.User;
import org.springframework.data.repository.CrudRepository;

//实现CrudRepository，会有增删改查方法
public interface JPATest extends CrudRepository<User,Integer> {
    //根据id和名称查询用户
    User findByIdAndName(long id,String name);
}

运行项目发现会打一下日志：