Using multiple DataSources in Spring-CodePudding

I have built a web app in Spring. Till now I have managed to connect to only one datasource but I need to connect to two Datasources.

Here is the code:

DbConfig class:

 @Configuration
public class DbConfig {
    
    @Bean
    @Primary
    public DataSource dataSourceMail() {
    
       
            
        HikariConfig config = new HikariConfig();
        config.setDriverClassName("oracle.jdbc.OracleDriver");
        config.setJdbcUrl("jdbc:oracle:thin:@1");
        config.setUsername("xxx");
        config.setPassword("xxx");
        config.setConnectionTimeout(20000);
        config.setMaximumPoolSize(100);
        config.addDataSourceProperty("cachePrepStmts", "true");
        config.addDataSourceProperty("prepStmtCacheSize", "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        
        
        return new HikariDataSource(config);
    }
    
    @Bean(name="inregIvg")
    public DataSource dataSourceInregIvg() {
        
        HikariConfig config = new HikariConfig();
        config.setDriverClassName("oracle.jdbc.OracleDriver");
        config.setJdbcUrl("jdbc:oracle:thin:@2");
        config.setUsername("yyy");
        config.setPassword("yyy");
        config.setConnectionTimeout(20000);
        config.setMaximumPoolSize(100);
        config.addDataSourceProperty("cachePrepStmts", "true");
        config.addDataSourceProperty("prepStmtCacheSize", "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        
        
        return new HikariDataSource(config);
        
    }
}

Repository:

    @Repository
public class TelefonMailRepository {
      
    
    
  
    
    public List<Map<String, Object>> findByNumber(String telefon){
        DbConfig dbc = new DbConfig();
        
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dbc.dataSourceInregIvg());
    List<Map<String, Object>> info1 = jdbcTemplate.queryForList("select cif, den_client from b101_conturi where telefon=?", telefon);
   
   return info1;
    
    }
    
    
    
    public Map<String, Object> findByNumber2(String telefon) {
        
        DbConfig dbc = new DbConfig();
        
        String query2 = "select cnp, nume, prenume from utilizatori where telefon=?";
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dbc.dataSourceMail());
        Map<String, Object> info = jdbcTemplate.queryForMap(query2,telefon);
        
        return info;
                
    } }

Service:

    @Service
public class TelefonMailService {
    
@Autowired    
private TelefonMailRepository repository;



public List<Map<String, Object>> getRaspuns(String telefon) {
   
    
    List<Map<String, Object>> mapList = new ArrayList<>();

    
    
mapList = repository.findByNumber(telefon);

    return mapList;
 
}

public Raspuns getRaspunsB(String telefon) {
    
    Map<String, Object> map = repository.findByNumber2(telefon);
    
    String cnp = (String)map.get("cnp").toString();
    String nume = (String)map.get("nume");
    String prenume = (String)map.get("prenume");
    
    Raspuns raspuns = new Raspuns();
    raspuns.setCnp(cnp);
    raspuns.setNume(nume);
    raspuns.setPrenume(prenume);
    
    return raspuns;
    
} }

Controller:

@PostMapping("/raspuns")
public String postDbRequest(@RequestParam("text2")String telefon, Model model) {

    Raspuns x = service.getRaspunsB(telefon);
    model.addAttribute("raspuns", x);
 
  
   List<Map<String, Object>> mapList = service.getRaspuns(telefon);
    model.addAttribute("map_list",mapList);
 
  
    
   
    return "raspuns";
}}

Data class:

    import lombok.Data;


@Data
public class Raspuns {
    
 private String cnp;
   
 private String nume;
   
 private String prenume;
 
 private String cif;
 
 private String den_client;
    
}

Thymeleaf index.html class:

    <!DOCTYPE html>
