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)

score 1 · Accepted Answer · edited Dec 18 '18 at 10:03

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
    }

I've no persistence.xml, so I get this error: `Error creating bean with name 'entityManagerFactorySecondaryDs' defined in class path resource [it/mycompany/DatasourceConfig.class]: Invocation of init method failed; nested exception is java.lang.IllegalStateException: No persistence units parsed from {classpath*:META-INF/persistence.xml}` — Accollativo, Dec 18 '18 at 08:13
hello i've updated my example with a sample of EntityManagerFactory configuration. i really hope it will help you. Keep us update if it was worthy to u. — Herve MULUDIKI, Dec 18 '18 at 08:26
I really need ENTITYMANAGER_PACKAGES_TO_SCAN? Like I said in the answer I've no package to scan because I haven't domain classes. And from when do I get all the others properties name? — Accollativo, Dec 18 '18 at 08:45
Sorry i was focused to use an entityManager because you used it in your request. But what you need is to use JdbcTemplate. this object will be perfect for what you want to achieve. I updated my answer to show you how you could use it. — Herve MULUDIKI, Dec 18 '18 at 09:11
I thought that using JPA I could handle an entity manager as jdbcTemplate. But I suppose you're right, I don't really need an entity manager if I have no package to scan for domain classes. — Accollativo, Dec 18 '18 at 10:00

Spring boot jpa accessing secondary datasource via entity manager

1 Answers1