上一篇:Spring Boot 2.0.2 教程 - 整合JdbcTemplate - 07
较为复杂的项目通常会连接多个数据源,在上一节的介绍中都是单数据源,本节介绍使用spring boot连接多个数据源,本例为mysql加oracle
pom.xml添加相关依赖
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.example</groupId> <artifactId>demo</artifactId> <version>0.0.1-SNAPSHOT</version> <packaging>war</packaging> <name>demo</name> <description>Demo project for Spring Boot</description> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.0.2.RELEASE</version> <relativePath/> </parent> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> <exclusions> <exclusion> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-logging</artifactId> </exclusion> </exclusions> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-log4j2</artifactId> <version>1.5.8.RELEASE</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-configuration-processor</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-tomcat</artifactId> <scope>provided</scope> </dependency> <!-- 单元测试 --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> </dependency> <!-- 引入jdbc,spring boot 默认会使用HikariCP作为数据库连接池 --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <!-- mysql驱动 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.35</version> </dependency> <!-- oracle驱动--> <dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc7</artifactId> <version>12.1.0.2</version> </dependency> <!-- spring-boot整合mybatis --> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.3.1</version> </dependency> </dependencies> <build> <finalName>demo</finalName> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>
注:引入oracle驱动可能不会成功,参考:maven无法下载ojdbc7驱动
项目整体结构
创建数据库及表
mysql
CREATE TABLE example ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_name` varchar(50) DEFAULT NULL, `addr` varchar(500) DEFAULT NULL, `sex` tinyint(4) DEFAULT NULL, PRIMARY KEY (`id`) )
oracle
CREATE TABLE test ( id number(11) NOT NULL , user_name varchar(50) DEFAULT NULL, addr varchar(500) DEFAULT NULL, sex number(1) DEFAULT NULL, PRIMARY KEY (id) )
application.properties新增mysql和oracle数据源配置
#mysql spring.datasource.mysql.driver-class-name=com.mysql.jdbc.Driver spring.datasource.mysql.jdbc-url=jdbc:mysql://127.0.0.1:3306/app_test?Unicode=true&characterEncoding=UTF-8&allowMultiQueries=true spring.datasource.mysql.username=root spring.datasource.mysql.password=root #oracle spring.datasource.oracle.driver-class-name=oracle.jdbc.driver.OracleDriver spring.datasource.oracle.jdbc-url=jdbc:oracle:thin:@127.0.0.1:1521:app_test spring.datasource.oracle.username=root spring.datasource.oracle.password=root #mybatis #mybatis.mapper-locations=classpath:mapper/*Dao.xml #mybatis.config-location=classpath:mapper/conf/mybatis_cfg.xml #mybatis.type-aliases-package=com.example.demo.model
注:使用spring.datasource-[标识],来去区分不同的数据源配置
创建数据源配置类
MysqlDataSource.java
package com.example.demo.conf; /** * 多数据源中有一个是主数据源,使用注解@primary进行声明 * MapperScan 定义包扫描的路径 */ @Configuration @MapperScan(basePackages = "com.example.demo.dao.mysql", sqlSessionTemplateRef = "mysqlSqlSessionTemplate") public class MysqlDataSource { /** * dataSource * @return */ @Primary @Bean(name="mysqlDS") @Qualifier("mysqlDS") @ConfigurationProperties(prefix="spring.datasource.mysql") public DataSource mysqlDataSource() { return DataSourceBuilder.create().build(); } /** * sessionFactory * @param dataSource * @return * @throws Exception */ @Bean(name = "mysqlSqlSessionFactory") @Primary public SqlSessionFactory mysqlSqlSessionFactory(@Qualifier("mysqlDS") DataSource dataSource) throws Exception { SqlSessionFactoryBean bean = new SqlSessionFactoryBean(); bean.setDataSource(dataSource); bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/mysql/*Dao.xml")); bean.setConfigLocation(new PathMatchingResourcePatternResolver().getResource("classpath:mapper/mysql/conf/mybatis_cfg.xml")); return bean.getObject(); } /** * TransactionManager * @param dataSource * @return */ @Bean(name = "mysqlTransactionManager") @Primary public DataSourceTransactionManager mysqlTransactionManager(@Qualifier("mysqlDS") DataSource dataSource) { return new DataSourceTransactionManager(dataSource); } /** * SessionTemplate * @param sqlSessionFactory * @return * @throws Exception */ @Bean(name = "mysqlSqlSessionTemplate") @Primary public SqlSessionTemplate mysqlSqlSessionTemplate(@Qualifier("mysqlSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception { return new SqlSessionTemplate(sqlSessionFactory); } /** * JdbcTemplate * @param dataSource * @return */ public JdbcTemplate mysqlJdbcTemplate(@Qualifier("mysqlDS") DataSource dataSource){ return new JdbcTemplate(dataSource); } }
OracleDataSource.java
package com.example.demo.conf; /** * 多数据源中有一个是主数据源,使用注解@primary进行声明 * MapperScan 定义包扫描的路径 */ @Configuration @MapperScan(basePackages = "com.example.demo.dao.oracle", sqlSessionTemplateRef = "oracleSqlSessionTemplate") public class OracleDataSource { /** * dataSource * @return */ @Bean(name="oracleDS") @Qualifier("oracleDS") @ConfigurationProperties(prefix="spring.datasource.oracle") public DataSource oracleDS() { return DataSourceBuilder.create().build(); } /** * sessionFactory * @param dataSource * @return * @throws Exception */ @Bean(name = "oracleSqlSessionFactory") public SqlSessionFactory oracleSqlSessionFactory(@Qualifier("oracleDS") DataSource dataSource) throws Exception { SqlSessionFactoryBean bean = new SqlSessionFactoryBean(); bean.setDataSource(dataSource); bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/oracle/*Dao.xml")); bean.setConfigLocation(new PathMatchingResourcePatternResolver().getResource("classpath:mapper/oracle/conf/mybatis_cfg.xml")); return bean.getObject(); } /** * TransactionManager * @param dataSource * @return */ @Bean(name = "oracleTransactionManager") public DataSourceTransactionManager oracleTransactionManager(@Qualifier("oracleDS") DataSource dataSource) { return new DataSourceTransactionManager(dataSource); } /** * SessionTemplate * @param sqlSessionFactory * @return * @throws Exception */ @Bean(name = "oracleSqlSessionTemplate") public SqlSessionTemplate oracleSqlSessionTemplate(@Qualifier("oracleSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception { return new SqlSessionTemplate(sqlSessionFactory); } /** * JdbcTemplate * @param dataSource * @return */ public JdbcTemplate oracleJdbcTemplate(@Qualifier("oracleDS") DataSource dataSource){ return new JdbcTemplate(dataSource); } }
创建Dao
ExampleDao.java
package com.example.demo.dao.mysql; @Mapper public interface ExampleDao { int insert(Example record); }
TestDao.java
package com.example.demo.dao.oracle; public interface TestDao { int insert(Test record); }
创建Model
Example.java
package com.example.demo.model.mysql; /** * example * @author */ public class Example implements Serializable { private Integer id; private String userName; private String addr; private Byte sex; //get set }
Test.java
package com.example.demo.model.oracle; /** * TEST * @author */ public class Test implements Serializable { private Long id; private String userName; private String addr; private Short sex; // get set }
mapper.xml配置文件
mybatis_cfg.xml,mysql和oracle相同
<?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="cacheEnabled" value="true" /><!-- 是否开启缓存 --> <setting name="lazyLoadingEnabled" value="true" /> <setting name="multipleResultSetsEnabled" value="true" /> <setting name="useColumnLabel" value="true" /> <setting name="useGeneratedKeys" value="true" /> <setting name="defaultExecutorType" value="REUSE" /> </settings> </configuration>
ExampleDao.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.example.demo.dao.mysql.ExampleDao"> <resultMap id="BaseResultMap" type="com.example.demo.model.mysql.Example"> <id column="id" jdbcType="INTEGER" property="id" /> <result column="user_name" jdbcType="VARCHAR" property="userName" /> <result column="addr" jdbcType="VARCHAR" property="addr" /> <result column="sex" jdbcType="TINYINT" property="sex" /> </resultMap> <sql id="Base_Column_List"> id, user_name, addr, sex </sql> <insert id="insert" keyColumn="id" keyProperty="id" parameterType="com.example.demo.model.mysql.Example" useGeneratedKeys="true"> insert into example (user_name, addr, sex ) values (#{userName,jdbcType=VARCHAR}, #{addr,jdbcType=VARCHAR}, #{sex,jdbcType=TINYINT} ) </insert> </mapper>
TestDao.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.example.demo.dao.oracle.TestDao"> <resultMap id="BaseResultMap" type="com.example.demo.model.oracle.Test"> <id column="ID" jdbcType="DECIMAL" property="id" /> <result column="USER_NAME" jdbcType="VARCHAR" property="userName" /> <result column="ADDR" jdbcType="VARCHAR" property="addr" /> <result column="SEX" jdbcType="DECIMAL" property="sex" /> </resultMap> <sql id="Base_Column_List"> ID, USER_NAME, ADDR, SEX </sql> <insert id="insert" parameterType="com.example.demo.model.oracle.Test" useGeneratedKeys="false"> insert into TEST (ID,USER_NAME, ADDR, SEX ) values (#{id,jdbcType=DECIMAL},#{userName,jdbcType=VARCHAR}, #{addr,jdbcType=VARCHAR}, #{sex,jdbcType=DECIMAL} ) </insert> </mapper>
测试类
ExampleDaoTest.java
package com.example.demo.test; @RunWith(SpringRunner.class) @SpringBootTest(classes = DemoApplication.class) public class ExampleDaoTest { @Autowired private ExampleDao exampleDao; @Test public void saveBean(){ Example bean = new Example(); bean.setUserName("ZhangSan"); bean.setAddr("ShangHai"); bean.setSex((byte)1); exampleDao.insert(bean); } }
TestDaoTest.java
package com.example.demo.test; @RunWith(SpringRunner.class) @SpringBootTest(classes = DemoApplication.class) public class TestDaoTest { @Autowired private TestDao testDao; @org.junit.Test public void saveBean(){ Test bean = new Test(); bean.setUserName("ZhangSan"); bean.setAddr("ShangHai"); bean.setSex((short)1); bean.setId(new Long(3)); testDao.insert(bean); } }