SpringBoot 基础(一) mybatis 通过druid配置多数据库
本次使用sqlServer和MySQL；Oracle本机没有安装，所以先留到以后
项目目录结构
1首先去pom种引入相关的maven
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>1.5.9.RELEASE</version>
<relativePath/>
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.7</java.version>
</properties>
<dependencies>
<!-- springboot 相关-->
<dependency>
<groupId>org.example</groupId>
<artifactId>01.Spring-Boot-Config</artifactId>
<version>1.0-SNAPSHOT</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.0.1</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!--sqlserver 驱动 -->
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
<version>6.4.0.jre8</version>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<!-- mysql驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.22</version>
</dependency>
<!-- druid数据源驱动 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.10</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
2.然后再添加application.yml文件，这里有两种配置方式
方式1：
spring: datasource: druid: # 数据库访问配置, 使用druid数据源 # 数据源1 mysql mysql: type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://127.0.0.1:33068/test?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai username: root password: Ee123 # 数据源2 sqlserver sqlserver: type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver url: jdbc:sqlserver://localhost:1433;DatabaseName=test username: sa password: Ee123 # # 数据源3 oracle # oracle: # type: com.alibaba.druid.pool.DruidDataSource # driver-class-name: oracle.jdbc.driver.OracleDriver # url: jdbc:oracle:thin:@localhost:1521:ORCL # username: test # password: 123456 # 连接池配置 initial-size: 5 min-idle: 5 max-active: 20 # 连接等待超时时间 max-wait: 30000 # 配置检测可以关闭的空闲连接间隔时间 time-between-eviction-runs-millis: 60000 # 配置连接在池中的最小生存时间 min-evictable-idle-time-millis: 300000 validation-query: select '1' from dual test-while-idle: true test-on-borrow: false test-on-return: false # 打开PSCache，并且指定每个连接上PSCache的大小 pool-prepared-statements: true max-open-prepared-statements: 20 max-pool-prepared-statement-per-connection-size: 20 # 配置监控统计拦截的filters, 去掉后监控界面sql无法统计, 'wall'用于防火墙 filters: stat,wall # Spring监控AOP切入点，如x.y.z.service.*,配置多个英文逗号分隔 aop-patterns: com.springboot.servie.*
方式2：(这里是显示了数据库相关的配置，其余省略)
spring: datasource: mysql: #数据源1 driverClassName: com.mysql.cj.jdbc.Driver url: jdbc:mysql://127.0.0.1:33068/test?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai username: root password: Ee123 type: com.alibaba.druid.pool.DruidDataSource sqlserver: #数据源3 driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver url: jdbc:sqlserver://localhost:1433;DatabaseName=test username: sa password: Ee123 type: com.alibaba.druid.pool.DruidDataSource
3.配置dataSource
mysql：（sql写在xml文件种）
@Configuration @MapperScan(basePackages = "com.item.springboot.mysqldao") public class MysqlDatasourceConfig { //因为都是 DataSource 类型，二配置多个dataSource都会用到这个·，所以通过起名称，通过名字来区分 @Primary @Bean(value = "mysqldataSource")
//目前使用application.yml 的是方式2 中的配置，如果使用方式1 则需要改为@ConfigurationProperties("spring.datasource.druid.mysql") @ConfigurationProperties("spring.datasource.mysql") public DataSource mysqlDataSource() { return DruidDataSourceBuilder.create().build(); } @Primary @Bean public DataSourceTransactionManager mysqlTransactionManager(@Qualifier("mysqldataSource") DataSource dataSource) { return new DataSourceTransactionManager(dataSource); } @Primary @Bean public SqlSessionFactory mysqlSqlSessionFactory(@Qualifier("mysqldataSource") DataSource dataSource) throws Exception { final SqlSessionFactoryBean sessionFactory=new SqlSessionFactoryBean(); sessionFactory.setDataSource(dataSource); //如果不使用xml的方式配置mapper，则可以省去下面这行mapper location的配置。 sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/mysql/*.xml")); return sessionFactory.getObject(); } }
sqlServer:(sql写在项目中)
@Configuration @MapperScan(basePackages = "com.item.springboot.sqlserverMapper") public class SqlServerDataSourceConfig { //数据源 //@Primary
//目前使用application.yml 的是方式2 中的配置，如果使用方式1 则需要改为@ConfigurationProperties("spring.datasource.druid.mysql")
@ConfigurationProperties(prefix = "spring.datasource.sqlserver") @Bean public DataSource tailDataSource(){ return new DruidDataSource(); } //@Primary @Bean public DataSourceTransactionManager utailTransactionManager(@Qualifier("tailDataSource") DataSource dataSource) { return new DataSourceTransactionManager(dataSource); } //@Primary @Bean public SqlSessionFactory tailSqlSessionFactory(@Qualifier("tailDataSource") DataSource dataSource) throws Exception { SqlSessionFactoryBean bean = new SqlSessionFactoryBean(); bean.setDataSource(dataSource); // bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:templates/test/*.xml")); return bean.getObject(); } }
4.sqlServer调用(简洁模式)
持久层：sqlserverMapper=》StudentMapper
@Mapper public interface StudentMapper { @Select(" select * from test ") List<Map<String, Object>> getAll(); }
调用
@Repository @Mapper public interface StudentMapper { @Select(" select * from test ") List<Map<String, Object>> getAll(); }
5.mysql调用(传统模式)
5.1 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.item.springboot.mysqldao.MysqlStudentMapper">
<select id="getAllStudents" resultType="java.util.Map">
select * from student
</select>
</mapper>
5.2mysqldao层实现对xml文件中数据库的调用
@Mapper @Repository public interface MysqlStudentMapper { List<Map<String, Object>> getAllStudents(); }
5.3service.impl实现对dao层的调用
@Service("studentService")
public class StudentServiceImpl implements StudentService {
@Autowired
private MysqlStudentMapper mysqlStudentMapper;
@Override
public List<Map<String, Object>> getAllStudentsFromMysql() {
return this.mysqlStudentMapper.getAllStudents();
}
}
5.4 service层对service.impl的调用
public interface StudentService { List<Map<String, Object>> getAllStudentsFromMysql(); }
5.5 controller
@Autowired private StudentService studentService; @RequestMapping("querystudentsfrommysql") public List<Map<String, Object>> queryStudentsFromMysql(){ return studentService.getAllStudentsFromMysql(); }
注意·当前两个dataSource是不能同时使用的(mysql和sqlServer再同一时间只能使用一个)，因为在dataSource文件夹下配置的是同种类型。spring是通过类型进行加载
