7 Understanding Data Access

The most important functions of a session is to provide access to a data source.

This chapter describes the concepts behind data access within a session that are unique to EclipseLink.

About Externally Managed Transactional Data Sources

EclipseLink transactional data sources are externally managed if the connection pool is managed by a transaction service (such as an application server controlled transaction or a JTA transaction).

A JTA managed data source or connection pool is commonly used in Java EE applications and normally required in EJB applications. Use an externally-managed connection pool as follows:

  • Configure the session to use an ExternalTransactionController to integrate EclipseLink's persistence unit with the external transaction service. Use the eclipselink.target-server persistence unit property to configure the name of the class that implements the ExternalTransactionController interface.

  • Use the boolean external-transaction-controller option on the persistence unit to indicate that the transaction is managed by a transaction manager and should not be managed by EclipseLink. This can also be used if the datasource does not support transactions to specify the connection's login and inform EclipseLink that the connection is maintained by the external controller.

  • You may need to configure the EclipseLink read connection pool or sequence connection pool to use a non-JTA connection pool in order to avoid transactional overhead. For more information, see Default (Write) and Read Connection Poolsand Sequence Connection Pools.

About Data Source Login Types

The login (if any) associated with a session determines how the EclipseLink runtime connects to the project's data source.

For projects that do not persist to a data source, a login is not required. For projects that do persist to a data source, a login is always required. A login includes details of data source access, such as authentication, use of connection pools, and use of external transaction controllers. A Login owns a data source platform.

A data source platform includes options specific to a particular data source including binding, use of native SQL, use of batch writing, and sequencing.

You can use a login in a variety of roles. A login's role determines where and how you create it. The login role you choose depends on the type of project you are creating and how you intend to use the login.

There is a session login type for each project type that persists to a data source.

Note that there is no XML login. EclipseLink XML projects are used for nonpersistent, in-memory object to XML data transformation and consequently there is no data source to log in to.

If you are creating a project that accesses a relational database, you must configure the project with a DatabaseLogin. Your choice of DatabasePlatform further customizes your project for a particular type of database. can be configured through the datasource-login persistence unit option. This option contains attributes for configuring user name, password, the name of the class implementing the data source platform, and others.

About Data Source Platform Types

EclipseLink abstracts the details of your underlying data source using data source platform classes.

A data source platform is owned by your project's Login. Specify your database platform at the project level for all sessions, or override this project-level configuration at the session level.

To configure most platform options, you must use an amendment method, or a preLogin event listener.

EclipseLink interacts with databases using structured query language (SQL). Because each database platform uses its own variation on the basic SQL language, EclipseLink must adjust the SQL it uses to communicate with the database to ensure that the application runs smoothly.

The type of database platform you choose determines the specific means by which the EclipseLink runtime accesses the database, including the type of Java Database Connectivity (JDBC) driver to use. JDBC is an application programming interface (API) that gives Java applications access to a database. EclipseLink relational projects rely on JDBC connections to read objects from, and write objects to, the database. EclipseLink applications use either individual JDBC connections or a JDBC connection pool, depending on the application architecture.

The DataPlatform class encapsulates behavior specific to a database platform (such as, Oracle, Sybase, DBase), and provides the protocol for EclipseLink to access this behavior

EclipseLink provides a variety of database-specific platforms that let you customize your project for your target database. For a list of supported database platforms, see org.eclipse.persistence.config.TargetDatabase class and Database Support.

A list of supported application servers can be found in org.eclipse.persistence.config.TargetServer and Application Server Support. The name of the application server you wish to use can be specified with the eclipselink.target-server.persistence unit property.

The datasource-login persistence unit option contains other data source properties you can configure, such as user name, password, connection pooling, and so on. You can implement the Login class, and then set properties specific to database.

Simple JDBC Authentication

When you configure an EclipseLink database login with a user name and password, EclipseLink provides these credentials to the JDBC driver that you configure your application to use.

By default, EclipseLink reads passwords from the persistence.xml file.

Oracle Database Proxy Authentication

EclipseLink supports proxy authentication with Oracle Database in Java SE applications and Java EE applications with the Oracle JDBC driver and external connection pools only.

Note:

EclipseLink does not support Oracle Database proxy authentication with JTA.

