Chapter7: SpringBoot与数据访问网站首页 技术教程

Chapter7: SpringBoot与数据访问

crysw 2024-10-06 12:01:04

简介Chapter7: SpringBoot与数据访问

1. JDBC

1.1 依赖及配置

<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<scope>runtime</scope>
	<version>8.0.12</version>
</dependency>

全局配置文件配置数据源基础参数

## 数据源
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.url=jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

数据源的配置属性封装在DataSourceProperties里面，支持在全局配置文件配置spring.datasource.xxx的属性。

@ConfigurationProperties(prefix = "spring.datasource")
public class DataSourceProperties
		implements BeanClassLoaderAware, EnvironmentAware, InitializingBean {
    private Class<? extends DataSource> type;
    private String driverClassName;
    private String url;
    private String username;
	private String password;
    // ...
    // 构建数据源
    public DataSourceBuilder initializeDataSourceBuilder() {
		return DataSourceBuilder.create(getClassLoader()).type(getType())
				.driverClassName(determineDriverClassName()).url(determineUrl())
				.username(determineUsername()).password(determinePassword());
	}
    // ...
}

1.2 数据源自动配置原理

数据源的自动配置查看自动配置类DataSourceAutoConfiguration, 导入了DataSourceConfiguration数据源配置类，数据源实例化的相关配置都在这个类中;

@Configuration
@ConditionalOnClass({ DataSource.class, EmbeddedDatabaseType.class })
@EnableConfigurationProperties(DataSourceProperties.class)
@Import({ Registrar.class, DataSourcePoolMetadataProvidersConfiguration.class })
public class DataSourceAutoConfiguration {
    // ...
    @Configuration
	@Conditional(PooledDataSourceCondition.class)
	@ConditionalOnMissingBean({ DataSource.class, XADataSource.class })
	@Import({ DataSourceConfiguration.Tomcat.class, DataSourceConfiguration.Hikari.class,
			DataSourceConfiguration.Dbcp.class, DataSourceConfiguration.Dbcp2.class,
			DataSourceConfiguration.Generic.class })
	@SuppressWarnings("deprecation")
	protected static class PooledDataSourceConfiguration {

	}
    // ...
}

上面的自动配置类中导入了配置类DataSourceConfiguration, web模块自动导入了spring-boot-starter-tomcat, 默认使用org.apache.tomcat.jdbc.pool.DataSource作为数据源, 可以使用spring.datasource.type指定数据源类型。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-e7uqYZUV-1686468913853)(SpringBoot学习笔记-atguigu.assets/1686146719144.png)]

