Spring Boot JPA Custom Datasource for Native Query

Question

I am using two different databases in my Spring Boot Application , i have defined two different configurations for each database:

DB1

@Configuration
@EnableJpaRepositories(basePackages = {
        "com.company.bod.repositries.db1" }, entityManagerFactoryRef = "db1EntityManager", transactionManagerRef = "db1TransactionManager")
public class db1DBDatasource {

    @Autowired
    private Environment env;
    
    @Bean
    public LocalContainerEntityManagerFactoryBean db1EntityManager() {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(db1Datasource());
        em.setPackagesToScan(new String[]{"com.company.bod.entities.db1"});
        em.setPersistenceUnitName("db1EntityManager");
        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);
        HashMap<String, Object> properties = new HashMap<String, Object>();
        properties.put("hibernate.dialect",env.getProperty("hibernate.dialect"));
        properties.put("hibernate.show-sql",env.getProperty("jdbc.show-sql"));
        em.setJpaPropertyMap(properties);
        return em;
    }

    @Bean
    public DataSource db1Datasource() {

        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(env.getProperty("spring.db1-datasource.driver-class-name"));
        dataSource.setUrl(env.getProperty("spring.db1-datasource.jdbc-url"));
        dataSource.setUsername(env.getProperty("spring.db1-datasource.username"));
        dataSource.setPassword(env.getProperty("spring.db1-datasource.password"));

        return dataSource;
    }
    

    @Bean
    public PlatformTransactionManager db1TransactionManager() {
        JpaTransactionManager transactionManager= new JpaTransactionManager();
        transactionManager.setEntityManagerFactory( db1EntityManager().getObject());
        return transactionManager;
    }

}

DB2

package com.company.bod.config.db;

import java.util.HashMap;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;

@Configuration
@EnableJpaRepositories(basePackages = {
        "com.company.bod.repositries.bodtracker" }, entityManagerFactoryRef = "db2EntityManager", transactionManagerRef = "db2TransactionManager")
public class db2DataSource {

    @Autowired
    private Environment env;
    
    @Primary
    @Bean
    public LocalContainerEntityManagerFactoryBean db2EntityManager() {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(db2Datasource());
        em.setPackagesToScan(new String[]{"com.company.bod.entities.bodtracker"});
        em.setPersistenceUnitName("db2EntityManager");
        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);
        HashMap<String, Object> properties = new HashMap<String, Object>();
        // HashMap<string, object=""> properties = new HashMap<>();
        properties.put("hibernate.dialect",env.getProperty("hibernate.dialect"));
        properties.put("hibernate.show-sql",env.getProperty("jdbc.show-sql"));
        em.setJpaPropertyMap(properties);
        return em;
    }

    @Primary
    @Bean
    public DataSource db2Datasource() {

        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(env.getProperty("spring.second-datasource.driver-class-name"));
        dataSource.setUrl(env.getProperty("spring.second-datasource.jdbc-url"));
        dataSource.setUsername(env.getProperty("spring.second-datasource.username"));
        dataSource.setPassword(env.getProperty("spring.second-datasource.password"));

        return dataSource;
    }
    
    @Primary
    @Bean
    public PlatformTransactionManager db2TransactionManager() {
        JpaTransactionManager transactionManager= new JpaTransactionManager();
        transactionManager.setEntityManagerFactory( db2EntityManager().getObject());
        return transactionManager;
    }

}

Now i have created a new Custom Service under the package com.company.bod.repositries.db1

package com.company.bod.repositries.db1;

import java.util.List;

import com.company.bod.entities.crm.CRMAttributes;

public interface NativeRepositryCustom {
    
    public List<CRMAttributes> getAttributesbyId(String id, List<String> names );
    
    
}

Impl Class

package com.company.bod.repositries.db1;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import org.springframework.stereotype.Repository;
import com.company.bod.entities.crm.CRMAttributes;
@Repository
public class CustomNativeRepositoryImpl implements NativeRepositryCustom{
    @PersistenceContext
    private EntityManager entityManager; 
    
    @Override
    public List<CRMAttributes> getAttributesbyFullfillOrderId(String fullfillorderid, List<String> names ) {
        String q = "SELECT " + 
                "  name, `value` " + 
                "FROM crmord.tbl_fulfill_order_prop " + 
                "where name IN (:names)  " + 
                "AND fulfill_order_id=:orderid";
        
        Query query = entityManager.createNativeQuery(q);
        query.setParameter("names", names);
        query.setParameter("orderid", fullfillorderid);
        List<CRMAttributes> list = query.getResultList();
        return list;
    }
    
    
}

but when i try to use CustomNativeRepositoryImpl application complaints about the table found. I am assuming this is due to db1 not being primary . How can i tell CustomNativeRepositoryImpl to use db1 datasource.

I have tried below but it didnt work:

package com.company.bod.repositries.db1;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Repository;

import com.company.bod.entities.crm.CRMAttributes;

@Repository
public class CustomNativeRepositoryImpl implements NativeRepositryCustom{

    @PersistenceContext
    private EntityManager entityManager; 
    
    @Qualifier("CrmDBDatasource")
    DataSource dataSource;
    
    @Override
    public List<CRMAttributes> getAttributesbyFullfillOrderId(String fullfillorderid, List<String> names ) {
        String q = "SELECT " + 
                "  name, `value` " + 
                "FROM crmord.tbl_fulfill_order_prop " + 
                "where name IN (:names)  " + 
                "AND fulfill_order_id=:orderid";
        
        Query query = entityManager.createNativeQuery(q);
        query.setParameter("names", names);
        query.setParameter("orderid", fullfillorderid);
        List<CRMAttributes> list = query.getResultList();
        return list;
    }

    
    
}

question from:https://stackoverflow.com/questions/65650135/spring-boot-jpa-custom-datasource-for-native-query

Answer 1

I went on to create an Entity with only required columns for the tbl_fulfill_order_prop and then interface using JPA Repository .