Oracle Database proxy authentication delivers the following security benefits:

  • A limited trust model, by controlling the users on whose behalf middle tiers can connect, and the roles the middle tiers can assume for the user.

  • Scalability, by supporting user sessions through Oracle Call Interface (OCI) and thick JDBC, and eliminating the overhead of reauthenticating clients.

  • Accountability, by preserving the identity of the real user through to the database, and enabling auditing of actions taken on behalf of the real user.

  • Flexibility, by supporting environments in which users are known to the database, and in which users are merely "application users" of which the database has no awareness.

Note:

Oracle Database supports proxy authentication in three-tiers only; it does not support it across multiple middle tiers.

For more information about authentication in Oracle Database, see "Preserving User Identity in Multitiered Environments" in the Oracle Database Security Guide.

Configure your EclipseLink database login to use proxy authentication to do the following:

  • address the complexities of authentication in a three-tier architecture (such as client-to-middle-tier and middle-tier-to-database authentication, and client reauthentication through the middle -tier to the database)

  • enhance database audit information (for even triggers and stored procedures) by using a specific user for database operations, rather than the generic pool user

  • simplify VPD/OLS configuration by using a proxy user, rather than setting user information directly in the session context with stored procedures

Auditing

Regardless of what type of authentication you choose, EclipseLink logs the name of the user associated with all database operations.

Example 7-1shows the CONFIG level EclipseLink logs when a ServerSession connects through the main connection for the sample user "scott", and a ClientSession uses proxy connection "jeff"

Your database server likely provides additional user auditing options. Consult your database server documentation for details.

Alternatively, you may consider using the EclipseLink persistence unit in conjunction with your database schema for auditing purposes.

Example 7-1 Logs with Oracle Database Proxy Authentication

[EclipseLink Config]--ServerSession(13)--Connection(14)--Thread(Thread[main,5,main])--connecting(DatabaseLogin( platform=>Oracle9Platform user name=> "scott" connector=>OracleJDBC10_1_0_2ProxyConnector datasource name=>DS)) 
[EclipseLink Config]--ServerSession(13)--Connection(34)--Thread(Thread[main,5,main])--Connected: jdbc:oracle:thin:@localhost:1521:orcl User: SCOTT 
[EclipseLink Config]--ClientSession(53)--Connection(54)--Thread(Thread[main,5,main])--connecting(DatabaseLogin(platform=>Oracle9Platform user name=> "scott" connector=>OracleJDBC10_1_0_2ProxyConnector datasource name=>DS)) 
[EclipseLink Config]--ClientSession(53)--Connection(56)--Thread(Thread[main,5,main])--Connected: jdbc:oracle:thin:@localhost:1521:orcl User: jeff

About Authentication

Authentication is the means by which a data source validates a user's identity and determines whether the user has sufficient privileges to perform a given action.

Authentication plays a central role in data security and user accountability and auditing.

For two-tier applications, simple JDBC authentication is usually sufficient.

Simple JDBC Authentication

When you configure an EclipseLink database login with a user name and password, EclipseLink provides these credentials to the JDBC driver that you configure your application to use.

By default, EclipseLink reads passwords from the persistence.xml file.

Oracle Database Proxy Authentication

EclipseLink supports proxy authentication with Oracle Database in Java SE applications and Java EE applications with the Oracle JDBC driver and external connection pools only.

Note:

EclipseLink does not support Oracle Database proxy authentication with JTA.

Oracle Database proxy authentication delivers the following security benefits:

  • A limited trust model, by controlling the users on whose behalf middle tiers can connect, and the roles the middle tiers can assume for the user.

  • Scalability, by supporting user sessions through Oracle Call Interface (OCI) and thick JDBC, and eliminating the overhead of reauthenticating clients.

  • Accountability, by preserving the identity of the real user through to the database, and enabling auditing of actions taken on behalf of the real user.

  • Flexibility, by supporting environments in which users are known to the database, and in which users are merely "application users" of which the database has no awareness.

Note:

Oracle Database supports proxy authentication in three-tiers only; it does not support it across multiple middle tiers.

For more information about authentication in Oracle Database, see "Preserving User Identity in Multitiered Environments" in the Oracle Database Security Guide.

Configure your EclipseLink database login to use proxy authentication to do the following:

  • address the complexities of authentication in a three-tier architecture (such as client-to-middle-tier and middle-tier-to-database authentication, and client reauthentication through the middle -tier to the database)

  • enhance database audit information (for even triggers and stored procedures) by using a specific user for database operations, rather than the generic pool user

  • simplify VPD/OLS configuration by using a proxy user, rather than setting user information directly in the session context with stored procedures

Auditing

Regardless of what type of authentication you choose, EclipseLink logs the name of the user associated with all database operations.