abstract class DataSourceConfiguration {
	@SuppressWarnings("unchecked")
	protected <T> T createDataSource(DataSourceProperties properties,
			Class<? extends DataSource> type) {
        // 根据导入的数据源类型type，实例化数据源
		return (T) properties.initializeDataSourceBuilder().type(type).build();
	}
	/**
	 * Tomcat Pool DataSource configuration.
	 */
	@ConditionalOnClass(org.apache.tomcat.jdbc.pool.DataSource.class)
	@ConditionalOnProperty(name = "spring.datasource.type", havingValue = "org.apache.tomcat.jdbc.pool.DataSource", matchIfMissing = true)
	static class Tomcat extends DataSourceConfiguration {
		@Bean
		@ConfigurationProperties(prefix = "spring.datasource.tomcat")
		public org.apache.tomcat.jdbc.pool.DataSource dataSource(
				DataSourceProperties properties) {
            // 创建数据库连接池
			org.apache.tomcat.jdbc.pool.DataSource dataSource = 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;
		}
	}
	/**
	 * Hikari DataSource configuration.
	 */
	@ConditionalOnClass(HikariDataSource.class)
	@ConditionalOnProperty(name = "spring.datasource.type", havingValue = "com.zaxxer.hikari.HikariDataSource", matchIfMissing = true)
	static class Hikari extends DataSourceConfiguration {
		@Bean
		@ConfigurationProperties(prefix = "spring.datasource.hikari")
		public HikariDataSource dataSource(DataSourceProperties properties) {
			return createDataSource(properties, HikariDataSource.class);
		}
	}
	/**
	 * DBCP DataSource configuration.
	 *
	 * @deprecated as of 1.5 in favor of DBCP2
	 */
	@ConditionalOnClass(org.apache.commons.dbcp.BasicDataSource.class)
	@ConditionalOnProperty(name = "spring.datasource.type", havingValue = "org.apache.commons.dbcp.BasicDataSource", matchIfMissing = true)
	@Deprecated
	static class Dbcp extends DataSourceConfiguration {
		@Bean
		@ConfigurationProperties(prefix = "spring.datasource.dbcp")
		public org.apache.commons.dbcp.BasicDataSource dataSource(
				DataSourceProperties properties) {
			org.apache.commons.dbcp.BasicDataSource dataSource = createDataSource(
					properties, org.apache.commons.dbcp.BasicDataSource.class);
			DatabaseDriver databaseDriver = DatabaseDriver
					.fromJdbcUrl(properties.determineUrl());
			String validationQuery = databaseDriver.getValidationQuery();
			if (validationQuery != null) {
				dataSource.setTestOnBorrow(true);
				dataSource.setValidationQuery(validationQuery);
			}
			return dataSource;
		}
	}
	/**
	 * DBCP DataSource configuration.
	 */
	@ConditionalOnClass(org.apache.commons.dbcp2.BasicDataSource.class)
	@ConditionalOnProperty(name = "spring.datasource.type", havingValue = "org.apache.commons.dbcp2.BasicDataSource", matchIfMissing = true)
	static class Dbcp2 extends DataSourceConfiguration {
		@Bean
		@ConfigurationProperties(prefix = "spring.datasource.dbcp2")
		public org.apache.commons.dbcp2.BasicDataSource dataSource(
				DataSourceProperties properties) {
			return createDataSource(properties,
					org.apache.commons.dbcp2.BasicDataSource.class);
		}
	}
	/**
	 * Generic DataSource configuration.
	 */
	@ConditionalOnMissingBean(DataSource.class)
    // 使用spring.datasource.type指定数据源类型
	@ConditionalOnProperty(name = "spring.datasource.type")
	static class Generic {
		@Bean
		public DataSource dataSource(DataSourceProperties properties) {
			return properties.initializeDataSourceBuilder().build();
		}
	}
}

DataSourceBuilder#build()方法实例化数据源, 默认支持的数据源类型有：org.apache.tomcat.jdbc.pool.DataSource
org.apache.commons.dbcp.BasicDataSource
org.apache.commons.dbcp2.BasicDataSource
com.zaxxer.hikari.HikariDataSource

public class DataSourceBuilder {

	private static final String[] DATA_SOURCE_TYPE_NAMES = new String[] {
			"org.apache.tomcat.jdbc.pool.DataSource",
			"com.zaxxer.hikari.HikariDataSource",
			"org.apache.commons.dbcp.BasicDataSource", // deprecated 过时了
			"org.apache.commons.dbcp2.BasicDataSource" };

	private Class<? extends DataSource> type;
	
	// ...
	
	public DataSource build() {
        // 返回的就是当前builder的数据源类型type
        //type出处: DataSourceProperties#initializeDataSourceBuilder().type(type)
        Class<? extends DataSource> type = getType();
        DataSource result = BeanUtils.instantiate(type);
        maybeGetDriverClassName();
        bind(result);
        return result;
	}

	public DataSourceBuilder type(Class<? extends DataSource> type) {
		this.type = type;
		return this;
	}
	// ...
}

1.3 数据源数据初始化

数据源自动配置类中实例化DataSourceInitializer，数据源的一些初始化工作在这里面完成。

@Configuration
@ConditionalOnClass({ DataSource.class, EmbeddedDatabaseType.class })
@EnableConfigurationProperties(DataSourceProperties.class)
@Import({ Registrar.class, DataSourcePoolMetadataProvidersConfiguration.class })
public class DataSourceAutoConfiguration {
    // ...
    @Bean
	@ConditionalOnMissingBean
	public DataSourceInitializer dataSourceInitializer(DataSourceProperties properties,
			ApplicationContext applicationContext) {
		return new DataSourceInitializer(properties, applicationContext);
	}
    // ...
}

DataSourceInitializer实现了ApplicationListener接口，重写onApplicationEvent方法, 里面会调用runDataScripts()方法执行指定sql文件中插入数据的sql语句。

class DataSourceInitializer implements ApplicationListener<DataSourceInitializedEvent> {
	DataSourceInitializer(DataSourceProperties properties,
			ApplicationContext applicationContext) {
		this.properties = properties;
		this.applicationContext = applicationContext;
	}
	
