Skip to main content

Notice: this Wiki will be going read only early in 2024 and edits will no longer be possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.

Jump to: navigation, search

EclipseLink/Examples/JPA/Multitenant/Tutorial

< EclipseLink‎ | Examples‎ | JPA‎ | Multitenant
Revision as of 13:22, 5 October 2011 by Shaun.smith.oracle.com (Talk | contribs) (1.5. Observe Results)

EclipseLink Multi-Tenant Tutorial

This tutorial will walk you through the steps necessary to take a simple domain model mapped with JPA and make it multitenant enabled with EclipseLink automatically generating additional criteria. EclipseLink multitenancy can be used with any database but also provides specific support for Oracle Virtual Private Database (VPD) usage.

Overview

  1. Setup and configure environment
    1. Run basic tests in tutorial example application
  2. Configure entities in the example for multi-tenancy using EclipseLink to apply additional tenant criteria
    1. Run multi-tenant tests
  3. Configure for multi-tenancy using EclipseLink and Oracle VPD
    1. Run multi-tenant VPD tests

Software Requirements

  • EclipseLink 2.3.1 - build 20110910 or later
  • Example application (SVN)
  • Oracle Database
    • Must support Oracle VPD
    • Database user must have proper priviledges

Application Domain

The tutorial domain is a to do list with a single entity "Task". Tasks are owned by a user which, for simplicity, is a String name.

Eclipselink mt tutorial domain.jpg

Tutorial Steps

NOTE: Please follow the steps carefully.

This tutorial provides a sample application that is ready to run and includes JUnit tests which create the schema, populate the sample data, and run example persistence operations to verify proper functionality. At each stage of the tutorial a different set of tests will be run.

Setup

After downloading the tutorial source (zip file) you must perform the following steps:

1.1. Unzip the tutorial

The zip file can be unzipped into any folder.

1.2. Open the tutorial projects in Eclipse

Using the Eclipse project explorer's import existing projects you opne the project named org.eclipse.persistence.example.jpa.vpd.

Eclipselink mt tutorial project.png

1.3. Configure the Project

Configure the project's classpath and dependent libraries. Make sure all libraries configured in the project exist and the project compiles successfully.

Eclipselink mt tutorial project props.png

As you can see this project, which runs in Java SE, only requires:

  • EclipseLink library
  • Java Persistence (JPA) 2.0
  • JDBC driver

1.4. Run Tests

In order to validate that the project and environment are properly configured you will need to run the Example Initial Tests from the main run menu:

Eclipselink mt tutorial initial tests.png

This will run 10 unit tests and should provide a JUnit result window:

Eclipselink mt tutorial initial tests.jpg

In the following Observe Results section we will review each test case and what it means.

1.5. Observe Results

In this test you will simply want to ensure that all tests pass. If you look at the console you will see the resulting SQL for the schema generation, sample population, and test case SELECT, INSERT, and UPDATE calls.

At the start of each test run all tables will be dropped, created, and populated with test data.

InitialTests.verifyReadAllTasks Code:

        List<Task> tasks = em.createQuery("SELECT t FROM Task t ORDER BY t.id", Task.class).getResultList();

SQL:

[EL Fine]: SELECT ID, DESCRIPTION, STATUS, USER_ID, VERSION, PARENT_ID FROM TASK ORDER BY ID ASC

InitialTests.verifyReadByDescriptionLike Code:

List<Task> tasks = em.createQuery("SELECT t FROM Task t WHERE t.description LIKE '%house%' ORDER BY t.id", Task.class).getResultList();}

SQL:

[EL Fine]: SELECT ID, DESCRIPTION, STATUS, USER_ID, VERSION, PARENT_ID FROM TASK WHERE DESCRIPTION LIKE ? ORDER BY ID ASC
	bind => [%house%]

InitialTests.verifyReadByDescriptionLike Code:

List<Task> tasks = em.createNativeQuery("SELECT * FROM TASK t WHERE t.description LIKE '%house%' ORDER BY t.id").getResultList();

SQL:

[EL Fine]: SELECT * FROM TASK t WHERE t.description LIKE %house% ORDER BY t.id

InitialTests.verifyReadAllTasksForUser1 Code:

List<Task> tasks = em.createQuery("SELECT t FROM Task t WHERE t.user = :USER ORDER BY t.id ", Task.class)
    .setParameter("USER", SampleTasks.USER1_TASKS.getUserId())
    .getResultList();

