Topic
6 replies Latest Post - ‏2010-06-18T18:19:03Z by OvidiuMarcu
nongentesimus
nongentesimus
5 Posts
ACCEPTED ANSWER

Pinned topic can I specify db schema name dynamically?

‏2010-06-09T11:40:38Z |
Hi!

I am using JPA with OpenJPA implementaton for an EJB3 application. The application is deployed on Websphere 7. The underlying database is an old AS400 DB2 database. In this database test system and production system are separated by a schema name (so the test system and the production system are on the same machine with the same database name, only their schema name is different).
My problem is that I could not find any way to specify a schema name at the data source level. I can specify the schema name in my entity classes using the schema attribute of the @Table annotation, but this attribute value is of course a compile-time constant (like every annotation attribute value in Java), so practically I should rewrite the source code when I deploy to test/prod systems. Or I can specify a default schema in the orm.xml, but that is still part of the application so this too can only be changed at build-time.
My actual solution is to specify the schema name at build-time but this is not very elegant, because it means that it is not the same application which is deployed on the test system and on the production system.
Is there any way to specify schema name dynamically on the datasource level?

Thanks

nongentesimus
Updated on 2010-06-18T18:19:03Z at 2010-06-18T18:19:03Z by OvidiuMarcu
  • SystemAdmin
    SystemAdmin
    45 Posts
    ACCEPTED ANSWER

    Re: can I specify db schema name dynamically?

    ‏2010-06-10T05:21:48Z  in response to nongentesimus
    You can create EntityManagerFactory using the constructor that takes the persistence unit name and properties maps as agrument. Define the correct schema name in the property map based on test and production.
    • nongentesimus
      nongentesimus
      5 Posts
      ACCEPTED ANSWER

      Re: can I specify db schema name dynamically?

      ‏2010-06-10T10:08:58Z  in response to SystemAdmin
      Hello!

      Thanks a lot for your answer. Well that is surely possible, but at the moment I use resource injection to create entity manager instances, and I find it very comfortable. I was hoping there is some hidden parameter somewhere in Websphere which can control the schema name... (there was/is something similar for CMP beans...)

      nongentesimus
    • nongentesimus
      nongentesimus
      5 Posts
      ACCEPTED ANSWER

      Re: can I specify db schema name dynamically?

      ‏2010-06-11T09:50:14Z  in response to SystemAdmin
      Actually I cannot even find what property should belong to the schema name. I tried "openjpa.jdbc.Schema" and "Jdbc.Schema" but non of them worked. It seems to me that the entityManagerFactory tries to find a getter and a setter in the entityManager class with the property name (which is in the map), but in the OpenJPA EntityManagerImpl class I cannot find any getter/setter that would access/modify schema name. Which property name did you think about?

      Thanks

      nongentesimus
      • nongentesimus
        nongentesimus
        5 Posts
        ACCEPTED ANSWER

        Re: can I specify db schema name dynamically?

        ‏2010-06-11T11:14:32Z  in response to nongentesimus
        Sorry, I misunderstood what you suggested. So you suggest to create the EntityManagerFactory (and not the EntityManager) with additional porperties.
        Something like this:

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("persistenceUnitName", map);
        EntityManager em = emf.createEntityManager();

        and this map should contain the schema name. But I still cannot find out what property name I should use for schema name. "openjpa.jdbc.Schema" or "schema" do not work for me.

        Thanks

        nongentesimus
        • nongentesimus
          nongentesimus
          5 Posts
          ACCEPTED ANSWER

          Re: can I specify db schema name dynamically?

          ‏2010-06-11T13:57:25Z  in response to nongentesimus
          For others who read this topic.
          Finally I found a solution.
          Actually there is a proeperty in Websphere on the datasource level, that you can set and which specifies the default schema. Your datasource helper class should be DB2AS400DataStoreHelper and than under custom properties there is a property called libraries. If you specify here onesString (without commas or spaces) that will be the default schema for your entity classes. Of course, you also have to ommit the schema definition from your @Table annotations.

          cheers

          nongentesimus
          • OvidiuMarcu
            OvidiuMarcu
            1 Post
            ACCEPTED ANSWER

            Re: can I specify db schema name dynamically?

            ‏2010-06-18T18:19:03Z  in response to nongentesimus
            Is this possible for WebSphere 6.1 version ?

            Can you be more precise on how I can define this parameter onesString as default schema ?

            Tks.