Basics

A basic attribute is one where the attribute class is a simple type such as String, Number, Date or a primitive. A basic attribute's value can map directly to the column value in the database. The following table summarizes the basic types and the database types they map to.

Java type	Database type
String (char, char[])	VARCHAR (CHAR, VARCHAR2, CLOB, TEXT)
Number (BigDecimal, BigInteger, Integer, Double, Long, Float, Short, Byte)	NUMERIC (NUMBER, INT, LONG, FLOAT, DOUBLE)
int, long, float, double, short, byte	NUMERIC (NUMBER, INT, LONG, FLOAT, DOUBLE)
byte[]	VARBINARY (BINARY, BLOB)
boolean (Boolean)	BOOLEAN (BIT, SMALLINT, INT, NUMBER)
java.util.Date	TIMESTAMP (DATE, DATETIME)
java.sql.Date	DATE (TIMESTAMP, DATETIME)
java.sql.Time	TIME (TIMESTAMP, DATETIME)
java.sql.Timestamp	TIMESTAMP (DATETIME, DATE)
java.util.Calendar	TIMESTAMP (DATETIME, DATE)
java.lang.Enum	NUMERIC (VARCHAR, CHAR)
java.util.Serializable	VARBINARY (BINARY, BLOB)

In JPA a basic attribute is mapped through the @Basic annotation or the <basic> element. The types and conversions supported depend on the JPA implementation and database platform. Some JPA implementations may support conversion between many different data-types or additional types, or have extended type conversion support, see the advanced section for more details. Any basic attribute using a type that does not map directly to a database type can be serialized to a binary database type.

The easiest way to map a basic attribute in JPA is to do nothing. Any attributes that have no other annotations and do not reference other entities will be automatically mapped as basic, and even serialized if not a basic type. The column name for the attribute will be defaulted, named the same as the attribute name, as uppercase. Sometimes auto-mapping can be unexpected if you have an attribute in your class that you did not intend to have persisted. You must mark any such non-persistent fields using the @Transient annotation or <transient> element.

Although auto-mapping makes rapid prototyping easy, you typically reach a point where you want control over your database schema. To specify the column name for a basic attribute the @Column annotation or <column> element is used. The column annotation also allows for other information to be specified such as the database type, size, and some constraints.

Example of basic mapping annotations

@Entity
public class Employee {
  // Id mappings are also basic mappings.
  @Id
  @Column(name="ID")
  private long id;       
  @Basic
  @Column(name="F_NAME")
  private String firstName;
  // The @Basic is not required in general because it is the default.
  @Column(name="L_NAME")
  private String lastName;
  // Any un-mapped field will be automatically mapped as basic and column name defaulted.
  private BigDecimal salary;       
  // Non-persistent fields must be marked as transient.
  @Transient
  private EmployeeService service;
  ...
}

Example of basic mapping XML

<entity name="Employee" class="org.acme.Employee" access="FIELD">
    <attributes>
        <id name="id">
            <column name="ID"/>
        </id>
        <basic name="firstName">
            <column name="F_NAME"/>
        </basic>
        <basic name="lastName">
            <column name="L_NAME"/>
        </basic>
        <transient name="service"/>
    </attributes>
</entity>

Common Problems

Translating Values

See Conversion

Truncated Data

A common issue is that data, such as Strings, written from the object are truncated when read back from the database. This is normally caused by the column length not being large enough to handle the object's data. In Java there is no maximum size for a String, but in a database VARCHAR field, there is a maximum size. You must ensure that the length you set in your column when you create the table is large enough to handle any object value. For very large Strings CLOBs can be used, but in general CLOBs should not be over used, as they are less efficient than a VARCHAR.

If you use JPA to generate your database schema, you can set the column length through the Column annotation or element, see Column Definition and Schema Generation.

How to exclude fields from INSERT or UPDATE statements, or default values in triggers?

See Insertable, Updatable

Advanced

Temporal, Dates, Times, Timestamps and Calendars

Dates, times, and timestamps are common types both in the database and in Java, so in theory mappings these types should be simple, right? Well sometimes this is the case and just a normal Basic mapping can be used, however sometimes it becomes more complex.

