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