SQL:

[EL Fine]: SELECT ID, DESCRIPTION, STATUS, USER_ID, VERSION, PARENT_ID FROM TASK WHERE (USER_ID = ?) ORDER BY ID ASC
	bind => [bsmith]

InitialTests.verifyReadAllTasksForUser2 Code:

List<Task> tasks = em.createQuery("SELECT t FROM Task t WHERE t.user = :USER ORDER BY t.id ", Task.class).setParameter("USER", SampleTasks.USER1_TASKS.getUserId()).getResultList();

SQL:

[EL Fine]: SELECT ID, DESCRIPTION, STATUS, USER_ID, VERSION, PARENT_ID FROM TASK WHERE (USER_ID = ?) ORDER BY ID ASC
	bind => [jdoe]

InitialTests.verifyCreateTask Code:

Task newUser1Task = new Task("User 1 Task");
newUser1Task.setUser(SampleTasks.USER1_TASKS.getUserId());
em.persist(newUser1Task);
 
Task newUser2Task = new Task("User 2 Task");
newUser1Task.setUser(SampleTasks.USER2_TASKS.getUserId());
em.persist(newUser2Task);

SQL:

[EL Fine]: INSERT INTO TASK (ID, DESCRIPTION, STATUS, USER_ID, VERSION, PARENT_ID) VALUES (?, ?, ?, ?, ?, ?)
	bind => [8, USER 1 Task, CREATED, jdoe, 1, NULL]
[EL Fine]: INSERT INTO TASK (ID, DESCRIPTION, STATUS, USER_ID, VERSION, PARENT_ID) VALUES (?, ?, ?, ?, ?, ?)
	bind => [9, USER 2 Task, CREATED, NULL, 1, NULL]
[EL Fine]: SELECT COUNT(ID) FROM TASK

InitialTests.updateSingleTask Code:

Task task = em.createNamedQuery("Task.findByDescription", Task.class).setParameter("DESC", SampleTasks.USER1_TASKS.getTasks()[0].getDescription()).getSingleResult();
task.setStatus(TaskStatus.COMPLETED);

SQL:

[EL Fine]: SELECT ID, DESCRIPTION, STATUS, USER_ID, VERSION, PARENT_ID FROM TASK WHERE (DESCRIPTION = ?)
	bind => [ORDER Pizza]
[EL Fine]: UPDATE TASK SET STATUS = ?, VERSION = ? WHERE ((ID = ?) AND (VERSION = ?))
	bind => [COMPLETED, 2, 1, 1]

InitialTests.updateAllUser1Tasks Code:

em.createQuery("UPDATE Task t SET t.status = :STATUS WHERE t.user = :USER")
    .setParameter("STATUS", TaskStatus.COMPLETED)
    .setParameter("USER", SampleTasks.USER1_TASKS.getUserId())
    .executeUpdate();

SQL:

[EL Fine]: UPDATE TASK SET VERSION = (VERSION + ?), STATUS = ? WHERE (USER_ID = ?)
	bind => [1, COMPLETED, bsmith]

Phase 1: @Multitenant

In the first phase of this tutorial we will convert this simple application from being a single tenant allowing use of all data to a multitenant version where each user can only see their own tasks.

1.1 Modify Task Class

The changes required in this phase of the tutorial is to enable multi-tenancy on the entity class Task. You will need to add the @Multitenant and @TenantDiscriminatorColumn annotations so the class looks like:

