Pitchou Pitchou - 1 year ago 159
Java Question

How to use the same entities to connect on 3 databases with spring boot?

I am facing a problem since last week and I have been searching everywhere without finding a solution... Am I blind ?

I am currently developping an Java Web Application using Spring Boot. I have 3 Oracle databases that have the same structure but not the same data because each database work in a specific environment.

I found many tips on "how to use multiple datasources" but all of them talk about different datasources that have specific entities ("user" and "order" for example). So they do not solve my problem.

Becauses my 3 databases have the same structure, I want to be able to use my entities and search for results on all my DBs.

So I tried to configure the app like this :

public class OracleConfiguration {

public DataSource recetteDataSource() {
return DataSourceBuilder.create().build();

public DataSource homologationDataSource() {
return DataSourceBuilder.create().build();

public DataSource productionDataSource() {
return DataSourceBuilder.create().build();

And put the config param in my

# Hibernate configuration
database-platform: org.hibernate.dialect.Oracle10gDialect
# Oracle configuration for "recette" database
url: "myURL_recette"
username: "userR"
password: "pwdR"
driver-class-name: "oracle.jdbc.OracleDriver"

# Oracle configuration for "homologation" database
url: "myURL_hom"
username: "userH"
password: "pwdH"
driver-class-name: "oracle.jdbc.OracleDriver"

# Oracle configuration for "production" database
url: "myURL_Prod"
username: "userP"
password: "pwdP"
driver-class-name: "oracle.jdbc.OracleDriver"

With this, I am able to access to the first registered database but not the 2 other.

Does someone have a solution or any idea about how to do this ?
If I am not clear enough, I will rephrase or add details.

EDIT : Added the main class where Spring autowire beans :

public class App {
public static void main(String[] args) {
System.out.println("My App ...");
SpringApplication.run(App.class, args);

EDIT : Added example of an entity :

@Table(name = "UPSTREAM")
public class Upstream implements Serializable {
private int numins;
private String name;

@Column(name = "NUMINS", nullable = false, precision = 0)
public int getNumins() {
return numins;

public void setNumins(int numins) {
this.numins = numins;

@Column(name = "NAME", nullable = false, length = 150)
public String getName() {
return nomfic;

public void setName(String name) {
this.name = name;

Repository :

public interface UpstreamRepository extends CrudRepository<Upstream, Long> {

Service :

@Service(value = "upstreamService")
public class UpstreamServiceImpl implements UpstreamService {

private EntityManager em;

private UpstreamRepository upstreamRepository;

public Upstream findOne(final String filename) {
final CriteriaBuilder cb = em.getCriteriaBuilder();
final CriteriaQuery<Upstream> cq = cb.createQuery(Upstream.class);
final Root<Upstream> root = cq.from(Upstream.class);
final EntityType<Upstream> entityType = root.getModel();

Predicate where = cb.conjunction();

if (filename != null && !filename.isEmpty()) {
// Add predicate for "name"
final Predicate namePredicate = root.get(entityType.getSingularAttribute("name")).in(filename);
where = cb.and(where, namePredicate);

// Query creation
final TypedQuery<Upstream> q = em.createQuery(cq);

return q.getSingleResult();

public UpstreamRepository getUpstreamRepository() {
return upstreamRepository;

public void setUpstreamRepository(final UpstreamRepository upstreamRepository) {
this.upstreamRepository = upstreamRepository;

Answer Source

What are you doing now:

You created 3 dataSource beans. One of them has @Primary annotation and as springBoot doc says :

if you are using the default auto-configuration for JDBC or JPA (then that one will be picked up by any @Autowired injections).

And here

private EntityManager em;

EM uses @Primary context.

How it should be:

You should create 3 custom EM's and specify dataSource manually for each of them. Each of them will have such properties:

 <property name="persistenceUnitName" value="name" />
 <property name="dataSource" ref="dataSource" />

Then you should inject EMs into your DAO class and call each of EM in a for loop.

private EntityManager entityManager;

Helpful article: http://www.codingpedia.org/ama/how-to-setup-multiple-data-sources-with-spring-and-jpa/

Creating EM via java spring config: http://www.baeldung.com/2011/12/13/the-persistence-layer-with-spring-3-1-and-jpa/

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download