TOC Special:Whatlinkshere_Configuring_a_Database_Login_(ELUG)[Related Topics]
In a relational database project, EclipseLink retrieves the table information from the database, for each descriptor. Each Workbench project contains an associated database. You can create multiple logins for each database.
The following table lists the configurable options for a database login.
[Table 94-1]#
Option to Configure | Workbench | Java |
---|---|---|
For each database session, you must specify the database platform (such as Oracle9i Database Server). This platform configuration overrides the platform at the project level, if configured.
For more information, see the following:
To specify the database platform options for a relational server (or database) session login, use this procedure:
-
Select a relational server (or database) session in the Navigator. Its properties appear in the Editor.
-
Click the Login tab. The Login tab appears.
-
Click the Connection subtab. The Connection subtab appears. [Figure 94-1]#Login Tab, Connection Subtab, Database Platform Option
-
Select the database platform from the menu of options. This menu includes all instances of
DatabasePlatform
in the EclipseLink classpath.
You configure connection information at the session level for your
EclipseLink application. This information is stored in the
sessions.xml
file. The EclipseLink runtime uses this information
whenever you perform a persistence operation using the session in your
application.
This connection configuration overrides the connection information at the project level, if configured. For more information about project-level configuration, see Configuring Development and Deployment Logins.
This connection configuration is overridden by the connection information at the connection pool level. For more information, see Configuring Connection Pool Connection Options.
To specify the connection options for a relational server (or database) session login, use this procedure:
-
Select a relational server (or database) session in the Navigator. Its properties appear in the Editor.
-
Click the Login tab. The Login tab appears.
-
Click the Connection subtab. The Connection subtab appears. Login Tab, Connection Subtab, Database Driver Login Tab, Connection Subtab
-
Complete each field on the Connection subtab.
Use the following information to enter data in the driver fields on the tab:
Field
Description
Database Driver
Specify the appropriate database driver:
Driver Manager: specify this option to configure the driver class and URL used to connect to the database. In this case, you must configure the Driver Class and Driver URL fields.
J2EE Data Source: specify this option to use a Java EE data source already configured on your target application server. In this case, you must configure the Datasource Name field.
Note: If you select J2EE Datasource, you must use external connection pooling. You cannot use internal connection pools with this Database Driver option (for more information, see Configuring External Connection Pooling).
Driver Class 1
Configure this field when Database Driver is set to Driver Manager. Select from the menu of options. This menu includes all JDBC drivers in the EclipseLink classpath.
Driver URL 1
Configure this field when Database Driver is set to Driver Manager. Select from the menu of options relevant to the selected Driver Class, and edit the URL to suit your data source.
Data Source Name 2
Configure this field when Database Driver is set to J2EE Datasource. Specify any valid JNDI name that identifies the Java EE data source preconfigured on your target application server (example: jdbc/EmployeeDB).
By convention, all such names should resolve to the JDBC subcontext (relative to the standard java:comp/env naming context that is the root of all provided resource factories).
Lookup Type2
Configure this field when Database Driver is set to J2EE Datasource. Specify the lookup method for determining the JNDI name:
Composite Name
Compound Name
String
1Applicable only when Database Driver is set to Driver Manager. 2Applicable only when Database Driver is set to J2EE Datasource.
You configure EclipseLink sequencing at the session or project level to tell EclipseLink how to obtain sequence values: that is, what type of sequences to use.
You can configure a session directly by using a session-level sequence configuration to override project-level sequence configuration, on a session-by-session basis, if required.
Using Workbench (see How to Configure Sequencing at the Session Level Using Workbench), you can configure table sequencing (see Table Sequencing) and native sequencing (Native Sequencing with an Oracle Database Platform and Native Sequencing with a Non-Oracle Database Platform), and you can configure a preallocation size that applies to all sequences (see Sequencing and Preallocation Size).
Using Java (see How to Configure Sequencing at the Session Level Using Java), you can configure any sequence type that EclipseLink supports ( Sequencing Types). You can create any number and combination of sequences. You can create a sequence object explicitly or use the default sequence that the platform creates. You can associate the same sequence with more than one descriptor and you can configure a separate preallocation size for each descriptor’s sequence.
If you are migrating a WebLogic CMP application to OC4J and EclipseLink persistence, after migration, you must manually configure your project to use EclipseLink unary sequence tables if your application originally used single-column sequence tables in WebLogic.
After configuring the sequence type at the session (or project) level, to enable sequencing, you must configure a descriptor with a sequence field and a sequence name (see Configuring Sequencing at the Descriptor Level).
For more information about sequencing, see Sequencing in Relational Projects.
To specify the sequencing information for a relational server (or database) session, use this procedure:
Use the following information to enter data in each field of the Sequencing subtab to configure the persistence type:
Field | Description |
---|---|
Preallocation Size |
Select the default preallocation size. Default is 50. The preallocation size you configure applies to all sequences. |
Default Sequence Table |
Select this option to use
table
sequencing with default sequence table name |
Native Sequencing |
Select this option to use a sequencing object (see Native Sequencing with an Oracle Database Platform or Native Sequencing with a Non-Oracle Database Platform) created by the database platform. This option applies to supported database platforms (see Database Platforms). |
Custom Sequence Table |
Select this option to use table sequencing with a sequence table name, sequence name field, and sequence count field name that you specify. |
Name |
Select the name of the sequence table. |
Name Field |
Select the name of the column used to store the sequence name. |
Counter Field |
Select the name of the column used to store the sequence count. |
Using Java, you can perform the following sequence configurations:
After you configure your login with a platform (see Configuring a Relational Database Platform at the Session Level), you can use the default sequence that the platform provides.
If you associate a descriptor with an unspecified sequence, the
EclipseLink runtime will create an instance of DefaultSequence
to
provide sequencing for that descriptor. For more information, see
Configuring
the Platform Default Sequence.
You can access the default platform sequence directly as the
Accessing the Platform Default Sequence example
shows. For example, by default, a DatabasePlatform
creates a table
sequence using the default table and column names (see
Table
Sequencing).
[Example 94-1]# Accessing the Platform Default Sequence
//\'\' \'\'assume\'\' \'\'that\'\' \'\'dbLogin\'\' \'\'owns\'\' \'\'a\'\' \'\'DatabasePlatform
TableSequence tableSeq2 = ((TableSequence)dbLogin.getDefaultSequence()).clone();
tableSeq2.setName("EMP_SEQ");
tableSeq2.setPreallocationSize(75);
dbLogin.addSequence(tableSeq2);
To avoid having to clone the platform default sequence, you can use the
DefaultSequence
class– a wrapper for the platform default
sequence–-as the following example shows. The new sequence named
EMP_SEQ
will be of the same type as the platform default sequence.
[Example 94-2]# Using the DefaultSequence Class
login.addSequence(new DefaultSequence("EMP_SEQ", 75));
You can override the default platform sequence, as the
Overriding the Platform Default Sequence example
shows. In this example, dbLogin
owns a DatabasePlatform
that
provides a default sequence of type TableSequence
. After setting the
default sequence to type UnaryTableSequence
, when you use the
DefaultSequence
class, it will access the new default sequence type.
In this example, the sequence named EMP_SEQ
will be of type
UnaryTableSequence
and have a preallocation size of 75.
[Example 94-3]# Overriding the Platform Default Sequence
//\'\' \'\'assume\'\' \'\'that\'\' \'\'dbLogin\'\' \'\'owns\'\' \'\'a\'\' \'\'DatabasePlatform
Sequence unaryTableSequence = new UnaryTableSequence();
unaryTableSequence.setPreallocationSize(40);
dbLogin.setDefaultSequence(unaryTableSequence);
dbLogin.addSequence(
new DefaultSequence("EMP_SEQ", 75)
//\'\' \'\'UnaryTableSequence
);
In addition to using the platform default sequence (see
Using the Platform Default
Sequence), you can explicitly create sequence instances and configure a
Login
with any combination of sequence types, each with their own
preallocation size, as the Configuring Multiple
Sequences Explicitly example shows. In this example, the sequence named
EMP_SEQ
will provide sequence values exclusively for instances of
the Employee
class and ADD_SEQ
will provide sequence values
exclusively for instances of the Address
class. The sequence named
PHONE_SEQ
will use the platform default sequence with a
preallocation size of 30 to provide sequence values for the Phone
class.
[Example 94-4]# Configuring Multiple Sequences Explicitly
login.addSequence(new TableSequence("EMP_SEQ", 25));
login.addSequence(new DefaultSequence("PHONE_SEQ", 30));
login.addSequence(new UnaryTableSequence("ADD_SEQ", 55));
login.addSequence(new NativeSequence("NAT_SEQ", 10));
If login owned a DatabasePlatform
(whose default sequence type is
TableSequence
), you could configure your sequences using the
platform default sequence type, as the Configuring
Multiple Sequences Using the Default Sequence Type example shows. In
this example, sequences EMP_SEQ
and PHONE_SEQ
share the same
TableSequence
table: EMP_SEQ
and PHONE_SEQ
represent rows in
this table.
[Example 94-5]# Configuring Multiple Sequences Using the Default Sequence Type
login.addSequence(new DefaultSequence("EMP_SEQ", 25));
login.addSequence(new DefaultSequence("PHONE_SEQ", 30));
login.addSequence(new UnaryTableSequence("ADD_SEQ", 55));
login.addSequence(new NativeSequence("NAT_SEQ", 10));
You can configure the query that EclipseLink uses to update or read a
sequence value for any sequence type that extends QuerySequence
.
In most applications, the queries that EclipseLink automatically uses are sufficient. However, if your application has special sequencing needs–-for example, if you want to use stored procedures for sequencing–-then you can configure the update and read queries that the EclipseLink sequence uses.
The following example illustrates how to specify a stored procedure that
updates a sequence and returns the new sequence value with a single SQL
select query. In this example, the stored procedure is named
UPDATE_SEQ
. It contains one input argument–-the name of the sequence
to update (SEQ_NAME
), and one output argument–-the value of the
sequence after the updated (SEQ_COUNT
). The stored procedure
increments the sequence value associated with the sequence named
SEQ_NAME
and returns the new sequence value in the output argument
named SEQ_COUNT
.
[Example 94-6]# Using a Stored Procedure for both Sequence Update and Select
ValueReadQuery seqReadQuery = new ValueReadQuery();
StoredProcedureCall spCall = new StoredProcedureCall();
spCall.setProcedureName("UPDATE_SEQ");
seqReadQuery.addNamedArgument("SEQ_NAME");
seqReadQuery.addNamedOutputArgument("SEQ_COUNT");
seqReadQuery.setCall(spCall);
((QuerySequence)login.getDefaultSequence()).setSelectQuery(seqReadQuery);
Using a Stored Procedure for Sequence Updates Only and Using a Stored Procedure for Sequence Selects Only examples illustrate how to specify separate stored procedures for sequence update and select actions.
In the Using a Stored Procedure for Sequence Updates
Only example, the stored procedure is named UPDATE_SEQ, and it contains
one input argument–the name of the sequence to update (SEQ_NAME
).
The stored procedure increments the sequence value associated with the
sequence named SEQ_NAME
.
[Example 94-7]# Using a Stored Procedure for Sequence Updates Only
DataModifyQuery seqUpdateQuery = new DataModifyQuery();
StoredProcedureCall spCall = new StoredProcedureCall();
spCall.setProcedureName("UPDATE_SEQ");
seqUpdateQuery.addNamedArgument("SEQ_NAME");
seqUpdateQuery.setCall(spCall);
((QuerySequence)login.getDefaultSequence()).setUpdateQuery(seqUpdateQuery);
In the Using a Stored Procedure for Sequence Selects
Only example, the stored procedure is named SELECT_SEQ
and it takes
one argument: the name of the sequence to select from (SEQ_NAME
).
The stored procedure reads one data value: the current sequence value
associated with the sequence name SEQ_NAME
.
[Example 94-8]# Using a Stored Procedure for Sequence Selects Only
ValueReadQuery seqReadQuery = new ValueReadQuery();
StoredProcedureCall spCall = new StoredProcedureCall();
spCall.setProcedureName("SELECT_SEQ");
seqReadQuery.addArgument("SEQ_NAME");
seqReadQuery.setCall(spCall);
login.((QuerySequence)getDefaultSequence()).setSelectQuery(seqReadQuery)
You can also create a QuerySequence
directly and add it to your
login, as this example shows.
[Example 94-9]# Using the QuerySequence Class
//\'\' \'\'Use\'\' \'\'the\'\' \'\'two-argument\'\' \'\'constructor:\'\' \'\'pass\'\' \'\'in\'\' \'\'sequence\'\' \'\'name\'\' \'\'and\'\' \'\'preallocation\'\' \'\'size.
//\'\' \'\'Alternatively,\'\' \'\'you\'\' \'\'can\'\' \'\'use\'\' \'\'zero-\'\' \'\'or\'\' \'\'one-argument\'\' \'\'(sequence\'\' \'\'name)\'\' \'\'constructor
login.addSequence(new QuerySequence("SEQ1", 75));
Some databases (such as Oracle Database and DB2) require that all tables be qualified by an identifier. This can be the creator of the table or database name on which the table exists. When you specify a table qualifier, EclipseLink uses this qualifier for all of the tables it references. Specify a table qualifier only if required and only if all of the tables have the same qualifier.
To specify a table qualifier, use this procedure:
In the Table Qualifier field enter the identifier used to qualify references to all tables in this database.
Most JDBC drivers support the run-time configuration of various options to customize driver operation to meet user needs. EclipseLink provides direct support (in API and the Workbench) for many of the most important options, as this section describes, as well as more advanced options (see Configuring Advanced Options).
You can also configure additional options by specifying properties (see Configuring Properties).
Note: Not all drivers support all JDBC options. Selecting a combination of options may result in different behavior from one driver to another. Before selecting JDBC options, consult your JDBC driver documentation. |
To specify the JDBC options for a relational server (or database) session login, use this procedure:
Use this table to enter data in the fields on the Options subtab to select the JDBC options to use with this session login:
Option
Description
Queries Should Bind All Parameters1
By default, EclipseLink binds all of the query’s parameters.
Deselect this option if you do not want EclipseLink to bind parameters.
Cache All Statements1
When selected, EclipseLink caches each prepared statement so that when reexecuted, you avoid the SQL preparation time which improves performance.
Byte Array Binding1
Select this option if you query binary large object (BLOB) data.
Streams for Binding1
Select this option if you use a JDBC driver that is more efficient at handling BLOB data using java.io.InputStream and java.io.OutputStream.
Native SQL
By default, EclipseLink generates SQL using JDBC SQL grammar. Select this option if you want EclipseLink to use database-specific SQL grammar, for example, if your database driver does not support the full JDBC SQL grammar.
Batch Writing2
Select this option if you use a JDBC driver that supports sending groups of INSERT, UPDATE, and DELETE statements to the database in a single transaction, rather than individually.
Select JDBC to use the batch writing capabilities of your JDBC driver.
Select EclipseLink to use the batch writing capabilities that EclipseLink provides. Select this option if your JDBC driver does not support batch writing.
Note: if you are using Oracle 9 Database platform, and you want to use EclipseLink batch writing in combination with optimistic locking, then you must enable parameter binding.
String Binding1
Select this option if you query large java.lang.String objects.
You can configure the maximum String length (default: 32000 characters).
1For more information, see
How
to Use Parameterized SQL (Parameter Binding) and Prepared Statement
Caching for Optimization. 2If you are using the MySQLPlatform
database platform (see
Data
Source Platform Types), use JDBC batch writing (do not use
EclipseLink batch writing). For more information, see
How
to Use Batch Writing for Optimization.
To enable prepared statement caching for all queries, configure at the
Login
level, as the following example shows. For more information,
see
How
to Use Parameterized SQL (Parameter Binding) and Prepared Statement
Caching for Optimization.
[Example 94-10]# Prepared Statement Caching at the Login Level
databaseLogin.cacheAllStatements();
databaseLogin.setStatementCacheSize(100);
Parameter binding is enabled by default in EclipseLink. To disable
binding, configure at the Login
level, as the following example
shows. For more information, see
How
to Use Parameterized SQL (Parameter Binding) and Prepared Statement
Caching for Optimization.
[Example 94-11]# Disabling Parameter Binding at the Login Level
databaseLogin.dontBindAllParameters();
To enable JDBC batch writing, use Login
method useBatchWriting
,
as this example shows:
[Example 94-12]# Using JDBC Batch Writing
project.getLogin().useBatchWriting();
project.getLogin().setMaxBatchWritingSize(100);
Most JDBC drivers support the run-time configuration of various options to customize driver operation to meet user needs. EclipseLink provides direct support (in API and Workbench) for many of the most important options (see Configuring JDBC Options), as well as more advanced options, as this section describes.
You can also configure additional options by specifying properties (see Configuring Properties).
Note: Not all drivers support all JDBC options. Selecting a combination of options may result in different behavior from one driver to another. Before selecting JDBC options, consult your JDBC driver documentation. |
To specify the advanced options for a relational server (or database) session login, use this procedure:
-
Select a relational server (or database) session in the Navigator. Its properties appear in the Editor.
-
Click the Login tab. The Login tab appears.
-
Click the Options subtab. The Options subtab appears. [Figure 94-7]#Login Tab, Options Subtab, Advanced Options
-
Complete the Advanced Options fields on the tab.
Use this table to enter data in the fields on the Options subtab to select the advanced options to use with this session login:
Option | Description |
---|---|
Force Field Names to Uppercase |
By default, EclipseLink uses the case of field names as returned by the database. If your application expects field names to be uppercase but the database does not return consistent case (for example, if you accessing different databases), enable this option. |
Optimize Data Conversion |
By default, EclipseLink optimizes data access by accessing the data from JDBC in the format the application requires. If you are using an older JDBC driver that does not perform data conversion correctly and conflicts with this optimization, disable this optimization. |
Trim String |
By default, EclipseLink discards the trailing blanks
from |
Properties |
Check this option to enable the use of properties for
this |
Use the following methods of
org.eclipse.persistence.sessions.DatabaseLogin
to configure advanced
options:
-
setShouldForceFieldNamesToUpperCase
–By default, EclipseLink uses the case of field names as returned by the database. If your application expects field names to be uppercase but the database does not return consistent case (for example, if you accessing different databases), use this method. -
setShouldOptimizeDataConversion
–By default, EclipseLink optimizes data access by accessing the data from JDBC in the format the application requires. If you are using an older JDBC driver that does not perform data conversion correctly and conflicts with this optimization, set this tofalse
. -
setShouldTrimStrings
–By default, EclipseLink discards the trailing blanks fromCHAR
field values. To read and writeCHAR
field values literally (including any trailing blanks), set this tofalse
. -
setProperties
–Set this to true to enable the use of properties for thisDatabaseLogin
(see Configuring Properties).
You can configure a session to use Oracle Database proxy authentication
with an Oracle Database platform in JSE applications and JEE
applications using Oracle JDBC driver release 10.1.0.2.0 or later. If an
external connection pool is used, then it should either contain Oracle
connections directly (OracleDataSource
), or the application server
should allow access to the underlying vendor-specific connection in the
managed connection. To achieve the latter, the server platform class in
Eclipselink needs to implement the java.sql.Connection
method
unwrapConnection(java.sql.Connection connection)
.
There is no Workbench support for this feature. To configure EclipseLink to use Oracle Database proxy authentication, you must use Java (see How to Configure Oracle Database Proxy Authentication Using Java).
For more information, see Oracle Database Proxy Authentication.
For information on proxy authentication in JPA applications, see Configuring Oracle Database Proxy Authentication for a JPA Application.
You can use EclipseLink support for Oracle Database proxy authentication by doing the following:
Providing Authenticated Reads and Writes of Secured Data Through the Use of an Exclusive Isolated Client Session
In this configuration, the client session is an isolated client session (see Isolated Client Sessions) that uses an exclusive proxy connection. You must acquire the client session using properties that specify proxy authentication user credentials. Reads and writes of secured data are performed through the proxy-authenticated connection. Reads of nonsecured data occur through nonproxy-authenticated connections.
If you are using Oracle Private Virtual Database (VPD) (see
Isolated
Client Sessions and Oracle Virtual Private Database (VPD)), use this
configuration to set up VPD support entirely in the database. That is,
rather than making the isolated client session execute SQL (see
Using
PostAcquireExclusiveConnection Event Handler and
Using
PreReleaseExclusiveConnection Event Handler), the database performs the
required set up in an after login trigger using the proxy
session_user
.
Providing Authenticated Writes for Database Auditing Purposes with a Client Session
In this configuration, isolated data or exclusive connections are not required. You must acquire the client session using properties that specify the proxy authentication user credentials.
Writes are performed through the proxy-authenticated connection. Reads occur through nonproxy-authenticated connections. This enables the database auditing process to access the user that performed the write operations.
Providing Authenticated Reads and Writes with a Database Session
In this configuration, you use a DatabaseSession
with proxy
properties.
Note: We recommend that you exclusively use server and client sessions in a three-tier environment.
Do not use database sessions in a three-tier environment. Ensure that a database session is used by a single user and not accessed concurrently.
To configure EclipseLink to use Oracle Database proxy authentication, do the following:
-
Decide on the proxy type you want to use and create appropriate users and roles.
-
User Name Authentication: To authenticate a proxy user
sarah
by user name only, create the user account on the Oracle Database using the following:alter user sarah grant connect through dbadminuser
with roles clerk, reports;
In this case, you will need to set the proxy properties shown in this table. [Table 94-2]#
Proxy Properties for User Name Authentication
Property Name Property Value "eclipselink.oracle.proxy-type"
PROXYTYPE_USER_NAME
PROXY_USER_NAME
"sarah"
PROXY_ROLES
String[] {"role1", "role2", ...}
-
User Name and Password Authentication: To authenticate a proxy user
sarah
by user name and password, create the user account on the Oracle Database using the following:alter user sarah grant connect through dbadminuser
authenticated using password
with roles clerk, reports;
In this case, you will need to set the proxy properties shown in this table. [Table 94-3]#
Proxy Properties for User Name and Password Authentication
Property Name Property Value "eclipselink.oracle.proxy-type"
PROXYTYPE_USER_NAME
PROXY_USER_NAME
"sarah"
PROXY_PASSWORD
"passwordforsarah"
PROXY_ROLES
String[] {"role1", "role2", ...}
-
Distinguished Name Authentication: To authenticate a proxy user
sarah
by globally unique distinguished name, create the user account on the Oracle Database using the following:create user sarah identified globally as
'CN=sarah,OU=americas,O=oracle,L=city,ST=ca,C=us';
alter user sarah grant connect through dbadminuser
authenticated using distinguished name
with roles clerk, reports;
In this case, you will need to set the proxy properties shown in this table. [Table 94-4]#
Proxy Properties for Distinguished Name Authentication
Property Name Property Value "eclipselink.oracle.proxy-type"
PROXYTYPE_DISTINGUISHED_NAME
PROXY_DISTINGUISHED_NAME
"CN=sarah,OU=americas,O=oracle,L=city,ST=ca,C=us"
PROXY_ROLES
String[] {"role1", "role2", ...}
-
Certificate Authentication: To authenticate a proxy user
sarah
by encrypted distinguished name, create the user account on the Oracle Database using the following:alter user sarah grant connect through dbadminuser
authenticated using certificate
with roles clerk, reports;
In this case, you will need to set the proxy properties shown in this table. [Table 94-5]#
Proxy Properties for User Name Authentication
Property Name Property Value "eclipselink.oracle.proxy-type"
PROXYTYPE_CERTIFICATE
PROXY_CERTIFICATE
byte[] {`
EncryptedCertificate}`
PROXY_ROLES
String[] {"role1", "role2", ...}
-
-
##Configure your session login using Java code. Do this through a
SessionCustomizer
.//\'\' \'\'If\'\' \'\'using\'\' \'\'Oracle\'\' \'\'VPD\'\' \'\'support,set\'\' \'\'the\'\' \'\'connection\'\' \'\'policy\'\' \'\'to\'\' \'\'exclusive
policy.setShouldUseExclusiveConnection(true);
-
Acquire a proxy-authenticated session using properties that specify the user credentials:
Session session = server.acquireClientSession();
session.setProperty("eclipselink.oracle.proxy-type", OracleConnection.PROXYTYPE_USER_NAME);
session.setProperty(oracle.jdbc.OracleConnection.PROXY_USER_NAME, "sarah");
Category:_EclipseLink_User’s_Guide[Category: EclipseLink User’s Guide] Category:_Release_1[Category: Release 1] Category:_Task[Category: Task]