This discussion is archived
3 Replies Latest reply: May 20, 2011 5:17 AM by r035198x RSS

JPA: how to organize multiple databases having the same schema definition?

836522 Newbie
Currently Being Moderated
Hi there,

I'm new to Java EE but I could finish some test projects on GlassFish 3 / Java EE 6 platform.

Now it's time to build the actual system and I need some help in that.

So, I can't really tell you the exact purpose of this system but let's look at the following example which is very close to what I need.

Say there is a database schema definition (DDL) for Oracle 11g and this schema is storing the inventory of a shop. So tables like product, category, price etc
Say there are 50 shops, and I'm getting a new database for each shop every month holding their inventory.

So every month I'm receiving a SQL file with INSERTs (DML) for every shop for the DDL I mentioned above.
I'm getting 50 SQL files for the 50 shops every month, so in a year you're talking about 600 SQL files to import.

If it was php/MySQL I could import them into a separate database each, call it shop1_May11, shop2_May11 etc reflecting the shop name and the month in the database name.

This is my question how you guys think I should achieve this in Jave EE / Oracle.

As the DDL is the same I thought to create an Entity for each table and use the Java Persistance API to create the databases and then I'd import the month/shop specific SQL files.

But this means I'm gonna create 50 new schemas in Oracle every month?

Or what's the best way to achieve this?

Thanks in advance

Edited by: 833519 on May 18, 2011 5:28 AM

Edited by: 833519 on May 18, 2011 5:29 AM

Edited by: 833519 on May 18, 2011 5:31 AM
  • 1. Re: JPA: how to organize multiple databases having the same schema definition?
    r035198x Pro
    Currently Being Moderated
    I'm not sure JPA is best for this because it looks like you receive a bunch sql statements that you simply need to run against some schema.
    Are you creating a backup database for all these shops or is it some kind of a replication strategy?
    Will the target database be queried at some point for the data?
  • 2. Re: JPA: how to organize multiple databases having the same schema definition?
    836522 Newbie
    Currently Being Moderated
    Hi,

    yes, we have to use these databases for reports, also, I'd like to be able to generate reports like 'How many sales in May?', so it'll run a JOIN query on every shop*_May11 databases.

    So you're saying JPA is best for databases that you don't create ad-hoc?

    I simply create the databases for each shop/month and I'll just use normal SQL queries if I need any data?
  • 3. Re: JPA: how to organize multiple databases having the same schema definition?
    r035198x Pro
    Currently Being Moderated
    JPA helps a lot in persisting and retrieving data that has relationships without having to worry about the relationships at a sQL level.
    So you get a java programmer that builds relationships using Java classes (the entities) and a persistence provider worries about the sql required to materialize those relationships.
    You seem to be using SQL inserts for persisting the data and you want to generate the reports from SQL as well.
    You can use it but you won't be gaining much of its advantages for this project.
    Consider reporting applications like the jasperserver for the reports.
    Also, I have seen people get away with using one database for this type of thing and just adding a clientid (and optionally branchid) to each table.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points