【Spring Boot 27】Springboot配置两个数据库（附代码+源码分析）

系统管理员 2021-09-08 22:52 473阅读 0赞

## 一、JDBCUtil方式连接第二个数据库 ##

### 1、执行select语句 ###

String sql = "select id,name,age from user";
    // 数据库连接
    DruidDataSource druidDataSource = JDBCUtils.getDruidDataSource(ds);
    JdbcTemplate jdbcTemplate = new JdbcTemplate(druidDataSource);
    List<Map<String, Object>> select = jdbcTemplate.queryForList(sql);

### 2、 JDBCUtils  ###

package com.guor.utils;
    
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    import javax.sql.DataSource;
    
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    
    import com.alibaba.druid.pool.DruidDataSource;
    
    public class JDBCUtils {
        private final static Logger log = LoggerFactory.getLogger(JDBCUtils.class);
    
        /**
         * 定义数据源
         */
        private static DataSource ds;
    
        /**
         * 定义数据库连接配置
         */
    
        static {
            try {
                /**
                 * 加载配置文件
                 */
                DruidDataSource dataSource = new DruidDataSource();
                dataSource.setDriverClassName("org.postgresql.Driver");
                dataSource.setUrl("jdbc:postgresql://127.0.0.1:5432/test");
                dataSource.setUsername("postgres");
                dataSource.setPassword("123456");
                ds = dataSource;
            } catch (Exception e) {
                log.error("create connection pool error,errorMessage:{}", e);
            }
        }
    
        /**
         * 获取数据源
         * @return 返回数据源
         */
        public static DataSource getDataSource(){
            return ds;
        }
    
        /**
         * 获取连接对象
         * @return 返回连接对象
         * @throws SQLException  抛出的编译异常
         */
        public static Connection getConn() throws SQLException {
            return ds.getConnection();
        }
    
        /**
         *  关闭连接
         * @param stmt  sql执行对象
         * @param conn  数据库连接对象
         */
        public static void close(Statement stmt, Connection conn){
            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    log.error("close error,errorMessage:{}", e);
                }
            }
    
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    log.error("close error,errorMessage:{}", e);
                }
            }
        }
    
        /**
         * 关闭资源的重载方法
         * @param rs    处理结果集的对象
         * @param stmt  执行sql语句的对象
         * @param conn  连接数据库的对象
         */
        public static void close(ResultSet rs, Statement stmt, Connection conn){
            if(rs != null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    log.error("close error,errorMessage:{}", e);
                }
            }
    
            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    log.error("close error,errorMessage:{}", e);
                }
            }
    
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    log.error("close error,errorMessage:{}", e);
                }
            }
        }
    }

## 二、jdbcTemplate.queryForList源码初探 ##

public class JdbcTemplate extends JdbcAccessor implements JdbcOperations {
        public JdbcTemplate(DataSource dataSource) {
            setDataSource(dataSource);
            afterPropertiesSet();
        }
        
        @Override
        public List<Map<String, Object>> queryForList(String sql) throws DataAccessException {
            return query(sql, getColumnMapRowMapper());
        }
    
        @Override
        public <T> List<T> query(String sql, RowMapper<T> rowMapper) throws DataAccessException {
            return result(query(sql, new RowMapperResultSetExtractor<>(rowMapper)));
        }
    
        @Override
        @Nullable
        public <T> T query(final String sql, final ResultSetExtractor<T> rse) throws DataAccessException {
            Assert.notNull(sql, "SQL must not be null");
            Assert.notNull(rse, "ResultSetExtractor must not be null");
            if (logger.isDebugEnabled()) {
                logger.debug("Executing SQL query [" + sql + "]");
            }
    
            /**
             * Callback to execute the query.
             */
            class QueryStatementCallback implements StatementCallback<T>, SqlProvider {
                @Override
                @Nullable
                public T doInStatement(Statement stmt) throws SQLException {
                    ResultSet rs = null;
                    try {
                        rs = stmt.executeQuery(sql);
                        return rse.extractData(rs);
                    }
                    finally {
                        JdbcUtils.closeResultSet(rs);
                    }
                }
                @Override
                public String getSql() {
                    return sql;
                }
            }
    
            return execute(new QueryStatementCallback());
        }
    
