4 Replies Latest reply: Jul 24, 2012 8:46 PM by 714026 RSS

    Ejb 2.0 to support multi tenancy db schema?

    714026
      Hi,

      i got an assignment to working with ejb2.0 to support multi tenancy db schema which mean, user may define the schema name before working on insert/update/delete.

      i wonder can ejb2.0 working on this cause its was like N years technology.

      anyone got some usefull information please share me (link as well). thanks all pros

      Regards

      Edited by: user8668224 on 22-Jul-2012 20:07
        • 1. Re: Ejb 2.0 to support multi tenancy db schema?
          ramp
          What kind of ejbs do you use? Entity beans - CMP / BMP? Which db are you working with?
          Do you append schema name to your db objects? Why?
          • 2. Re: Ejb 2.0 to support multi tenancy db schema?
            714026
            Hi ramp

            im using entity beans - CMP.

            the case scenario is, the software first design to be 1 db, 1 client, schema name is used when i create the ejb.

            now management wanted to be 1 db, multiple client with different schema, example

            global schema DBHOS will store the login user, and some common use table

            HospitalA(client) will use db schema DBHosA
            HospitalB(client) will use db schema DBHosB

            both schema will share the exactly table and transaction will be store base on the login user belonging. UserA login to application will be storing the schema name and connection string that connect to DBHosA

            now the problem is the Ejb that created is predefined in the XML while creating ejb

            example

            in orion-ejb-jar.xml stated as below
            <entity-deployment name="Gnsicodeline" data-source="jdbc/FisicienDS" table="MMS.GNSICODELINE">


            the `table=MMS.GNSICODELINE' , MMS was the schema and now i wanted it to be dynamically change base on the login user.

            is there anyway to do that?

            Million thanks in advance.
            • 3. Re: Ejb 2.0 to support multi tenancy db schema?
              ramp
              >
              in orion-ejb-jar.xml stated as below
              <entity-deployment name="Gnsicodeline" data-source="jdbc/FisicienDS" table="MMS.GNSICODELINE">


              the `table=MMS.GNSICODELINE' , MMS was the schema and now i wanted it to be dynamically change base on the login user.

              is there anyway to do that?
              Hmmmm, I am not sure if that's possible. There's a concept called dynamic queries where you can construct EJB-QL queries dynamically in your application code - so that may be worth investigating. I have never had to use it before.

              Other than that, the only way I can think of is to deploy two ejbs, one for each schema. All the artifacts except the ejb-jar.xml and your orion-ejb-jar.xml will be shared between the two. Look up the correct ejb based on the logged in user.
              • 4. Re: Ejb 2.0 to support multi tenancy db schema?
                714026
                Hi ramp,

                i cant change much on the code cause the software will be launch soon. creating ejb2.0 in jdev10 is way nightmare for all developer i guarantee.

                multiple ejb will only fit for static client site, but the problem is, there is more and more client coming in. :D

                Regards
                Brandon