SpringBoot 第五章SpringBoot与数据访问
资料
- 开启jdbc
spring.datasource.initialization-mode=always
简介
对于数据访问层，无论是sql还是nosql，springboot默认采用整合springdata的方式进行统一处理，添加大量的自动配置，屏蔽了很多设置，引入了各种template，xxxRepository来简化我们对数据访问层的操作
JDBC
需要的pom文件
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.15</version> </dependency>JDBC的去对应路径找配置文件的属性
@ConfigurationProperties(prefix = "spring.datasource") public class DataSourceProperties implements BeanClassLoaderAware, InitializingBean { private ClassLoader classLoader;测试类
@RunWith(SpringRunner.class) @SpringBootTest public class MyTest { @Autowired DataSource dataSource; @Test public void test() throws SQLException { //class com.zaxxer.hikari.HikariDataSource System.out.println(dataSource.getClass()); //HikariProxyConnection@683523720 wrapping com.mysql.cj.jdbc.ConnectionImpl@6a87026 System.out.println(dataSource.getConnection()); } }数据库配置文件
#数据库相关配置 spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver spring.datasource.url=jdbc:mysql://localhost:3306/tx?serverTimezone=UTC&setUnicode=true&characterEncoding=utf8 spring.datasource.username=root spring.datasource.password=123456
自动配置原理：org.springframework.boot.autoconfigure.jdbc：
可以使用spring.datasource.type指定自定义的数据源类型；默认的数据源 在spring.datasource.type取出配置文件 @Configuration(proxyBeanMethods = false) @ConditionalOnClass(HikariDataSource.class) @ConditionalOnMissingBean(DataSource.class) @ConditionalOnProperty(name = "spring.datasource.type", havingValue = "com.zaxxer.hikari.HikariDataSource", matchIfMissing = true) static class Hikari { @Bean @ConfigurationProperties(prefix = "spring.datasource.hikari") HikariDataSource dataSource(DataSourceProperties properties) { HikariDataSource dataSource = createDataSource(properties, HikariDataSource.class); if (StringUtils.hasText(properties.getName())) { dataSource.setPoolName(properties.getName()); } return dataSource; } }SpringBoot默认可以支持；
org.apache.tomcat.jdbc.pool.DataSource、HikariDataSource、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(); } } || \/ public T build() { Class<? extends DataSource> type = this.getType(); DataSource result = (DataSource)BeanUtils.instantiateClass(type); this.maybeGetDriverClassName(); //数据绑定 this.bind(result); return result; }DataSourceInitializationConfiguration
@Import({ DataSourcePoolMetadataProvidersConfiguration.class, DataSourceInitializationConfiguration.class }) public class DataSourceAutoConfiguration {} || \/ @Configuration(proxyBeanMethods = false) @Import({ DataSourceInitializerInvoker.class, DataSourceInitializationConfiguration.Registrar.class }) class DataSourceInitializationConfiguration {} || \/ DataSourceInitializerInvoker【是一个***】 class DataSourceInitializerInvoker implements ApplicationListener<DataSourceSchemaCreatedEvent>, InitializingBean { //监听每一个事件 @Override public void onApplicationEvent(DataSourceSchemaCreatedEvent event) { // NOTE the event can happen more than once and // the event datasource is not used here DataSourceInitializer initializer = getDataSourceInitializer(); if (!this.initialized && initializer != null) { initializer.initSchema();//---->下面的initSchema this.initialized = true; } } } || \/ DataSourceInitializer的 void initSchema() { List<Resource> scripts = getScripts("spring.datasource.data", this.properties.getData(), "data"); if (!scripts.isEmpty()) { if (!isEnabled()) { logger.debug("Initialization disabled (not running data scripts)"); return; } String username = this.properties.getDataUsername(); String password = this.properties.getDataPassword(); //runSchemaScripts();运行建表语句； //runDataScripts();也处理运行插入数据的sql语句； runScripts(scripts, username, password); } }默认只需要将文件命名为：
schema-*.sql==>InitializingBean#afterPropertiesSet() data-*.sql ===>DataSourceSchemaCreatedEvent 默认规则：schema.sql，schema-all.sql； 可以使用 schema: - classpath:department.sql 指定位置获取原理可以通过下面的方式得到
DataSourceInitializer 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); } //platform = "all" createSchema--->schema List<Resource> scripts = getScripts("spring.datasource.schema", this.properties.getSchema(), "schema"); initSchema ---> data List<Resource> scripts = getScripts("spring.datasource.data", this.properties.getData(), "data");@Autowired JdbcTemplate jdbcTemplate; jdbcTemplate.queryForList("select * from department");Druid 的配置
# 数据源其他配置 # 初始化大小，最小，最大 spring.datasource.initialSize=5 spring.datasource.minIdle=5 spring.datasource.maxActive=20 # 配置获取连接等待超时的时间 spring.datasource.maxWait=60000 # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 spring.datasource.timeBetweenEvictionRunsMillis=60000 # 配置一个连接在池中最小生存的时间，单位是毫秒 spring.datasource.minEvictableIdleTimeMillis=300000 spring.datasource.validationQuery=SELECT 1 FROM DUAL spring.datasource.testWhileIdle=true spring.datasource.testOnBorrow=false spring.datasource.testOnReturn=false # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙 spring.datasource.filters=stat,wall,slf4j spring.datasource.maxPoolPreparedStatementPerConnectionSize=20 spring.datasource.useGlobalDataSourceStat: true spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000 # StatViewServlet配置，说明请参考Druid Wiki，配置_StatViewServlet配置 spring.datasource.druid.stat-view-servlet.enabled=true spring.datasource.druid.stat-view-servlet.url-pattern=/druid/* spring.datasource.druid.stat-view-servlet.login-username=admin spring.datasource.druid.stat-view-servlet.login-password=123456 spring.datasource.druid.stat-view-servlet.allow=127.0.0.1 # WebStatFilter配置，说明请参考Druid Wiki，配置_配置WebStatFilter spring.datasource.druid.web-stat-filter.enabled=true spring.datasource.druid.web-stat-filter.url-pattern=/* spring.datasource.druid.web-stat-filter.exclusions='*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*'
hikari
# Hikari will use the above plus the following to setup connection pooling spring.datasource.type=com.zaxxer.hikari.HikariDataSource # 等待连接池分配连接的最大时长（毫秒），超过这个时长还没可用的连接则发生SQLException， 默认:30秒 spring.datasource.hikari.connection-timeout=30000 # 最小连接数 spring.datasource.hikari.minimum-idle=5 # 最大连接数 spring.datasource.hikari.maximum-pool-size=15 # 自动提交 spring.datasource.hikari.auto-commit=true # 一个连接idle状态的最大时长（毫秒），超时则被释放（retired），默认:10分钟 spring.datasource.hikari.idle-timeout=600000 # 连接池名字 spring.datasource.hikari.pool-name=DatebookHikariCP # 一个连接的生命时长（毫秒），超时而且没被使用则被释放（retired），默认:30分钟 1800000ms，建议设置比数据库超时时长少60秒，参考MySQL wait_timeout参数（show variables like '%timeout%';） --> spring.datasource.hikari.max-lifetime=28740000 spring.datasource.hikari.connection-test-query=SELECT 1
使用Druid 进行加载
<dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.17</version> </dependency>
需要修改默认的数据源
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
效果
springboot + mybatis
pom文件
<dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.0</version> </dependency>直接return一个对象 需要转化为json数据显示到页面 需要在Bean加入get方法 否则无法转化为json 通过insert 可以得到 通过请求已经加入了很多数据 但是页面却无法显示
Bean对象代码
public class Department { private Integer id; private String departmentName; public void setId(Integer id) { this.id = id; } public void setDepartmentName(String departmentName) { this.departmentName = departmentName; } public Integer getId() { return id; } public String getDepartmentName() { return departmentName; } @Override public String toString() { return "Department{" + "id=" + id + ", departmentName='" + departmentName + '\'' + '}'; } }控制类
@RestController public class DepartmentController { @Autowired DepartmentMapper departmentMapper; @GetMapping("/dept/{id}") public Department getDepartment(@PathVariable("id") Integer id) { Department department = departmentMapper.getDepartment(id); return department; } @GetMapping("/dept") public Department insertDepartment(Department department) { departmentMapper.insertDepartment(department); return department; } }Mapper类
@Repository @Mapper <----配置后既能识别Mybatis public interface DepartmentMapper { @Select(value = "select * from department where id = #{id}") public Department getDepartment(Integer id); @Insert("insert department (departmentName) values(#{departmentName})") public int insertDepartment(Department department); }利用下面的代码，可以得到插入后的id【自增主键】
@Options(useGeneratedKeys = true)
开启驼峰命名
@Bean //这是mybatis的配置 public ConfigurationCustomizer configurationCustomizer(){ return new ConfigurationCustomizer() { @Override public void customize(org.apache.ibatis.session.Configuration configuration) { configuration.setMapUnderscoreToCamelCase(true); } }; }
通过普通的xml进行配置
- Consider defining a bean of type 'XXX' in you configuration
@MapperScan(value = "com.project.springbootdemo.mapper") @SpringBootApplication public class SpringbootdemoApplication {} - mybatis配置文件
<?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="mapUnderscoreToCamelCase" value="true"/> </settings> </configuration> - mapper配置文件
<?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.project.springbootdemo.mapper.DepartmentMapper"> <select id="getDepartment" resultType="com.project.springbootdemo.bean.Department"> select * from department where id = #{id} </select> <insert id="insertDepartment" parameterType="com.project.springbootdemo.bean.Department"> insert into department (departmentName) values(#{departmentName}) </insert> </mapper> - mapper接口
@Repository public interface DepartmentMapper { public Department getDepartment(Integer id); public int insertDepartment(Department department); } - application配置文件
mybatis.config-location=classpath:mybatis/mybatis-config.xml mybatis.mapper-locations=classpath:mybatis/mapper/*.xml
Spring Data
JPA
配置文件
spring.jpa.hibernate.ddl-auto=update
bean
@Entity @Table(name = "department") public class Department { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) @Column(name = "id") private Integer id; @Column(name = "departmentName") private String departmentName; set+get }
