Chapter7: SpringBoot与数据访问

尚硅谷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 DataSourcePropertiesimplements 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)@Deprecatedstatic 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 {@Beanpublic 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@ConditionalOnMissingBeanpublic 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方法@Overridepublic 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 hereif (!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;}// 初始化@PostConstructpublic 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=allString 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.sqlfallbackResources.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());@Autowiredprivate 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")@Beanpublic DataSource druidDataSource() {return new DruidDataSource();}// 配置Druid监控// 1. 配置一个管理后台的Servlet@Beanpublic 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@Beanpublic 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")@ConditionalOnMissingBeanpublic 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 configurationPUBLIC "-//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 mapperPUBLIC "-//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 {@AutowiredUserRepository 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返回}
}

测试 http://localhost:8082/boot1/user?lastName=admin&email=[e-mail]

访问 http://localhost:8082/boot1/user/1