java springboot 双数据源连接sqlserver mysql

系统管理员 2021-11-19 15:10 426阅读 0赞

背景：

测试某台机器能否连接sqlserver

1 首先安装sqlserver 2008:

https://www.cnblogs.com/yzl050819/p/8284242.html

首次通过sql server 2008 managerment studio登录服务器为 机器名\\SQLEXPRESS

2 配置sqlserver登录用户（此前为windows）

https://www.cnblogs.com/chlyA-F/p/6075675.html

1）左侧的对象资源管理器->安全性->登录名，右击sa->属性，为sa用户添加密码，选择sql server身份验证，在“状态”项中授予连接到数据库和登录启用;

![1060009-20161117213853826-421144397.png][]

![1060009-20161117213919748-1597760006.png][]

2）右击对象资源管理器的根节点，选择属性->安全性->sql server和windows身份验证模式，然后就这样

3 配置远程连接及端口

sql server configuration manage － sql server网络适配－sqlexpress的协议－TCP／IP（启用）－ip地址－IPALL－TCP端口1433

studio－根节点属性－连接－允许远程连接勾选

4 spring boot配置数据源

https://www.cnblogs.com/wang-yaz/p/9561188.html

spring.datasource.second.jdbc-url=jdbc:sqlserver://xxxxx:1433;DatabaseName=test
    spring.datasource.second.username=xxx
    spring.datasource.second.password=xxxx
    spring.datasource.second.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
    
    注意1：连接数据库的方式不一样，mysql是/test ,sqlServer是;DatabaseName=test
    spring.datasource.url=jdbc:mysql://xxxx/test  
    spring.datasource.second.jdbc-url=jdbc:sqlserver://xxxxx:1433;DatabaseName=test

5 测试代码

https://blog.csdn.net/TTTTTdzhao/article/details/81627023

try {
    			JdbcTemplate jdbcTemplate = (JdbcTemplate)SpringUtil.getBean("secondaryJdbcTemplate");
    			Connection connection = jdbcTemplate.getDataSource().getConnection();
    
    			if(connection != null) {
    				logger.info("sqlserver 数据库连接成功！");
    			}
    			else {
    				logger.info("sqlserver 数据库连接失败！");
    			}
    
    			List list = jdbcTemplate.queryForList("select * from master.dbo.MSreplication_options");
    			logger.info("sql 输出{}", list);
    		} catch (Exception e) {
    			logger.error(e.getMessage());
    		}

代码：

/**
     * Created by xxx on 19/7/11.
     */
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.beans.BeansException;
    import org.springframework.context.ApplicationContext;
    import org.springframework.context.ApplicationContextAware;
    import org.springframework.stereotype.Component;
    
    /**
     * 取得spring上下文工具类
     * Created by xxx on 18/6/21.
     */
    @Component
    public class SpringUtil implements ApplicationContextAware {
    
        private static Logger LOGGER = LoggerFactory.getLogger(SpringUtil.class);
    
        private static ApplicationContext applicationContext = null;
    
        @Override
        public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
            if(SpringUtil.applicationContext == null){
                SpringUtil.applicationContext  = applicationContext;
            }
            LOGGER.info("---------------Spring Application Context---------------");
        }
    
        public static ApplicationContext getApplicationContext() {
            return applicationContext;
        }
    
        public static Object getBean(String name){
            return getApplicationContext().getBean(name);
    
        }
    
        public static <T> T getBean(Class<T> clazz){
            return getApplicationContext().getBean(clazz);
        }
    
        public static <T> T getBean(String name,Class<T> clazz){
            return getApplicationContext().getBean(name, clazz);
        }
    
    }

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.core.io.support.ResourcePatternResolver;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.jdbc.datasource.DataSourceTransactionManager;
    import org.springframework.transaction.PlatformTransactionManager;
    import javax.sql.DataSource;
    
    @Configuration
    public class SqlServerDataSourceConfig {
    
        @Bean(name = "sqlServerDataSource")
        @Qualifier("sqlServerDataSource")
        @ConfigurationProperties(prefix="spring.datasource.second")
        public DataSource getMyDataSource(){
            return DataSourceBuilder.create().build();
        }
    
        @Bean(name = "mysqlDataSource")
        @Qualifier("mysqlDataSource")
        @ConfigurationProperties(prefix="spring.datasource.out")
        public DataSource getOutDataSource(){
            return DataSourceBuilder.create().build();
        }
    
        @Bean(name = "secondaryJdbcTemplate")
        public JdbcTemplate secondaryJdbcTemplate(
                @Qualifier("sqlServerDataSource") DataSource dataSource) {
            return new JdbcTemplate(dataSource);
        }
    
        @Bean(name = "mysqlJdbcTemplate")
        public JdbcTemplate mysqlJdbcTemplate(
                @Qualifier("mysqlDataSource") DataSource dataSource) {
            return new JdbcTemplate(dataSource);
        }
    }

import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    import org.springframework.jdbc.core.JdbcTemplate;
    
    import java.sql.Connection;
    import java.sql.SQLException;
    import java.util.List;
    
    @SpringBootApplication
    public class UserApplication {
    
    	private static Logger logger = LoggerFactory.getLogger(UserApplication.class);
    
    	public static void main(String[] args) {
    		SpringApplication.run(UserApplication.class, args);
    
    
    		try {
    			JdbcTemplate jdbcTemplate = (JdbcTemplate)SpringUtil.getBean("secondaryJdbcTemplate");
    			Connection connection = jdbcTemplate.getDataSource().getConnection();
    
    			if(connection != null) {
    				logger.info("sqlserver 数据库连接成功！");
    			}
    			else {
    				logger.info("sqlserver 数据库连接失败！");
    			}
    
    			List list = jdbcTemplate.queryForList("select * from vvvvv");
    			logger.info("sql 输出{}", list);
    		} catch (Exception e) {
    			logger.error(e.getMessage());
    		}
    		
    		try {
    			JdbcTemplate jdbcTemplate = (JdbcTemplate)SpringUtil.getBean("mysqlJdbcTemplate");
    			Connection connection = jdbcTemplate.getDataSource().getConnection();
    
    			if(connection != null) {
    				logger.info("mysql 数据库连接成功！");
    			}
    			else {
    				logger.info("mysql 数据库连接失败！");
    			}
    		} catch (Exception e) {
    			logger.error(e.getMessage());
    		}
    
    	}
    
    }

spring.datasource.second.jdbc-url=jdbc:sqlserver://192.168.xx.xx:1433
    spring.datasource.second.username=sa
    spring.datasource.second.password=xxxxxxx
    spring.datasource.second.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
    
    
    
    spring.datasource.out.jdbcUrl=jdbc:mysql://192.168.xx.xx:3306?useUnicode=true&characterEncoding=utf8
    spring.datasource.out.driverClassName=com.mysql.jdbc.Driver
    spring.datasource.out.username=xxx
    spring.datasource.out.password=xxx

转载于:https://www.cnblogs.com/silyvin/p/11169594.html

[1060009-20161117213853826-421144397.png]: /images/20211119/34e0e4ab8f2e4d9c9ea6ff33b644ddc5.png
[1060009-20161117213919748-1597760006.png]: /images/20211119/3b94b864d59f46aa91778f16c66d679b.png