SpringBoot(九) -- SpringBoot与数据访问
一.简介
对于数据访问层，无论是SQL还是NOSQL，Spring Boot默认采用整合Spring Data的方式进行统一处理，添加大量自动配置，屏蔽了很多设置。引入各种xxxTemplate，xxxRepository来简化我们对数据访问层的操作。对我们来说只需要进行简单的设置即可。
二.整合JDBC和数据源
1.查看POM依赖:
2.配置datasource:
3.观察其自动装配:
4.在其运行时,具有一个DataSourceConfiguration的类,可以实现自定义的数据源配置,默认启用tomcat.jdbc数据源,同时还支持HikariDataSource,dbcp,dbcp2等数据源,我们还可以自定义数据源:
1 /**
2 * Generic DataSource configuration.
3 */
4 @ConditionalOnMissingBean(DataSource.class)
5 @ConditionalOnProperty(name = "spring.datasource.type")
6 static class Generic {
7
8 @Bean
9 public DataSource dataSource(DataSourceProperties properties) {
10 return properties.initializeDataSourceBuilder().build();
11 }
12
13 }
4.自定义的数据源使用DataSourceBulider来实现自定义数据源的装配,使用反射去创建我们自定义的数据源:
1 public DataSource build() {
2 Class<? extends DataSource> type = getType();
3 DataSource result = BeanUtils.instantiate(type);
4 maybeGetDriverClassName();
5 bind(result);
6 return result;
7 }
5.在DataSourceInitializer类中定义了一个 DataSourceInitializer implements ApplicationListener<DataSourceInitializedEvent>,该类主要可以帮助我们在程序初始化的时候自动云运行 schema-*.sql data-*.sql:
1 @PostConstruct
2 public void init() {
3 if (!this.properties.isInitialize()) {
4 logger.debug("Initialization disabled (not running DDL scripts)");
5 return;
6 }
7 if (this.applicationContext.getBeanNamesForType(DataSource.class, false,
8 false).length > 0) {
9 this.dataSource = this.applicationContext.getBean(DataSource.class);
10 }
11 if (this.dataSource == null) {
12 logger.debug("No DataSource found so not initializing");
13 return;
14 }
15 runSchemaScripts();
16 }
6.可以在程序运行时帮助我们执行建表语句;
7.运行插入数据的sql语句:
1 @Override
2 public void onApplicationEvent(DataSourceInitializedEvent event) {
3 if (!this.properties.isInitialize()) {
4 logger.debug("Initialization disabled (not running data scripts)");
5 return;
6 }
7 // NOTE the event can happen more than once and
8 // the event datasource is not used here
9 if (!this.initialized) {
10 runDataScripts();
11 this.initialized = true;
12 }
13 }
8.其默认规则只需要将这些文件命名为scheme-*.sql(建表语句),data-*.sql(数据形式的sql文件);将建表语句的sql文件放置在resources文件夹下命名为schema-all(schema).sql,则将在程序运行时自动创建我们的建表语句:
9.我们也可以在配置属性文件中直接指定schema:
10.如果我们要操作数据库,SpringBoot还具有一个默认的自动配置:JdbcTemplateAutoConfiguration:自动配置了JdbcTemplate操纵数据库
三.数据库操纵演示
自定义一个Controller实现数据查询:
1 package com.skykuqi.springboot.datajdbc.controller;
2
3 import org.springframework.beans.factory.annotation.Autowired;
4 import org.springframework.jdbc.core.JdbcTemplate;
5 import org.springframework.stereotype.Controller;
6 import org.springframework.web.bind.annotation.GetMapping;
7 import org.springframework.web.bind.annotation.ResponseBody;
8
9 import java.util.List;
10 import java.util.Map;
11
12 /**
13 * @author : S K Y
14 * @version :0.0.1
15 */
16 @Controller
17 public class HelloController {
18
19 @Autowired
20 JdbcTemplate template;
21
22 @ResponseBody
23 @GetMapping("/query")
24 public Map<String, Object> map() {
25 List<Map<String, Object>> list = template.queryForList("select * from department");
26 return list.get(0);
27 }
28 }
四.整合使用druid数据源
1.引入druid:
1 <!--引入druid数据源-->
2 <dependency>
3 <groupId>com.alibaba</groupId>
4 <artifactId>druid</artifactId>
5 <version>1.1.8</version>
6 </dependency>
2.配置druid数据源
1 package com.skykuqi.springboot.datajdbc.condig;
2
3 import com.alibaba.druid.pool.DruidDataSource;
4 import com.alibaba.druid.support.http.StatViewServlet;
5 import com.alibaba.druid.support.http.WebStatFilter;
6 import org.springframework.boot.context.properties.ConfigurationProperties;
7 import org.springframework.boot.web.servlet.FilterRegistrationBean;
8 import org.springframework.boot.web.servlet.ServletRegistrationBean;
9 import org.springframework.context.annotation.Bean;
10 import org.springframework.context.annotation.Configuration;
11
12 import javax.sql.DataSource;
13 import java.util.Collections;
14 import java.util.HashMap;
15 import java.util.Map;
16
17 /**
18 * @author : S K Y
19 * @version :0.0.1
20 */
21 @Configuration
22 public class DruidConfig {
23 @Bean
24 @ConfigurationProperties(prefix = "spring.datasource")
25 public DataSource druid() {
26 return new DruidDataSource();
27 }
28
29 //配置Durid的监控
30 //1. 配置一个管理后台的Service
31 @Bean
32 public ServletRegistrationBean statViewServlet() {
33 ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
34 Map<String, String> initParameters = new HashMap<>();
35 //配置登录后台时的用户名
36 initParameters.put("loginUsername", "admin");
37 //配置登录后台的密码
38 initParameters.put("loginPassword", "123456");
39 //默认允许所有访问,配置只允许本地登录
40 initParameters.put("allow", "127.0.0.1");
41 //阻止该地址的访问
42 initParameters.put("deny", "192.168.1.108");
43 bean.setInitParameters(initParameters);
44 return bean;
45 }
46
47 //2.配置一个监控的filter
48 @Bean
49 public FilterRegistrationBean webStartFilter() {
50 FilterRegistrationBean bean = new FilterRegistrationBean();
51 bean.setFilter(new WebStatFilter());
52 Map<String, String> initParameters = new HashMap<>();
53 //设置不拦截以下请求
54 initParameters.put("exclusions","*.js,*.css,/druid");
55 //设置拦截以下所有请求
56 bean.setInitParameters(initParameters);
57 bean.setUrlPatterns(Collections.singletonList("/*"));
58 return bean;
59 }
60 }
1 spring:
2 datasource:
3 username: root
4 password: 123456
5 url: jdbc:mysql://192.168.1.108:3306/jdbc?useSSL=false
6 driver-class-name: com.mysql.jdbc.Driver
7 schema:
8 - classpath:department.sql
9 type: com.alibaba.druid.pool.DruidDataSource
10 # 数据源其他配置
11 initialSize: 5
12 minIdle: 5
13 maxActive: 20
14 maxWait: 60000
15 timeBetweenEvictionRunsMillis: 60000
16 minEvictableIdleTimeMillis: 300000
17 validationQuery: SELECT 1 FROM DUAL
18 testWhileIdle: true
19 testOnBorrow: false
20 testOnReturn: false
21 poolPreparedStatements: true
22 # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
23 filters: stat,wall,log4j
24 maxPoolPreparedStatementPerConnectionSize: 20
25 useGlobalDataSourceStat: true
26 connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
五.整合Mybatis
1.查看POM依赖
1 <dependencies>
2 <dependency>
3 <groupId>org.springframework.boot</groupId>
4 <artifactId>spring-boot-starter-jdbc</artifactId>
5 </dependency>
6 <dependency>
7 <groupId>org.springframework.boot</groupId>
8 <artifactId>spring-boot-starter-web</artifactId>
9 </dependency>
10 <dependency>
11 <groupId>org.mybatis.spring.boot</groupId>
12 <artifactId>mybatis-spring-boot-starter</artifactId>
13 <version>2.1.1</version>
14 </dependency>
15
16 <dependency>
17 <groupId>mysql</groupId>
18 <artifactId>mysql-connector-java</artifactId>
19 <scope>runtime</scope>
20 </dependency>
21 <dependency>
22 <groupId>org.springframework.boot</groupId>
23 <artifactId>spring-boot-starter-test</artifactId>
24 <scope>test</scope>
25 </dependency>
26 </dependencies>
2.配置使用Mybatis
(1)配置数据源相关;
(2)在数据库中建立相关数据表
(3)创建javaBean;
(4)使用SpringBoot整合Mybatis;
1 package com.skykuqi.springboot.mybatis.mapper;
2
3 import com.skykuqi.springboot.mybatis.entity.Department;
4 import org.apache.ibatis.annotations.*;
5
6 /**
7 * @author : S K Y
8 * @version :0.0.1
9 */
10 @Mapper //指定这是一个操作数据库的mapper
11 public interface DepartmentMapper {
12 @Select("select * from department where id=#{id}")
13 Department queryDepartmentById(Integer id);
14
15 @Delete("delete from department where id=#{id}")
16 int deleteDepartmentById(Integer id);
17
18 @Insert("insert into department(departmentName) values(#{departmentName}) ")
19 int insertDepartment(Department department);
20
21 @Update("update department set departmentName=#{departmentName} where id=#{id}")
22 int updataDepartment(Department department);
23 }
1 package com.skykuqi.springboot.mybatis.controller;
2
3 import com.skykuqi.springboot.mybatis.entity.Department;
4 import com.skykuqi.springboot.mybatis.mapper.DepartmentMapper;
5 import org.springframework.beans.factory.annotation.Autowired;
6 import org.springframework.web.bind.annotation.GetMapping;
7 import org.springframework.web.bind.annotation.PathVariable;
8
9 /**
10 * @author : S K Y
11 * @version :0.0.1
12 */
13 @org.springframework.web.bind.annotation.RestController
14 public class RestController {
15 @Autowired
16 DepartmentMapper departmentMapper;
17
18 @GetMapping("/dept/{id}")
19 public Department getDepartment(@PathVariable("id") Integer id) {
20 return departmentMapper.queryDepartmentById(id);
21 }
22
23 @GetMapping("/dept")
24 public Department insertDept(Department department) {
25 departmentMapper.insertDepartment(department);
26 return department;
27 }
28 }
--实现插入语句的id值回显:
1 @Options(useGeneratedKeys = true,keyProperty = "id")
2 @Insert("insert into department(departmentName) values(#{departmentName}) ")
3 int insertDepartment(Department department);
(5)当javaBean中的名称与数据库中的名称不一致时.例如javaBean中为departmentName,而在数据库中为department_name,在Mybatis的自动配置中存在ConfigurationCustomizer相关的配置,我们可以自定义实现该接口:
1 package com.skykuqi.springboot.mybatis.config;
2
3 import org.mybatis.spring.boot.autoconfigure.ConfigurationCustomizer;
4 import org.springframework.context.annotation.Bean;
5 import org.springframework.context.annotation.Configuration;
6
7 /**
8 * @author : S K Y
9 * @version :0.0.1
10 */
11 @Configuration
12 public class MyBatisConfig {
13 @Bean
14 public ConfigurationCustomizer configurationCustomizer() {
15 return configuration -> {
16 configuration.setMapUnderscoreToCamelCase(true); //开启驼峰命名法
17 };
18 }
19 }
(6)可以使用@MapperScan 来配置mapper包扫描,避免过多的Mapper注解的实现
3.使用配置文件的方式来进行Mybatis的整合
(1)在yml配置文件中注册配置类:
(2)在xml文件中配置启用mapUnderscoreToCamelCase
1 <?xml version="1.0" encoding="UTF-8" ?>
2 <!DOCTYPE configuration
3 PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
4 "http://mybatis.org/dtd/mybatis-3-config.dtd">
5 <configuration>
6 <settings>
7 <setting name="mapUnderscoreToCamelCase" value="true"/>
8 </settings>
9 </configuration>
(3)编写mapper.xml
1 <?xml version="1.0" encoding="UTF-8"?>
2 <!DOCTYPE mapper
3 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
4 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
5 <mapper namespace="com.skykuqi.springboot.mybatis.mapper.EmployeeMapper">
6 <select id="queryEmpById" parameterType="Integer"
7 resultType="com.skykuqi.springboot.mybatis.entity.Employee">
8 select *
9 from employee
10 where id = #{id}
11 </select>
12
13 <insert id="insertEmp" parameterType="com.skykuqi.springboot.mybatis.entity.Employee">
14 insert into employee(lastName, email, gender, d_id)
15 values (#{lastName}, #{email}, #{gender}, #{d_id})
16 </insert>
17 </mapper>