Example 7-1shows the CONFIG level EclipseLink logs when a ServerSession connects through the main connection for the sample user "scott", and a ClientSession uses proxy connection "jeff"

Your database server likely provides additional user auditing options. Consult your database server documentation for details.

Alternatively, you may consider using the EclipseLink persistence unit in conjunction with your database schema for auditing purposes.

Example 7-2 Logs with Oracle Database Proxy Authentication

[EclipseLink Config]--ServerSession(13)--Connection(14)--Thread(Thread[main,5,main])--connecting(DatabaseLogin( platform=>Oracle9Platform user name=> "scott" connector=>OracleJDBC10_1_0_2ProxyConnector datasource name=>DS)) 
[EclipseLink Config]--ServerSession(13)--Connection(34)--Thread(Thread[main,5,main])--Connected: jdbc:oracle:thin:@localhost:1521:orcl User: SCOTT 
[EclipseLink Config]--ClientSession(53)--Connection(54)--Thread(Thread[main,5,main])--connecting(DatabaseLogin(platform=>Oracle9Platform user name=> "scott" connector=>OracleJDBC10_1_0_2ProxyConnector datasource name=>DS)) 
[EclipseLink Config]--ClientSession(53)--Connection(56)--Thread(Thread[main,5,main])--Connected: jdbc:oracle:thin:@localhost:1521:orcl User: jeff

About Connections

A connection is an object that provides access to a data source by way of the driver you configure your application to use. Relational projects use JDBC to connect to the data source; EIS projects use JCA. EclipseLink uses the interface org.eclipse.persistence.internal.databaseaccess.Accessor to wrap data source connections. This interface is accessible from certain events.

Typically, when using a server session, EclipseLink uses a different connection for both reading and writing. This lets you use nontransactional connections for reading and avoid maintaining connections when not required.

By default, an EclipseLink server session acquires connections lazily: that is, only during the commit operation of a persistence unit. Alternatively, you can configure EclipseLink to acquire a write connections at the time you acquire a client sessions.

Connections can be allocated from internal or external connection pools.

About Connection Pools

connection pool is a service that creates and maintains a shared collection (pool) of data source connections on behalf of one or more clients.

The connection pool provides a connection to a process on request, and returns the connection to the pool when the process is finished using it. When it is returned to the pool, the connection is available for other processes. Because establishing a connection to a data source can be time-consuming, reusing such connections in a connection pool can improve performance.

EclipseLink uses connection pools to manage and share the connections used by server and client sessions. This feature reduces the number of connections required and allows your application to support many clients.

You can configure your session to use internal connection pools provided by EclipseLink or external connection pools provided by a JDBC driver or Java EE container.

You can use connection pools in your EclipseLink application for a variety of purposes, such as reading, writing, sequencing, and other application-specific functions.

Internal Connection Pools

For non-Java EE applications, you typically use internal connection pools. By default, EclipseLink sessions use internal connection pools.

Using internal connection pools, you can configure the default (write) and read connection pools. You can also create additional connection pools for object identity, or any other purpose.

Internal connection pools allow you to optimize the creation of read connections for applications that read data only to display it and only infrequently modify data. This also allow you to use Workbench to configure the default (write) and read connection pools and to create additional connection pools for object identity or any other purpose.

External Connection Pools

An external connection pool is a collection of reusable connections to a single data source provided by a JDBC driver or Java EE container.

For Java EE applications, you typically use external connection pools.

If you are using an external transaction controller (JTA), you must use external connection pools to integrate with the JTA.

Using external connection pools, you can use Java to configure the default (write) and read connection pools and create additional connection pools for object identity, or any other purpose.

External connection pools enable your EclipseLink application to do the following:

  • Integrate into a Java EE-enabled system.

  • Integrate with JTA transactions (JTA transactions require a JTA-enabled data source).

  • Leverage a shared connection pool in which multiple applications use the same data source.

  • Use a data source configured and managed directly on the server.

Default (Write) and Read Connection Pools

A server session provides a read connection pool and a write connection pool.

These could be different pools, or if you use external connection pooling, the same connection pool.

All read queries use connections from the read connection pool and all queries that write changes to the data source use connections from the write connection pool. You can configure attributes of the default (write) and read connection pools.

Whenever a new connection is established, EclipseLink uses the connection configuration you specify in your session's DatasourceLogin. Alternatively, when you use an external transaction controller, you can define a separate connection configuration for a read connection pool to avoid the additional overhead, if appropriate.

Use the connection-pool.read property to configure a read connection pool for non-transaction read queries. By default, EclipseLink does not use a separate read connection pool; the default pool is used for read queries. For more information, see connection-pool.read in Java Persistence API (JPA) Extensions Reference for EclipseLink.

Sequence Connection Pools

An essential part of maintaining object identity is sequencing–managing the assignment of unique values to distinguish one instance from another

For more information, see About Cache Type and Size.

Sequencing involves reading and writing a special sequence resource maintained by your data source.

By default, EclipseLink includes sequence operations in a separate transaction. This avoids complications during the write transaction, which may lead to deadlocks over the sequence resource. However, when using an external transaction controller (such as a JTA data source or connection pool), EclipseLink cannot use a different transaction for sequencing. Use a sequence connection pool to configure a non-JTA transaction pool for sequencing. This is required only for table sequencing–not native sequencing.

In each server session, you can create one connection pool, called a sequence connection pool, that EclipseLink uses exclusively for sequencing. With a sequence connection pool, EclipseLink satisfies a request for a new object identifier outside of the transaction from which the request originates. This allows EclipseLink to immediately commit an update to the sequence resource, which avoids deadlocks.

Note:

If you use a sequence connection pool and the original transaction fails, the sequence operation does not roll back.

You should use a sequence connection pool, if the following applies:

  • You use table sequencing (that is, non-native sequencing).

  • You use external transaction controller (JTA).

You should not use a sequence connection pool, if the following applies:

  • You do not use sequencing, or use the data source's native sequencing.

  • You have configured the sequence table to avoid deadlocks.

  • You use non-JTA data sources.

You can configure a sequence connection pool with the eclipselink.connection-pool.sequence persistence unit property. This property allows the connection pool to allocate generated IDs, and is required only for TABLE sequencing. By default, EclipseLink does not use a separate sequence connection pool; the default pool is used for sequencing. For more information, see connection-pool.sequence in Java Persistence API (JPA) Extensions Reference for EclipseLink.

Application-Specific Connection Pools

When you use internal EclipseLink connection pools in a session, you can create one or more connection pools that you can use for any application purpose.

These are called named connection pools, as you can give them any name you want and use them for any purpose.

Typically, use these named connection pools to provide pools of different security levels. For example, the "default" connection pool may only allow access to specific tables but the "admin" connection pool may allow access to all tables.

About Data Partitioning Policies

Data partitioning allows an application to scale its data across more than a single database machine.

For more information on data partitioning, see "Using Data Partitioning to Scale Data" in Solutions Guide for EclipseLink.

Some databases support clustering the database across multiple machines. Oracle RAC allows for a single database to span multiple different server nodes. Oracle RAC also supports table and node partitioning of data. A database cluster allows for any of the data to be accessed from any node in the cluster. However, it is generally more efficient to partition the data access to specific nodes, to reduce cross node communication. For more information, see "Clustered Databases and Oracle RAC" in Solutions Guide for EclipseLink.

About Tenant Isolation

EclipseLink offers considerable flexibility in how you can design and implement features for isolating tenants.

Eclipselink includes the following:

Application Isolation options

  • Separate container/server

  • Separate application within the same container/server

  • Separate entity manager factory and shared cache within the same application

  • Shared entity manager factory with tenant isolation per entity manager

Data isolation options

  • Separate database

  • Separate schema/tablespace

  • Separate tables

  • Shared table with row isolation

  • Query filtering

  • Oracle Virtual Private Database (VPD)

EclipseLink includes the following options for providing multi-tenancy in the data source:

  • Single-table multi-tenancy allows tenants to share tables. Each tenant has its own rows, identified by discriminator columns, and those rows are invisible to other tenants. See Single Table Multi-Tenancy.

  • With table-per-tenant multi-tenancy, each tenant has its own table or tables, identified by table tenant discriminators, and those tables are invisible to other users. See .

  • With (VDP) multi-tenancy, tenants use a VDP database, which provides the functionality to support multiple tenants sharing the same table. See .

Single Table Multi-Tenancy

With single-table multi-tenancy, any table (Table or SecondaryTable) to which an entity or mapped superclass maps can include rows for multiple tenants.

Access to tenant-specific rows is restricted to the specified tenant.

Tenant-specific rows are associated with the tenant by using one or more tenant discriminator columns. Discriminator columns are used with application context values to limit what a persistence context can access.

