how to run multiple enterprise application and JPA in Netbeans 6.1

In my previous post I wrote about problem that happens when two different enterprise application (or better ejb modules) use different JPAs.

problem scope

I discovered that this problem happens when two or more JPA access to the same DBMS (I found this problem in postgres, mysql and derby)

problem description

The problem is related to the connection pool used by the persistence unit:

in both  ServerResources/sun-resources.xml  files has been wrote  the same connection pool:

<jdbc-resource enabled=”true” jndi-name=”EA1_dataSource” object-type=”user” pool-name=”derby_netPool”/>

<jdbc-resource enabled=”true” jndi-name=”dataSource_EA2″ object-type=”user” pool-name=”derby_netPool”/>

each time an enterprise application is ran he overwrites the bound database, in this way the EA launched before fails, because she tries to access to a database with another structure than the one expected.

how to solve it

the screenshot below shown the connection pool shared by the two EA

It’s easy understand that is related only to VIR database

1) I created a new JDBC connection pool (derby_netPool2) using the same general settings provided by the original one

2) In the second step I inserted the data provided into ServerResources/sun-resources.xml file of the EA related to TRAVEL database

3) I manually substitute derby_netPool2 to derby_netPool in the ServerResources/sun-resources.xml file of the EA related to TRAVEL database

4) clear and build – run EA1 – run EA2

all EAs work properly!

multiple enterprise application and JPA does not work in Netbeans 6.1

The last version of Netbeans IDE(6.0-6.1) has significant new features in
the managing of JPA and in the entity classes generation.

Unfortunately I'm very stressed by the error that I'm going to describe in
this post.

I created 2 different enterprise application with Netbeans 6.1.
Each of them has:
- an EJB container with a persistence unit, a db schema, entity classes
generated from database and a session facade bean for one Entity
- a web application with a servlet that access to the session bean and
call a findAll() method
important: each enterprise access to different databases, with different
tables. Application server is Glassfish v2 in bundle with Netbeans

If I start netbeans and I launch one of them all works fine.
The problem begin when I "run" the second one: the latter works fine(as
the first before), but now the first doesn't work.

the error is due to the fact that all the two application launches the
query on the same database(the database of the second  enterprise
application launched)!
Obviously the second app works. The first app throws an Exception due to
the fact that the related table isn't available in the database.

In my example:
EA1: search the table PERSON in database TRAVEL
EA2: search the table EMPLOYEE in database VIR
(app 2 is the last app launched)
EMPLOYEE is retrieved without any error
PERSON launch the following exception

Let me explain all the process:

build the first enterprise application
The first step is to create the first Enterprise application called EA1

and the related:
- EJB container
- WAR container
 
Then I start with the creation of the persistance layer:
before the database schema is created
 
linked to the TRAVEL database provided as example by default in netbeans6.1

The persistence unit is created subsequently (EA1PU)

with a new JNDI bound to the same database selected into database
schema (EA1_dataSource)

Entity classe of "person" table contained in the bound database
will be generated in package ea1pck

In order to make them available from the WAR application a facade
session bean will be generated from the "person" entity class

The folloving picture shows the finished EJB container


now we can complete the WAR application, building a servlet able
to call te method findAll() exposed by the facade bean previously created
the servlet has been called "NewServlet" and below there is the code
calling findAll() and writing on screen the size of the ruturned vector

the application runned returns the right value of rows in the table PERSON

it's seems correct, but now I'm going to create the second
enterprise application..

build the second enterprise application

the second application called EA2 is built in the same way of the first one,
but database schema, persistence unit, table have different names and
targets of the first ones

Here is the creation of the new database schema (DBSchemaEA2)

the target is the database VIR provided in bundle with Netbeans 6.1

Here is the creation of the PU called ejbPUEA2

and the new JNDI, tareted on the same database of the database schema


Executed but not shown, are the creation of the entity class of the
"EMPLOYEE" table and
the creation of the related session facade bean

This is the final of EJB container


In the WAR application is created a servlet executing a request to
the employee facade bean calling findAll() method


when the servlet is executed the screen output will be the number of
EMPLOYEE available in employee table in VIR database

(that's different from the 6 person returned in enterprise
application 1)

