1

I'm making a reporting dashboard which connects to multiple databases, at the moment I'm just using the java.sql library to connect and building my own connection classes.

I've also been doing the ticket monster tutorial in my spare time, though, and it uses JPA to connect to the database.

  • Is it worth the effort to map everything when I will only ever read from the DB?
  • How does one connect to multiple databases using JPA?

(I will be packaging this as a .war/.ear to run on websphere.)

ESP
  • 979
  • 2
  • 9
  • 21
  • 2
    Using an ORM is usefull even for simple SELECT. You get caching, pooling, and lots of flexibility in schema definition (adding and removing columns). – elbuild Feb 04 '14 at 10:07
  • So, would I be able to create an object which only selects certain columns from the table? As I've used it on ticketmonster, it has always been to define the table. – ESP Feb 04 '14 at 10:49
  • Yes you can, as long as the column you exclude are nullable (the SQL statement will fail otherwise). – elbuild Feb 04 '14 at 16:57

3 Answers3

2

This is what i think about the usage of JPA

Is it worth the effort to map everything when I will only ever read from the DB?

One main usage of JPA is make things more structured and easy to use state as the requirements grow. If you just need some limited number of data to be displayed, then go for the direct way, but make sure that you put an end after that. The building and maintaining JPA reduces a lot of overhead, it significantly simplify database programming. My suggestion is to go with the JPA, because its easy to create, its easy to configure and will make the code looks beautiful. So why you go for a complex way.

How does one connect to multiple databases using JPA?

You can use many annotations like one to one, one to many etc. You can look here for different fetch strategies.

Using multiple Database Server in one project.

Well let me tell you one thing first, the JPA class is an object-relational mapping facility . It has nothing to do with the Server that you are using.

You can specify the schema name using this 

@Table(name = "TABLENAME", schema="DATABASENAME")

But that wont help you switch the servers. So if you want to use multiple server then you must create multiple connections. And execute the corresponding query.

Dealing with multiple server is already explained in this link.

Community
  • 1
  • 1
Dileep
  • 5,362
  • 3
  • 22
  • 38
  • I don't need the databases to interact with each other. I thought that oneToOne and oneToMany were for joining tables? How do I create multiple connections using the xml? Cheers. – ESP Feb 04 '14 at 10:43
  • @ESP connection in the sense..?? could you elaborate your requirement ? – Dileep Feb 04 '14 at 11:20
  • I have multiple separate database servers. I need to display information from all of these databases in one page/application. How do I set up connections to multiple databases (DB2, Oracle, SQL server) and keep the connection alive for the duration of the application? – ESP Feb 04 '14 at 11:45
  • @ESP Multiple database have nothing to do with JPA. If you are using multiple Database from database server then you can point the Db using the database.table-name in the JPA.But in your case you need something to fetch data from two different URL so there you must create separate connections. Then execute query separately. – Dileep Feb 04 '14 at 18:15
  • You can, however, also add multiple persistence units in your persistence.xml and use that to access separate databases. If you have an entity 'Foo' that is part of persistence unit 'A', you won't be able to save a 'Foo' to persistence unit 'B', however. But, you can have 'Foo' from PU A and 'Bar' from PU B with no problem. – Shadowman Feb 04 '14 at 18:43
  • @Dileep Not necessarily. You can point to the same server but a different database. I do it all the time. – Shadowman Feb 05 '14 at 14:37
  • @Shadowman In this case he wants to access different database servers and i don't see much advantage in using multiple persistence unit when the data sources are the same. Although that too is an option. – Dileep Feb 05 '14 at 14:58
1

Connecting to multiple databases involves setting up multiple datasources, and you need to create multiple persistence units/EntityManagerFactory instances with matching EntityManager instances to be able to access those different datasources individually through JPA.

Gimby
  • 5,095
  • 2
  • 35
  • 47
0

That depends on your application. If you have complex object graphs, it might be worthwhile using JPA for the conversion. You can define several EntityManager objects, for each schema.

Amir Kost
  • 2,148
  • 1
  • 16
  • 30
  • As it stands, it will have a table of counts and possibly one bar chart. I suppose if I ever wanted to extend functionality then JPA would be an advantage. – ESP Feb 04 '14 at 10:48
  • 1
    Strange, I don't find SessionFactory in JPA (what the OP is asking about). Must be talking about something else – Neil Stockton Feb 04 '14 at 11:46