简单的用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

看后台有打印结果表示配置成功

热门工具换一换