Spring Boot 2.0.2 教程 - mybatis多数据源配置,Mysql+Oracle - 08
原创    java_world    发表于:2018-10-19 14:58:41
  阅读 :108   收藏   编辑

上一篇: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驱动

项目整体结构 

1

创建数据库及表

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);
    }

}


评论
条评论