Re: [eclipselink-users] eclipselink 2.4 TABLE_PER_TENANT shared EMF

[Guy Pelletier <guy.pelletier@xxxxxxxxxx>]

Hi Justin,

Glassfish V3.0 is run against EclipseLink version 2.0 which does not include the multitenant table per tenant feature. That feature is available in EclipseLink 2.4.

You'll need to update the version of Eclipselink on Glassfish. Here's a blog that can help you with that.

https://blogs.oracle.com/GlassFishPersistence/entry/updating_eclipselink_bundles_in_glassfish

Cheers,
Guy

On 24/08/2012 4:37 PM, Justin Joe wrote:

Hi Guy,

i have tried native queries as well but no chance.

I have seen one weird log line though which is
[#|2012-08-24T10:17:05.006+0300|INFO|glassfishv3.0|org.eclipse.persistence.session.file:.../_multi-tenant-pu|_ThreadID=40;_ThreadName=Thread-1;|EclipseLink, version: Eclipse Persistence Services - 2.0.0.v20091031-r5713|#]  

I think this refers to JPA v2.0 because i have eclipselink 2.4 jars in the library and i guess if i was not using 2.4 there wouldn't be ENUMs for these lines
@Multitenant(MultitenantType.TABLE_PER_TENANT)
@TenantTableDiscriminator(type = TenantTableDiscriminatorType.SCHEMA, contextProperty = "eclipselink.tenant-id")

what do you think about this log line ?

and this is the code i am executing :

@Entity
@Table(name = "created_report")
@Multitenant(MultitenantType.TABLE_PER_TENANT)
@TenantTableDiscriminator(type = TenantTableDiscriminatorType.SCHEMA, contextProperty = "eclipselink.tenant-id")
@NamedQueries({
    @NamedQuery(name = "CreatedReport.find", query = "SELECT r FROM CreatedReport r ORDER BY r.id DESC"),
    ....
})
public class CreatedReport implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name = "id")
    private Integer id;
    @Basic(optional = false)
    @Column(name = "date")
    @Temporal(TemporalType.TIMESTAMP)
    private Date date;
    @JoinColumn(name = "reportId", referencedColumnName = "id")
    @ManyToOne(optional = false)
    private Report report;

....
}

my @Stateless bean

@Stateless
@Local
public class CreatedReportJPAController {
  
    @Resource
    private SessionContext context;
  
    @PersistenceUnit(unitName="multi-tenant-pu")
    protected EntityManagerFactory emf;
  
    public List<CreatedReport> findAll() {
        List<CreatedReport> resultList = null;
        EntityManager em = null;
        EntityTransaction tx = null;
        Query query;
        try {
            em = emf.createEntityManager(getProperties());
            tx = em.getTransaction();
            tx.begin();
            em.setProperty(EntityManagerProperties.MULTITENANT_PROPERTY_DEFAULT, getSchema());
          
            query = em.createNamedQuery("Report.find");
            resultList = (List<CreatedReport>) query.getResultList();
        } catch (Exception ex) {
            tx.rollback();
            System.out.println(ex.toString());
        } finally {
            if (em != null && em.isOpen())
                em.close();
        }
        return resultList;
    }
  
    private HashMap getProperties(){
        HashMap properties = new HashMap();
        properties.put(EntityManagerProperties.JDBC_DRIVER, "org.postgresql.Driver");
        properties.put(EntityManagerProperties.JDBC_URL, "jdbc:postgresql://localhost:5432/reportDB");
        properties.put(EntityManagerProperties.JDBC_USER, "postgres");
        properties.put(EntityManagerProperties.JDBC_PASSWORD, "");
        properties.put(EntityManagerProperties.MULTITENANT_PROPERTY_DEFAULT, getSchema());
        return properties;
    }