The results of queries on the mapped tables are limited to the tenant discriminator value(s) provided as property values. This applies to all insert, update, and delete operations on the table. When multi-tenant metadata is applied at the mapped superclass level, it is applied to all subentities unless they specify their own multi-tenant metadata.

Table-Per-Tenant Multi-Tenancy

Table-per-tenant multi-tenancy allows multiple tenants of an application to isolate their data in one or more tenant-specific tables.

Multiple tenants' tables can be in a shared schema, identified using a prefix or suffix naming pattern; or they can be in separate, tenant-specific schemas. Table-per-tenant entities can be mixed with other multi-tenant type entities within the same persistence unit.

The table-per-tenant multi-tenant type is used in conjunction with:

  • A tenant table discriminator that specifies the type of discriminator (schema or name with prefix or suffix)

  • A tenant ID to identify the user (configured per entity manager or at the entity manager factory, if isolating the table-per-tenant per persistence unit.)

A single application instance with a shared EntityManagerFactory for a persistence unit can be responsible for handling requests from multiple tenants.

Alternatively, separate EntityManagerFactory instances can be used for each tenant. (This is required when using extensions per tenant.) In this case, tenant-specific schema and table names are defined in an eclipselink-orm.xml configuration file. A MetadataSource must be registered with a persistence unit. The MetadataSource is used to support additional persistence unit metadata provided from outside the application. See also metadata-source in Java Persistence API (JPA) Extensions Reference for EclipseLink.

The table-per-tenant multi-tenant type enables individual tenant table(s) to be used at the entity level. A tenant context property must be provided on each entity manager after a transaction has started.

  • The table(s) (Table and SecondaryTable) for the entity are individual tenant tables based on the tenant context. Relationships within an entity that uses a join or a collection table are also assumed to exist within the table-per-tenant context.

  • Multi-tenant metadata can only be applied at the root level of the inheritance hierarchy when using a SINGLE_TABLE or JOINED inheritance strategy. Multi-tenant metadata can be specified in a TABLE_PER_CLASS inheritance hierarchy.

For information on constructing table-per-tenant multi-tenancy, see "Using Table-Per-Tenant Multi-Tenancy" in Solutions Guide for EclipseLink.

VPD Multi-Tenancy

A Virtual Private Database (VPD) uses security controls to restrict access to database objects based on various parameters.

For example, the Oracle Virtual Private Database supports security policies that control database access at the row and column level. Oracle VPD adds a dynamic WHERE clause to SQL statements issued against the table, view, or synonym to which the security policy was applied.

Oracle Virtual Private Database enforces security directly on the database tables, views, or synonyms. Because security policies are attached directly to these database objects, and the policies are automatically applied whenever a user accesses data, there is no way to bypass security.

When a user directly or indirectly accesses a table, view, or synonym that is protected with an Oracle Virtual Private Database policy, Oracle Database dynamically modifies the SQL statement of the user. This modification creates a WHERE condition (called a predicate) returned by a function implementing the security policy. Oracle Virtual Private Database modifies the statement dynamically, transparently to the user, using any condition that can be expressed in or returned by a function. Oracle Virtual Private Database policies can be applied to SELECTINSERTUPDATEINDEX, and DELETE statements.

When using EclipseLink VPD Multitenancy, the database handles the tenant filtering on all SELECTINSERTUPDATEINDEX and DELETE queries.

To use EclipseLink VPD multi-tenancy, you must first configure VPD in the database and then specify multi-tenancy on the entity or mapped superclass using @Multitenant and @TenantDiscriminatorColumn annotations.

For more information on constructing VPD Multi-Tenancy, see “Using VPD Multi-Tenancy” in Solutions Guide for EclipseLink.

About Heterogeneous Batch Writing

The current release provides persistence unit properties to optimize transactions with multiple writes.

The eclipselink.jdbc.batch-writing property configures the use of batch writing to optimize transactions with multiple writes. Batch writing allows multiple heterogeneous dynamic SQL statements to be sent to the database as a single execution, or multiple homogeneous parameterized SQL statements to be executed as a single batch execution. Note that not all JDBC drivers, or databases support batch writing.

The eclipselink.jdbc.batch-writing.size property configures the batch size used for batch writing. For parameterized batch writing this is the number of statements to batch, default 100. For dynamic batch writing, this is the size of the batched SQL buffer, default 32k.

The eclipselink.jdbc.batch-writing persistence property can also be used with query hints to configure if a modify query can be batched through batch writing. Some types of queries cannot be batched, such as DDL on some databases. Disabling batch writing will also allow the row count to be returned.