        @Override
        @Nullable
        public <T> T execute(StatementCallback<T> action) throws DataAccessException {
            Assert.notNull(action, "Callback object must not be null");
    
            Connection con = DataSourceUtils.getConnection(obtainDataSource());
            Statement stmt = null;
            try {
                stmt = con.createStatement();
                applyStatementSettings(stmt);
                T result = action.doInStatement(stmt);
                handleWarnings(stmt);
                return result;
            }
            catch (SQLException ex) {
                // Release Connection early, to avoid potential connection pool deadlock
                // in the case when the exception translator hasn't been initialized yet.
                String sql = getSql(action);
                JdbcUtils.closeStatement(stmt);
                stmt = null;
                DataSourceUtils.releaseConnection(con, getDataSource());
                con = null;
                throw translateException("StatementCallback", sql, ex);
            }
            finally {
                JdbcUtils.closeStatement(stmt);
                DataSourceUtils.releaseConnection(con, getDataSource());
            }
        }
        ...
    }

public interface Statement extends Wrapper, AutoCloseable {
        ResultSet executeQuery(String sql) throws SQLException;
        ...
    }
    
    public abstract class JdbcAccessor implements InitializingBean {
        @Nullable
        private DataSource dataSource;
        
        public void setDataSource(@Nullable DataSource dataSource) {
            this.dataSource = dataSource;
        }
    }

## 三、更优雅的方式 -> 通过配置类方式实现 ##

### 1、application.yml ###

server:
      port: 8080
    
    spring:
      application:
        name: test
      datasource:
        sqlserver:
          jdbc-url: jdbc:sqlserver://127.0.0.1:1433;DatabaseName=test
          driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
          username: sa
          password: sa
        postgres:
          jdbc-url: jdbc:postgresql://127.0.0.1:5432/test
          driverClassName: org.postgresql.Driver
          username: postgres
          password: 123456

### 2、配置类 ###

package com.guor.config;
    
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.mybatis.spring.SqlSessionFactoryBean;
    import org.mybatis.spring.SqlSessionTemplate;
    import org.mybatis.spring.annotation.MapperScan;
    import org.springframework.beans.factory.annotation.Qualifier;
    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.core.io.support.PathMatchingResourcePatternResolver;
    import org.springframework.jdbc.datasource.DataSourceTransactionManager;
    
    import javax.sql.DataSource;
    
    @Configuration
    @MapperScan(basePackages = "com.guor.dao.postgres", sqlSessionTemplateRef  = "postgresSqlSessionTemplate")
    public class PostgresConfig {
        @Bean(name = "postgresDataSource")
        @ConfigurationProperties(prefix = "spring.datasource.postgres")
        public DataSource postgresDataSource() {
            return DataSourceBuilder.create().build();
        }
    
        @Bean(name = "postgresSqlSessionFactory")
        public SqlSessionFactory postgresSqlSessionFactory(@Qualifier("postgresDataSource") DataSource dataSource) throws Exception {
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(dataSource);
            bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("com/guor/dao/postgres/mapping/*.xml"));
            return bean.getObject();
        }
    
        @Bean(name = "postgresTransactionManager")
        public DataSourceTransactionManager postgresTransactionManager(@Qualifier("postgresDataSource") DataSource dataSource) {
            return new DataSourceTransactionManager(dataSource);
        }
    
        @Bean(name = "postgresSqlSessionTemplate")
        public SqlSessionTemplate postgresSqlSessionTemplate(@Qualifier("postgresSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
            return new SqlSessionTemplate(sqlSessionFactory);
        }
    }

package com.guor.config;
    
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.mybatis.spring.SqlSessionFactoryBean;
    import org.mybatis.spring.SqlSessionTemplate;
    import org.mybatis.spring.annotation.MapperScan;
    import org.springframework.beans.factory.annotation.Qualifier;
    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 org.springframework.core.io.support.PathMatchingResourcePatternResolver;
    import org.springframework.jdbc.datasource.DataSourceTransactionManager;
    
    import javax.sql.DataSource;
    
    @Configuration
    @MapperScan(basePackages = "com.guor.dao.sqlserver", sqlSessionTemplateRef  = "sqlserverSqlSessionTemplate")
    public class SqlserverConfig {
        @Bean(name = "sqlserverDataSource")
        @ConfigurationProperties(prefix = "spring.datasource.sqlserver")
        @Primary
        public DataSource sqlserverDataSource() {
            return DataSourceBuilder.create().build();
        }
    
        @Bean(name = "sqlserverSqlSessionFactory")
        @Primary
        public SqlSessionFactory sqlserverSqlSessionFactory(@Qualifier("sqlserverDataSource") DataSource dataSource) throws Exception {
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(dataSource);
            bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("com/guor/dao/sqlserver/mapping/*.xml"));
            return bean.getObject();
        }
    
        @Bean(name = "sqlserverTransactionManager")
        @Primary
        public DataSourceTransactionManager sqlserverTransactionManager(@Qualifier("sqlserverDataSource") DataSource dataSource) {
            return new DataSourceTransactionManager(dataSource);
        }
    
        @Bean(name = "sqlserverSqlSessionTemplate")
        @Primary
        public SqlSessionTemplate sqlserverSqlSessionTemplate(@Qualifier("sqlserverSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
            return new SqlSessionTemplate(sqlSessionFactory);
        }
    }