    // 重写ApplicationListener#onApplicationEvent方法
	@Override
	public void onApplicationEvent(DataSourceInitializedEvent event) {
		if (!this.properties.isInitialize()) {
			logger.debug("Initialization disabled (not running data scripts)");
			return;
		}
		// NOTE the event can happen more than once and
		// the event datasource is not used here
		if (!this.initialized) {
            // 执行数据库脚本
			runDataScripts();
			this.initialized = true;
		}
	}

    // 执行数据库脚本
	private void runDataScripts() {
        // 获取spring.datasource.data配置的sql脚本
		List<Resource> scripts = getScripts("spring.datasource.data",
				this.properties.getData(), "data");
		String username = this.properties.getDataUsername();
		String password = this.properties.getDataPassword();
        // 运行脚本
		runScripts(scripts, username, password);
	}
	 // 运行脚本
	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);
		}
		DataSource dataSource = this.dataSource;
		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();
		}
        // 获取数据库连接，执行sql语句
		DatabasePopulatorUtils.execute(populator, dataSource);
	}
}

DataSourceInitializer中的init方法使用@PostConstruct注解声明，在实例化完成后会执行该方法进行初始化工作，里面通过runSchemaScripts() 运行sql脚本执行了创建表的操作。

class DataSourceInitializer implements ApplicationListener<DataSourceInitializedEvent> {
	DataSourceInitializer(DataSourceProperties properties,
			ApplicationContext applicationContext) {
		this.properties = properties;
		this.applicationContext = applicationContext;
	}
	
    // 初始化
	@PostConstruct
	public void init() {
		if (!this.properties.isInitialize()) {
			logger.debug("Initialization disabled (not running DDL scripts)");
			return;
		}
		if (this.applicationContext.getBeanNamesForType(DataSource.class, false,
				false).length > 0) {
			this.dataSource = this.applicationContext.getBean(DataSource.class);
		}
		if (this.dataSource == null) {
			logger.debug("No DataSource found so not initializing");
			return;
		}
        // 运行schema脚本
		runSchemaScripts();
	}
	// 运行schema脚本
	private void runSchemaScripts() {
        // 获取spring.datasource.schema配置的schema脚本
		List<Resource> scripts = getScripts("spring.datasource.schema",
				this.properties.getSchema(), "schema");
		if (!scripts.isEmpty()) {
			String username = this.properties.getSchemaUsername();
			String password = this.properties.getSchemaPassword();
			runScripts(scripts, username, password);
			try {
				this.applicationContext
						.publishEvent(new DataSourceInitializedEvent(this.dataSource));
				// The listener might not be registered yet, so don't rely on it.
				if (!this.initialized) {
					runDataScripts();
					this.initialized = true;
				}
			}
			catch (IllegalStateException ex) {
				logger.warn("Could not send event to complete DataSource initialization ("
						+ ex.getMessage() + ")");
			}
		}
	}
}

上面运行的两个脚本都是从DataSourceInitializer#getScripts方法中查找得到的，支持执行类路径下指定的脚本文件，如果没有指定就执行默认的脚本。

spring.datasource.platform配置xxx，那么脚本文件默认命名为schema-xxx.sql 或 data-xxx.sql ；

spring.datasource.platform没有配置，脚本文件默认为schema.sql或schema-all.sql, 可以使用schema属性指定加载sql的位置

// resources=spring.datasource.data or spring.datasource.schema指定的sql脚本
// fallback=data or schame
private List<Resource> getScripts(String propertyName, List<String> resources,
		String fallback) {
	if (resources != null) {
        // 如果spring.datasource.data 或 spring.datasource.schema有配置指定的脚本，就执行此处查找脚本资源。
		return getResources(propertyName, resources, true);
	}
    // 如果spring.datasource.data 或 spring.datasource.schema没有配置，就查找类路径下默认的脚本
    // platform=all
	String platform = this.properties.getPlatform();
	List<String> fallbackResources = new ArrayList<String>();
    // 默认脚本 schema-all.sql or data-all.sql 
	fallbackResources.add("classpath*:" + fallback + "-" + platform + ".sql");
    // 默认脚本 schema.sql or data.sql
	fallbackResources.add("classpath*:" + fallback + ".sql");
	return getResources(propertyName, fallbackResources, false);
}

全局配置文件配置执行的脚本, 建表脚本teacher.sql和初始化数据脚本initTeacher.sql

# 初始化执行sql
#spring.datasource.platform=xxx
spring.datasource.schema=classpath*:ddlSql/teacher.sql
spring.datasource.data=classpath*:ddlSql/initTeacher.sql

