Hello!

We ask for your opinion if our approach is a good one and if TEIID is the right tool to choose.

We just started implementing and using TEIID for our application.

We have an existing system with 9 identical oracle databases (with different data) for 9 customers

You may ask, why not use 1 database. Well thats how it is :-D and we can’t change it right now.

We want to use a single JEE-Application that accesses the 9 databases. If the client is from customer-1 he will only access database-1. Same with the others.

Our idea was to use a multisource  with customerId as SOURCE_NAME.

Our queries would look like that:

SELECT * FROM Orders WHERE customerId=1

Then the system should fetch orders from database-1

In our new application we want to use hibernate and the application is deployed on JBoss EAP 6.2.

We installed TEIID 8.9.1 on top of JBoss and defined the datasources as multisource.

For simple select, insert, delete and update statements it works perfectly fine. We just add the SOURCE_NAME and get the dates from the desired datasource.

Now we have a problem when we try something like that:

@Entity

public class Vorlagen{

    @Id

    private long id;

    @OneToOne(fetch = FetchType.LAZY)

    @JoinColumn(“AAR_ID”)

    private Anfragearten anfragearten;

    private String SOURCE_NAME; (added with addColumn in vdb, not in Database)

}

@Entity

public class Anfragearten{

    @Id

    private long id;

    private String text;

    private String SOURCE_NAME; (added with addColumn in vdb, not in Database)

}

Now to get “text” in Anfragearten:

Session session = (Session) em.getDelegate();

Criteria criteria = session.createCriteria(Vorlagen.class);

criteria.add(Restrictions.eq(“id”, 2));

criteria.add(Restrictions.eq(“SOURCE_NAME”,”1”));

List <Vorlagen> vorlagen = criteria.list();

Vorlage vorlage = vorlagen.get(0);

Anfragearten anfrageart = Vorlage.getAnfragearten();

// Until now everything is fine.

anfrageart.getText();

Now we get an Exception which tells us that more than one row with the given identifier 5 was found.

It seems that all datasources are accessed and we get more than one Anfragearten with id 5.

Since the join is only for @JoinColumn(“AAR_ID”), this makes sense. We would have to include the SOURCE_NAME somehow in our join.

Is it possible to use Hibernate and Teiid in that way?

Even if it is possible, do you think it is a good idea or is it better to use a more conservative approach like defining 9 EntityManagers, one for each database?

Has anybody used Teiid like this?

Thanks!