### 3、UserPostgresMapper  ###

package com.guor.dao.postgres;
    
    import java.util.List;
    import java.util.Map;
    
    public interface UserPostgresMapper {
        List<Map<String, Object>> getUsersByPostgres();
    }

### 4、UserPostgresMapper.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.guor.dao.postgres.UserPostgresMapper">
        <select id="getUsersByPostgres" resultType="java.util.LinkedHashMap">
            SELECT * FROM t_user
        </select>
    </mapper>

### 5、UserSqlserverMapper ###

package com.guor.dao.sqlserver;
    
    import java.util.List;
    import java.util.Map;
    
    public interface UserSqlserverMapper {
        List<Map<String, Object>> getUsersFromSqlserver();
    }

### 6、UserSqlserverMapper.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.guor.dao.sqlserver.UserSqlserverMapper">
        <select id="getUsersFromSqlserver" resultType="java.util.LinkedHashMap">
            SELECT * FROM t_user
        </select>
    </mapper>

### 7、controller ###

package com.guor.controller;
    
    import java.util.List;
    import java.util.Map;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import com.guor.dao.postgres.UserPostgresMapper;
    import com.guor.dao.sqlserver.UserSqlserverMapper;
    
    @RestController
    @RequestMapping("user")
    public class UserController {
        @Autowired
        private UserSqlserverMapper userMapper;
        
        @GetMapping("/getUsersBySqlserver")
        public List<Map<String, Object>> getUsersBySqlserver(){
            return userMapper.getUsersFromSqlserver();
        }
        
        @Autowired
        private UserPostgresMapper userPostgresMapper;
        
        @GetMapping("/getUsersByPostgres")
        public List<Map<String, Object>> getUsersByPostgres(){
            return userPostgresMapper.getUsersByPostgres();
        }
    }

### 8、浏览器访问 ###