@Entity
@Multitenant
@TenantDiscriminatorColumn(name = "USER_ID", contextProperty = "example.user-id")
@NamedQuery(name = "Task.findByDescription", query = "SELECT t FROM Task t WHERE t.description = :DESC")
public class Task {

The @Multitenant annotation indicates that entity data from multiple tenants will be stored in the same table using the SINGLE_TABLE strategy (default). This means that EclipseLink must augment all generated SQL when querying the database and it must populate the table's columns which are used to identify the tenant. Since we don't want to use the dfeault column name we are specifying the column and the context property name used to identify the tenant using the @TenantDiscriminatorColumn.

Now that multi-tenancy is enabled the Task's knowledge of its user must be removed. This is so that your application, which functions in the scope of a specific User has no idea that other user's exist or their data is in the same table. To have this work we can simply mark the Task's user attribute as @Transient.

@Transient
//@Column(name = "USER_ID")
private String user;

Now we are all set to use the Task class in a multi-tenant enabled application. The only change is that the persistence context (EntityManager) must be provided the tenant identifier using the property specified in the @TenantDiscriminatorColumn annotation.

Map<String, Object> properties = new HashMap<String, Object>();
properties.put("example.user-id", "bsmith");
EntityManager em = getEMF().createEntityManager(properties);

1.2. Run Tests

In this phase you will need to run the "Example Multitenant Tests" run configuration. This will run a set of JUnit tests which verify the configuration and exercise the functionality. This run configuration is available under the main run menu as:

Eclipselink mt tutorial mt tests.png

1.3. Observe Results

In addition to all of the tests passing you will want to observe the changes in the application's behaviour based on your changes to the Task class.

[EL Fine]: INSERT INTO TASK (ID, DESCRIPTION, STATUS, VERSION, PARENT_ID, USER_ID) VALUES (?, ?, ?, ?, ?, ?)
	bind => [3, Put house up FOR sale, CREATED, 1, NULL, bsmith]
[EL Fine]: INSERT INTO TASK (ID, DESCRIPTION, STATUS, VERSION, PARENT_ID, USER_ID) VALUES (?, ?, ?, ?, ?, ?)
	bind => [1, ORDER Pizza, CREATED, 1, NULL, bsmith]
[EL Fine]: INSERT INTO TASK (ID, DESCRIPTION, STATUS, VERSION, PARENT_ID, USER_ID) VALUES (?, ?, ?, ?, ?, ?)
	bind => [2, Tip Pizza delivery driver, CREATED, 1, 1, bsmith]
[EL Fine]: INSERT INTO TASK (ID, DESCRIPTION, STATUS, VERSION, PARENT_ID, USER_ID) VALUES (?, ?, ?, ?, ?, ?)
	bind => [7, Paint house, CREATED, 1, NULL, jdoe]
[EL Fine]: INSERT INTO TASK (ID, DESCRIPTION, STATUS, VERSION, PARENT_ID, USER_ID) VALUES (?, ?, ?, ?, ?, ?)
	bind => [6, Pay Bills, CREATED, 1, NULL, jdoe]
[EL Fine]: INSERT INTO TASK (ID, DESCRIPTION, STATUS, VERSION, PARENT_ID, USER_ID) VALUES (?, ?, ?, ?, ?, ?)
	bind => [4, Drive kids TO school, CREATED, 1, NULL, jdoe]
[EL Fine]: INSERT INTO TASK (ID, DESCRIPTION, STATUS, VERSION, PARENT_ID, USER_ID) VALUES (?, ?, ?, ?, ?, ?)
	bind => [5, Feed Fish, CREATED, 1, NULL, jdoe]

TEST?

 
[EL Fine]: SELECT ID, USER_ID, DESCRIPTION, STATUS, VERSION, PARENT_ID FROM TASK WHERE (USER_ID = ?) ORDER BY ID ASC
	bind => [bsmith]

TEST?

 
[EL Fine]: SELECT ID, USER_ID, DESCRIPTION, STATUS, VERSION, PARENT_ID FROM TASK WHERE (USER_ID = ?) ORDER BY ID ASC
	bind => [jdoe]

TEST?

 
[EL Warning]: Exception [EclipseLink-6174] (Eclipse Persistence Services - 2.3.1.qualifier): org.eclipse.persistence.exceptions.QueryException
Exception Description: No VALUE was provided FOR the SESSION property [example.user-id]. This exception IS possible WHEN USING additional criteria OR tenant discriminator COLUMNS WITHOUT specifying the associated contextual property. These properties must be SET through Entity Manager, Entity Manager Factory OR persistence unit properties. IF USING native EclipseLink, these properties should be SET directly ON the SESSION.
Query: ReadAllQuery(referenceClass=Task SQL="SELECT ID, USER_ID, DESCRIPTION, STATUS, VERSION, PARENT_ID FROM TASK WHERE (USER_ID = ?) ORDER BY ID ASC")

TEST?

 
[EL Fine]: SELECT ID, USER_ID, DESCRIPTION, STATUS, VERSION, PARENT_ID FROM TASK WHERE (DESCRIPTION LIKE ? AND (USER_ID = ?)) ORDER BY ID ASC
	bind => [%house%, bsmith]

TEST?

 
[EL Fine]: INSERT INTO TASK (ID, DESCRIPTION, STATUS, VERSION, PARENT_ID, USER_ID) VALUES (?, ?, ?, ?, ?, ?)
	bind => [8, USER 1 Task, CREATED, 1, NULL, bsmith]
[EL Fine]: SELECT COUNT(ID) FROM TASK WHERE (USER_ID = ?)
	bind => [bsmith]

TEST?

 
[EL Fine]: SELECT ID, USER_ID, DESCRIPTION, STATUS, VERSION, PARENT_ID FROM TASK WHERE ((DESCRIPTION = ?) AND (USER_ID = ?))
	bind => [ORDER Pizza, bsmith]
[EL Fine]: UPDATE TASK SET STATUS = ?, VERSION = ? WHERE ((ID = ?) AND (VERSION = ?))
	bind => [COMPLETED, 2, 1, 1]

TEST?

 
[EL Fine]: UPDATE TASK SET VERSION = (VERSION + ?), STATUS = ? WHERE (USER_ID = ?)
	bind => [1, COMPLETED, bsmith]

Note:

