526互联

mybatis配置多数据源

发布时间 2023-05-29 22:37:39作者: x-cuke

yml中配置mybatis的日志输出信息


mybatis:
  # spring boot集成mybatis的方式打印sql
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

maven引入相关的配置

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.2.2</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.16</version>
</dependency>
<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>11.2.0.3</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.10</version>
</dependency>

数据元的配置


import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;

@Configuration
public class DataSourceConfiguration {

    //默认使用这个实体的DataSource
    @Primary
    @Bean("firstDataSource")
    public DataSource firstDataSource(){
        /**
         * <dependency>
         *             <groupId>com.alibaba</groupId>
         *             <artifactId>druid</artifactId>
         *             <version>1.1.10</version>
         *         </dependency>
         * */
        DruidDataSource oracleDataSource = new DruidDataSource();
        // oralce 数据库账户密码基础配置
        oracleDataSource.setUsername("CXK");
        oracleDataSource.setPassword("caoxuekun");
        oracleDataSource.setUrl("jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.143)(PORT = 49161)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = helowin)))");
        oracleDataSource.setDriverClassName("oracle.jdbc.OracleDriver");
        return oracleDataSource;
    }


    @Bean("secondDataSource")
    public DataSource secondDataSource(){
        /**
         * <dependency>
         *             <groupId>com.alibaba</groupId>
         *             <artifactId>druid</artifactId>
         *             <version>1.1.10</version>
         *         </dependency>
         * */
        DruidDataSource mysqlDataSource = new DruidDataSource();
        // oralce 数据库账户密码基础配置
        mysqlDataSource.setUsername("root");
        mysqlDataSource.setPassword("caoxuekun");
        mysqlDataSource.setUrl("jdbc:mysql://127.0.0.1:3306/caoxuekun?characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8");
        mysqlDataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        return mysqlDataSource;
    }
}

SqlSessionFactory的创建


import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;
import java.io.IOException;

@Configuration

@MapperScan(basePackages="com.moredatabase.mapper",sqlSessionFactoryRef="firstSqlSessionFactory")
public class FirstMybatisConfig {

    @Primary
    @Bean("firstSqlSessionFactory")
    public SqlSessionFactory firstSqlSessionFactory(@Qualifier("firstDataSource")DataSource dataSource)  {
        // 数据源设置
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        //mapper的xml文件位置
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        String locationPattern = "classpath:mapper/*.xml";
        try {
            sqlSessionFactoryBean.setMapperLocations(resolver.getResources(locationPattern));
        } catch (IOException e) {
            e.printStackTrace();
        }
        //对应数据库的entity位置
//        String typeAliasesPackage = "com.moredatabase.mapper";
//        sqlSessionFactoryBean.setTypeAliasesPackage(typeAliasesPackage);
        SqlSessionFactory sqlSessionFactory = null;
        try {
            sqlSessionFactory = sqlSessionFactoryBean.getObject();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return sqlSessionFactory;
    }

}

第二个sqlSessionFactory实体的创建


import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;
import java.io.IOException;

@Configuration
@MapperScan(basePackages="com.moredatabase.mysql",sqlSessionFactoryRef="secondSqlSessionFactory")
public class SecondMybatisConfig {

    @Bean("secondSqlSessionFactory")
    public SqlSessionFactory secondSqlSessionFactory(@Qualifier("secondDataSource") DataSource dataSource)  {

        SqlSessionFactory sqlSessionFactory = null;

        try{
            // 数据源设置
            SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
            sqlSessionFactoryBean.setDataSource(dataSource);
            //mapper的xml文件位置
            PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
            String locationPattern = "classpath:mapper2/*.xml";
            sqlSessionFactoryBean.setMapperLocations(resolver.getResources(locationPattern));

            //对应数据库的entity位置
//        String typeAliasesPackage = "com.moredatabase.entry";
//        sqlSessionFactoryBean.setTypeAliasesPackage(typeAliasesPackage);
            sqlSessionFactory = sqlSessionFactoryBean.getObject();
        }catch (Exception e){
            System.out.println("******************************************************");
            e.printStackTrace();
        }

        return sqlSessionFactory;
    }
}

接口的创建


import com.moredatabase.entry.Cxk;
import com.moredatabase.entry.Test;
import com.moredatabase.mapper2.CxkMapper;
import com.moredatabase.mapper.TestMapper;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import java.util.List;

@RestController
public class HelloController {

    @RequestMapping("hello")
    public String hello(){
        System.out.println("coming...");
        return "SUCCESS";
    }


    @Resource(name = "secondSqlSessionFactory")
    @Qualifier("secondSqlSessionFactory")
    private SqlSessionFactory secondSqlSessionFactory;

    @Resource(name = "firstSqlSessionFactory")
    @Qualifier("firstSqlSessionFactory")
    private SqlSessionFactory firstSqlSessionFactory;


    @RequestMapping("getAllCxk")
    public List<Cxk> getAllCxk(){
        System.out.println("getAllCxk");
        SqlSession sqlSession = secondSqlSessionFactory.openSession();
        CxkMapper mapper = sqlSession.getMapper(CxkMapper.class);
        List<Cxk> allCxk = mapper.getAllCxk();
        return allCxk;
    }

    @RequestMapping("getAllTest")
    @ResponseBody
    public List<Test> getAllTest(){
        System.out.println("getAllTest");
        SqlSession sqlSession = firstSqlSessionFactory.openSession();
        TestMapper mapper = sqlSession.getMapper(TestMapper.class);
        List<Test> allTest = mapper.getAllTest();
        return allTest;

    }


}

Mapper的创建


import com.moredatabase.entry.Test;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface TestMapper {


    List<Test> getAllTest();

}


import com.moredatabase.entry.Cxk;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface CxkMapper{

    List<Cxk> getAllCxk();

}

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.moredatabase.mapper.TestMapper">
 
  <select id="getAllTest" resultType="com.moredatabase.entry.Test">
    select * from test
  </select>

</mapper>

<?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.moredatabase.mapper2.CxkMapper">
 
  <select id="getAllCxk" resultType="com.moredatabase.entry.Cxk">
    select * from cxk
  </select>

</mapper>