> [http://localhost:8080/user/getUsersByPostgres][http_localhost_8080_user_getUsersByPostgres]
> 
> \[\{"id":1,"name":"zs","age":18,"version":"1","deleted":0\},\{"id":1,"name":"ls","age":28,"creator\_id":87368736,"created\_time":"2021-06-11T02:43:48.000+0000"\},\{"id":2,"name":"ww","age":35,"creator\_id":87368736,"created\_time":"2021-06-11T05:29:20.000+0000"\}\]

> [http://localhost:8080/user/getUsersBySqlserver][http_localhost_8080_user_getUsersBySqlserver]
> 
> \[\{"id":1,"name":"zs","age":18\},\{"id":2,"name":"ls","age":20\}\]

### 9、pom ###

<?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>
    
        <parent>
            <groupId>com.guor</groupId>
            <artifactId>test-pom</artifactId>
            <version>1.0.0</version>
            <relativePath>../pom.xml</relativePath>
        </parent>
    
        <artifactId>test</artifactId>
        <name>test</name>
        <description>test</description>
        <version>1.0.0</version>
    
        <dependencies>
            <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.postgresql</groupId>
                <artifactId>postgresql</artifactId>
            </dependency>
            <dependency>
                <groupId>commons-io</groupId>
                <artifactId>commons-io</artifactId>
                <version>2.5</version>
            </dependency>
            <dependency>
                <groupId>com.microsoft.sqlserver</groupId>
                <artifactId>mssql-jdbc</artifactId>
            </dependency>
            <dependency>
                <groupId>com.google.guava</groupId>
                <artifactId>guava</artifactId>
                <version>20.0</version>
            </dependency>
            <dependency>
                <groupId>cglib</groupId>
                <artifactId>cglib-nodep</artifactId>
                <version>3.2.4</version>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-configuration-processor</artifactId>
                <optional>true</optional>
            </dependency>
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid</artifactId>
                <version>1.0.16</version>
            </dependency>
        </dependencies>
        <build>
            <finalName>test</finalName>
        </build>
    </project>

## 四、配置类内容初探 ##

### 1、SqlSessionFactory ###

SqlSessionFactory是MyBatis的关键对象，它是个单个数据库映射关系经过编译后的内存镜像，SqlSessionFactory对象的实例可以通过SqlSessionFactoryBuilder对象类获得，而SqlSessionFactoryBuilder则可以从XML配置文件或一个预先定制的Configuration的实例构建出SqlSessionFactory的实例。每一个MyBatis的应用程序都以一个SqlSessionFactory对象的实例为核心，同时SqlSessionFactory也是线程安全的，SqlSessionFactory一旦被创建,应该在应用执行期间都存在，在应用运行期间不要重复创建多次,建议使用单例模式，SqlSessionFactory是创建SqlSession的工厂。

### 2、SqlSession ###

SqlSession是MyBatis的关键对象，是执行持久化操作的独享，类似于JDBC中的Connection。它是应用程序与持久层之间执行交互操作的一个单线程对象，也是MyBatis执行持久化操作的关键对象。SqlSession对象完全包含以数据库为背景的所有执行SQL操作的方法，它的底层封装了JDBC连接，可以用SqlSession实例来直接执行被映射的SQL语句。每个线程都应该有它自己的SqlSession实例。SqlSession的实例不能被共享，同时SqlSession也是线程不安全的，绝对不能讲SqlSeesion实例的引用放在一个类的静态字段甚至是实例字段中。也绝不能将SqlSession实例的引用放在任何类型的管理范围中，比如Servlet当中的HttpSession对象中，使用完SqlSeesion之后关闭Session很重要，应该确保使用finally块来关闭它。

### 3、SqlSession创建过程 ###

mybatis框架主要是围绕着SqlSessionFactory进行的，创建过程大概如下：

1.  定义一个Configuration对象，其中包含数据源、事务、mapper文件资源以及影响数据库行为属性设置settings。
2.  通过配置对象，则可以创建一个SqlSessionFactoryBuilder对象
3.  通过 SqlSessionFactoryBuilder 获得SqlSessionFactory 的实例。
4.  SqlSessionFactory 的实例可以获得操作数据的SqlSession实例，通过这个实例对数据库进行操作。

### 4、SqlSessionTemplate ###

SqlSessionTemplate是MyBatis-Spring的核心。这个类负责管理MyBatis的SqlSession,调用MyBatis的SQL方法，翻译异常。SqlSessionTemplate是线程安全的，可以被多个DAO所共享使用。  
当调用SQL方法时，包含从映射器getMapper()方法返回的方法，SqlSessionTemplate将会保证使用的SqlSession是和当前Spring的事务相关的。此外，它管理session的生命周期，包含必要的关闭，提交或回滚操作。  
SqlSessionTemplate实现了SqlSession，这就是说要对MyBatis的SqlSession进行简易替换。  
SqlSessionTemplate通常是被用来替代默认的MyBatis实现的DefaultSqlSession，因为它不能参与到Spring的事务中也不能被注入，因为它是线程不安全的。相同应用程序中两个类之间的转换可能会引起数据一致性的问题。  
SqlSessionTemplate对象可以使用SqlSessionFactory作为构造方法的参数来创建。

### 5、DataSourceTransactionManager 核心事务管理器 ###

对JDBC（Java Data Base Connectivity,java数据库连接）进行事务管理，在spring中是对JdbcTemplate进行事务管理。

往期精彩内容：

[Java知识体系总结][Java]

[Spring框架总结][Spring]

[超详细的springBoot学习笔记][springBoot]

[常见数据结构与算法整理总结][Link 1]

[Java设计模式：23种设计模式全面解析][Java_23]

[Java面试题总结（附答案）][Java 1]

[Linux知识体系总结][Linux]

[Redis知识体系总结][Redis]

[http_localhost_8080_user_getUsersByPostgres]: http://localhost:8080/user/getUsersByPostgres
[http_localhost_8080_user_getUsersBySqlserver]: http://localhost:8080/user/getUsersBySqlserver
[Java]: https://blog.csdn.net/guorui_java/article/details/112391105
[Spring]: https://blog.csdn.net/guorui_java/article/details/104018528
[springBoot]: https://blog.csdn.net/guorui_java/article/details/104137262
[Link 1]: https://blog.csdn.net/guorui_java/article/details/106217472
[Java_23]: https://blog.csdn.net/guorui_java/article/details/104026988
[Java 1]: https://blog.csdn.net/guorui_java/article/details/107803283
[Linux]: https://blog.csdn.net/guorui_java/article/details/117430091
[Redis]: https://blog.csdn.net/guorui_java/article/details/116850879