Notice: This Wiki is now read only and edits are no longer possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.
EclipseLink/Examples/JPA/Multitenant/Tutorial
Contents
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
- Setup and configure environment
- Run basic tests in tutorial example application
- Configure entities in the example for multi-tenancy using EclipseLink to apply additional tenant criteria
- Run multi-tenant tests
- Configure for multi-tenancy using EclipseLink and Oracle VPD
- 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.
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:
Unzip the tutorial
The zip file can be unzipped into any folder.
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.
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.
As you can see this project, which runs in Java SE, only requires:
- EclipseLink library
- Java Persistence (JPA) 2.0
- JDBC driver
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:
This will run 10 unit tests and should provide a JUnit result window:
In the following Observe Results section we will review each test case and what it means.
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]
@Multitenant
In this section 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.
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);
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:
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.
MultitenantTests.verifyReadAllTasksForUser1
Code:
List<Task> tasks = em.createQuery("SELECT t FROM Task t ORDER BY t.id ", Task.class).getResultList();
[EL Fine]: SELECT ID, USER_ID, DESCRIPTION, STATUS, VERSION, PARENT_ID FROM TASK WHERE (USER_ID = ?) ORDER BY ID ASC bind => [bsmith]
MultitenantTests.verifyReadAllTasksForUser2
Code:
List<Task> tasks = em.createQuery("SELECT t FROM Task t ORDER BY t.id ", Task.class).getResultList();
[EL Fine]: SELECT ID, USER_ID, DESCRIPTION, STATUS, VERSION, PARENT_ID FROM TASK WHERE (USER_ID = ?) ORDER BY ID ASC bind => [jdoe]
MultitenantTests.verifyReadAllTasks
Code:
em.createQuery("SELECT t FROM Task t ORDER BY t.id", Task.class).getResultList();
Output:
[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")
MultitenantTests.verifyReadByDescriptionLike
Code:
List<Task> tasks = em.createQuery("SELECT t FROM Task t WHERE t.description LIKE '%house%' ORDER BY t.id", Task.class).getResultList();
[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]
MultitenantTests.verifyNativeReadByDescriptionLike
Output: none
If you modify the code to log the exception you would see:
Code:
EntityManager em = getEMF().createEntityManager(); try { em.createNativeQuery("SELECT t FROM TASK t WHERE t.description LIKE '%house%' ORDER BY t.id").getResultList(); } catch (QueryException qe) { qe.printStackTrace(); return; } finally { em.close(); }
Output:
[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]
LOCAL Exception Stack: Exception [EclipseLink-6175] (Eclipse Persistence Services - 2.3.1.qualifier): org.eclipse.persistence.exceptions.QueryException Exception Description: Native SQL queries have been disabled. This IS done either BY setting the persistence unit property eclipselink.jdbc.allow-native-sql-queries TO FALSE OR HAVING at least one multitenant entity defined IN your persistence unit. CHECK your persistence unit specification. TO allow native SQL queries, SET this property TO TRUE. Alternatively, individual queries may bypass this setting BY setting the eclipselink.jdbc.allow-native-sql-query query hint TO TRUE. Query: DataReadQuery(SQL="SELECT t FROM TASK t WHERE t.description LIKE '%house%' ORDER BY t.id")
MultitenantTests.verifyCreateTask_User1
Code:
em.getTransaction().begin(); Task newUser1Task = new Task("User 1 Task"); em.persist(newUser1Task); em.flush(); int numtasks = em.createQuery("SELECT COUNT(t) FROM Task t", Number.class).getSingleResult().intValue();
SQL:
[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]
MultitenantTests.updateSingleTask
Code:
em.getTransaction().begin(); Task task = em.createNamedQuery("Task.findByDescription", Task.class).setParameter("DESC", USER1_TASKS.getTasks()[0].getDescription()).getSingleResult(); assertNotNull(task); task.setStatus(TaskStatus.COMPLETED); em.getTransaction().commit();
SQL:
[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]
MultitenantTests.updateAllUser1Tasks
Code:
em.getTransaction().begin(); em.createQuery("UPDATE Task t SET t.status = :STATUS").setParameter("STATUS", TaskStatus.COMPLETED).executeUpdate(); em.getTransaction().commit();
SQL:
[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.
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: