简单的用jdbc使spring boot项目同时连接Oracle和Mysql数据源
1.首先在pom.xml中添加依赖
//jdbc连接数据库
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
//第一个数据源：mysql
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
//第二个数据源：ojdbc6，oracle无法从maven库中下载，需要下载jar包手动导入
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>10.2.0.4.0</version>
</dependency>
2.下载了一个可以使用mysql语句的第三方app :mysql workbench；（这一步是为了方便测试，可以忽略，下载mysql
workbench，需要依赖于另一个插件，按照步骤下载即可）
3.在appliction.properties中添加配置信息
//第一数据源
spring.datasource.primary.url=jdbc:oracle:thin:@10.72.2.22:1521:coapinst
spring.datasource.primary.username=coapinst
spring.datasource.primary.password=coapinst
spring.datasource.primary.driver-class-name=oracle.jdbc.OracleDriver
//第二数据源
spring.datasource.secondary.url=jdbc:mysql://10.72.2.21:3306/drools
spring.datasource.secondary.username=root
spring.datasource.secondary.password=admin
spring.datasource.secondary.driver-class-name=com.mysql.jdbc.Driver
4.添加数据配置类，直接添加即可（如果是只有两个的话，无需改动，再多的话，依照上面的模板添加）
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;
@Configuration
public class DataSourceConfig {
@Bean(name = "primaryDataSource")
@Qualifier("primaryDataSource")//该注解指定注入的Bean的名称，Spring框架使用byName方式寻找合格的
bean，这样就消除了byType方式产生的歧义
@ConfigurationProperties(prefix="spring.datasource.primary")//读取配置文件里前缀
为"spring.datasource.primary"的语句
public DataSource primaryDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "secondaryDataSource")
@Qualifier("secondaryDataSource")
@Primary//有时候我们能保证同一个类型在spring容器中只有一个实例，有时候我们保证不了，此时不讨论by
name注入。这个时候@Primary注解就非常重要了
@ConfigurationProperties(prefix="spring.datasource.secondary")
public DataSource secondaryDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "primaryJdbcTemplate")
public JdbcTemplate primaryJdbcTemplate(
@Qualifier("primaryDataSource") DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
@Bean(name = "secondaryJdbcTemplate")
public JdbcTemplate secondaryJdbcTemplate(
@Qualifier("secondaryDataSource") DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
}
5.添加测试类
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
@RestController
public class CxzdbController {
@Autowired
@Qualifier("primaryJdbcTemplate")
protected JdbcTemplate jdbcTemplate1;
@Autowired
@Qualifier("secondaryJdbcTemplate")
protected JdbcTemplate jdbcTemplate2;
// @Autowired
// protected JdbcTemplate jdbcTemplate;
@RequestMapping("/test")
public List<Map<String, Object>> getCxzdb(){
// String sql = "SELECT * FROM sys_user";
String sql = "SELECT * FROM USER";
List<Map<String, Object>> resObj = (List<Map<String, Object>>)
jdbcTemplate1.execute(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection con) throws
SQLException {
return con.prepareStatement(sql);
}
}, new PreparedStatementCallback<List<Map<String, Object>>>() {
@Override
public List<Map<String, Object>> doInPreparedStatement(PreparedStatement ps)
throws SQLException, DataAccessException {
ps.execute();
ResultSet rs = ps.getResultSet();
while(rs.next()){
System.out.println("==" + rs.getString(1));
System.out.println("==" + rs.getString(2));
System.out.println("==" + rs.getString(3));
System.out.println("==" + rs.getString(4));
System.out.println("==" + rs.getString(5));
// Map<String, Object> map = new HashMap<>();
// map.put("id", rs.getString("id"));
}
return null;
}
} );
return resObj;
}
}
6. 启动之后访问：http://localhost:8080/test
看后台有打印结果表示配置成功