SpringBoot database operations

Integrated JdbcTemplate  

1 import dependency:

<!--jdbc starter-->       
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
<--Introducing database driver-->

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

2: Configuration file

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
###Specify the data source type. The default is HikariDataSource
#spring.datasource.type=com.zaxxer.hikari.HikariDataSource
#spring.datasource.type=org.apache.commons.dbcp2.BasicDataSource

DataSource automatic assembly principle

1: SpringBoot binds the configuration properties starting with spring.datasource to the through the @ ConfigurationProperties annotation

On the DataSourceProperties class.

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

2: springboot uses the assembly principle: ORG \ springframework \ boot \ spring boot autoconfigure \ 2.5.4 \ spring boot autoconfigure-2.5.4. Jar\ The DataSourceAutoConfiguration class under ORG \ springframework \ boot \ autoconfigure \ JDBC package automatically configures the data source

@Configuration(
    proxyBeanMethods = false
)
@ConditionalOnClass({DataSource.class, EmbeddedDatabaseType.class})
@ConditionalOnMissingBean(
    type = {"io.r2dbc.spi.ConnectionFactory"}
)
//Bind the properties of the configuration file to the DataSourceProperties class and put it into the container
@EnableConfigurationProperties({DataSourceProperties.class})
@Import({DataSourcePoolMetadataProvidersConfiguration.class, InitializationSpecificCredentialsDataSourceInitializationConfiguration.class, SharedCredentialsDataSourceInitializationConfiguration.class})
public class DataSourceAutoConfiguration {


//When we do not configure DateSource, we will configure the connection pool and introduce the DataSourceConfiguration data source configuration class
    @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: Data source configuration class DataSourceConfiguration

To put it bluntly, we judge whether we have a custom configured data source. If not, we configure the data source according to the data source type specified by our type. By default, the HikariCP data source is used in SpringBoot2 instead of the tomcat data source used in SpringBoot1

Hikari benefits: About the default data source of springboot_ qq78827534 blog - CSDN blog_ Springboot default data source

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: Operation results:

a: The default data source type is com.zaxxer.hikari.HikariDataSource, and the JdbcTemplate is automatically assembled in the automatic container.

  b: If we use: spring.datasource.type=org.apache.commons.dbcp2.BasicDataSource to specify the data source type and import dependencies

Dependency:

        <!-- dbcp data source -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-dbcp2</artifactId>
        </dependency>
        <!-- tomcat data source-->
        <!--<dependency>-->
            <!--<groupId>org.apache.tomcat</groupId>-->
            <!--<artifactId>tomcat-jdbc</artifactId>-->
            <!--<version>8.5.20</version>-->
        <!--</dependency>-->

to configure:  

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
###Specify the data source type. The default is HikariDataSource
#spring.datasource.type=com.zaxxer.hikari.HikariDataSource
#Specify the configuration data source type
spring.datasource.type=org.apache.commons.dbcp2.BasicDataSource

  View run results:

 

  The principle of JdbcTemplate is:

Auto configuration package D: \ maven \ repository \ ORG \ springframework \ boot \ spring boot autoconfigure \ 2.5.4 \ spring boot autoconfigure-2.5.4. Jar\ The JdbcTemplateConfiguration class under ORG \ springframework \ boot \ autoconfigure \ JDBC automatically configures the JdbcTemplate for us. We can configure the query timeout (query timeout property) with spring.jdbc as the prefix in the configuration file. These properties will be bound to the JdbcProperties class.

For example: spring. JDBC. Template. Query timeout = 10     The timeout is 10s

Source code of 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;
    }
}

Integrated JPA

1: Introduce dependency

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

2: View jar package dependencies

  The introduction of spring boot starter data JPA will automatically introduce spring boot starter JDBC and hibernate for us

Configuration file: Customize configuration properties through 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
###Specify the data source type. The default is HikariDataSource
#spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.type=org.apache.commons.dbcp2.BasicDataSource
spring.jdbc.template.query-timeout=10
##Configure the data definition language, i.e. create, drop and alert of the table
spring.jpa.hibernate.ddl-auto=update
##sql display
spring.jpa.show-sql=true

DDL and DML:

DDL: data definition language, scope of application: manage some objects (database,table) in the database, create, drop and alter

DML: data operation language, simple operation of data in the database, CRUD (addition, deletion, modification and query)

spring.jpa.hibernate.ddl-auto has four values by default:

DDL Auto: create ---- each time the program is run, a new table will be created if there is no table, and the data in the table will be cleared
DDL Auto: create drop ---- the table will be emptied every time the program ends
DDL Auto: update ---- every time the program runs, a new table will be created if there is no table, and the data in the table will not be cleared, but will only be updated
DDL Auto: validate ---- the running program will verify whether the field types of the data and the database are the same, and an error will be reported if they are different

The most commonly used is update

For example:

I'll create an entity User first

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;
//Adding a new attribute will automatically add a column to the USER table
  private String address;
}

Create Jpa

package com.example.springboot.jpa;

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

//When implementing CrudRepository, there will be methods of adding, deleting, modifying and querying
public interface JPATest extends CrudRepository<User,Integer> {
    //Query users by id and name
    User findByIdAndName(long id,String name);
}

When running the project, you will find the following log:

  Operation results:

 

Tags: Java Spring Boot

Posted on Sat, 25 Sep 2021 14:48:32 -0400 by drtanz