springboot与jdbc数据访问
1、springboot与JDBC
(1)、 pom.xml
<!--jdbc相关-->
<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>
</dependency>
(2)、 配置文件:
spring:
datasource:
username: root
password: root
url: jdbc:mysql://127.0.0.1:3306/jdbc?serverTimezone=Asia/Shanghai
driver-class-name: com.mysql.cj.jdbc.Driver
- 注意:当使用
springboot2.x
以上版本时,会默认引入mysql8.0
以上版本,如果使用驱动是driverClassName=com.mysql.jdbc.Driver
则会出现如下错误:
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
因此需要将驱动写为:driver-class-name: com.mysql.cj.jdbc.Driver
; com.mysql.jdbc.Driver
是mysql-connctor-java5
及5之前的,而com.mysql.cj.jdbc.Driver
是mysql-connector-java6
及6之后的;mysql8.0
以上版本还会存在时区问题,会报如下错误:
The server time zone value 'Öйú±ê׼ʱ¼ä' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.
需要设置一个ServerTimeZone
属性来设置一个时区,可以选择东8区的Hongkong
或者Asia/Shanghai
作为参数,url
修改后应为:url: jdbc:mysql://127.0.0.1:3306/jdbc?serverTimezone=Asia/Shanghai
(3)、 测试:
@SpringBootTest
class SpringBoot06DataJdbcApplicationTests {
@Autowired
DataSource dataSource;
@Test
void contextLoads() throws SQLException {
System.out.println("类:" + dataSource.getClass());
Connection connection = dataSource.getConnection();
System.out.println("链接:" + connection);
connection.close();
}
}
(4)、 输出结果:
类:class com.zaxxer.hikari.HikariDataSource
链接:HikariProxyConnection@865430810 wrapping com.mysql.cj.jdbc.ConnectionImpl@2e1ddc90
(5)、 结论:springboot2.x
默认使用的是HikariDataSource
数据源,数据源的相关配置在DataSourceProperties
类中。
(6)、数据源的自动配置原理(参照org.springframework.boot.autoconfigure.jdbc
下相关类)
<1>、DataSourceConfiguration
作用:根据配置类创建数据源,springboot2.x
默认使用的是Hikari
连接池,可以使用spring.datasource.type
来指定其他数据源类型。
以Hikari
数据源为例,分析注入各个数据源需要什么条件:
/**
* Hikari DataSource configuration.
*/
@Configuration(proxyBeanMethods = false)
@ConditionalOnClass(HikariDataSource.class)
@ConditionalOnMissingBean(DataSource.class)
@ConditionalOnProperty(name = "spring.datasource.type", havingValue = "com.zaxxer.hikari.HikariDataSource",
matchIfMissing = true)
(1)、@ConditionalOnClass(HikariDataSource.class)
要导入各个数据源对应的依赖,springboot2.x
默认导入的就是Hikari
(2)、@ConditionalOnMissingBean(DataSource.class)``ioc
容器中不存在数据源组件
(3)、@ConditionalOnProperty
以Hikari
为例,如果配置文件中没有spring.datasource.type
,则默认spring.datasource.type =com.zaxxer.hikari.HikariDataSource
,如果配置了spring.datasource.type
则 需要等于com.zaxxer.hikari.HikariDataSource
才能够满足条件。
(4)、springboot
默认支持的数据源:org.apache.tomcat.jdbc.pool.DataSource、HikariDataSource、BasicDataSource、oracle.ucp.jdbc.PoolDataSource、还可以自定义数据源
- 总结:
springboot2.x
默认支持的就是Hikari
数据源,如果需要使用其他数据源,只需要排除HikariDataSource
,导入其他的数据源即可,如果是springboot
支持的数据源,就不需要在配置文件中配置spring.datasource.type
,如果是别的数据源springboot
不支持的,如c3p0
,则需要在配置文件中配置spring.datasource.type=com.mchange.v2.c3p0.ComboPooledDataSource
<2>、自定义数据源类型
/**
* Generic DataSource configuration.
*/
@Configuration(proxyBeanMethods = false)
@ConditionalOnMissingBean(DataSource.class)
@ConditionalOnProperty(name = "spring.datasource.type")
static class Generic {
@Bean
DataSource dataSource(DataSourceProperties properties) {
//使用DataSourceBuilder创建数据源,利用反射创建响应type的数据源,并且通过bind方法绑定相关属性
return properties.initializeDataSourceBuilder().build();
}
}
2、DataSourceAutoConfiguration—>DataSourceInitializationConfiguration—>DataSourceInitializerInvoker(ApplicationListener)
作用:
runSchemaScripts();
运行建表语句;
runDataScripts();
运行插入数据的sql语句
- 注意:
springboot2.x
默认是吧自动建表关闭的,需要在application.yml
中手动开启initialization-mode: always
- 源码分析
/**
* Bean to handle {@link DataSource} initialization by running {@literal schema-*.sql} on
* {@link InitializingBean#afterPropertiesSet()} and {@literal data-*.sql} SQL scripts on
* a {@link DataSourceSchemaCreatedEvent}.
*
* @author Stephane Nicoll
* @see DataSourceAutoConfiguration
*/
class DataSourceInitializerInvoker implements ApplicationListener<DataSourceSchemaCreatedEvent>, InitializingBean {
@Override
public void afterPropertiesSet() {
DataSourceInitializer initializer = getDataSourceInitializer();
if (initializer != null) {
//执行建表语句
boolean schemaCreated = this.dataSourceInitializer.createSchema();
if (schemaCreated) {
initialize(initializer);
}
}
}
}
class DataSourceInitializer {
/**
* Create the schema if necessary.
* @return {@code true} if the schema was created
* @see DataSourceProperties#getSchema()
*/
//建表方法
boolean createSchema() {
//得到建表的sql文件
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;
}
String username = this.properties.getSchemaUsername();
String password = this.properties.getSchemaPassword();
runScripts(scripts, username, password);
}
return !scripts.isEmpty();
}
//获取建表sql的位置:resources是自己制定sql位置,是一个list,如果没有指定则默认去类路径下schema-all.sql或者schema.sql
//fallback(schema)
private List<Resource> getScripts(String propertyName, List<String> resources, String fallback) {
if (resources != null) {
return getResources(propertyName, resources, true);
}
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);
}
}
也可以在配置文件中指定sql语句的位置:
spring:
datasource:
username: root
password: root
url: jdbc:mysql://127.0.0.1:3306/jdbc?serverTimezone=Asia/Shanghai
driver-class-name: com.mysql.cj.jdbc.Driver
#springboot2.x默认是吧自动建表关闭的,需要手动开启
initialization-mode: always
schema:
#dep前面没有空格!
- classpath:dep-*.sql
3、 操作数据库:springboot
自动在IOC
容器中注入了JdbcTemplate
来操作数据库,需要使用JdbcTemplate
直接@Autowired
自动注入即可
org.springframework.boot.autoconfigure.jdbc.JdbcTemplateConfiguration
@Configuration(proxyBeanMethods = false)
@ConditionalOnMissingBean(JdbcOperations.class)
class JdbcTemplateConfiguration {
@Bean
@Primary
JdbcTemplate jdbcTemplate(DataSource dataSource, JdbcProperties properties) {
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
JdbcProperties.Template template = properties.getTemplate();
jdbcTemplate.setFetchSize(template.getFetchSize());
jdbcTemplate.setMaxRows(template.getMaxRows());
if (template.getQueryTimeout() != null) {
jdbcTemplate.setQueryTimeout((int) template.getQueryTimeout().getSeconds());
}
return jdbcTemplate;
}
}
/**
* @author cls
* @version 1.0
* @date 2020/12/8/0008
*/
@Controller
public class HelloController {
//直接自动注入即可
@Autowired
JdbcTemplate jdbcTemplate;
@GetMapping("/query")
@ResponseBody
public Map<String,Object> map(){
List<Map<String, Object>> maps = jdbcTemplate.queryForList("select * from department");
return maps.get(0);
}
}