Some databases do not have DATE and TIME types, only TIMESTAMP fields, however some do have separate types, and some just have DATE and TIMESTAMP. Originally in Java 1.0, Java only had a java.util.Date type, which was both a date, time and milliseconds. In Java 1.1 this was expanded to support the common database types with java.sql.Date, java.sql.Time, and java.sql.Timestamp, then to support internationalization Java created the java.util.Calendar type and virtually deprecated (almost all of the methods) the old date types (which JDBC still uses).

If you map a Java java.sql.Date type to a database DATE, this is just a basic mapping and you should not have any issues (ignore Oracle's DATE type that is/was a timestamp for now). You can also map java.sql.Time to TIME, and java.sql.Timestamp to TIMESTAMP. However if you have a java.util.Date or java.util.Calendar in Java and wish to map it to a DATE or TIME, you may need to indicate that the JPA provider perform some sort of conversion for this. In JPA the @Temporal annotation or <temporal> element is used to map this. You can indicate that just the DATE or TIME portion of the date/time value be stored to the database. You could also use Temporal to map a java.sql.Date to a TIMESTAMP field, or any other such conversion.

Example of temporal annotation

@Entity
public class Employee {
    ...
    @Basic
    @Temporal(DATE)
    private Calendar startDate;
    ...
}

Example of temporal XML

<entity name="Employee" class="org.acme.Employee" access="FIELD">
    <attributes>
        ...
        <basic name="startDate">
            <temporal>DATE</temporal>
        </basic>
    </attributes>
</entity>

Milliseconds

The precision of milliseconds is different for different temporal classes and database types, and on different databases. The java.util.Date and Calendar classes support milliseconds. The java.sql.Date and java.sql.Time classes do not support milliseconds. The java.sql.Timestamp class supports nanoseconds.

On many databases the TIMESTAMP type supports milliseconds. On Oracle prior to Oracle 9, there was only a DATE type, which was a date and a time, but had no milliseconds. Oracle 9 added a TIMESTAMP type that has milliseconds (and nanoseconds), and now treats the old DATE type as only a date, so be careful using it as a timestamp. MySQL has DATE, TIME and DATETIME types. DB2 has a DATE, TIME and TIMESTAMP types, the TIMESTAMP supports microseconds. Sybase and SQL Server just have a DATETIME type which has milliseconds, but at least on some versions has precision issues, it seems to store an estimate of the milliseconds, not the exact value.

If you use timestamp version locking you need to be very careful of your milliseconds precision. Ensure your database supports milliseconds precisely otherwise you may have issues, especially if the value is assigned in Java, then differs what gets stored on the database, which will cause the next update to fail for the same object.

In general I would not recommend using a timestamp and as primary key or for version locking. There are too many database compatibility issues, as well as the obvious issue of not supporting two operations in the same millisecond.

Timezones

Temporals become a lot more complex when you start to consider time zones, internationalization, eras, locals, day-light savings time, etc. In Java only Calendar supports time zones. Normally a Calendar is assumed to be in the local time zone, and is stored and retrieved from the database with that assumption. If you then read that same Calendar on another computer in another time zone, the question is if you will have the same Calendar or will you have the Calendar of what the original time would have been in the new time zone? It depends on if the Calendar is stored as the GMT time, or the local time, and if the time zone was stored in the database.

Some databases support time zones, but most database types do not store the time zone. Oracle has two special types for timestamps with time zones, TIMESTAMPTZ (time zone is stored) and TIMESTAMPLTZ (local time zone is used). Some JPA providers may have extended support for storing Calendar objects and time zones.

TopLink, EclipseLink : Support the Oracle TIMESTAMPTZ and TIMESTAMPLTZ types using the @TypeConverter annotation and XML.

Forum Posts

Investigation of storing timezones in MySQL

Joda-Time

Joda-Time is a commonly used framework for date/time usage in Java. It replaces Java Calendars which many people find difficult to use and have poor performance. There is no standard Joda-Time support in JPA, but a Converter can be used to convert from Joda-Time classes and database types.

TopLink, EclipseLink : The base product offers no specific Joda-Time support, but there is a custom converter provided by a third party library, joda-time-eclipselink-integration.

Enums

Java Enums are typically used as constants in an object model. For example an Employee may have a gender of enum type Gender (MALE, FEMALE).

By default in JPA an attribute of type Enum will be stored as a Basic to the database, using the integer Enum values as codes (i.e. 0, 1). JPA also defines an @Enumerated annotation and <enumerated> element (on a <basic>) to define an Enum attribute. This can be used to store the Enum as the STRING value of its name (i.e. "MALE", "FEMALE").

For translating Enum types to values other than the integer or String name, such as character constants, see Translating Values.

Example of enumerated annotation

public enum Gender {
    MALE,
    FEMALE
}

@Entity
public class Employee {
    ...
    @Basic
    @Enumerated(EnumType.STRING)
    private Gender gender;
    ...
}

Example of enumerated XML

<entity name="Employee" class="org.acme.Employee" access="FIELD">
    <attributes>
        ...
        <basic name="gender">
            <enumerated>STRING</enumerated>
        </basic>
    </attributes>
</entity>

LOBs, BLOBs, CLOBs and Serialization

A LOB is a Large OBject, such as a BLOB (Binary LOB), or a CLOB (Character LOB). It is a database type that can store a large binary or string value, as the normal VARCHAR or VARBINARY types typically have size limitations. A LOB is often stored as a locator in the database table, with the actual data stored outside of the table. In Java a CLOB will normally map to a String, and a BLOB will normally map to a byte[], although a BLOB may also represent some serialized object.

By default in JPA any Serializable attribute that is not a relationship or a basic type (String, Number, temporal, primitive), will be serialized to a BLOB field.

JPA defines the @Lob annotation and <lob> element (on a <basic>) to define that an attribute maps to a LOB type in the database. The annotation is just a hint to the JPA implementation that this attribute will be stored in a LOB, as LOBs may need to be persisted specially. Sometimes just mapping the LOB as a normal Basic will work fine as well.

Various databases and JDBC drivers have various limits for LOB sizes. Some JDBC drivers have issues beyond 4k, 32k or 1meg. The Oracle thin JDBC drivers had a 4k limitation in some versions for binding LOB data. Oracle provided a workaround for this limitation, which some JPA providers support. For reading LOBs, some JDBC drivers prefer using streams, some JPA providers also support this option.

Typically the entire LOB will be read and written for the attribute. For very large LOBs reading the value always, or reading the entire value may not be desired. The fetch type of the Basic could be set to LAZY to avoid reading a LOB unless accessed. Support for LAZY fetching on Basic is optional in JPA, so some JPA providers may not support it. A workaround, which is often a good idea in general given the large performance cost of LOBs, is to store the LOB in a separate table and class and define a OneToOne to the LOB object instead of a Basic. If the entire LOB is never desired to be read, then it should not be mapped. It is best to use direct JDBC to access and stream the LOB in this case. It may be possible to map the LOB to a java.sql.Blob/java.sql.Clob in your object to avoid reading the entire LOB, but these require a live connection, so may have issues with detached objects.

Example of lob annotation

@Entity
public class Employee {
    ...
    @Basic(fetch=FetchType.LAZY)
    @Lob
    private byte[] picture;
    ...
}

Example of lob XML

<entity name="Employee" class="org.acme.Employee" access="FIELD">
    <attributes>
        ...
        <basic name="picture" fetch="LAZY">
            <lob/>
        </basic>
    </attributes>
</entity>

Lazy Fetching

The fetch attribute can be set on a Basic mapping to use LAZY fetching. By default all Basic mappings are EAGER, which means the column is selected whenever the object is selected. By setting the fetch to LAZY, the column will not be selected with the object. If the attribute is accessed, then the attribute value will be selected in a separate database select. Support for LAZY is an optional feature of JPA, so some JPA providers may not support it. Typically support for lazy on basics will require some form of byte code weaving, or dynamic byte code generation, which may have issues in certain environments or JVMs, or may require preprocessing your application's persistence unit jar.

Only attributes that are rarely accessed should be marked lazy, as accessing the attribute causes a separate database select, which can hurt performance. This is especially true if a large number of objects is queried. The original query will require one database select, but if each object's lazy attribute is accessed, this will require n database selects, which can be a major performance issue.

Using lazy fetching on basics is similar to the concept of fetch groups. Lazy basics is basically support for a single default fetch group. Some JPA providers support fetch groups in general, which allow more sophisticated control over what attributes are fetched per query.

TopLink, EclipseLink : Support lazy basics and fetch groups. Fetch groups can be configured through the EclipseLink API using the FetchGroup class.

Optional

A Basic attribute can be optional if its value is allowed to be null. By default everything is assumed to be optional, except for an Id, which can not be optional. Optional is basically only a hint that applies to database schema generation, if the persistence provider is configured to generate the schema. It adds a NOT NULL constraint to the column if false. Some JPA providers also perform validation of the object for optional attributes, and will throw a validation error before writing to the database, but this is not required by the JPA specification. Optional is defined through the optional attribute of the Basic annotation or element.

Column Definition and Schema Generation

There are various attributes on the Column annotation and element for database schema generation. If you do not use JPA to generate your schema you can ignore these. Many JPA providers do provide the feature of auto generation of the database schema. By default the Java types of the object's attributes are mapped to their corresponding database type for the database platform you are using. You may require configuring your database platform with your provider (such as a persistence.xml property) to allow schema generation for your database, as many database use different type names.

The columnDefinition attribute of Column can be used to override the default database type used, or enhance the type definition with constraints or other such DDL. The length, scale and precision can also be set to override defaults. Since the defaults for the length are just defaults, it is normally a good idea to set these to be correct for your data model's expected data, to avoid data truncation. The unique attribute can be used to define a unique constraint on the column, most JPA providers will automatically define primary key and foreign key constraints based on the Id and relationship mappings.

JPA does not define any options to define an index. Some JPA providers may provide extensions for this. You can also create your own indexes through native queries

Example of column annotations

@Entity
public class Employee {
  @Id
  @Column(name="ID")
  private long id;
  @Column(name="SSN", unique=true, nullable=false, description="description")
  private long ssn;
  @Column(name="F_NAME", length=100)
  private String firstName;
  @Column(name="L_NAME", length=200)
  private String lastName;
  @Column(name="SALARY", scale=10, precision=2)
  private BigDecimal salary;       
  @Column(name="S_TIME", columnDefinition="TIMESTAMPTZ")
  private Calendar startTime;
  @Column(name="E_TIME", columnDefinition ="TIMESTAMPTZ")
  private Calendar endTime;
  ...
}

Example of column XML

<entity name="Employee" class="org.acme.Employee" access="FIELD">
    <attributes>
        <id name="id">
            <column name="ID"/>
        </id>
        <basic name="ssn">
            <column name="SSN" unique="true" optional="false"/>
        </basic>
        <basic name="firstName">
            <column name="F_NAME" length="100"/>
        </basic>
        <basic name="lastName">
            <column name="L_NAME" length="200"/>
        </basic>
        <basic name="startTime">
            <column name="S_TIME" columnDefinition="TIMESTAMPTZ"/>
        </basic>
        <basic name="endTime">
            <column name="E_TIME" columnDefinition="TIMESTAMPTZ"/>
        </basic>
    </attributes>
</entity>

If using BigDecimal with Postgresql, JPA maps salary to a table column of type NUMERIC(38,0). You can adjust scale and precision for BigDecimal within the @Column annotation.

   @Column(precision=8, scale=2)
   private BigDecimal salary;

Insertable, Updatable / Read Only Fields / Returning

The Column annotation and XML element defines insertable and updatable options. These allow for this column, or foreign key field to be omitted from the SQL INSERT or UPDATE statement. These can be used if constraints on the table prevent insert or update operations. They can also be used if multiple attributes map to the same database column, such as with a foreign key field through a ManyToOne and Id or Basic mapping. Setting both insertable and updatable to false, effectively mark the attribute as read-only.

insertable and updatable can also be used in the database table defaults, or auto assigns values to the column on insert or update. Be careful in doing this though, as this means that the object's values will be out of synch with the database, unless it is refreshed. For IDENTITY or auto assigned id columns a GeneratedValue should normally be used, instead of setting insertable to false. Some JPA providers also support returning auto assigned fields values from the database after insert or update operations. The cost of refreshing or returning fields back into the object can affect performance, so it is normally better to initialize field values in the object model, not in the database.

TopLink, EclipseLink : Support returning insert and update values back into the object using the ReturnInsert and ReturnUpdate annotations and XML elements.

Converters (JPA 2.1)

A common problem in storing values to the database is that the value desired in Java differs from the value used in the database. Common examples include using a boolean in Java and a 0, 1 or a 'T', 'F' in the database. Other examples are using a String in Java and a DATE in the database, or mapping custom Java types such as Joda-Time types, or a Money type.

JPA 2.1 defines the @Converter, @Convert annotations and <converter>, <convert> XML elements. A Converter is a user defined class that provides custom conversion routines in Java code. It must implement the AttributeConverter interface and be annotated with the @Converter annotation (or specified in XML). A Converter can be used in one of two ways. Normally it is specified on a mapping using the @Convert annotation or <convert> XML element. Another option, if converting a custom type, is to have the Converter applied to any mapped attribute that has that type. To define such as global converter the autoApply flag is added to the @Converter annotation. The @Convert disableConversion flag can be used to disable a global converter from being applied. The @Convert attributeName option can be used to override inherited or embeddable conversions.

Example Converter

@Entity
public class Employee {
    ...
    @Convert(converter=BooleanTFConverter.class)
    private Boolean isActive;
    ...
}

@Converter
public class BooleanTFConverter implements AttributeConverter<Boolean, String>{
    @Override
    public String convertToDatabaseColumn(Boolean value) {
        if (Boolean.TRUE.equals(value)) {
            return "T";
        } else {
            return "F";
        }
    }
    @Override
    public Boolean convertToEntityAttribute(String value) {
        return "T".equals(value);
    }
}

Example global Converter

@Entity
public class Employee {
    ...
    private Boolean isActive;
    ...
}

@Converter(autoApply=true)
public class BooleanTFConverter implements AttributeConverter<Boolean, String>{
    @Override
    public String convertToDatabaseColumn(Boolean value) {
        if (Boolean.TRUE.equals(value)) {
            return "T";
        } else {
            return "F";
        }
    }
    @Override
    public Boolean convertToEntityAttribute(String value) {
        return "T".equals(value);
    }
}

Conversion

Previous to JPA 2.1 there was no standard way to convert between a data-type and an object-type. One way to accomplish this was to translate the data through property get/set methods.

@Entity
public class Employee {
    ...
    private boolean isActive;
    ...
    @Transient
    public boolean getIsActive() {
        return isActive;
    }
    public void setIsActive(boolean isActive) {
        this.isActive = isActive;
    }
    @Basic
    private String getIsActiveValue() {
        if (isActive) {
            return "T";
        } else {
            return "F";
        }
    }
    private void setIsActiveValue(String isActive) {
        this.isActive = "T".equals(isActive);
    }
}

Also for translating date/times see, Temporals.

As well some JPA providers have special conversion support.

TopLink, EclipseLink : Support translation using the @Convert, @Converter, @ObjectTypeConverter and @TypeConverter annotations and XML.

Custom Types

JPA defines support for most common database types, however some databases and JDBC driver have additional types that may require additional support.

Some custom database types include:

TIMESTAMPTZ, TIMESTAMPLTZ (Oracle)
TIMESTAMP WITH TIMEZONE (Postgres)
XMLTYPE (Oracle)
XML (DB2)
NCHAR, NVARCHAR, NCLOB (Oracle)
Struct (STRUCT Oracle)
Array (VARRAY Oracle)
BINARY_INTEGER, DEC, INT, NATURAL, NATURALN, BOOLEAN (Oracle)
POSITIVE, POSITIVEN, SIGNTYPE, PLS_INTEGER (Oracle)
RECORD, TABLE (Oracle)
SDO_GEOMETRY (Oracle)
LOBs (Oracle thin driver)

To handle persistence to custom database types you may be able to use a Converter or special feature of your JPA provider. Otherwise you may need to mix raw JDBC code with your JPA objects. Some JPA providers provide custom support for many custom database types, some also provide custom hooks for adding your own JDBC code to support a custom database type.

TopLink, EclipseLink : Support several custom database types including, TIMESTAMPTZ, TIMESTAMPLTZ, XMLTYPE, NCHAR, NVARCHAR, NCLOB, object-relational Struct and Array types, PLSQL types, SDO_GEOMETRY and LOBs.