Accessing tables with same name in different DBMS schemas


EJB design: Accessing tables with same name in different DBMS schemas

  1. I have an application in which the users access the same data, but in different DBMS schemas according to which company they work for.

    I.e. user John works for ACME, so he will find his customer data in the table ACME.CUSTOMER.

    User Mary works for ABC and will find her customer data in the table ABC.CUSTOMER.

    The tables are identical, each time we get a new client we copy an existing empty schema into the new schema belonging to the new customer.

    How can I utilise EJB to access the data, while at the same time not duplicating the EJBs? I.e. I would like to have only 1 CustomerBean, CustomerHome and Customer class to handle all the various dbms schemas.

    I have come up with a solution of adding the schema name to the primary key, in addition to using BMPs in which I dynamically create the correct SQL:

    Here is an excerpt from the BMP code:
        String sql = "SELECT * FROM " + primarykey.schema
                      + ".TABLENAME WHERE primkey = ? ";

    A typical use from a client like for instance JSP would be:

        Customer cust = custHome.findByPrimaryKey( new CustomerPK(schema, customerId));

    Does anyone have a better solution? If not, how can I automate the process of generating the 200+ beans?

    I have seen several tools, but none which will let me change the schema name of the table on the fly.

    I guess we might have to write the generator ourselves, but would like to avoid it.

    Do you have any ideas?


  2. Steinar we had a similar issue in our design, what we did was to add an additional field Company in CUSTOMER tables which identifies the customer. This save the effort of handling multimode EJB's and also make the system generic.

    I understand you may be having some constrain under which you want to use different schema name, but still check this option again.

  3. Yes, that is how I would have designed it in the first place, but the database has existed for quite a few years and there are several legacy applications accessing the tables, so changing the tables is not an option :-)

    After looking around most of this summer, I have decided to implement a BMP generator in which I basically extend the primary key class with the "schema". Thus enabling me to go like this:

       findByPrimaryKey(new CustomerPK(schema, customerId));

    The schema is identified when the users log in to our application.

    I have tested it, and it is actually quite fast, only thing is that I have to spend a week writing the BMP generation code, which is not so bad :-)
  4. Hi,

    I think my solution would be to have deployed two instances of the Customer EJB. Each one would be pointing at a different scheme and registered in the JNDI tree with a appropriately different name.

    Therefore the only difference in my code would be when I accessed the EJB Home off the JNDI tree.

    CustomerHomeLocal home = null;
    Context ctx = new InitialContext();
    if (company.equal("ACME")
        home = (CompanyHomeLocal)ctx.lookup("ACMECustomer");
        home = (CompanyHomeLocal)ctx.lookup("ABCCustomer");

    Although this makes the look up code more complex you could encapsulate it in a ServiceLocator.

  5. Hmmm, several instances of the EJB, this sounds interesting, how would I go about this?

    I've got approx. 10 different schemas with approx. 200 tables in each.

    How can I automate this? Will this not imply a heavy burden on the deployer?