2 Replies Latest reply on Apr 14, 2015 2:52 PM by Steven Hawkins

    Is TEIID the right tool for us?

    Herbert Wirnsberger Newbie

      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!

        • 1. Re: Is TEIID the right tool for us?
          Steven Hawkins Master

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

           

          So the flow here is that you are getting the correct Vorlage entity from the desired source, but then Hibernate with the lazy load will get the Anfragearten based only upon the AAR_ID, which exists in several of the source instances.

           

          There is a behavior of multisource which assumes that any pushdown of a join should be considered partitioned - see System Properties - Teiid 8.10 (draft) - Project Documentation Editor org.teiid.implicitMultiSourceJoin

           

          So if you use an eager load, then you should get just the Anfragearten for that source.  However if the join is not pushed down, then you'd still not get the appropriate results.  Unfortunately that is legacy behavior that we'll need to address.

           

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

           

          As long as you are open to a small change in your domain model, there are ways in JPA/Hibernate to use a composite key.  Having the explicit multisource column in the key should always give you the results you want.

           

          > 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?

           

          It should generally be easier to have a single logical DB if you are predominately querying across them all.

           

          > Has anybody used Teiid like this?

           

          It has come up and we've had several talks with the hibernate folks on seeing what we can do to provide an alternative to the now defunct hibernate shards.

          • 2. Re: Is TEIID the right tool for us?
            Steven Hawkins Master

            This is a little delayed, but newer versions of Hibernate have database multi-tenancy Chapter 16. Multi-tenancy