Pitchou Pitchou - 16 days ago 8
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 :

@Configuration
public class OracleConfiguration {

@Bean
@Primary
@ConfigurationProperties(prefix="datasource.recette")
public DataSource recetteDataSource() {
return DataSourceBuilder.create().build();
}

@Bean
@ConfigurationProperties(prefix="datasource.homologation")
public DataSource homologationDataSource() {
return DataSourceBuilder.create().build();
}

@Bean
@ConfigurationProperties(prefix="datasource.production")
public DataSource productionDataSource() {
return DataSourceBuilder.create().build();
}
}


And put the config param in my
application.yml
:

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

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

# Oracle configuration for "production" database
production:
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 :

@SpringBootApplication
@EnableAutoConfiguration
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 :

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

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

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

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

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


Repository :

@Transactional
public interface UpstreamRepository extends CrudRepository<Upstream, Long> {
}


Service :

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

@PersistenceContext
private EntityManager em;

@Resource
private UpstreamRepository upstreamRepository;

@Override
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();

cq.select(root);
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
cq.where(where);
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

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

@PersistenceContext
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.

@PersistenceContext(unitName="name")
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/

Comments