all seems running correctly but now if I call again the servlet in
enterprise application 1 it doesn't return anything


looking at the glassfish log , is displayed the following error:

Internal Exception: java.sql.SQLSyntaxErrorException: Table 'PERSON'
does not exist.
Error Code: -1
Call: SELECT PERSONID, JOBTITLE, FREQUENTFLYER, NAME, LASTUPDATED
FROM PERSON
Query: ReportQuery(ea1pck.Person)
        at oracle.toplink.essentials.exceptions.DatabaseException.sqlException(DatabaseException.java:319)
        at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:566)
        at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:452)
        at oracle.toplink.essentials.threetier.ServerSession.executeCall(ServerSession.java:473)
        at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228)
        at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:214)
        at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:285)
        at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:615)
        at oracle.toplink.essentials.internal.queryframework.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2416)
        at oracle.toplink.essentials.internal.queryframework.ExpressionQueryMechanism.selectAllReportQueryRows(ExpressionQueryMechanism.java:2382)
        at oracle.toplink.essentials.queryframework.ReportQuery.executeDatabaseQuery(ReportQuery.java:802)
        at oracle.toplink.essentials.queryframework.DatabaseQuery.execute(DatabaseQuery.java:628)
        at oracle.toplink.essentials.queryframework.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:692)
        at oracle.toplink.essentials.queryframework.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:746)
        at oracle.toplink.essentials.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2233)
        at oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:952)
        at oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:924)
        at oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:367)
        at oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.getResultList(EJBQueryImpl.java:478)
        at ea1pck.PersonFacade.findAll(PersonFacade.java:39)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at com.sun.enterprise.security.application.EJBSecurityManager.runMethod(EJBSecurityManager.java:1067)
        at com.sun.enterprise.security.SecurityUtil.invoke(SecurityUtil.java:176)
        at com.sun.ejb.containers.BaseContainer.invokeTargetBeanMethod(BaseContainer.java:2895)
        at com.sun.ejb.containers.BaseContainer.intercept(BaseContainer.java:3986)
        at com.sun.ejb.containers.EJBLocalObjectInvocationHandler.invoke(EJBLocalObjectInvocationHandler.java:197)
        ... 38 more
Caused by: java.sql.SQLSyntaxErrorException: Table 'PERSON' does not exist.
        at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
        at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
        at org.apache.derby.client.am.Connection.prepareStatement(Unknown Source)
        at com.sun.gjc.spi.base.ConnectionHolder.prepareStatement(ConnectionHolder.java:475)
        at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1162)
        at oracle.toplink.essentials.internal.databaseaccess.DatabaseCall.prepareStatement(DatabaseCall.java:612)
        at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:485)
        ... 65 more
Caused by: org.apache.derby.client.am.SqlException: Table 'PERSON' does not exist.
        at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)
        at org.apache.derby.client.net.NetS
tatementReply.parsePrepareError(Unknown Source)
        at org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply(Unknown Source)
        at org.apache.derby.client.net.NetStatementReply.readPrepareDescribeOutput(Unknown Source)
        at org.apache.derby.client.net.StatementReply.readPrepareDescribeOutput(Unknown Source)
        at org.apache.derby.client.net.NetStatement.readPrepareDescribeOutput_(Unknown Source)
        at org.apache.derby.client.am.Statement.readPrepareDescribeOutput(Unknown Source)
        at org.apache.derby.client.am.PreparedStatement.readPrepareDescribeInputOutput(Unknown Source)
        at org.apache.derby.client.am.PreparedStatement.flowPrepareDescribeInputOutput(Unknown Source)
        at org.apache.derby.client.am.PreparedStatement.prepare(Unknown Source)
        at org.apache.derby.client.am.Connection.prepareStatementX(Unknown Source)
        ... 70 more

the application 1 is now looking for the PERSON table in the database
of the enterprise application 2, obviously he cannot found it and launches
an error. If I launch again EA1 with the run method in netbeans the servlet
of EA1-WAR will run right, but the servlet of EA2-WAR will show an error
such: "Table 'EMPLOYEE' does not exist." because se will looking for the
table in the database of EA1.

can somone help me?
(see my next post to see my rough solution.