Spring boot jpa accessing secondary datasource via entity manager

Question

I have 2 datasources. The primary DB is well-written, so I use it with JPA for multiple query. Instead the secondary datasource use a really ugly database, but I need to do only one big query (and no other operations). Following this link I was able to set the secondary datasource (on weblogic), so now my goal is call a native query on secondary datasource.

Here my code:

application.properties

spring.datasource.jiano.jndi-name=jdbc/JianoDS
spring.datasource.jiano.driver-class-oracle.jdbc.driver.OracleDriver
spring.datasource.jiano.hikari.connection-timeout=60000
spring.datasource.jiano.hikari.maximum-pool-size=5

spring.datasource.sgu.jndi-name=jdbc/sguDatasource
spring.datasource.sgu.driver-class-oracle.jdbc.driver.OracleDriver
spring.datasource.sgu.hikari.connection-timeout=60000
spring.datasource.sgu.hikari.maximum-pool-size=5    

Spring boot main:

@ComponentScan   
@SpringBootApplication
public class BemonitorcaaApplication extends SpringBootServletInitializer implements WebApplicationInitializer {

    public static void main(String[] args) {
        SpringApplication.run(BemonitorcaaApplication.class, args);
    }

    @Override
    protected SpringApplicationBuilder configure(SpringApplicationBuilder builder) {
        return builder.sources(BemonitorcaaApplication.class);
    }
}   

I added the class below for handling multiple datasource:

@Configuration
public class DatasourceConfig {
    @Value("${spring.datasource.jiano.jndi-name}")
    private String primaryJndiName;

    @Value("${spring.datasource.sgu.jndi-name}")
    private String secondaryJndiName;

    private JndiDataSourceLookup lookup = new JndiDataSourceLookup();

    @Primary
    @Bean(destroyMethod = "") // destroy method is disabled for Weblogic update app ability
    public DataSource primaryDs() {
        return lookup.getDataSource(primaryJndiName);
    }

    @Bean(name = "sguDs", destroyMethod = "") // destroy method is disabled for Weblogic update app ability
    public DataSource secondaryDs() {
        return lookup.getDataSource(secondaryJndiName);
    }           
} 

The controller:

    @RestController
    @RequestMapping("/test")
    public class IapaController {
    
        @Autowired
        IapaService iapaService;
        
        @PersistenceContext
        EntityManager em;
    
    
        @RequestMapping("/v1/the-only-query-on-2nd-datasource")
        public List<String> test2ndDS() {
            List<String> itemList = em.createQuery("Select a.text ......." )
                    .getResultList();   //Not working   
            return itemList ;
        }
    
        @RequestMapping("/v1/primary-ds-is-working-fine")
        public List<IapaTipiAndamenti> test1stDS() {
            return iapaService.test1stDS(); //This is working, here for example I will use a typical jpa findAll
        }

//...other jpa methods for the primary datasource
    }

The entity manager is not working, I tried to add the entity manager configuration inside DatasourceConfig but it doesn't work. (For example I don't have a package to scan, because I do only a native query on the secondary datasource, that return a primitive type, so no domain or repository classes.)

How can I fix the entity manager? (I'm using Spring boot 1.5.17.RELEASE)

Answer 1

Hello you can use a simple JdbcTemplate object with your second datasource like

@Configuration
public class DatasourceConfig {
    @Value("${spring.datasource.jiano.jndi-name}")
    private String primaryJndiName;

    @Value("${spring.datasource.sgu.jndi-name}")
    private String secondaryJndiName;

    private JndiDataSourceLookup lookup = new JndiDataSourceLookup();

    @Primary
    @Bean(destroyMethod = "") // destroy method is disabled for Weblogic update app ability
    public DataSource primaryDs() {
        return lookup.getDataSource(primaryJndiName);
    }

    @Bean(name = "sguDs", destroyMethod = "") // destroy method is disabled for Weblogic update app ability
    public DataSource secondaryDs() {
        return lookup.getDataSource(secondaryJndiName);
    }    

    @Bean
    public JdbcTemplate jdbcTemplate(){
      return new JdbcTemplate(secondaryDs());
    }
}

And then in your controller try:

@RestController
@RequestMapping("/test")
public class IapaController {

    @Autowired
    IapaService iapaService;

    @Autowired
    JdbcTemplate jdbcTemplate;

    @RequestMapping("/v1/the-only-query-on-2nd-datasource")
    public List<String> test2ndDS() {
        String query = "Select * ....";
        List<String> itemList = (List<String>) jdbcTemplate.queryForList(query, String.class);   
        return itemList ;
    }

    @RequestMapping("/v1/primary-ds-is-working-fine")
    public List<IapaTipiAndamenti> test1stDS() {
        return iapaService.test1stDS(); //This is working, here for example I will use a typical jpa findAll
    }

//...other jpa methods for the primary datasource
    }