  • The SQL is augmented form the initial tests so that the SELECT's include a comparison for the USER_ID.
  • The exception shown is intentional. The test case is attempting to read all tasks without providing the context property required.

Phase 2: @Multitenant(VPD)

In this stage of the tutorial you are going to switch from using EclipseLink's additional criteria being added to all queries on @Multitenant types to the database looking after the additional criteria. This approach is specific to the Oracle database and allows all users of the database to leverage the same logic filtering.

2.1. Setup Database

In order to use Oracle VPD the user 'mttorial will need to have permission. This requires the user to have specific permissions or to have the DBA role. For simplicity in this tutorial you will simply grant the user the DBA role.

2.2. Modify Task Class

@Entity
@Multitenant(MultitenantType.VPD)
@TenantDiscriminatorColumn(name = "USER_ID", contextProperty = "example.user-id")
@NamedQuery(name = "Task.findByDescription", query = "SELECT t FROM Task t WHERE t.description = :DESC")
public class Task {

2.3. Run Tests

In this phase you will need to run the EclipseLink Multitenant(VPD) tests run configuration.

Eclipselink mt tutorial mt-vpd tests.png

2.4. Observer Results

[EL Fine]: INSERT INTO TASK (ID, DESCRIPTION, STATUS, VERSION, PARENT_ID, USER_ID) VALUES (?, ?, ?, ?, ?, ?)
	bind => [3, Put house up FOR sale, CREATED, 1, NULL, bsmith]
[EL Fine]: INSERT INTO TASK (ID, DESCRIPTION, STATUS, VERSION, PARENT_ID, USER_ID) VALUES (?, ?, ?, ?, ?, ?)
	bind => [1, ORDER Pizza, CREATED, 1, NULL, bsmith]
[EL Fine]: INSERT INTO TASK (ID, DESCRIPTION, STATUS, VERSION, PARENT_ID, USER_ID) VALUES (?, ?, ?, ?, ?, ?)
	bind => [2, Tip Pizza delivery driver, CREATED, 1, 1, bsmith]
[EL Fine]: CALL DBMS_SESSION.CLEAR_IDENTIFIER()
[EL Fine]: CALL DBMS_SESSION.SET_IDENTIFIER(?)
	bind => [jdoe]
[EL Fine]: INSERT INTO TASK (ID, DESCRIPTION, STATUS, VERSION, PARENT_ID, USER_ID) VALUES (?, ?, ?, ?, ?, ?)
	bind => [4, Drive kids TO school, CREATED, 1, NULL, jdoe]
[EL Fine]: INSERT INTO TASK (ID, DESCRIPTION, STATUS, VERSION, PARENT_ID, USER_ID) VALUES (?, ?, ?, ?, ?, ?)
	bind => [6, Pay Bills, CREATED, 1, NULL, jdoe]
[EL Fine]: INSERT INTO TASK (ID, DESCRIPTION, STATUS, VERSION, PARENT_ID, USER_ID) VALUES (?, ?, ?, ?, ?, ?)
	bind => [5, Feed Fish, CREATED, 1, NULL, jdoe]
[EL Fine]: INSERT INTO TASK (ID, DESCRIPTION, STATUS, VERSION, PARENT_ID, USER_ID) VALUES (?, ?, ?, ?, ?, ?)
	bind => [7, Paint house, CREATED, 1, NULL, jdoe]
[EL Fine]: CALL DBMS_SESSION.CLEAR_IDENTIFIER()
[EL Fine]: SELECT ID, USER_ID, DESCRIPTION, STATUS, VERSION, PARENT_ID FROM TASK ORDER BY ID ASC=

TEST?

 

SQL Output:

[EL Fine]: SELECT t.* FROM TASK t WHERE t.description LIKE %house% ORDER BY t.id
[EL Fine]: CALL DBMS_SESSION.SET_IDENTIFIER(?)
	bind => [bsmith]
[EL Fine]: SELECT t.* FROM TASK t WHERE t.description LIKE %house% ORDER BY t.id
[EL Fine]: CALL DBMS_SESSION.CLEAR_IDENTIFIER()

TEST?

 

SQL Output:

[EL Fine]: CALL DBMS_SESSION.SET_IDENTIFIER(?)
	bind => [jdoe]
[EL Fine]: SELECT t.* FROM TASK t WHERE t.description LIKE %house% ORDER BY t.id
[EL Fine]: CALL DBMS_SESSION.CLEAR_IDENTIFIER()

TEST?

 

SQL Output:

[EL Fine]: CALL DBMS_SESSION.SET_IDENTIFIER(?)
	bind => [bsmith]
[EL Fine]: SELECT ID, USER_ID, DESCRIPTION, STATUS, VERSION, PARENT_ID FROM TASK ORDER BY ID ASC
[EL Fine]: CALL DBMS_SESSION.CLEAR_IDENTIFIER()

TEST?

 

SQL Output:

[EL Fine]: CALL DBMS_SESSION.SET_IDENTIFIER(?)
	bind => [jdoe]
[EL Fine]: SELECT ID, USER_ID, DESCRIPTION, STATUS, VERSION, PARENT_ID FROM TASK ORDER BY ID ASC
[EL Fine]: CALL DBMS_SESSION.CLEAR_IDENTIFIER()

TEST?

 

SQL Output:

[EL Fine]: CALL DBMS_SESSION.SET_IDENTIFIER(?)
	bind => [bsmith]
[EL Fine]: SELECT ID, USER_ID, DESCRIPTION, STATUS, VERSION, PARENT_ID FROM TASK WHERE DESCRIPTION LIKE ? ORDER BY ID ASC
	bind => [%house%]
[EL Fine]: CALL DBMS_SESSION.CLEAR_IDENTIFIER()

TEST?

 

SQL Output:

[EL Fine]: CALL DBMS_SESSION.SET_IDENTIFIER(?)
	bind => [bsmith]
[EL Fine]: INSERT INTO TASK (ID, DESCRIPTION, STATUS, VERSION, PARENT_ID, USER_ID) VALUES (?, ?, ?, ?, ?, ?)
	bind => [8, USER 1 Task, CREATED, 1, NULL, bsmith]
[EL Fine]: SELECT COUNT(ID) FROM TASK
[EL Fine]: CALL DBMS_SESSION.CLEAR_IDENTIFIER()

TEST?

 

SQL Output:

[EL Fine]: CALL DBMS_SESSION.SET_IDENTIFIER(?)
	bind => [bsmith]
[EL Fine]: SELECT ID, USER_ID, DESCRIPTION, STATUS, VERSION, PARENT_ID FROM TASK WHERE (DESCRIPTION = ?)
	bind => [ORDER Pizza]
[EL Fine]: UPDATE TASK SET STATUS = ?, VERSION = ? WHERE ((ID = ?) AND (VERSION = ?))
	bind => [COMPLETED, 2, 1, 1]
[EL Fine]: CALL DBMS_SESSION.CLEAR_IDENTIFIER()

TEST?

 

SQL Output:

[EL Fine]: CALL DBMS_SESSION.SET_IDENTIFIER(?)
	bind => [bsmith]
[EL Fine]: UPDATE TASK SET VERSION = (VERSION + ?), STATUS = ?
	bind => [1, COMPLETED]
[EL Fine]: CALL DBMS_SESSION.CLEAR_IDENTIFIER()


Learn More

For additional information on EclipseLink's Multi-tenancy support see:

Back to the top