<html xmlns:th ="http://www.thymeleaf.org" >
<head>
<meta charset ="UTF-8" ></meta>
<meta name ="viewport" content ="width=device-width, initial-scale=1, shrink-to-fit=no" >
<!-- Read CSS -->
<link rel ="stylesheet" th:href ="@{/webjars/bootstrap/css/bootstrap.min.css}" >
<link rel ="stylesheet" th:href ="@{/stil.css}" >
<!-- Read JS -->
<script th:src ="@{/webjars/jquery/jquery.min.js}" defer ></script>
<script th:src ="@{/webjars/bootstrap/js/bootstrap.min.js}" defer ></script>
<title> Cautare telefon sau e-mail</title>
</head>
<body class ="bg-light" >
    <h2 text-align="center">Cautare telefon sau email</h2>
<div class ="text-center" >
<form method ="post" action ="/raspuns" >
<div >
    <input type ="number" minlength="10" maxlength="10"  name ="text2" th:value ="${text2_value}" placeholder="Numar telefon"/>
    
    <input type ="submit" value ="Cauta" class ="btn btn-primary" margin-top="2%"/>
</div>
    </form>
    <br></br>
<form method ="post" action ="/raspuns2" >
    <div class ="form-group" >
<input type ="email" class ="form-control" placeholder ="Adresa email" th:value="${email_value}"
name ="email" />

<input type ="submit" value ="Cauta" class ="btn btn-primary" margin-top="2%"/>
</div>
    
</form>

</div>
   
</body>
</html>

Thymleaf html response and display answer class:

    <!DOCTYPE html>
<html xmlns:th ="http://www.thymeleaf.org" >
<head>
<meta charset ="UTF-8" ></meta>
<meta name ="viewport" content ="width=device-width, initial-scale=1, shrink-to-fit=no" >
<!-- Read CSS -->
<link rel ="stylesheet" th:href ="@{/webjars/bootstrap/css/bootstrap.min.css}" >
<link rel ="stylesheet" th:href ="@{/stil.css}" >
<!-- Read JS -->
<script th:src ="@{/webjars/jquery/jquery.min.js}" defer ></script>
<script th:src ="@{/webjars/bootstrap/js/bootstrap.min.js}" defer ></script>
<title> Cautare telefon sau email</title>
</head>
<body>
    <form  action="/">
    <button class ="btn btn-primary">Acasa</button>    
    </form>
<h1> Cautare telefon</h1>

<table >
    
  <tbody>
    <tr th:each="map : ${map_list}" >
<td> CNP:</td>
<td th:text ="${map.get('cif')}" ></td>
<td> Nume:</td>
<td th:text ="${map.get('den_client')}" ></td>
</tr>




  </tbody>
</table>
<table>
<tr>
<td> CNP:</td>
<td th:text ="${raspuns.cnp}" ></td>
</tr>
<tr>
<td> Nume:</td>
<td th:text ="${raspuns.nume}" ></td>
</tr>
<tr>
<td> Prenume:</td>
<td th:text ="${raspuns.prenume}" ></td>
</tr>
</table>

</body>
</html>

Yesterday I interogated a phone number that exists in database A and I received answer and when I interogated database B it gave me no results. Today I did a build and clean and when I interogated database B it gave me a response but from database A did not give me an answer it gave me no results. I do not understand how and why it does not connect to both databases at once. Thanks

CodePudding user response：

Create a JdbcTemplate for each Datasource in your config class. Then, inject those JdbcTemplate beans into the appropriate Repository class.

You should not be instantiating classes that are managed by Spring, except in config classes.

CodePudding user response：

You have to inject datasource to Repository class. If you call db.getDatasource() then each call creates datasource.

class Repository{

   private final Datasource dtSource;
   private final JdbcTemplate jdbcTemplate;
   public Repository(@Qualifier("mydatasource") Datasource dtSource){
      This.jdbcTemplate = new JdbcTemplate(dtSource);
   }
}

Simply you can inject beans with @Bean annotated method. It creates bean in spring context