In Oracle stored procedures are typically written in Oracle's PL/SQL language. PL/SQL in Oracle supports some additional data-types, that Oracle does not support through SQL or JDBC.
These include types such as BOOLEAN, TABLE and RECORD. Accessing these types or procedures is difficult from Java, as these types are not supported by JDBC. One workaround is to wrap the PL/SQL stored procedures with normal stored procedures that transform the PL/SQL types to standard SQL/JDBC types, such as INT, VARRAY (Array), and OBJECT TYPE (Struct). Some JPA providers have extended support for calling PL/SQL stored procedures.
TopLink / EclipseLink : Support PL/SQL stored procedures and functions using the @NamedPLSQLStoredProcedureQuery, @NamedPLSQLStoredFunctionQuery
annotations or XML, or the PLSQLStoredProcedureCall, PLSQLStoredFunctionCall classes.
[edit] Structured Object-Relational Data Types
Back in the hay day of object-oriented databases (OODBMS) many of the relational database vendors decided to added object-oriented concepts to relational data. These new hybrid databases were called Object-Relational in that they could store both object and relational data. These object-relational data-types were standardized as part of SQL3 and support was added for them from Java in the JDBC 2.0 API. Although there was lots of hype around the new forms of data, object-relational data never caught on much, as people seemed to prefer their standard relational data. I would not normally recommend using object-relational data, as relational data is much more standard, but if you have really complex data, it may be something to investigate.
Some common object-relational database features include:
• Object types (structures)
• Arrays and array types
• Nested tables
• Inheritance
• Object ids (OIDs)
• Refs
Databases that support object-relational data include:
• Oracle
• DB2
• PostgreSQL
The basic model allows you to define Structs or Object-types to represent your data, the structures can have nested structures, arrays of basic data or other structures, and refs to other structures. You can then store a structure in a normal relational table column, or create a special table to store the structures directly. Querying is basic SQL, with a few extensions to handle traversing the special types.
JPA does not support object-relational data-types, but some JPA providers may offer some support.
TopLink / EclipseLink : Support object-relational data-types through their @Struct, @Structure, @Array annotations and XML, or their
ObjectRelationalDataTypeDescriptor and mapping classes. Custom support is also offered for Oracle spatial database JGeometry structures and other structured data-types using the @StructConverter annotation or XML.
See also,
• Complex data stored procedures (Blog)
[edit] XML Data Types
With the advent of XML databases, many relational database decided to add enhanced XML support. Although it was always possible to store XML in a relational database just using a VARCHAR or CLOB column, having the database aware of the XML data does have its advantages. The main advantage is databases that offer XML support allow querying of the XML data using XPath or XQuery syntax. Some databases also allow the XML data to be stored more efficiently than in Lob storage.
Databases with XML support include:
• Oracle (XDB)
• DB2
• PostgreSQL
JPA has no extended support for XML data, although it is possible to store an XML String into the database, just mapped as a Basic. Some JPA provider may offer extended XML data support. Such as query extensions, or allow mapping an XML DOM.
If you wish to map the XML data into objects, you could make use of the JAXB specification. You may even be able to integrate this with your JPA objects.
TopLink / EclipseLink : Support Oracle XDB XMLType columns using their
DirectToXMLTypeMapping. XMLTypes can be mapped either as String or as an XML DOM (Document). Query extensions are provided for XPath queries within Expression queries. EclipseLink also includes a JAXB implementation for object-XML mapping.
[edit] Filters
Some times it is desirable to filter some of the contents of a table from all queries. This is normally because the table is shared, either by multiple types, applications, tenants, or districts, and the JPA application is only interested in a subset of the rows. It may also be that the table includes historical or archive rows that should be ignored by the JPA application.
JPA does not provide any specific support for filtering data, but there are some options available. Inheritance can be used to include a type check on the rows for a class. For example, if you had a STATUS column in an EMPLOYEE table, you could define an Employee and a CurrentEmployee subclass whose discriminator STATUS was ACTIVE, and always use CurrentEmployee in the application. Similarly you could define an ACMEEmployee subclass that used the TENANT column as its class discriminator of value ACME. Another solution is to use database views to filter the data and map the entities to the views.
These solutions do not work with dynamic filtering, where the filter criteria parameters are not know until runtime (such as tenant, or district). Also complex criteria cannot be modeled through inheritance, although database views should still work. One solution is to always append the criteria to any query, such as appending a JPQL string, or JPA Criteria in the application code. Virtual Private Database (VPD) support may also provide a solution. Some databases such as Oracle support VPD, allow context based filtering of rows based on the connected user or proxy certificate.
Some JPA providers have specific support for filtering data.
TopLink / EclipseLink : Support filtering data through their @AdditionalCriteria annotation and XML. This allows an arbitrary JPQL fragment to be appended to all queries for the entity. The fragment can contain parameters that can be set through persistence unit or context properties at runtime. Oracle VPD is also supported, include Oracle proxy authentication and isolated data.
[edit] History
A common desire in database applications is to maintain a record and history of the database changes. This can be used for tracking and auditing purposes, or to allow undoing of changes, or to keep a record of the system's data over time. Many database have auditing functionality that allows some level of tracking changes made to the data. Some databases such as Oracle's
Flashback feature allow the automatic tracking of history at the row level, and even allow
querying on past versions of the data.
It is also possible for an application to maintain its own history through its data model. All that is required is to add a START and END timestamp column to the table. The current row is then the one in which the END timestamp is null. When a row is inserted its START timestamp will be set to the current time. When a row is updated, instead of updating the row a new row will be inserted with the same id and data, but a different START timestamp, and the old row will be updated to set its END timestamp to the current time. The primary key of the table will need to have the START timestamp added to it.
History can also be used to avoid deletion. Instead of deleting a row, the END timestamp can just be set to the current time. Another solution is to add a DELETED boolean column to the table, and record when a row is deleted.
The history data could either be stored in-place in the altered table, or the table could be left to only contain the current version of the data, and a mirror history table could be added to store the data. In the mirror case, database triggers could be used to write to the history table. For the in- place case a database view could be used to give a view of the table as of the current time. To query the current data from a history table, any query must include the clause where the END is NULL. To query as of a point in time, the where clause must include where the point in time is between the START and END timestamps.
JPA does not define any specific history support.
Oracle flashback can be used with JPA, but any queries for historical data will need to use native SQL.
If a mirror history table is used with triggers, JPA can still be used to query to current data. A subclass or sibling class could also be mapped to the history table to allow querying of history data.
If a database view is used, the JPA could be used by mapping the Entity to the view. If a history table is used JPA could still be used to map to the table, and a start and end attribute could be added to the object. Queries for the current data could append the current time to the query. Relationships are more difficult, as JPA requires relationships to be by primary key, and historical relationships would not be.
TopLink / EclipseLink : Support Oracle flashback querying as well as application specific history. Historical queries can be defined using the query hint
"eclipselink.history.as-of" or Expression queries. Automatic tracking of history is also supported using the HistoryPolicy API that supports maintaining and querying a mirror history table.
[edit] Logical Deletes
[edit] Auditing
See, Auditing and Security.
[edit] Replication
Data replication can be used to backup data, for fault tolerance and fail-over, or for load balancing and scaling the database.
For replication, changes are written to multiple databases, either by the application, JPA
provider, or database back-end. For fail-over, if one of the databases goes down, the other can be used without loss of data or application downtime. For load-balancing, read requests can be load balanced across the replicated databases to reduce the load on each database, and improve the scalability of the application.
Most enterprise database support some form of automatic backup or replication. Clustered database such as Oracle RAC also allow for load balancing, fail-over and high availability. If your database supports replication or clustering, then it is normally transparent to JPA. A specialize DataSource (such as Oracle UCP, or WebLogic GridLink) may need to be used to handle load-balancing and fail-over.
JPA does not define any specific support for data replication, but some JPA provider provide replication support. If your database does not support replication, you can implement it yourself through having multiple persistence units, and persisting and merging your objects to both databases.
TopLink / EclipseLink : Support replication, load-balancing and fail-over. Replication and load balancing is supported through EclipseLink's partitioning support using the @ReplicationPartitioning, and @RoundRobinPartitioning annotations and XML.
[edit] Partitioning
Data partitioning can be used to scale an application across multiple database machines, or with a clustered database such as Oracle RAC.
Partitioning splits your data across each of the database nodes. There is horizontal partitioning, and vertical partitioning. Vertical partitioning is normally the easiest to implement. You can just put half of your classes on one database, and the other half on another. Ideally the two sets would be isolated from each other and not have any cross database relationships. This can be done directly in JPA, by having two different persistence units, one for each database.
For horizontal partitioning you need to split your data across multiple database nodes. Each database node will have the same tables, but each node's table will only store part of the data. You can partition the data by the data values, such as range partitioning, value partitioning, hash partitioning, or even round robin. JPA does not define any data partitioning support, so you either need to define a different class per partition, or use JPA vendor specific functionality.
TopLink / EclipseLink : Support both horizontal and vertical data partitioning. Hash, value, range, pinned and custom partitioning is supported at the Session, Entity, and Query level. Partitioning is support through the @Partitioning, @HashPartitioning, @RangePartitioning, @ValuePartitioning, @PinnedPartitioning, and
@Partitioned annotations and XML. See also,
• Data Partitioning - Scaling the Database (Blog)
[edit] Data Integration
[edit] EIS, NoSQL, Legacy, XML, and Non-relational Data
See, EIS and Non-relational Data Sources