背景
因为项目需要查询其他系统的pg数据库,详细见Spring Boot整合PostgreSQL
由于之前没有pg数据库,且内网没有pg数据库的测试环境,所以参照上文自己部署了一套,并且自己写了一套干净的小demo,实现功能后提交代码到内网环境,环境对接耽误很多时间,最终还是解决了,使用pg数据库一定要注意开放远程登录功能,查询出其他系统的数据了,可是现有的数据库查询却出现了问题了,报错信息是psqlexception-error-relation-table-name-does-not-exist。分析是由于多数据源的问题导致。
问题复盘
数据源配置文件
使用外网自己搭建的pg数据库和原有的mysql数据库进行试验进行问题复现。首先添加新数据源的配置文件:
#原数据源信息
spring.pgsql.datasource.url=jdbc:postgresql://192.168.1.106:5432/testdb
spring.pgsql.datasource.username=postgres
spring.pgsql.datasource.password=pg123
spring.pgsql.datasource.driverClassName=org.postgresql.Driver
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.properties.hibernate.hbm2ddl.auto=update
#新数据源信息
spring.datasource.url=jdbc:mysql://192.168.1.105:3306/jpatest
spring.datasource.username=root
spring.datasource.password=Root1234
spring.datasource.driverClassName=com.mysql.jdbc.Driver
此时没有将配置文件内容读到配置类。
模拟业务接口
//原正常接口
@RequestMapping(value = "/getUser")
public String get() {
JSONObject jsonObject = new JSONObject();
LocalDate date = LocalDate.now();
System.out.println(date.toString());
String sql = "select name1,name2,name3,name4,name5,name6 from users where xingqi =?";
try{
List o = jdbcTemplate.queryForList(sql, date.toString());
jsonObject.put("messageCode","0000");
jsonObject.put("info", o);
}catch (Exception e) {
e.printStackTrace();
jsonObject.put("0021","error");
}
return jsonObject.toString();
}
//新增接口
@RequestMapping(value = "/get")
public String getTest(){
int id = 1;
return services.getName(id);
}
新增接口对应实现(简单实现)
public interface Services {
String getName(int id);
}
@Service
public class Impl implements Services {
@Autowired
private Dao dao;
@Override
public String getName(int id) {
return dao.getName(id);
}
}
@Service
public interface Dao {
@Select("select username from jpa_user where userId = #{id}")
String getName(@Param("id") int id);
}
原配置类
@Configuration
public class DataConfig {
@Value("${spring.pgsql.datasource.username}")
private String user;
@Value("${spring.pgsql.datasource.password}")
private String pass;
@Value("${spring.pgsql.datasource.url}")
private String url;
@Value("${spring.pgsql.datasource.driverClassName}")
private String driverClassName;
@Bean(name = "data_pgsql")
public DataSource dataSource() {
return getDruidDataSource(user,pass,url,driverClassName);
}
private DruidDataSource getDruidDataSource(String name,String password,String url,String driver){
DruidDataSource druidDataSource = new DruidDataSource();
druidDataSource.setDriverClassName(driver);
druidDataSource.setUrl(url);
druidDataSource.setUsername(name);
druidDataSource.setPassword(password);
druidDataSource.setInitialSize(1);
druidDataSource.setMinIdle(1);
druidDataSource.setMaxActive(20);
druidDataSource.setMaxWait(60000);
druidDataSource.setTimeBetweenEvictionRunsMillis(60000);
druidDataSource.setMinEvictableIdleTimeMillis(300000);
druidDataSource.setPoolPreparedStatements(true);
druidDataSource.setMaxPoolPreparedStatementPerConnectionSize(20);
return druidDataSource;
}
@Bean(name = "jdbc_pgsql")
public JdbcTemplate getJdbcTemplate(@Qualifier("data_pgsql") DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
}
项目启动,调用get方法,问题复现:
解决
分析
在原有配置类中添加新增的数据源配置,并且将其用@Primary修饰,原有数据源配置不变。
编码
@Configuration
public class DataConfig {
@Value("${spring.pgsql.datasource.username}")
private String user;
@Value("${spring.pgsql.datasource.password}")
private String pass;
@Value("${spring.pgsql.datasource.url}")
private String url;
@Value("${spring.pgsql.datasource.driverClassName}")
private String driverClassName;
@Value("${spring.datasource.username}")
private String user1;
@Value("${spring.datasource.password}")
private String pass1;
@Value("${spring.datasource.url}")
private String url1;
@Value("${spring.datasource.driverClassName}")
private String driverClassName1;
@Bean(name = "data_pgsql")
public DataSource dataSource() {
return getDruidDataSource(user,pass,url,driverClassName);
}
@Bean(name = "data_mysql")
@Qualifier("mysqlDataSource")
@Primary
public DataSource mysqlDataSource() {
return getDruidDataSource(user1,pass1,url1,driverClassName1);
}
private DruidDataSource getDruidDataSource(String name,String password,String url,String driver){
DruidDataSource druidDataSource = new DruidDataSource();
druidDataSource.setDriverClassName(driver);
druidDataSource.setUrl(url);
druidDataSource.setUsername(name);
druidDataSource.setPassword(password);
druidDataSource.setInitialSize(1);
druidDataSource.setMinIdle(1);
druidDataSource.setMaxActive(20);
druidDataSource.setMaxWait(60000);
druidDataSource.setTimeBetweenEvictionRunsMillis(60000);
druidDataSource.setMinEvictableIdleTimeMillis(300000);
druidDataSource.setPoolPreparedStatements(true);
druidDataSource.setMaxPoolPreparedStatementPerConnectionSize(20);
return druidDataSource;
}
@Bean(name = "jdbc_pgsql")
public JdbcTemplate getJdbcTemplate(@Qualifier("data_pgsql") DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
@Bean(name = "jdbc_mysql")
public JdbcTemplate getMyJdbcTemplate(@Qualifier("data_mysql") DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
}