6.Spring Boot与数据访问_hikari.chema-CSDN博客

本文链接：https://blog.csdn.net/qq_32498953/article/details/102965069

# 6.Spring与数据库访问 ## 6.1 JDBC ### 6.1.1 JDBC 连接基本配置 ```xml org.springframework.boot spring-boot-starter-jdbc

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

```yml
spring:
  datasource:
    username: root
    password: 123456
    url: jdbc:mysql://10.8.31.209:3306/jdbc?&autoReconnect=true
    driver-class-name:  com.mysql.cj.jdbc.Driver

默认是用com.zaxxer.hikari.HikariDataSource作为数据源；

数据源的相关配置都在DataSourceProperties里面；

自动配置原理：

org.springframework.boot.autoconfigure.jdbc：

参考DataSourceConfiguration，根据配置创建数据源，默认使用hikari连接池，可以直接使用
spring.datasource.type指定自定义的数据源类型；
SpringBoot默认可以支持；
1. com.zaxxer.hikari.HikariDataSource
2. org.apache.tomcat.jdbc.pool.DataSource
3. org.apache.commons.dbcp2.BasicDataSource
自定义数据源类型可参考这个实现

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

		@Bean
		DataSource dataSource(DataSourceProperties properties) {
			// 使用DataSourceBuilder创建数据源，利用反射创建响应type的数据源，并绑定相关的属性
			return properties.initializeDataSourceBuilder().build();
		}

	}

6.1.1 自动运行 SQL 文件解析

核心类DataSourceAutoConfiguration

org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration

DataSourceAutoConfiguration 这个类导入了另一个类：DataSourceInitializationConfiguration

@Configuration(proxyBeanMethods = false)
@ConditionalOnClass({ DataSource.class, EmbeddedDatabaseType.class })
@EnableConfigurationProperties(DataSourceProperties.class)
@Import({ DataSourcePoolMetadataProvidersConfiguration.class, DataSourceInitializationConfiguration.class })
public class DataSourceAutoConfiguration 
{
	...
}
// 该类导入了DataSourceInitializationConfiguration.Registrar，完成了DataSourceInitializerPostProcessor后置处理器的注册
// 该类导入了DataSourceInitializerInvoker，这个类完成了sql文件的自动执行
@Configuration(proxyBeanMethods = false)
@Import({ DataSourceInitializerInvoker.class, DataSourceInitializationConfiguration.Registrar.class })
class DataSourceInitializationConfiguration {


	static class Registrar implements ImportBeanDefinitionRegistrar {

		private static final String BEAN_NAME = "dataSourceInitializerPostProcessor";

		@Override
		public void registerBeanDefinitions(AnnotationMetadata importingClassMetadata,
				BeanDefinitionRegistry registry) {
			if (!registry.containsBeanDefinition(BEAN_NAME)) {
				GenericBeanDefinition beanDefinition = new GenericBeanDefinition();
				beanDefinition.setBeanClass(DataSourceInitializerPostProcessor.class);
				beanDefinition.setRole(BeanDefinition.ROLE_INFRASTRUCTURE);
				// We don't need this one to be post processed otherwise it can cause a
				// cascade of bean instantiation that we would rather avoid.
				beanDefinition.setSynthetic(true);
				registry.registerBeanDefinition(BEAN_NAME, beanDefinition);
			}
		}

	}

}

关键类DataSourceInitializerInvoker

DataSourceInitializerInvoker类中初始化了DataSourceInitializer，其中封装了主数据源，DataSourceProperties 其中的值取决于spring.datasource 配置属性。
调用dataSourceInitializer.createSchema();方法运行 sql文件

	// afterPropertiesSet，方法是DataSourceInitializerInvoker初始化完成的时候执行的方法
	@Override
	public void afterPropertiesSet() {
		DataSourceInitializer initializer = getDataSourceInitializer();
		if (initializer != null) {
			// 这个执行DDLSQL（创建表）
			boolean schemaCreated = this.dataSourceInitializer.createSchema();
			if (schemaCreated) {
				// 这里执行DML SQL（初始化数据）
				initialize(initializer);
			}
		}
	}

	private void initialize(DataSourceInitializer initializer) {
		try {
			this.applicationContext.publishEvent(new DataSourceSchemaCreatedEvent(initializer.getDataSource()));
			// The listener might not be registered yet, so don't rely on it.
			if (!this.initialized) {
				this.dataSourceInitializer.initSchema();
				this.initialized = true;
			}
		}
		catch (IllegalStateException ex) {
			logger.warn(LogMessage.format("Could not send event to complete DataSource initialization (%s)",
					ex.getMessage()));
		}
	}

核心方法dataSourceInitializer.createSchema()

首先定位到script资源
在运行Scripts资源

	boolean createSchema() {
		// 关键：是从这里定位到Scripts资源的！！！
		List<Resource> scripts = getScripts("spring.datasource.schema", this.properties.getSchema(), "schema");
		if (!scripts.isEmpty()) {
			if (!isEnabled()) {
				logger.debug("Initialization disabled (not running DDL scripts)");
				return false;
			}
			 //从properties获取SchemaUsername，chemaPassword
			String username = this.properties.getSchemaUsername();
			String password = this.properties.getSchemaPassword();
			 //这里运行scripts资源
			runScripts(scripts, username, password);
		}
		return !scripts.isEmpty();
	}

Scripts 资源定位：getScripts()

	// 如何获取Scripts，默认获取地址是哪个
	private List<Resource> getScripts(String propertyName, List<String> resources, String fallback) {
	//如果配置文件中定义了schema位置，则直接用这个位置定位资源
		if (resources != null) {
			return getResources(propertyName, resources, true);
		}
		 //默认获取地址：classpath*:schema-all.sql,classpath*:schema.sql
		String platform = this.properties.getPlatform();
		List<String> fallbackResources = new ArrayList<>();
		fallbackResources.add("classpath*:" + fallback + "-" + platform + ".sql");
		fallbackResources.add("classpath*:" + fallback + ".sql");
		return getResources(propertyName, fallbackResources, false);
	}

运行 Scripts

private void runScripts(List<Resource> resources, String username, String password) {
		if (resources.isEmpty()) {
			return;
		}
		ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
		populator.setContinueOnError(this.properties.isContinueOnError());
		populator.setSeparator(this.properties.getSeparator());
		if (this.properties.getSqlScriptEncoding() != null) {
			populator.setSqlScriptEncoding(this.properties.getSqlScriptEncoding().name());
		}
		for (Resource resource : resources) {
			populator.addScript(resource);
		}
		 //这里获取的还是@primary指定的数据源
		DataSource dataSource = this.dataSource;
		 //关键是这一步，如果username这两个多有的话，数据源就变成spring.datasource.url了，初始化schema的数据源，独立于其他的数据源
        //如果要用@primary的数据源，不指定data-username，schema-username即可即可
		if (StringUtils.hasText(username) && StringUtils.hasText(password)) {
			dataSource = DataSourceBuilder.create(this.properties.getClassLoader())
					.driverClassName(this.properties.determineDriverClassName()).url(this.properties.determineUrl())
					.username(username).password(password).build();
		}
		DatabasePopulatorUtils.execute(populator, dataSource);
	}

总结

DataSourceAutoConfiguration 导入DataSourceInitializationConfiguration
DataSourceInitializationConfiguration导入 DataSourceInitializerInvoker
在DataSourceInitializerInvoker初始化后DataSourceInitializerInvoker会调用 afterPropertiesSet 方法，进而运行 sql文件
默认使用主数据源运行 classpath*:schema-all.sql,classpath*:schema.sql和 classpath*:data-all.sql,classpath*:data.sql
只能通过 spring.datasource- 来指定 sql文件的路径
如果不想使用主数据源运行sql文件，可以通过 spring.datasource-来指定数据源，但必须指定对应的用户名密码

示例：

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `departmentName` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

    schema:
      - classpath:department.sql
    initialization-mode: always
    # always 在项目启动的时候就会重新创建表

6.1.2 JDBCTemplate

SpringBoot自动配置了JdbcTemplate操作数据库,在使用的时候可以直接将其使用Sping的@Autowired注解进行注入

示例：使用JDBCTemplate

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @GetMapping("query")
    public Map<String, Object> map() {
        return jdbcTemplate.queryForList("select * from department").get(0);
    }

6.2 druid

6.2.1 druid整合

1.依赖druid

        <!-- 引入druid数据源 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.8</version>
        </dependency>

2.将关联配置

spring:
  datasource:
    username: root
    password: 123456
    url: jdbc:mysql://10.8.31.89:3306/jdbc
    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
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
#    schema:
#      - classpath:department.sql

@Configuration
public class DruidConfig {
    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource druid() {
        return new DruidDataSource();
    }
}

6.2.2 druid的监控

1. 注入 StatViewServlet

@Bean
    public ServletRegistrationBean setViewServlet() {
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        Map<String, String> initParams = new HashMap<>();
        initParams.put("initParams", "admin");
        initParams.put("loginPassword", "123456");
        initParams.put("allow", "");
        initParams.put("deny", " 192.168.56.1");
        servletRegistrationBean.setInitParameters(initParams);
        return servletRegistrationBean;
    }

2.注入webStartFilter

    @Bean
    public FilterRegistrationBean webStartFilter() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
        Map<String, String> initParams = new HashMap<>();

        filterRegistrationBean.setFilter(new WebStatFilter());
        initParams.put("exclusions","*.js,*.css,/druid/*");
        filterRegistrationBean.setInitParameters(initParams);
        filterRegistrationBean.setUrlPatterns(Arrays.asList("/*"));
        return filterRegistrationBean;
    }

durid监控图

6.3 整合mybaties

6.3.1 基础环境搭建

1. 引入依赖

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

依赖导入

1. 配置数据源，相关属性见上一节
1. 给数据库建表
1. 创建javaBean

1. 注解版Mapper

/**
 * 这个一个操作数据的Mapper
 */
@Mapper
public interface DepartmentMapper {
    @Select("select * from department where id=#{id}")
    public Department getDepartById(Integer id);

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

    @Options(useGeneratedKeys = true, keyProperty = "id")
    @Insert("INSERT into department(departmentName) VALUES(#{departmentName})")
    public int insertDept(Department department);

    @Update("update department SET departmentName=#{departmentName} WHERE id=#{id}")
    public int updateDept(Department department);

}

2. 自定义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);
            }
        };
    }
}

3. 使用MapperScan批量扫描Mapper

@MapperScan(value = “com.study.springboot.mapper”)

4. XML配置版Mapper

// @Mapper或者@MapperScaner将揭开装配到容器中
public interface EmployeeMapper {
    public Employee getEmployeeById(Integer id);

    public void insertEmp(Employee employee);
}

<?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.study.springboot.mapper.EmployeeMapper">
    <select id="getEmployeeById" resultType="com.study.springboot.bean.Employee">
        SELECT * FROM employee WHERE id = #{id}
    </select>
    <insert id="insertEmp">
        INSERT INTO employee(lastName,email,gender,d_id) VALUES(#{lastName},#{email},#{gender},#{email},#{dId})
    </insert>
</mapper>

5. Mybatis全局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="com.study.springboot.mapper.EmployeeMapper">
    <select id="getEmployeeById" resultType="com.study.springboot.bean.Employee">
        SELECT * FROM employee WHERE id = #{id}
    </select>
    <insert id="insertEmp">
        INSERT INTO employee(lastName,email,gender,d_id) VALUES(#{lastName},#{email},#{gender},#{email},#{dId})
    </insert>
</mapper>

6. Mybatis与SpringBoot文件关联

mybatis:
  config-location: classpath:mybatis/mybatis.config.xml
  mapper-locations: classpath:mybatis/mapper/*.xml

6.4 整合SpringDataJPA

6.4.1 SpringData简介

JPA

6.4.2 整合SpringDataJPA

JPA:ORM（Object Relational Mapping）；

1. 编写一个实体类（bean）和数据表进行映射，并且配置好关系

package com.study.entity;

import javax.persistence.*;

// 使用JPA注解配置映射关系
@Entity
// @Table来指定和那个数据表对应，如果省略默认标注就是user
@Table(name = "tbl_user")
public class User {
    // 这个是一个主键
    @Id
    // 自增主键
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    @Column(name = "last_name",length = 50)
    private String lastName;
    // 省略默认就是属性名
    @Column
    private String email;

    public Integer getId() {
        return id;
    }

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

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }
}

1. 编写一个Dao接口来操作实体类对应的表（Repository）

// 继承JPARepository来完成对数据库的操作
public interface UserRepository extends JpaRepository<User, Integer> {
}

1. 基本配置

spring:
  jpa:
    hibernate:
      #   更新或者创建数据表结构
      ddl-auto: update
      #  控制台显示SQL
    show-sql: true