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)

Accollativo · Accepted Answer · 2018-12-18 10:03:31Z

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
    }

edited Dec 18, 2018 at 10:03

Accollativo

1,5794 gold badges35 silver badges59 bronze badges

answered Dec 17, 2018 at 15:57

Herve MULUDIKI

697 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Accollativo Over a year ago

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}

Herve MULUDIKI Over a year ago

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.

Accollativo Over a year ago

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?

Herve MULUDIKI Over a year ago

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.

Accollativo Over a year ago

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.

Collectives™ on Stack Overflow

Spring boot jpa accessing secondary datasource via entity manager

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related