    private String getSchema() {
        returns the schema through context.getCallerPrincipal().getName() call
    }

and here is the important log lines :

after em = emf.createEntityManager(getProperties());

[#|2012-08-24T10:17:04.794+0300|FINEST|glassfishv3.0|org.eclipse.persistence.session.file:..../_multi-tenant-pu.properties|_ThreadID=40;_ThreadName=Thread-1;ClassName=null;MethodName=null;|Begin deploying Persistence Unit multi-tenant-pu; session file:.../_multi-tenant-pu; state Predeployed; factoryCount 1|#]

[#|2012-08-24T10:17:04.936+0300|INFO|glassfishv3.0|org.hibernate.validation.engine.resolver.DefaultTraversableResolver|_ThreadID=40;_ThreadName=Thread-1;|Instantiated an instance of org.hibernate.validation.engine.resolver.JPATraversableResolver.|#]

[#|2012-08-24T10:17:04.999+0300|FINEST|glassfishv3.0|org.eclipse.persistence.session.file:.../_multi-tenant-pu.properties|_ThreadID=40;_ThreadName=Thread-1;ClassName=null;MethodName=null;|property=eclipselink.target-server; value=SunAS9; translated value=org.eclipse.persistence.platform.server.sunas.SunAS9ServerPlatform|#]

[#|2012-08-24T10:17:05.001+0300|FINEST|glassfishv3.0|org.eclipse.persistence.session.file:.../_multi-tenant-pu.properties|_ThreadID=40;_ThreadName=Thread-1;ClassName=null;MethodName=null;|property=eclipselink.logging.level; value=FINEST; translated value=FINEST|#]

[#|2012-08-24T10:17:05.012+0300|FINE|glassfishv3.0|org.eclipse.persistence.session.file:.../_multi-tenant-pu|_ThreadID=40;_ThreadName=Thread-1;ClassName=null;MethodName=null;|Detected Vendor platform: org.eclipse.persistence.platform.database.PostgreSQLPlatform|#]

[#|2012-08-24T10:17:05.015+0300|CONFIG|glassfishv3.0|org.eclipse.persistence.session.file:.../_multi-tenant-pu.connection|_ThreadID=40;_ThreadName=Thread-1;|connecting(DatabaseLogin(
        platform=>PostgreSQLPlatform
        user name=> ""
        connector=>JNDIConnector datasource name=>null
))|#]

[#|2012-08-24T10:17:05.016+0300|CONFIG|glassfishv3.0|org.eclipse.persistence.session.file:.../_multi-tenant-pu.connection|_ThreadID=40;_ThreadName=Thread-1;|Connected: jdbc:postgresql://localhost:5432/reportDB?loginTimeout=0&socketTimeout=0&prepareThreshold=5&unknownLength=2147483647&loglevel=0&tcpkeepalive=false
        User: postgres
        Database: PostgreSQL  Version: 9.1.4
        Driver: PostgreSQL Native Driver  Version: PostgreSQL 9.1 JDBC4 (build 902)|#]

[#|2012-08-24T10:17:05.019+0300|CONFIG|glassfishv3.0|org.eclipse.persistence.session.file:.../_multi-tenant-pu.connection|_ThreadID=40;_ThreadName=Thread-1;|connecting(DatabaseLogin(
        platform=>PostgreSQLPlatform
        user name=> ""
        connector=>JNDIConnector datasource name=>null
))|#]

[#|2012-08-24T10:17:05.020+0300|CONFIG|glassfishv3.0|org.eclipse.persistence.session.file:.../_multi-tenant-pu.connection|_ThreadID=40;_ThreadName=Thread-1;|Connected: jdbc:postgresql://localhost:5432/reportDB?loginTimeout=0&socketTimeout=0&prepareThreshold=5&unknownLength=2147483647&loglevel=0&tcpkeepalive=false
        User: postgres
        Database: PostgreSQL  Version: 9.1.4
        Driver: PostgreSQL Native Driver  Version: PostgreSQL 9.1 JDBC4 (build 902)|#]

[#|2012-08-24T10:17:05.022+0300|FINEST|glassfishv3.0|org.eclipse.persistence.session.file:.../_multi-tenant-pu.sequencing|_ThreadID=40;_ThreadName=Thread-1;ClassName=null;MethodName=null;|sequencing connected, state is NoPreallocation_State|#]

[#|2012-08-24T10:17:05.023+0300|FINEST|glassfishv3.0|org.eclipse.persistence.session.file:.../_multi-tenant-pu.sequencing|_ThreadID=40;_ThreadName=Thread-1;ClassName=null;MethodName=null;|sequence SEQ_GEN_IDENTITY: preallocation size 1|#]

[#|2012-08-24T10:17:05.198+0300|INFO|glassfishv3.0|org.eclipse.persistence.session.file:.../_multi-tenant-pu|_ThreadID=40;_ThreadName=Thread-1;|file:.../_multi-tenant-pu login successful|#]


[#|2012-08-24T10:17:05.246+0300|FINER|glassfishv3.0|org.eclipse.persistence.session.file:.../_multi-tenant-pu.jpa_metamodel|_ThreadID=40;_ThreadName=Thread-1;ClassName=null;MethodName=null;|No Canonical Metamodel classes found during initialization.|#]

........................

[#|2012-08-24T10:17:07.802+0300|FINEST|glassfishv3.0|org.eclipse.persistence.session.file:.../_multi-tenant-pu.properties|_ThreadID=40;_ThreadName=Thread-1;ClassName=null;MethodName=null;|End deploying Persistence Unit multi-tenant-pu; session file:.../_multi-tenant-pu; state Deployed; factoryCount 1|#]

[#|2012-08-24T10:17:07.803+0300|FINEST|glassfishv3.0|org.eclipse.persistence.session.file:.../_multi-tenant-pu.properties|_ThreadID=40;_ThreadName=Thread-1;ClassName=null;MethodName=null;|property=javax.persistence.jdbc.user; value=postgres|#]

[#|2012-08-24T10:17:07.804+0300|FINEST|glassfishv3.0|org.eclipse.persistence.session.file:.../_multi-tenant-pu.properties|_ThreadID=40;_ThreadName=Thread-1;ClassName=null;MethodName=null;|property=javax.persistence.jdbc.password; value=xxxxxx|#]

[#|2012-08-24T10:17:07.805+0300|FINEST|glassfishv3.0|org.eclipse.persistence.session.file:.../_multi-tenant-pu.properties|_ThreadID=40;_ThreadName=Thread-1;ClassName=null;MethodName=null;|property=javax.persistence.jdbc.driver; value=org.postgresql.Driver|#]

[#|2012-08-24T10:17:07.806+0300|FINEST|glassfishv3.0|org.eclipse.persistence.session.file:.../_multi-tenant-pu.properties|_ThreadID=40;_ThreadName=Thread-1;ClassName=null;MethodName=null;|property=javax.persistence.jdbc.url; value=jdbc:postgresql://localhost:5432/reportDB|#]


after tx = em.getTransaction();
         tx.begin();

[#|2012-08-24T10:20:38.447+0300|FINER|glassfishv3.0|org.eclipse.persistence.session.file:.../_multi-tenant-pu.connection|_ThreadID=40;_ThreadName=Thread-1;ClassName=null;MethodName=null;|client acquired|#]

after em.setProperty(EntityManagerProperties.MULTITENANT_PROPERTY_DEFAULT, getSchema());

[#|2012-08-24T10:21:10.160+0300|WARNING|glassfishv3.0|org.eclipse.persistence.session.file:.../_multi-tenant-pu.properties|_ThreadID=40;_ThreadName=Thread-1;|setProperties method called on EntityManager when active persistence context already exists therefore properties used to create persistence context will be ignored. |#]

and after query.getResultList();

[#|2012-08-24T10:21:50.165+0300|FINEST|glassfishv3.0|org.eclipse.persistence.session.file:.../_multi-tenant-pu.query|_ThreadID=40;_ThreadName=Thread-1;ClassName=null;MethodName=null;|Execute query ReadAllQuery(name="CreatedReport .find" referenceClass=CreatedReport sql="SELECT t2.id, t2.date, t2.reportId FROM created_report t2, report t1 WHERE (t1.id = t2.reportId)")|#]

[#|2012-08-24T10:21:50.168+0300|FINEST|glassfishv3.0|org.eclipse.persistence.session.file:.../_multi-tenant-pu.connection|_ThreadID=40;_ThreadName=Thread-1;ClassName=null;MethodName=null;|reconnecting to external connection pool|#]

thanks,
Justin


On 8/23/2012 10:51 AM, Guy Pelletier wrote:

Hi Justin,

There may be a bug with table per tenant and named queries.

What do you see when you do a persist? Then a find?

Set the logging to FINEST and send your results please, along with the code you are executing.

Cheers,
Guy

On 23/08/2012 12:45 PM, Justin Joe wrote:

Hi Guy,

this is my annotations for my Model class
@Entity
@Table(name = "report")
@Multitenant(MultitenantType.TABLE_PER_TENANT)
@TenantTableDiscriminator(type = TenantTableDiscriminatorType.SCHEMA, contextProperty = "eclipselink.tenant-id")
@NamedQuery(name = "Report.find", query = "SELECT r FROM Report r ORDER BY r.id DESC")

as my first test i ran these and query went through the default(public) schema;
Query query = em.createNamedQuery("Report.findAll");
List<Report> result = query.getResultList();
em.close();

but in the rest of the application, i will have to use
em.find()
em.refresh()
em.persist()
em.flush()
and some of my Model classes have some cascaded steps as well but i thought it doesn't matter as long as i specify the schema for the EM ?

thanks,
Justin

On 8/23/2012 7:52 AM, Guy Pelletier wrote:

Hi Justin,

Can you provide your model? What kind of queries are you executing? Are you talking about an em.find or a native query?

Cheers,
Guy

On 22/08/2012 4:58 PM, Justin Joe wrote:

Hi Guy,

i have tried many things to get it working but still the system queried from default schema instead of the one i specify dynamically.

these are what i tried :

- @PersistenceContext(unitName="multi-tenant-pu")
protected EntityManager em ;
schema = dynamically getting this ;
em.getTransaction().begin();
em.setProperty(EntityManagerProperties.MULTITENANT_PROPERTY_DEFAULT, "schema");

- if i try
    <persistence-unit name="multi-tenant-pu" transaction-type="JTA">
    <jta-data-source>jdbc/postgreSQL_multi-tenant-pu</jta-data-source>

it says you can not call em.getTransaction().begin(); on a container-managed EntityManager

- if i go with
    <persistence-unit name="multi-tenant-pu" transaction-type="RESOURCE_LOCAL">

you cann't inject EM, you should go through EMF

- so I tried
    <persistence-unit name="multi-tenant-pu" transaction-type="JTA">
    <jta-data-source>jdbc/postgreSQL_multi-tenant-pu</jta-data-source>

and created the resources on domain.xml (sun-glassfish)
again it says you can not call em.getTransaction().begin(); on a container-managed EntityManager

- at the end this worked without getting an exception
    <persistence-unit name="multi-tenant-pu" transaction-type="RESOURCE_LOCAL">

and I injected emf at the class level in my @Stateless bean
    @PersistenceUnit(unitName="multi-tenant-pu")
     protected EntityManagerFactory emf ;

and created my EM in the functions like
     HashMap properties = new HashMap();    
     properties.put(EntityManagerProperties.MULTITENANT_PROPERTY_DEFAULT, "schema");
     properties.put(EntityManagerProperties.JDBC_USER, "postgres");
     properties.put(EntityManagerProperties.JDBC_PASSWORD, "");

    em = emf.createEntityManager(properties);
    em.getTransaction().begin();
    em.setProperty(EntityManagerProperties.MULTITENANT_PROPERTY_DEFAULT, "schema");

but when i looked at the DB logs to see the queries coming through, i saw that queries are like:
SELECT * FROM table instead of [schema].table and the data i am seeing was coming from the default schema
btw at the DB level postgres user has permissions on both schemas

what do you think i am missing ?

thanks in advance
Justin


On 8/21/2012 1:24 PM, Guy Pelletier wrote:

Hi Justin,

The JDBC_USER and JDBC_PASSWORD properties are not dynamic properties and will have no effect on an injected EM (you should see a log warning)

You will be already logged in at this point and your example:

@PersistenceContext(name="multi-tenant-pu")
protected EntityManager em ;
schema = dynamically getting this ;
em.getTransaction().begin();
em.setProperty(EntityManagerProperties.MULTITENANT_PROPERTY_DEFAULT, "schema");

Will work if the database is provisioned to have access to schema1.report and schema2.report etc. with the persistence unit login credential (specified in your persistence.xml)

Otherwise you will have to use the following architecture

@PersistenceUnit(name="multi-tenant-pu")
protected EntityManagerFactory emf ;
user = dynamically getting this ;
password = dynamically getting this ;
schema = dynamically getting this ;

em = emf.createEntityManager(properties)

where properties will contain the dynamic properties (user, password, schema etc.).

Cheers,
Guy

On 21/08/2012 3:21 PM, Justin Joe wrote:

Hi,

I am trying to make a container-managed app an application-managed app because of the multi-tenancy requirement.
Before i was being able to inject the EM because the app was being deployed to every customer's machine separately  and i was using JTA connection pools through sun-glassfish.

but now i will have just one app instance and all the tenants will be using it by logging in with their own credentials.

so my question is :
Can i keep using container-managed transactions via TENANT_PER_TABLE solution and have connection-pools ?
i thought i can't and used transaction-type = "RESOURCE_LOCAL" instead

my DB is like this: (tables in different schemas are the same)

DB1:
  schema1 (tenant1)
      report
      ........
  schema2 (tenant2)
      report
      ........
 

if i can use this in my @Stateless EJB Bean classes, this would be great :

@PersistenceContext(name="multi-tenant-pu")
protected EntityManager em ;

schema = dynamically getting this ;

em.getTransaction().begin();
em.setProperty(EntityManagerProperties.MULTITENANT_PROPERTY_DEFAULT, "schema");
em.setProperty(EntityManagerProperties.JDBC_USER, "username");
em.setProperty(EntityManagerProperties.JDBC_PASSWORD, "password");

em.createNativeQuery
.....

i will try this and post the results anyways but do you think i am missing something ?

thanks in advance

On 8/21/2012 11:00 AM, Guy Pelletier wrote:

Hi Justin,

If you are injecting the EM why do you need a reference back to the EMF? You should just set the multitenant property on the EM? EMF's are shared by default.

With a non-shared EMF, you will need to ensure the following property is set to false in your persistence unit definition:

eclipselink.multitenant.tenants-share-emf

And you will also have to provide a unique session name though the following property:

eclipselink.session-name

Cheers,
Guy

On 21/08/2012 1:35 PM, Justin Joe wrote:

Hi Guy,

thanks for the answer.

the problem is actually injecting the PU. Because when I was using container-managed transactions and leaving the management to the app-server, i was going like this

@PersistenceContext
protected EntityManager em ;

EMF was being taken care of by the app-server but now i have to create or reference it somehow and i dont know how ?

and the second thing is that i tried to use non-shared EMF example too and set 'eclipse-tenant-id' to the schema that i am getting dynamically
when i debugged it, it seems like it is working but it is actually querying from the default schema 'public' even though i specify a different schema which the DB user has permission on.

so i can't get it working to query from different schemas basically.

any help at this point would be much appreciated

cheers,
Justin

On 8/21/2012 5:19 AM, Guy Pelletier wrote:

Hi Justin,

To answer your questions

1 - Yes, the EM should be created through the EMF. The sample code you are seeing is from our testing framework and the createEntityManager method is simply a wrapper to an emf.createEntityManager() call.

2 - MULTI_TENANT_PU is simply the name of the persistence unit since we have many different persistence units in our testing suites. If you are injecting the PU you don't need to worry about this.

Cheers,
Guy

On 21/08/2012 4:16 AM, Justin Joe wrote:

hi,

I have an existing JavaEE app using

• eclipselink 2.4 (JPA 2.0)
• glassfish 3.1
• EJB 3.0
• PostgreSQL 9.1

I want to add multi-tenancy support and i should be able to change the schema at runtime.

Basically; i will have a different schema per tenant with the same types of tables in it. this was a business decision for data isolation (SaaS - shared DB, seperate schema)

i want the application to share the same EntityManagerFactory because creating it for every DB operation is very expensive and when i am creating EntityManagers for every single DB transaction, i want to pass tenant-discriminator (schema) and JDBC_USER.

it seems that eclipselink added this support at 2.4 and i went through this example

http://wiki.eclipse.org/EclipseLink/DesignDocs/Multi-Tenancy/TablePerTenant

i have added these annotations in my Model classes

@Multitenant(MultitenantType.TABLE_PER_TENANT)
@TenantTableDiscriminator(type = TenantTableDiscriminatorType.SCHEMA, 
        contextProperty = "eclipselink.tenant-id")

and in my @Stateless bean, there should be a way of passing in EMF

@Inject
private EntityManagerFactory emf ;

EntityManager em = emf.createEntityManager();

so my question is :

How can i inject the shared EMF into my EJB beans ?

I am having a hard time getting this part working

// Shared EMF
EntityManager em = createEntityManager(MULTI_TENANT_PU);
em.getTransaction().begin();
em.setProperty(EntityManagerProperties.MULTITENANT_PROPERTY_DEFAULT, "gpelleti");

for this line;

EntityManager em = createEntityManager(MULTI_TENANT_PU);

1. Should 'createEntityManager' not be called through EMF
2. what is 'MULTI_TENANT_PU' type ?

thanks in advance

_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

--

Guy Pelletier

ORACLE Canada, 45 O'Connor Street Suite 400 Ottawa, Ontario Canada K1P 1A4
Oracle is committed to developing practices and products that help protect the environment

_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

--

Guy Pelletier

ORACLE Canada, 45 O'Connor Street Suite 400 Ottawa, Ontario Canada K1P 1A4
Oracle is committed to developing practices and products that help protect the environment

--

Guy Pelletier

ORACLE Canada, 45 O'Connor Street Suite 400 Ottawa, Ontario Canada K1P 1A4
Oracle is committed to developing practices and products that help protect the environment

--

Guy Pelletier

ORACLE Canada, 45 O'Connor Street Suite 400 Ottawa, Ontario Canada K1P 1A4
Oracle is committed to developing practices and products that help protect the environment

--

Guy Pelletier

ORACLE Canada, 45 O'Connor Street Suite 400 Ottawa, Ontario Canada K1P 1A4
Oracle is committed to developing practices and products that help protect the environment

--

Guy Pelletier

ORACLE Canada, 45 O'Connor Street Suite 400 Ottawa, Ontario Canada K1P 1A4
Oracle is committed to developing practices and products that help protect the environment