第六章 Spring Boot 与数据访问
数据访问简介
JDBC
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>
配置数据源
支持的数据源
HikariDataSource
org.apache.tomcat.jdbc.pool.DataSource
org.apache.commons.dbcp2.BasicDataSource
自定义 DataSource
DataSourceInitializer
自动运行建表语句
运行插入数据的 sql 语句
文件名命名规则
scheme-*.sql
data-*.sql
或者指定文件名
application.yml
spring: # 配置数据源 datasource: username: root password: 123456 url: jdbc:mysql://localhost:3306/data driver-class-name: com.mysql.cj.jdbc.Driver # 始终执行初始化 initialization-mode: ALWAYS # 指定自动建表sql schema: - classpath:schema-all.sql
schema-all.sql
create table if not exists `person` ( `id` int, `name` varchar(255) );
-- 注意需要有分号结尾
查询数据
package com.example.demo.controller; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import java.util.List; import java.util.Map; /** * 人物数据接口 */ @RestController @RequestMapping("/person") public class PersonController { @Autowired private JdbcTemplate jdbcTemplate; @GetMapping("/list") public List<Map<String, Object>> list(){ String sql = "select * from person"; List<Map<String, Object>> list = jdbcTemplate.queryForList(sql); return list; } }
整合 Druid&配置数据源监控
Druid 能够提供强大的监控和扩展功能
druid 的配置
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,log4j maxPoolPreparedStatementPerConnectionSize: 20 useGlobalDataSourceStat: true connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMills=500
依赖
<dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.21</version> </dependency>
配置
spring: # 配置数据源 datasource: # 自定义数据源
type: com.alibaba.druid.pool.DruidDataSource
# 统计sql
filters: stat,wall
自定义配置类
package com.example.demo.config; import com.alibaba.druid.pool.DruidDataSource; import com.alibaba.druid.support.http.ResourceServlet; import com.alibaba.druid.support.http.StatViewServlet; import com.alibaba.druid.support.http.WebStatFilter; import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.boot.web.servlet.FilterRegistrationBean; import org.springframework.boot.web.servlet.ServletRegistrationBean; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import javax.sql.DataSource; import java.util.Arrays; import java.util.HashMap; import java.util.Map; @Configuration public class DruidConfig { // 加载druid的自定义参数 @ConfigurationProperties(prefix="spring.datasource") @Bean public DataSource druid(){ return new DruidDataSource(); } // 配置Druid监控 @Bean public ServletRegistrationBean statViewServlet(){ ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*"); Map<String, String> params = new HashMap<>(); params.put(ResourceServlet.PARAM_NAME_USERNAME, "admin"); params.put(ResourceServlet.PARAM_NAME_PASSWORD, "123456"); bean.setInitParameters(params); return bean; } // 配置filter @Bean public FilterRegistrationBean webStatFilter(){ FilterRegistrationBean bean = new FilterRegistrationBean(); bean.setFilter(new WebStatFilter()); bean.setUrlPatterns(Arrays.asList("/*")); Map<String, String> params = new HashMap<>(); params.put(WebStatFilter.PARAM_NAME_EXCLUSIONS, "*.js,*.css"); bean.setInitParameters(params); return bean; } }
后台监控地址
http://localhost:8080/druid/ • 1
整合 MyBatis（一）-基础环境搭建
mybatis-spring-boot-starter
<dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.3</version> </dependency>
整合 MyBatis（二）-注解版 MyBatis
src/main/java/com/example/demo/mapper/DepartmentMapper.java
package com.example.demo.mapper; import com.example.demo.pojo.Department; import org.apache.ibatis.annotations.*; // 指定这是一个操作数据库的mapper // @Mapper public interface DepartmentMapper { @Select("select * from department where id = #{id}") public Department getById(Integer id); @Delete("delete from department where id = #{id}") public int deleteById(Integer id); // 插入数据后自增主键自动设置到department @Options(useGeneratedKeys = true, keyProperty = "id") @Insert("insert into department(name) values(#{name}) ") public int insert(Department department); @Update("update department set name = #{name} where id = #{id} ") public int update(Department department); }
