java springboot 双数据源连接sqlserver mysql

背景：

测试某台机器能否连接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身份验证，在“状态”项中授予连接到数据库和登录启用;

        

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