From Wikibooks, the open-content textbooks collection

[edit] Advanced Topics

[edit] Views

A database VIEW is a virtual view of a table or query. Views are useful for hiding the complexity of a table, set of tables, or data-set.

In JPA you can map to a VIEW the same as a table, using the @Table annotation. You can then map each column in the view to your object's attributes. Views are normally read-only, so object's mapping to views are normally also read-only. In most databases views can also be updatable depending on how complex to query is that they encapsulate. Even for complex queries database triggers can normally be used to update into the view.

Views can often be used in JPA to workaround mapping limitations. For example if a table join is not supported by JPA, or a database function is desired to be called to transform data, this can normally be done inside a view, so JPA can just map to the simplified data.

Using views does require database expertise, and the definition of views can be database dependent.

[edit] Interfaces

Interfaces can be used for two main purposes in a model. The first is as a public interface that defines the public API for the class. The second is as a common type that allows multiple distinct implementers.

[edit] Public Interfaces

If you have a public interface in JPA, you just need to map the implementation class and are fine for the most part. One issue is that you need to use the implementation class for queries, as JPA does not know about the interface. For JPQL the default alias is also the implementation class, but you could redefine this to be the public interface by setting the name of the Entity to be the public interface.

Some JPA providers allow interfaces to be defined.

TopLink / EclipseLink : Support defining and querying on public interfaces using a DescriptorCustomizer and the InterfacePolicy.

[edit] Example public interface alias

@Entity(name="Employee")
public class EmployeeImpl {
  ...
}

[edit] Interface Types

If you have a common interfaces with multiple distinct implementers, this can have some issues. If you use the interface to define a variable relationship, then this is difficult to map. JPA has no direct support for interfaces or variable relationships. You could change the interface to be a abstract class, then use TABLE_PER_CLASS inheritance to map it. Otherwise, you could split the relationship into multiple relationships, one per each implementer, or you could just remove the relationship and query for the related objects instead. Querying on an interface is also difficult, you would need to query on each implementer of the interface, and then union the results in memory.

Some JPA providers have support for mapping interfaces, and for variable relationships.

TopLink / EclipseLink : Support mapping interfaces using a SessionCustomizer and a RelationalDescriptor and InterfacePolicy. Variable relationships can be defined using the @VariableOneToOne annotation or XML.

[edit] Stored Procedures

A stored procedure is a procedure or function that resides on the database. Stored procedures are typically written in some database specific language that is similar to SQL, such as PL/SQL on Oracle. Some databases such as Oracle also support stored procedures written in Java.

Stored procedures can be useful to perform batch data processing tasks. By writing the task in the database, it avoids the cost of sending the data to and from the database client, so can operate much more efficiently. Stored procedures can also be used to access database specific functionality that can only be accessed on the server. Stored procedures can also be used if strict security requirements as required, to avoid giving users access to the raw tables or unverified SQL operations. Some legacy application have also been written in database procedural languages, and need to be integrated with.

The disadvantages of using stored procedures is they are less flexible that using SQL, and require developing and maintaining functionality that is often written in a different language than the application developers may be used to, and difficult to develop and debug, and typically using a limited procedural programming language. There is also a general misconception that using stored procedures will improve performance, in that if you put the same SQL the application is executing inside a stored procedure it will somehow become faster. This is a false, and normally the opposite is true, as stored procedures restrict the dynamic ability of the persistence layer to optimize data retrieval. Stored procedures only improve performance when they use more optimal SQL than the application, typically when they perform an entire task on the database.

JPA does not have any direct support for stored procedures. Some types of stored procedures can be executed in JPA through using native queries. Native queries in JPA allow any SQL that returns nothing, or returns a database result set to be executed. The syntax to execute a stored procedure depends on the database. JPA does not support stored procedures that use OUTPUT or INOUT parameters. Some databases such as DB2, Sybase and SQL Server allow for stored procedures to return result sets. Oracle does not allow results sets to be returned, only OUTPUT parameters, but does define a CURSOR type that can be returned as an OUTPUT parameter. Oracle also supports stored functions, that can return a single value. A stored function can normally be executed using a native SQL query by selecting the function value from the Oracle DUAL table.

Some JPA providers have extended support for stored procedures, some also support overriding any CRUD operation for an Entity with a stored procedure or custom SQL. Some JPA providers have support for CURSOR OUTPUT parameters.

TopLink / EclipseLink : Support stored procedures using the @NamedStoredProcedureQuery annotation or XML, or the StoredProcedureCall class. Overriding any CRUD operation for a class or relationship are also supported using a DescriptorCustomizer and the DescriptorQueryManager class. CURSOR OUTPUT parameters are supported.

[edit] Example executing a stored procedure on Oracle

EntityManager em = getEntityManager();
Query query = em.createNativeQuery("BEGIN VALIDATE_EMP(P_EMP_ID=>?); END;");
query.setParameter(1, empId);
query.executeUpdate();

[edit] PL/SQL Stored Procedures

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 using the the PLSQLStoredProcedureCall class.

[edit] Structured Object-Relational Data Types

[edit] XML Data Types

[edit] Filters

[edit] History

[edit] Logical Deletes

[edit] Auditing

How to Audit your Data?

JPA gives you advanced options to improve and automate the quality of your data. The most useful options are:

• @Version (automated update of a date/timestamp field of a entity)
• @PrePersist (automated called before your entity will be persisted)
• @PreUpdate (automated called before your entity will be updated)

For example you want to track which user updates the data at which time:

package com.ctlmb.vip.backend.util;
 
import java.io.Serializable;
import java.sql.Timestamp;
 
import javax.ejb.EJBContext;
import javax.ejb.EJBException;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.persistence.Column;
import javax.persistence.MappedSuperclass;
import javax.persistence.PrePersist;
import javax.persistence.PreUpdate;
import javax.persistence.Transient;
import javax.persistence.Version;
 
 
/**
 * This class automates the auditing of the data.
 * @author sandro.roeder
 */
@MappedSuperclass
public abstract class BaseEntity implements Serializable {
     private static final long serialVersionUID = 5358827562227811055L;
 
     protected EJBContext getContext() {
        try {
	       InitialContext context = new InitialContext();
	       return (EJBContext)context.lookup("java:comp/EJBContext");
        } catch (NamingException e) {
            throw new EJBException(e);
        }
    };
 
    @Column(name="CHG_TIMESTAMP")
    @Version
    private Timestamp chgTimestamp;
    @Column(name="CHG_USER")
    private String chgUser;
 
    @PrePersist
    @PreUpdate
    void updateChgUser() {
        this.setChgUser(UserUtil.getLoggedOnUser(getContext()).getName());
    }
 
    public final Timestamp getChgTimestamp() {
        return chgTimestamp;
    }
 
    public final void setChgTimestamp(Timestamp chgTimestamp) {
        this.chgTimestamp = chgTimestamp;
    }
 
    public final String getChgUser() {
        return chgUser;
    }
 
    public final void setChgUser(String chgUser) {
        this.chgUser = chgUser;
    }
}

[edit] Replication