1 springboot配置数据源
-
Dependencies中选择Spring Web、JDBC API、MySQL Driver
-
配置数据源:resources下创建application.yaml
spring: datasource: username: root password: c50hst url: jdbc:mysql://localhost:3306/demo?serverTimezone=UTC&useUnicode=true@characterEncoding=utf-8 driver-class-name: com.mysql.jdbc.Driver
-
测试数据源是否配置成功
package com.mashibing; import org.junit.jupiter.api.Test; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest; import javax.sql.DataSource; import java.sql.Connection; import java.sql.SQLException; @SpringBootTest class SpringbootDatabaseApplicationTests { @Autowired DataSource dataSource; @Test void contextLoads() throws SQLException { //springboot默认使用com.zaxxer.hikari.HikariDataSource作为数据源 System.out.println(dataSource.getClass()); Connection connection = dataSource.getConnection(); System.out.println(connection); connection.close(); } }
-
crud操作
- 获取到了数据源,就可以进一步通过数据源获取到数据库连接(java.sql.Connection),有了Connection,就可以使用Connection和原生的JDBC语句来操作数据库
- 即使不使用第三方数据库操作框架(例如MyBatis),Spring本身也对原生的JDBC做了轻量级的封装,即org.springframework.jdbc.core.JdbcTemplate
- springboot不仅提供了默认的数据源,同时默认配置好了JdbcTemplate放在容器中,开发人员只需自己注入即可使用
- JdbcTemplate的自动配置是依赖org.springframework.boot.autoconfigure.jdbc包下的org.springframework.boot.autoconfigure.jdbc.JdbcTemplateAutoConfiguration类实现的
package com.mashibing.contoller; 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.PathVariable; import org.springframework.web.bind.annotation.RestController; import java.util.List; import java.util.Map; @RestController public class JDBCController { @Autowired JdbcTemplate jdbcTemplate; @GetMapping("/emplist") public List<Map<String,Object>> empList(){ String sql = "select * from emp"; List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql); return maps; } @GetMapping("/addEmp") public String addUser(){ String sql = "insert into emp(empno,ename) values(1111,'zhangsan')"; jdbcTemplate.update(sql); return "success"; } @GetMapping("/updateEmp/{id}") public String updateEmp(@PathVariable("id") Integer id){ String sql = "update emp set ename=? where empno = "+id; String name = "list"; jdbcTemplate.update(sql,name); return "update success"; } @GetMapping("/deleteEmp/{id}") public String deleteEmp(@PathVariable("id")Integer id){ String sql = "delete from emp where empno = "+id; jdbcTemplate.update(sql); return "delete success"; } }
2 自定义数据源
-
springboot默认使用DataSourceAutoConfiguration类来注入数据源,数据源的配置信息在DataSourceProperties类中存放
-
默认使用HikariDataSource数据源,如果想改为使用阿里的druid数据源,需要进行如下操作
-
添加druid的maven配置
<dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.12</version> </dependency>
-
yaml配置文件中新增spring.datasource.type
spring: datasource: username: root password: c50hst url: jdbc:mysql://localhost:3306/demo?serverTimezone=UTC&useUnicode=true@characterEncoding=utf-8 driver-class-name: com.mysql.jdbc.Driver type: com.alibaba.druid.pool.DruidDataSource
-
运行SpringbootDatabaseApplicationTests测试类,发现数据源类型已经更改
3 为druid数据源添加独有配置
-
yaml
spring: datasource: username: root password: c50hst url: jdbc:mysql://localhost:3306/demo?serverTimezone=UTC&useUnicode=true@characterEncoding=utf-8 driver-class-name: com.mysql.jdbc.Driver type: com.alibaba.druid.pool.DruidDataSource #springboot默认提供的数据源的配置类DataSourceProperties中,只有username、password等属性,没有initialSize、minIdle等属性,因此无法将下方内容加载到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
-
自定义配置类,并将DruidDataSource作为bean交给spring管理
package com.mashibing.config; import com.alibaba.druid.pool.DruidDataSource; import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import javax.sql.DataSource; @Configuration public class DruidConfig { @ConfigurationProperties(prefix = "spring.datasource") @Bean public DataSource druidDataSource(){ return new DruidDataSource(); } }
-
运行SpringbootDatabaseApplicationTests测试类
DruidDataSource druidDataSource = (DruidDataSource) dataSource; //打印5,说明配置生效 System.out.println(druidDataSource.getInitialSize());
4 使用druid数据源提供的监控功能
-
Druid数据源具有监控功能,并提供了一个web界面方便用户进行监控
-
druid监控的管理控制台的servlet为StatViewServlet、某些请求不想被druid监控时,可以使用WebStatFilter过滤器,druid提供了StatViewServlet和WebStatFilter,但需要手动对它们进行注册
package com.mashibing; import com.alibaba.druid.pool.DruidDataSource; 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.servlet.Servlet; import javax.sql.DataSource; import java.util.Arrays; import java.util.HashMap; import java.util.Map; @Configuration public class DruidConfig { @ConfigurationProperties(prefix = "spring.datasource") @Bean public DataSource druidDataSource() { return new DruidDataSource(); } @Bean public ServletRegistrationBean druidServletRegistrationBean() { //输入localhost:8080/druid就能进入控制台 ServletRegistrationBean<Servlet> servletRegistrationBean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*"); Map<String, String> initParams = new HashMap<>(); //控制台的用户名和密码 initParams.put("loginUsername", "admin"); initParams.put("loginPassword", "123456"); //1. allow:允许谁可以访问 //a. 表示只有本机可以访问 //initParams.put("allow", "localhost") //b. 为空或者为null时,表示允许所有访问 //initParams.put("allow", ""): initParams.put("allow", ""); //2. deny:拒绝谁访问 //initParams.put("deny", "192.168.1.20");表示禁止此ip访问 servletRegistrationBean.setInitParameters(initParams); return servletRegistrationBean; } //某些请求不想进行监控,可以使用过滤器 @Bean public FilterRegistrationBean webStatFilter() { FilterRegistrationBean bean = new FilterRegistrationBean(); bean.setFilter(new WebStatFilter()); //exclusions:设置哪些请求进行过滤排除掉,从而不进行统计 Map<String, String> initParams = new HashMap<>(); initParams.put("exclusions", "*.js,*.css,/druid/*"); bean.setInitParameters(initParams); //"/*" 表示过滤所有请求 bean.setUrlPatterns(Arrays.asList("/*")); return bean; } }
-
需要在yaml中新增以下内容从而开启监控
#配置监控统计拦截的filters #stat:监控统计 #log4j:日志记录 #wall:防御sql注入 filters: stat,wall,log4j maxPoolPreparedStatementPerConnectionSize: 20 useGlobalDataSourceStat: true connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
-
druid的监控需要log4j依赖,如果不添加,启动报错
java.lang.ClassNotFoundException: org.apache.log4j.Priority
<!-- https://mvnrepository.com/artifact/log4j/log4j --> <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.17</version> </dependency>
-
输入localhost:8080/emplist,就会调用数据库语句,之后在localhost:8080/druid监控的SQL监控页面中就能看到这个语句
5 springboot配置多数据源
-
在spring2.0.1中引入了AbstractRoutingDataSource,该类充当了DataSource的路由中介,能有在运行时,根据determineCurrentLookupKey返回值来动态切换到真正的DataSource上
-
该类的targetDataSources属性中可以存放一组有效的数据源,defaultTargetDataSource属性中存放系统默认使用的数据源,我们可以通过自定义一个AbstractRoutingDataSource类型的bean对象替换原有的AbstractRoutingDataSource对象,并重写该类的determineCurrentLookupKey方法从而动态地切换系统使用的数据源
-
DynamicDataSource:重写AbstractRoutingDataSource,具体注册逻辑在下面DataSourceConfig中完成
package com.mashibing.mult; import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource; import javax.sql.DataSource; import java.util.Map; public class DynamicDataSource extends AbstractRoutingDataSource { public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) { //存放默认使用的数据源 super.setDefaultTargetDataSource(defaultTargetDataSource); //存放所有的数据源 super.setTargetDataSources(targetDataSources); //用来将targetDataSources的属性写入resolvedDataSources中,真正执行语句时,使用的是resolvedDataSources中的数据源 super.afterPropertiesSet(); } //返回一个key值,真正使用的数据源会通过targetDataSources.get(key)获取 @Override protected Object determineCurrentLookupKey() { return DynamicDataSourceContextHolder.getDataSourceType(); } }
-
DynamicDataSourceContextHolder:该类组合了一个ThreadLocal类型变量,这样就可以保证不同线程获取其使用的数据源时,不互相干扰
package com.mashibing.mult; public class DynamicDataSourceContextHolder { //使用ThreadLocal维护变量,ThreadLocal为每个使用该变量的线程提供独立的变量副本,所以每一个线程都可以独立地改变自己的副本,而不会影响其它线程所对应的副本 private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<>(); public static void setDataSourceType(String dataSourceType){ System.out.printf("切换到{%s}数据源", dataSourceType); CONTEXT_HOLDER.set(dataSourceType); } public static String getDataSourceType(){ return CONTEXT_HOLDER.get(); } public static void clearDataSourceType(){ CONTEXT_HOLDER.remove(); } }
-
DataSourceConfig:注入所有的数据源对象、以及刚才创建的DynamicDataSource类的对象
package com.mashibing.mult; import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty; import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.boot.jdbc.DataSourceBuilder; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.context.annotation.Primary; import javax.sql.DataSource; import java.util.HashMap; import java.util.Map; @Configuration public class DataSourceConfig { @Bean @ConfigurationProperties("spring.datasource.remote") public DataSource remoteDataSource() { return DataSourceBuilder.create().build(); } @Bean @ConfigurationProperties("spring.datasource.local") public DataSource localDataSource() { return DataSourceBuilder.create().build(); } @Bean(name = "dynamicDataSource") //DynamicDataSource实现了AbstractRoutingDataSource,此时spring中会有多个AbstractRoutingDataSource类型的bean,Primary表示,优先使用当前bean对AbstractRoutingDataSource类型的变量进行注入 @Primary public DynamicDataSource dataSource(DataSource remoteDataSource, DataSource localDataSource) { Map<Object, Object> targetDataSources = new HashMap<>(); targetDataSources.put(DataSourceType.REMOTE.name(), remoteDataSource); targetDataSources.put(DataSourceType.LOCAL.name(), localDataSource); return new DynamicDataSource(remoteDataSource, targetDataSources); } }
-
application.yaml:DataSourceConfig中通过配置文件中的值初始化数据源,因此需要引入配置文件
spring: datasource: local: username: root password: c50hst driver-class-name: com.mysql.jdbc.Driver #分别连接c50hst和demo库 jdbc-url: jdbc:mysql://localhost:3306/c50hst?serverTimezone=UTC&useUnicode=true@characterEncoding=utf-8 remote: username: root password: c50hst driver-class-name: com.mysql.jdbc.Driver jdbc-url: jdbc:mysql://localhost:3306/demo?serverTimezone=UTC&useUnicode=true@characterEncoding=utf-8
-
DataSourceType:targetDataSources数据源集合中,key值由枚举类DataSourceType中的枚举值决定
package com.mashibing.mult; public enum DataSourceType { REMOTE, LOCAL }
-
此时只要在具体方法中,调用
DynamicDataSourceContextHolder.setDataSourceType
方法,就可以改变使用的数据源 -
为了更加方便地切换注解可以考虑引入一个注解DataSource配置在具体的方法上,然后利用spring AOP的功能,在调用具体方法的前后,自动加入调用
DynamicDataSourceContextHolder.setDataSourceType
的逻辑,就可以实现根据不同注解,使用不同数据源 -
DataSource:新增的注解,属性value的值为DataSourceType中定义的枚举值
package com.mashibing.mult; import java.lang.annotation.*; //该注解用在方法上 @Target(ElementType.METHOD) @Retention(RetentionPolicy.RUNTIME) @Documented public @interface DataSource { //该注解修饰的方法,所使用的数据源的key值 DataSourceType value() default DataSourceType.REMOTE; }
-
DataSourceAspect:利用AOP在DataSource注解前后,自动根据DataSource的value属性的值,来修改DynamicDataSourceContextHolder中线程环境变量CONTEXT_HOLDER中的值
package com.mashibing.mult; import org.aspectj.lang.ProceedingJoinPoint; import org.aspectj.lang.annotation.Around; import org.aspectj.lang.annotation.Aspect; import org.aspectj.lang.annotation.Pointcut; import org.aspectj.lang.reflect.MethodSignature; import org.springframework.core.annotation.Order; import org.springframework.stereotype.Component; import java.lang.reflect.Method; //之前没选中AOP模块,因此需要在springboot官网或maven官网中查找springboot AOP的pom依赖,并加入到pom依赖中 @Aspect @Order(1) @Component public class DataSourceAspect { //@annotation表示定义切点为具有指定注解的方法 @Pointcut("@annotation(com.mashibing.mult.DataSource)") public void dsPointCut() { } @Around("dsPointCut()") public Object around(ProceedingJoinPoint point) throws Throwable { MethodSignature signature = (MethodSignature) point.getSignature(); Method method = signature.getMethod(); DataSource dataSource = method.getAnnotation(DataSource.class); if (dataSource != null) { //方法执行前,获取该方法上DataSource注解的value值,放入到DynamicDataSourceContextHolder的CONTEXT_HOLDER属性中 DynamicDataSourceContextHolder.setDataSourceType(dataSource.value().name()); } try { //DataSource注解修饰的方法的执行 return point.proceed(); } finally { // 销毁数据源 在执行方法之后 DynamicDataSourceContextHolder.clearDataSourceType(); } } }
-
测试方法
package com.mashibing.mult; 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.RestController; import java.util.List; import java.util.Map; @RestController public class EmpController { @Autowired JdbcTemplate jdbcTemplate; @GetMapping("/local") @DataSource(value = DataSourceType.LOCAL) public List<Map<String, Object>> local(){ List<Map<String, Object>> maps = jdbcTemplate.queryForList("select * from emp"); return maps; } @GetMapping("/remote") @DataSource(value = DataSourceType.REMOTE) public List<Map<String, Object>> remote(){ List<Map<String, Object>> maps = jdbcTemplate.queryForList("select * from emp"); return maps; } }
-
启动项目的过程中会发生循环依赖的问题,需要让springboot默认注入数据源的DataSourceAutoConfiguration类失效
package com.mashibing; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration; @SpringBootApplication(exclude = DataSourceAutoConfiguration.class) public class SpringbootDatabaseApplication { public static void main(String[] args) { SpringApplication.run(SpringbootDatabaseApplication.class, args); } }
-
此时输入http://localhost:8080/remote和http://localhost:8080/local会获取到不同的查询结果,说明配置的多数据源生效
6 springboot整合mybatis
-
Dependencies中多选一项MyBatis Framework
<!--pom依赖中会自动增加下面的依赖--> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.3</version> </dependency>
-
Emp:实体类
package com.mashibing.entity; import java.sql.Date; import java.util.Objects; public class Emp { private Integer empno; private String ename; private String job; private Integer mgr; private Date hiredate; private Double sal; private Double comm; private Integer deptno; public Emp() { } public Emp(Integer empno, String ename) { this.empno = empno; this.ename = ename; } public Emp(Integer empno, String ename, String job, Integer mgr, Date hiredate, Double sal, Double comm, Integer deptno) { this.empno = empno; this.ename = ename; this.job = job; this.mgr = mgr; this.hiredate = hiredate; this.sal = sal; this.comm = comm; this.deptno = deptno; } public Integer getEmpno() { return empno; } public void setEmpno(Integer empno) { this.empno = empno; } public String getEname() { return ename; } public void setEname(String ename) { this.ename = ename; } public String getJob() { return job; } public void setJob(String job) { this.job = job; } public Integer getMgr() { return mgr; } public void setMgr(Integer mgr) { this.mgr = mgr; } public Date getHiredate() { return hiredate; } public void setHiredate(Date hiredate) { this.hiredate = hiredate; } public Double getSal() { return sal; } public void setSal(Double sal) { this.sal = sal; } public Double getComm() { return comm; } public void setComm(Double comm) { this.comm = comm; } public Integer getDeptno() { return deptno; } public void setDeptno(Integer deptno) { this.deptno = deptno; } @Override public boolean equals(Object o) { if (this == o) return true; if (!(o instanceof Emp)) return false; Emp emp = (Emp) o; return Objects.equals(empno, emp.empno) && Objects.equals(ename, emp.ename) && Objects.equals(job, emp.job) && Objects.equals(mgr, emp.mgr) && Objects.equals(hiredate, emp.hiredate) && Objects.equals(sal, emp.sal) && Objects.equals(comm, emp.comm) && Objects.equals(deptno, emp.deptno); } @Override public int hashCode() { return Objects.hash(empno, ename, job, mgr, hiredate, sal, comm, deptno); } @Override public String toString() { return "Emp{" + "empno=" + empno + ", ename='" + ename + '\'' + ", job='" + job + '\'' + ", mgr=" + mgr + ", hiredate=" + hiredate + ", sal=" + sal + ", comm=" + comm + ", deptno=" + deptno + '}'; } }
-
EmpMapper:Mapper接口
package com.mashibing.mapper; import com.mashibing.entity.Emp; import org.apache.ibatis.annotations.Mapper; import org.springframework.stereotype.Repository; import java.util.List; //@Mapper有两个作用,1是会将下方接口实例化后交给spring管理,2是允许使用注解方式,不必再定义xml @Mapper //@Repository作用为交给spring容器管理,但此处@Mapper已经达到了这个目的,本身不用再写@Repository,不过发现不写@Repository会导致自动注入EmpMapper处编译报错,虽然不影响启动和执行 @Repository public interface EmpMapper { List<Emp> selectEmp(); Emp selectEmpById(Integer empno); Integer addEmp(Emp emp); Integer updateEmp(Emp emp); Integer deleteEmp(Integer empno); }
-
在resources下创建Emp.xml文件
<?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.mashibing.mapper.EmpMapper"> <select id="selectEmp" resultType="Emp"> select * from emp </select> <select id="selectEmpById" resultType="Emp"> select * from emp where empno = #{empno} </select> <insert id="addEmp" parameterType="Emp"> insert into emp (empno,ename) values (#{empno},#{ename}) </insert> <update id="updateEmp" parameterType="Emp"> update emp set ename=#{ename} where empno = #{empno} </update> <delete id="deleteEmp" parameterType="int"> delete from emp where empno = #{empno} </delete> </mapper>
-
yaml配置文件
spring: datasource: username: root password: c50hst url: jdbc:mysql://localhost:3306/demo?serverTimezone=UTC&useUnicode=true@characterEncoding=utf-8 driver-class-name: com.mysql.jdbc.Driver mybatis: #配置文件位置 mapper-locations: classpath:*.xml #相当于mybatis配置文件中的typeAliases,定义之后,xml中就可以使用类名替代类的全限定名,如果在xml中都使用的是全限定类名,此项就不必定义 type-aliases-package: com.mashibing.entity
-
编写controller进行测试
package com.mashibing.contoller; import com.mashibing.entity.Emp; import com.mashibing.mapper.EmpMapper; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RestController; import java.util.List; @RestController public class EmpController { @Autowired private EmpMapper empMapper; //选择全部用户 @GetMapping("/selectEmp") public String selectEmp(){ List<Emp> emps = empMapper.selectEmp(); for (Emp Emp : emps) { System.out.println(Emp); } return "ok"; } //根据id选择用户 @GetMapping("/selectEmpById") public String selectEmpById(){ Emp emp = empMapper.selectEmpById(1234); System.out.println(emp); return "ok"; } //添加一个用户 @GetMapping("/addEmp") public String addEmp(){ empMapper.addEmp(new Emp(1234,"heheda")); return "ok"; } //修改一个用户 @GetMapping("/updateEmp") public String updateEmp(){ empMapper.updateEmp(new Emp(1234,"heihei")); return "ok"; } //根据id删除用户 @GetMapping("/deleteEmp") public String deleteEmp(){ empMapper.deleteEmp(1234); return "ok"; } }