teacher.sql脚本内容

 CREATE TABLE `teacher` (
  `tid` int(11) NOT NULL AUTO_INCREMENT,
  `tname` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

initTeacher.sql脚本内容

insert into teacher(tid,tname) values (null,'王老师'),(null,'crysw老师');

启动应用，查看启动日志。

[2023-06-07 23:01:36.036] [com.alibaba.druid.pool.DruidDataSource] [main] [923] [INFO ] {dataSource-1} inited
[2023-06-07 23:01:36.036] [org.springframework.jdbc.datasource.init.ScriptUtils] [main] 
// 运行sql脚本teacher.sql
[441] [INFO ] Executing SQL script from URL [file:/D:/Develops/IdeaProjects/study-spring-boot/spring-boot-atguigu/spring-boot-02-config/target/classes/ddlSql/teacher.sql]
[2023-06-07 23:01:36.036] [org.springframework.jdbc.datasource.init.ScriptUtils] [main] 
// 创建表teacher
[473] [DEBUG] 0 returned as update count for SQL: CREATE TABLE `teacher` ( `tid` int(11) NOT NULL AUTO_INCREMENT, `tname` varchar(50) DEFAULT NULL, PRIMARY KEY (`tid`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
[2023-06-07 23:01:36.036] [org.springframework.jdbc.datasource.init.ScriptUtils] [main] [476] [DEBUG] SQLWarning ignored: SQL state 'HY000', error code '1681', message [Integer display width is deprecated and will be removed in a future release.]
[2023-06-07 23:01:36.036] [org.springframework.jdbc.datasource.init.ScriptUtils] [main] [476] [DEBUG] SQLWarning ignored: SQL state 'HY000', error code '3719', message ['utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.]
[2023-06-07 23:01:36.036] [org.springframework.jdbc.datasource.init.ScriptUtils] [main] [507] [INFO ] Executed SQL script from URL [file:/D:/Develops/IdeaProjects/study-spring-boot/spring-boot-atguigu/spring-boot-02-config/target/classes/ddlSql/teacher.sql] in 28 ms.
[2023-06-07 23:01:36.036] [org.springframework.jdbc.datasource.DataSourceUtils] [main] [329] [DEBUG] Returning JDBC Connection to DataSource
[2023-06-07 23:01:36.036] [org.springframework.jdbc.datasource.DataSourceUtils] [main] [110] [DEBUG] Fetching JDBC Connection from DataSource
// 运行sql脚本initTeacher.sql
[2023-06-07 23:01:36.036] [org.springframework.jdbc.datasource.init.ScriptUtils] [main] [441] [INFO ] Executing SQL script from URL [file:/D:/Develops/IdeaProjects/study-spring-boot/spring-boot-atguigu/spring-boot-02-config/target/classes/ddlSql/initTeacher.sql]
[2023-06-07 23:01:36.036] [org.springframework.jdbc.datasource.init.ScriptUtils] [main] 
// 插入初始化数据到teacher表
[473] [DEBUG] 2 returned as update count for SQL: insert into teacher(tid,tname) values (null,'王老师'),(null,'crysw老师')
[2023-06-07 23:01:36.036] [org.springframework.jdbc.datasource.init.ScriptUtils] [main] [507] [INFO ] Executed SQL script from URL [file:/D:/Develops/IdeaProjects/study-spring-boot/spring-boot-atguigu/spring-boot-02-config/target/classes/ddlSql/initTeacher.sql] in 4 ms.

1.4 数据模板

spring提供了数据库模板JdbcTemplate给我们来操作数据库, 自动配置类JdbcTemplateAutoConfiguration提供了jdbcTemplate组件。

@Configuration
@ConditionalOnClass({ DataSource.class, JdbcTemplate.class })
@ConditionalOnSingleCandidate(DataSource.class)
@AutoConfigureAfter(DataSourceAutoConfiguration.class)
public class JdbcTemplateAutoConfiguration {

	private final DataSource dataSource;

	public JdbcTemplateAutoConfiguration(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	@Bean
	@Primary
	@ConditionalOnMissingBean(JdbcOperations.class)
	public JdbcTemplate jdbcTemplate() {
		return new JdbcTemplate(this.dataSource);
	}
}

编写controller，使用数据模板jdbcTemplate操作数据库测试。

@Controller
public class HelloController {

    Logger logger = LoggerFactory.getLogger(this.getClass());

    @Autowired
    private JdbcTemplate jdbcTemplate;


    @ResponseBody
    @GetMapping("/queryTeacher")
    public Map<String, Object> queryTeacher() {
        List<Map<String, Object>> teachers = jdbcTemplate.queryForList("select * from teacher");
        return teachers.get(0);

    }
}

访问 http://localhost:8082/boot1/queryTeacher, 返回结果：

{
  "tid": 1,
  "tname": "王老师"
}

2. 整合Druid数据源

2.1 依赖及配置

导入依赖

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

全局配置文件指定数据库连接池类型（指定数据源）

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource

编写配置类，整合Druid数据源

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

    // 配置Druid监控
    // 1. 配置一个管理后台的Servlet
    @Bean
    public ServletRegistrationBean statViewServlet() {
        ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");

        Map<String, String> initParams = new HashMap<>();
        // 设置Druid监控登录用户和密码
        initParams.put("loginUsername", "admin");
        initParams.put("loginPassword", "123456");
        initParams.put("allow", ""); // 默认允许所有
        initParams.put("deny", "192.168.1.103"); // 拒绝该主机访问
        bean.setInitParameters(initParams);
        return bean;
    }

    // 2. 配置一个web监控的filter
    @Bean
    public FilterRegistrationBean webStatFilter() {

        FilterRegistrationBean filterBean = new FilterRegistrationBean();
        filterBean.setFilter(new WebStatFilter());

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

        filterBean.setInitParameters(filterParams);
        filterBean.setUrlPatterns(Arrays.asList("/*"));
        return filterBean;
    }
}

2.2 druid监控

启动应用，访问 http://localhost:8082/boot1/druid 进入Druid监控登录界面
在这里插入图片描述

访问查询请求 , http://localhost:8082/boot1/queryTeacher 查看监控。
在这里插入图片描述

2.3 druid自动配置类

上面是自定义的数据源配置类，提供了DruidDataSource实例。还可以使用druid官方提供的启动器提供了自动配置类 DruidDataSourceAutoConfigure, 不用自己编写配置类。

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.2.3</version>
</dependency>

自动配置类 DruidDataSourceAutoConfigure

@Configuration
@ConditionalOnClass(DruidDataSource.class)
@AutoConfigureBefore(DataSourceAutoConfiguration.class)
@EnableConfigurationProperties({DruidStatProperties.class, DataSourceProperties.class})
@Import({DruidSpringAopConfiguration.class,
         // 监控的servlet配置
    DruidStatViewServletConfiguration.class,
         // 监控的filter配置
    DruidWebStatFilterConfiguration.class,
         // 
    DruidFilterConfiguration.class})
public class DruidDataSourceAutoConfigure {

    private static final Logger LOGGER = LoggerFactory.getLogger(DruidDataSourceAutoConfigure.class);

    @Bean(initMethod = "init")
    @ConditionalOnMissingBean
    public DataSource dataSource() {
        LOGGER.info("Init DruidDataSource");
        return new DruidDataSourceWrapper();
    }
}

druid的监控servlet配置中，需要指定监控界面开启为true，才能访问监控界面；

@ConditionalOnWebApplication
@ConditionalOnProperty(name = "spring.datasource.druid.stat-view-servlet.enabled", havingValue = "true")
public class DruidStatViewServletConfiguration {//...
}

druid监控url请求，需要配置开启webFilter，才能实现请求的拦截监控。

@ConditionalOnWebApplication
@ConditionalOnProperty(name = "spring.datasource.druid.web-stat-filter.enabled", havingValue = "true")
public class DruidWebStatFilterConfiguration {//...
}

在全局配置文件中配置druid监控的相关参数

# 开启druid监控
spring.datasource.druid.stat-view-servlet.enabled=true
spring.datasource.druid.stat-view-servlet.login-username=admin
spring.datasource.druid.stat-view-servlet.login-password=12345678
spring.datasource.druid.stat-view-servlet.reset-enable=false
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
spring.datasource.druid.web-stat-filter.enabled=true
spring.datasource.druid.web-stat-filter.url-pattern=/*
spring.datasource.druid.web-stat-filter.exclusions=*.js,*.css,/druid/*

注释掉自定义的数据源配置类，再次启动应用访问 http://localhost:8082/boot1/druid 也成功进入Druid监控登录界面。随机触发几个请求，也可以实现监控。
在这里插入图片描述

druid监控博文学习

Spring系列之集成Druid连接池及监控配置 - 掘金

SpringBoot开启Druid监控统计功能(SQL监控、慢SQL记录、Spring监控、去广告)

3. 整合Mybatis

3.1 依赖管理

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

依赖关系图
在这里插入图片描述

3.2 mybatis核心配置及参数配置

在类路径下添加mybatis核心配置文件mybatis-config.xml

<?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>
        <!-- 打开延迟加载开关 -->
        <setting name="lazyLoadingEnabled" value="true"/>
        <!-- 将积极加载改为消极加载，即按需加载 -->
        <setting name="aggressiveLazyLoading" value="false"/>
        <!-- 开启二级缓存 -->
        <setting name="cacheEnabled" value="true"/>
        <!--开启驼峰映射-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
</configuration>

在全局配置文件application.properties中配置mybatis参数

## mybatis配置
mybatis.config-location=classpath:mybatis/mybatis-config.xml
mybatis.mapper-locations=classpath:mybatis/mapper/*.xml
mybatis.type-aliases-package=com.aiguigu.springboot02config.entities

3.3 mapper接口开发

注解版mapper接口，不需要mapper映射xml文件，sql直接写在接口方法上。

// 指定这是一个操作数据库的mapper接口
//@Mapper  // 注释掉后在启动类上加@MapperScan(value = {"com.aiguigu.spriingboot02config.mapper"})
public interface DepartmentMapper {

    @Select("select * from department where id=#{id}")
    public Department getDeptById(Integer id);

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

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

    @Update("update department set department_name=#{departmentName} where id=#{id}")
    public int updateDept(Department department);
}

mapper接口+mapper映射xml文件的方式

// @Mapper 或 @MapperScan将接口扫描装配到容器中
public interface EmployeeMapper {
    Employee2 getEmpById(Integer id);

    void insertEmp(Employee2 employee);
}

EmployeeMapper.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.aiguigu.springboot02config.mapper.EmployeeMapper">

    <select id="getEmpById" resultType="com.aiguigu.springboot02config.entities.Employee2">
        select * from employee where id = #{id}
    </select>

    <insert id="insertEmp" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
        insert into employee(lastName,email,gender,d_id)
        values(#{lastName},#{email},#{gender},#{d_id})
    </insert>
</mapper>

4. 整合JPA

4.1 Spring Data简介

Spring Data项目简化了基于Spring框架应用的数据访问技术，包括非关系型数据库、Map-Reduce框架、云数据服务等，另外也包含对关系数据库的访问支持。

Spring Data为我们提供使用统一的API来对数据访问层进行操作；这主要是Spring Data Commons项目来实现的。Spring Data Commons让我们在使用关系型或非关系型数据访问技术时都基于Spring提供的统一标准，标准包含了CRUD(创建、获取、更新、删除)、查询、排序和分页的相关操作。

Repository<T ID extends Serializable> 统一的Repository接口 ;

RevisionRepository<T, ID extends Serializable, N extends Number & Comparable<N>> 基于乐观锁机制；

CrudRepository<T, ID extends Serializable> 基本的CRUD操作；

PagingAndSortingRepository<T, ID extends Serializable> 基本CRUD及分页；

xxxTemplate 数据访问模板类 (JdbcTemplate, RedisTemplate);

JPA与Spring Data

JpaRepository 基本功能，编写接口继承JpaRepository，就有了crud及分页等基本功能；
定义符合规范的方法命名，在接口中只需要声明符合规范的方法，就拥有对应的功能；

在这里插入图片描述

@Query 自定义查询，定制查询SQL；

整合框架图

4.2 使用Spring Data JPA

导入依赖

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

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

// 使用JPA注解配置映射关系
@Entity // 标识这是一个实体类，和数据库表映射
@Table(name = "tb_user") // 指定映射的库表, 如果省略默认表名就是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 User() {
    }

    public User(Integer id, String lastName, String email) {
        this.id = id;
        this.lastName = lastName;
        this.email = email;
    }
 	// setXXX and getXXX   
}

编写一个Dao接口操作，实体类对应的数据库表（Repository）。

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

基本的配置

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

编写controller使用repository

@RestController
public class UserController {
    @Autowired
    UserRepository userRepository;

    @GetMapping("/user/{id}")
    public User getUser(@PathVariable("id") Integer id) {
        User user = userRepository.findOne(id);
        return user;
    }

    @GetMapping("/user")
    public User insertUser(User user) {
        User _user = userRepository.save(user);
        return _user; // 封装了自增主键id返回
    }
}