Chapter 9. Complex and legacy schemas

In this chapter

• Improving the SQL schema with custom DDL
• Integrating with a legacy database
• Mapping composite keys

In this chapter, we focus on the most important part of your system: the database schema, where your collection of integrity rules resides—the model of the real world that you’ve created. If your application can auction an item only once in the real world, your database schema should guarantee that. If an auction always has a starting price, your database model should include an appropriate constraint. If data satisfies all integrity rules, the data is consistent, a term you’ll meet again in section 11.1.

We also assume that consistent data is correct: everything the database states, either explicitly or implicitly, is true; everything else is false. If you want to know more about the theory behind this approach, look up the closed-world assumption (CWA).

Sometimes you can start a project top-down. There is no existing database schema and maybe not even any data—your application is completely new. Many developers like to let Hibernate automatically generate the scripts for a database schema. You’ll probably also let Hibernate deploy the schema on the test database on your development machine or your continuous build systems for integration testing. Later, a DBA will take the generated scripts and write an improved and final schema for production deployment. The first part of this chapter shows you how to improve the schema from within JPA and Hibernate, to make your DBA happy.

At the other end of the spectrum are systems with existing, possibly complex schemas, with years’ worth of data. Your new application is just a small gear in a big machine, and your DBA won’t allow any (sometimes even non-disruptive) changes to the database. You need a flexible object/relational mapping so you don’t have to twist and bend the Java classes too much when things don’t fit right away. This will be the subject of the second half of this chapter, including a discussion of composite primary and foreign keys.

Let’s start with a clean-room implementation and Hibernate-generated schemas.

9.1. Improving the database schema

Hibernate reads your Java domain model classes and mapping metadata and generates schema DDL statements. You can export them into a text file or execute them directly on your database whenever you run integration tests. Because schema languages are mostly vendor-specific, every option you put in your mapping metadata has the potential to bind the metadata to a particular database product—keep this in mind when using schema features.

Hibernate creates the basic schema for your tables and constraints automatically; it even creates sequences, depending on the identifier generator you select. But there are some schema artifacts Hibernate can’t and won’t create automatically. These include all kinds of highly vendor-specific performance options and any other artifacts that are relevant only for the physical storage of data (tablespaces, for example). Besides these physical concerns, your DBA will often supply custom additional schema statements to improve the generated schema. DBAs should get involved early and verify the automatically generated schema from Hibernate. Never go into production with an unchecked automatically generated schema.

If your development process allows, changes made by the DBA can flow back into your Java systems, for you to add to mapping metadata. In many projects, the mapping metadata can contain all the necessary schema changes from a DBA. Then Hibernate can generate the final production schema during the regular build by including all comments, constraints, indexes, and so on.

In the following sections, we show you how to customize the generated schema and how to add auxiliary database schema artifacts (we call them objects sometimes; we don’t mean Java objects here). We discuss custom data types, additional integrity rules, indexes, and how you can replace some of the (sometimes ugly) auto-generated artifact names produced by Hibernate.

First, let’s look at how you can add custom SQL statements to Hibernate’s automatic schema-generation process.

9.1.1. Adding auxiliary database objects

You can hook the following three types of custom SQL scripts into Hibernate’s schema-generation process:

• The create script executes when the schema is generated. A custom create script can run before, after, or instead of Hibernate’s automatically generated scripts. In other words, you can write an SQL script that runs before or after Hibernate generates tables, constraints, and so on from your mapping metadata.
• The drop script executes when Hibernate removes schema artifacts. Just like the create script, a drop script can run before, after, or instead of Hibernate’s automatically generated statements.
• The load script always executes after Hibernate generates the schema, as the last step after creation. Its main purpose is importing test or master data, before your application or unit test runs. It can contain any kind of SQL statement, including DDL statements such as ALTER, if you want to further customize the schema.

This customization of the schema-generation process is actually standardized; you configure it with JPA properties in persistence.xml for a persistence unit.

Listing 9.1. Custom schema-generation properties in persistence.xml

Path: /model/src/main/resources/META-INF/persistence.xml

1. By default, Hibernate expects one SQL statement per line in scripts. This switches to the more convenient multiline extractor. SQL statements in scripts are terminated with semicolon. You can write your own org.hibernate.tool.hbm2ddl.ImportSqlCommand-Extractor implementation if you want to handle the SQL script in a different way.
2. This property defines when the create and drop scripts should be executed. Your custom SQL scripts will contain CREATE DOMAIN statements, which must be executed before the tables using these domains are created. With these settings, the schema generator runs the create script first before reading your ORM metadata (annotations, XML files) and creating the tables. The drop script executes after Hibernate drops the tables, giving you a chance to clean up anything you created. Other options are -metadata (ignore custom script sources) and script (only use a custom script source; ignore ORM metadata in annotations and XML files).
3. This is the location of the custom SQL script for creation of the schema. The path is (a) the location of the script resource on the classpath; (b) the location of the script as a file:// URL; or, if neither (a) nor (b) matches, (c) the absolute or relative file path on the local file system. This example uses (a).
4. This is the custom SQL script for dropping the schema.
5. This load script runs after the tables have been created.

We’ve mentioned that DDL is usually highly vendor-specific. If your application has to support several database dialects, you may need several sets of create/drop/load scripts to customize the schema for each database dialect. You can solve this with several persistence unit configurations in the persistence.xml file.

Alternatively, Hibernate has its own proprietary configuration for schema customization in an hbm.xml mapping file.

Listing 9.2. Custom schema generation with a Hibernate proprietary configuration

<hibernate-mapping xmlns="http://www.hibernate.org/xsd/orm/hbm">
<enter/>
    <database-object>
        <create>
            CREATE ...

        </create>
        <drop>
            DROP ...
        </drop>
        <dialect-scope name="org.hibernate.dialect.H2Dialect"/>
        <dialect-scope name="org.hibernate.dialect.PostgreSQL82Dialect"/>
    </database-object>
<enter/>
</hibernate-mapping>

package org.jpwh.model.complexschemas;
<enter/>
import org.hibernate.dialect.Dialect;
import org.hibernate.boot.model.relational.AbstractAuxiliaryDatabaseObject;
<enter/>
public class CustomSchema

    extends AbstractAuxiliaryDatabaseObject {
<enter/>
    public CustomSchema() {
        addDialectScope("org.hibernate.dialect.Oracle9Dialect");
    }
<enter/>
    @Override
    public String[] sqlCreateStrings(Dialect dialect) {
        return new String[]{"[CREATE statement]"};
    }
<enter/>
    @Override
    public String[] sqlDropStrings(Dialect dialect) {
        return new String[]{"[DROP statement]"};
    }
}

<hibernate-mapping xmlns="http://www.hibernate.org/xsd/orm/hbm">
<enter/>
    <database-object>
        <definition class="org.jpwh.model.complexschemas.CustomSchema"/>
        <dialect-scope name="org.hibernate.dialect.H2Dialect"/>
        <dialect-scope name="org.hibernate.dialect.PostgreSQL82Dialect"/>
    </database-object>
<enter/>
</hibernate-mapping>

9.1.2. SQL constraints

Systems that ensure data integrity only in application code are prone to data corruption and often degrade the quality of the database over time. If the data store doesn’t enforce rules, a trivial undetected application bug can cause unrecoverable problems such as incorrect or lost data.

In contrast to ensuring data consistency in procedural (or object-oriented) application code, database management systems allow you to implement integrity rules with declarations, as a database schema. The advantages of declarative rules are fewer possible errors in code and a chance for the DBMS to optimize data access.

In SQL databases, we identify four kinds of rules:

• Domain constraints— A domain is (loosely speaking, and in the database world) a data type in a database. Hence, a domain constraint defines the range of possible values a particular data type can handle. For example, an INTEGER data type is usable for integer values. A CHAR data type can hold character strings: for example, all characters defined in ASCII or some other encoding. Because we mostly use data types built-in the DBMS, we rely on the domain constraints as defined by the vendor. If supported by your SQL database, you can use the (often limited) support for custom domains to add additional constraints for particular existing data types, or create user-defined data types (UDT).
• Column constraints— Restricting a column to hold values of a particular domain and type creates a column constraint. For example, you declare in the schema that the EMAIL column holds values of VARCHAR type. Alternatively, you could create a new domain called EMAIL_ADDRESS with further constraints and apply it to a column instead of VARCHAR. A special column constraint in an SQL database is NOT NULL.
• Table constraints— An integrity rule that applies to several columns or several rows is a table constraint. A typical declarative table constraint is UNIQUE: all rows are checked for duplicate values (for example, each user must have a distinct email address). A rule affecting only a single row but multiple columns is “the auction end time has to be after the auction start time.”
• Database constraints— If a rule applies to more than one table, it has database scope. You should already be familiar with the most common database constraint, the foreign key. This rule guarantees the integrity of references between rows, usually in separate tables, but not always (self-referencing foreign key constraints aren’t uncommon). Other database constraints involving several tables aren’t uncommon: for example, a bid can only be stored if the auction end time of the referenced item hasn’t been reached.

Most (if not all) SQL database management systems support these kinds of constraints and the most important options of each. In addition to simple keywords such as NOT NULL and UNIQUE, you can usually also declare more complex rules with the CHECK constraint that applies an arbitrary SQL expression. Still, integrity constraints are one of the weak areas in the SQL standard, and solutions from vendors can differ significantly.

Furthermore, non-declarative and procedural constraints are possible with database triggers that intercept data-modification operations. A trigger can then implement the constraint procedure directly or call an existing stored procedure.

Integrity constraints can be checked immediately when a data-modification statement is executed, or the check can be deferred until the end of a transaction. The violation response in SQL databases is usually rejection without any possibility of customization. Foreign keys are special because you can typically decide what should happen with ON DELETE or ON UPDATE for referenced rows.

Hibernate passes on database constraint violations in error exceptions; check whether the exception in your transaction has a cause, somewhere in the exception chain, of type org.hibernate.exception.ConstraintViolationException. This exception can provide more information about the error, such as the name of the failed database constraint.

Let’s take a closer look at the implementation of integrity constraints.

Adding domain and column constraints

The SQL standard includes domains, which unfortunately are rather limited and often not supported by the DBMS. If your system supports SQL domains, you can use them to add constraints to data types.

In your custom SQL create script, define an EMAIL_ADDRESS domain based on the VARCHAR data type:

Path: /model/src/main/resources/complexschemas/CreateScript.sql.txt

create domain if not exists
  EMAIL_ADDRESS as varchar
  check (position('@', value) > 1);

The additional constraint is a check of the presence of an @ symbol in the string. The (relatively minor) advantage of such domains in SQL is the abstraction of common constraints into a single location. Domain constraints are always checked immediately when data is inserted and modified.

You can now use this domain in your mappings, like a built-in data type:

Path: /model/src/main/java/org/jpwh/model/complexschemas/custom/User.java

Several constraints are present in this mapping. The NOT NULL constraint is common; you’ve seen it many times before. The second is a UNIQUE column constraint; users can’t have duplicate email addresses. At the time of writing, there was unfortunately no way to customize the name of this single-column unique constraint in Hibernate; it will get an ugly auto-generated name in your schema. Last, the columnDefinition refers to the domain you’ve added with your custom create script. This definition is an SQL fragment, exported into your schema directly, so be careful with database-specific SQL.

If you don’t want to create domains first, apply the CHECK keyword directly as a -single-column constraint:

Path: /model/src/main/java/org/jpwh/model/complexschemas/custom/User.java

This constraint restricts valid username values to a maximum length of 15 characters, and the string can’t begin with admin to avoid confusion. You can call any SQL functions supported by your DBMS; the columnDefinition is always passed through into the exported schema.

Note that you have a choice: creating and using a domain or adding a single--column constraint has the same effect. Domains are usually easier to maintain and avoid duplicating.

At the time of writing, Hibernate doesn’t support its proprietary annotation @org.hibernate.annotations.Check on individual properties; you use it for table-level constraints.

Table-level constraints

@Entity
@org.hibernate.annotations.Check(
    constraints = "AUCTIONSTART < AUCTIONEND"
)
public class Item {
<enter/>
    @NotNull
    protected Date auctionStart;
<enter/>
    @NotNull
    protected Date auctionEnd;
<enter/>
    // ...
}

@Entity
@Table(
    name = "USERS",
    uniqueConstraints =
        @UniqueConstraint(
            name = "UNQ_USERNAME_EMAIL",
            columnNames = { "USERNAME", "EMAIL" }
        )
)
public class User {
<enter/>
    // ...
}

The last kinds of constraints we discuss are database-wide rules that span several tables.

Database constraints

A user can only make bids until an auction ends. Your database should guarantee that invalid bids can’t be stored so that whenever a row is inserted into the BID table, the CREATEDON timestamp of the bid is checked against the auction ending time. This kind of constraint involves two tables: BID and ITEM.

You can create a rule that spans several tables with a join in a subselect in any SQL CHECK expression. Instead of referring only to the table on which the constraint is declared, you may query (usually for the existence or nonexistence of a particular piece of information) a different table. The problem is that you can’t use the @org.hibernate.annotations.Check annotation on either the Bid or Item class. You don’t know which table Hibernate will create first.

Therefore, put your CHECK constraint into an ALTER TABLE statement that executes after all the tables have been created. A good place is the load script, because it always executes at that time:

Path: /model/src/main/resources/complexschemas/LoadScript.sql.txt

alter table BID
  add constraint AUCTION_BID_TIME
  check(
    CREATEDON <= (
      select i.AUCTIONEND from ITEM i where i.ID = ITEM_ID
    )
  );

A row in the BID table is now valid if its CREATEDON value is less than or equal to the auction end time of the referenced ITEM row.

By far the most common rules that span several tables are referential integrity rules. They’re widely known as foreign keys, which are a combination of two things: a key value copy from a related row and a constraint that guarantees that the referenced value exists. Hibernate creates foreign key constraints automatically for all foreign key columns in association mappings. If you check the schema produced by Hibernate, you’ll notice that these constraints also have automatically generated database identifiers—names that aren’t easy to read and that make debugging more difficult. You see this kind of statement in the generated schema:

alter table BID add constraint FKCFAEEDB471BF59FF
    foreign key (ITEM_ID) references ITEM

This statement declares the foreign key constraint for the ITEM_ID column in the BID table, referencing the primary key column of the ITEM table. You can customize the name of the constraint with the foreignKey option in an @JoinColumn mapping:

Path: /model/src/main/java/org/jpwh/model/complexschemas/custom/Bid.java

@Entity
public class Bid {
<enter/>
    @ManyToOne
    @JoinColumn(
        name = "ITEM_ID",
        nullable = false,
        foreignKey = @ForeignKey(name = "FK_ITEM_ID")
    )
    protected Item item;
<enter/>
    // ...
}

A foreignKey attribute is also supported in @PrimaryKeyJoinColumn, @MapKeyJoinColumn, @JoinTable, @CollectionTable, and @AssociationOverride mappings.

The @ForeignKey annotation has some rarely needed options we haven’t shown:

• You can write your own foreignKeyDefinition, an SQL fragment such as -FOREIGN KEY ([column]) REFERENCES [table]([column]) ON UPDATE [action]. Hibernate will use this SQL fragment instead of the provider-generated fragment, it can be in the SQL dialect supported by your DBMS.
• The ConstraintMode setting is useful if you want to disable foreign key generation completely, with the value NO_CONSTRAINT. You can then write the foreign key constraint yourself with an ALTER TABLE statement, probably in a load script as we’ve shown.

Naming constraints properly is not only good practice, but also helps significantly when you have to read exception messages.

This completes our discussion of database integrity rules. Next, we look at some optimization you might want to include in your schema for performance reasons.

9.1.3. Creating indexes

Indexes are a key feature when optimizing the performance of a database application. The query optimizer in a DBMS can use indexes to avoid excessive scans of the data tables. Because they’re relevant only in the physical implementation of a database, indexes aren’t part of the SQL standard, and the DDL and available indexing options are product specific. You can, however, embed the most common schema artifacts for typical indexes in mapping metadata.

Many queries in CaveatEmptor will probably involve the username of a User entity. You can speed up these queries by creating an index for the column of this property. Another candidate for an index is the combination of USERNAME and EMAIL columns, which you also use frequently in queries. You can declare single or multicolumn indexes on the entity class with the @Table annotation and its indexes attribute:

Path: /model/src/main/java/org/jpwh/model/complexschemas/custom/User.java

@Entity
@Table(
    name = "USERS",
    indexes = {
        @Index(
            name = "IDX_USERNAME",
            columnList = "USERNAME"
        ),

        @Index(
            name = "IDX_USERNAME_EMAIL",
            columnList = "USERNAME, EMAIL"
        )
    }
)
public class User {
<enter/>
    // ...
}

If you don’t provide a name for the index, a generated name is used.

We don’t recommend adding indexes to your schema ad hoc because it feels like an index could help with a performance problem. Get the excellent book SQL Tuning by Dan Tow (Tow, 2003) if you want to learn efficient database-optimization techniques and especially how indexes can get you closer to the best-performing execution plan for your queries.

Customizing the database schema is often possible only if you’re working on a new system with no existing data. If you have to deal with an existing legacy schema, one of the most common issues is working with natural and composite keys.

9.2. Handling legacy keys

We mentioned in section 4.2.3 that we think natural primary keys can be a bad idea. Natural keys often make it difficult to change the data model when business requirements change. They may even, in extreme cases, impact performance. Unfortunately, many legacy schemas use (natural) composite keys heavily; and for the reason we discourage the use of composite keys, it may be difficult to change the legacy schema to use non-composite natural or surrogate keys. Therefore, JPA supports natural and composite primary and foreign keys.

9.2.1. Mapping a natural primary key

If you encounter a USERS table in a legacy schema, it’s likely that USERNAME is the primary key. In this case, you have no surrogate identifier that Hibernate generates automatically. Instead, your application has to assign the identifier value when saving an instance of the User class:

Path: /examples/src/test/java/org/jpwh/test/complexschemas/NaturalPrimaryKey.java

User user = new User("johndoe");
em.persist(user);

Here the user’s name is an argument of the only public constructor of the User class:

Path: /model/src/main/java/org/jpwh/model/complexschemas/naturalprimarykey/User.java

@Entity
@Table(name = "USERS")
public class User {
<enter/>
    @Id
    protected String username;
<enter/>
    protected User() {
    }
<enter/>
    public User(String username) {
        this.username = username;
    }
<enter/>
    // ...
}

Hibernate calls the protected no-argument constructor when it loads a User from the database and then assigns the username field value directly. When you instantiate a User, call the public constructor with a username. If you don’t declare an identifier generator on the @Id property, Hibernate expects the application to take care of the primary key value assignment.

Composite (natural) primary keys require a bit more work.

9.2.2. Mapping a composite primary key

Suppose the primary key of the USERS table is a composite of the two columns USERNAME and DEPARTMENTNR. You write a separate composite identifier class that declares just the key properties and call this class UserId:

Path: /model/src/main/java/org/jpwh/model/complexschemas/compositekey/embedded/UserId.java

1. This class has to be @Embeddable and Serializable—any type used as an identifier type in JPA must be Serializable.
2. You don’t have to mark the properties of the composite key as @NotNull; their database columns are automatically NOT NULL when embedded as the primary key of an entity.
3. The JPA specification requires a public no-argument constructor for an embeddable identifier class. Hibernate accepts protected visibility.
4. The only public constructor should have the key values as arguments.
5. You have to override the equals() and hashCode() methods with the same semantics the composite key has in your database. In this case, this is a straightforward comparison of the username and departmentNr values.

These are the important parts of the UserId class. You probably will also have some getter methods to access the property values.

Now map the User entity with this identifier type as an @EmbeddedId:

Path: /model/src/main/java/org/jpwh/model/complexschemas/compositekey/embedded/User.java

Just as for regular embedded components, you can override individual attributes and their mapped columns, as you saw in section 5.2.3. Figure 9.1 shows the database schema.

Figure 9.1. The USERS table has a composite primary key.

Any public constructor of User should require an instance of UserId, to force you to provide a value before saving the User (an entity class must have another, no-argument constructor, of course):

Path: /examples/src/test/java/org/jpwh/test/complexschemas/CompositeKeyEmbeddedId.java

UserId id = new UserId("johndoe", "123");
User user = new User(id);
em.persist(user);

This is how you load an instance of User:

Path: /examples/src/test/java/org/jpwh/test/complexschemas/CompositeKeyEmbeddedId.java

UserId id = new UserId("johndoe", "123");
User user = em.find(User.class, id);
assertEquals(user.getId().getDepartmentNr(), "123");

Next, suppose DEPARTMENTNR is a foreign key referencing a DEPARTMENT table, and that you wish to represent this association in the Java domain model as a many-to-one association.

9.2.3. Foreign keys in composite primary keys

Look at the schema in figure 9.2.

Figure 9.2. Part of the USERS composite primary key is also a foreign key.

Your first mapping option is with a dedicated annotation @MapsId, designed for this purpose. Start by renaming the departmentNr property to departmentId in the -UserId embedded identifier class introduced in the previous section:

Path: /model/src/main/java/org/jpwh/model/complexschemas/compositekey/mapsid/UserId.java

@Embeddable
public class UserId implements Serializable {
<enter/>
    protected String username;
<enter/>
    protected Long departmentId;
<enter/>
    // ...
}

The type of the property is now Long, not String. Next, add the department association with an @ManyToOne mapping to the User entity class:

Path: /model/src/main/java/org/jpwh/model/complexschemas/compositekey/mapsid/User.java

@Entity
@Table(name = "USERS")
public class User {
<enter/>
    @EmbeddedId
    protected UserId id;
<enter/>
    @ManyToOne
    @MapsId("departmentId")
    protected Department department;
<enter/>
    public User(UserId id) {
        this.id = id;
    }
<enter/>
    // ...
}

The @MapsId annotation tells Hibernate to ignore the value of UserId#departmentId when saving an instance of User. Hibernate uses the identifier of the Department assigned to User#department when saving a row into the USERS table:

Path: /examples/src/test/java/org/jpwh/test/complexschemas/CompositeKeyMapsId.java

Hibernate ignores whatever value you set as the UserId#departmentId when saving; here it’s even set to null. This means you always need a Department instance when storing a User. JPA calls this a derived identifier mapping.

When you load a User, only the identifier of a Department is necessary:

Path: /examples/src/test/java/org/jpwh/test/complexschemas/CompositeKeyMapsId.java

UserId id = new UserId("johndoe", DEPARTMENT_ID);
User user = em.find(User.class, id);
assertEquals(user.getDepartment().getName(), "Sales");

We don’t like this mapping strategy much. This is a better variation without @MapsId:

Path: /model/src/main/java/org/jpwh/model/complexschemas/compositekey/readonly/User.java

With a simple insertable=false, updatable=false, you make the User#department property read-only. That means you can only query data by calling someUser.get-Department(), and you have no public setDepartment() method. The property responsible for the database updates of the DEPARTMENTID column in the USERS table is UserId#departmentId.

Therefore, you now have to set the department’s identifier value when saving a new User:

Path: /examples/src/test/java/org/jpwh/test/complexschemas/CompositeKeyReadOnly.java

Note that User#getDepartment() returns null because you didn’t set the value of this property. Hibernate only populates it when you load a User:

Path: /examples/src/test/java/org/jpwh/test/complexschemas/CompositeKeyReadOnly.java

UserId id = new UserId("johndoe", DEPARTMENT_ID);
User user = em.find(User.class, id);
assertEquals(user.getDepartment().getName(), "Sales");

Many developers prefer to encapsulate all these concerns in a constructor:

Path: /model/src/main/java/org/jpwh/model/complexschemas/compositekey/readonly/User.java

@Entity
@Table(name = "USERS")
public class User {
<enter/>
    public User(String username, Department department) {
        if (department.getId() == null)
            throw new IllegalStateException(
                "Department is transient: " + department
            );
        this.id = new UserId(username, department.getId());
        this.department = department;
    }
<enter/>
    // ...
}

This defensive constructor enforces how a User has to be instantiated and correctly sets all identifier and property values.

If the USERS table has a composite primary key, a foreign key referencing the table must also be a composite key.

9.2.4. Foreign keys to composite primary keys

For example, the association from Item to User, the seller, could require a mapping of a composite foreign key. Look at the schema in figure 9.3.

Figure 9.3. The item’s seller is represented with a composite foreign key in the ITEM table.

Hibernate can hide this detail in the Java domain model. Here’s the mapping of the Item#seller property:

Path: /model/src/main/java/org/jpwh/model/complexschemas/compositekey/manytoone/Item.java

@Entity
public class Item {
<enter/>
    @NotNull
    @ManyToOne
    @JoinColumns({
        @JoinColumn(name = "SELLER_USERNAME",
                    referencedColumnName = "USERNAME"),
        @JoinColumn(name = "SELLER_DEPARTMENTNR",
                    referencedColumnName = "DEPARTMENTNR")
    })
    protected User seller;
<enter/>
    // ...
}

You might not have seen the @JoinColumns annotation before; it’s a list of the composite foreign key columns underlying this association. Make sure you provide the referencedColumnName attribute, to link the source and target of the foreign key. Hibernate unfortunately won’t complain if you forget, and you may end up with a wrong column order in the generated schema.

In legacy schemas, a foreign key sometimes doesn’t reference a primary key.

9.2.5. Foreign key referencing non-primary keys

A foreign key constraint on the SELLER column in the ITEM table ensures that the seller of the item exists by requiring the same seller value to be present on some column in some row on some table. There are no other rules; the target column doesn’t need a primary key constraint or even a unique constraint. The target table can be any table. The value can be a numeric identifier of the seller or a customer number string; only the type has to be the same for the foreign key reference source and target.

Of course, a foreign key constraint usually references primary key column(s). Nevertheless, legacy databases sometimes have foreign key constraints that don’t follow this simple rule. Sometimes a foreign key constraint references a simple unique column—a natural non-primary key. Let’s assume that in CaveatEmptor, and as shown in figure 9.4, you need to handle a legacy natural key column called CUSTOMERNR on the USERS table:

Figure 9.4. The item’s seller foreign key references a non-primary column in USERS

Path: /model/src/main/java/org/jpwh/model/complexschemas/naturalforeignkey/User.java

@Entity
@Table(name = "USERS")
public class User implements Serializable {
<enter/>
    @Id
    @GeneratedValue(generator = Constants.ID_GENERATOR)
    protected Long id;
<enter/>
    @NotNull
    @Column(unique = true)
    protected String customerNr;
<enter/>
    // ...
}

So far, this is nothing special; you’ve seen such a simple unique property mapping before. The legacy aspect is the SELLER_CUSTOMERNR column in the ITEM table, with a foreign key constraint referencing the user’s CUSTOMERNR instead of the user’s ID:

Path: /model/src/main/java/org/jpwh/model/complexschemas/naturalforeignkey/Item.java

@Entity
public class Item {
<enter/>
    @NotNull
    @ManyToOne
    @JoinColumn(
        name = "SELLER_CUSTOMERNR",
        referencedColumnName = "CUSTOMERNR"
    )
    protected User seller;
<enter/>
    // ...
}

You specify the referencedColumnName attribute of @JoinColumn to declare this relationship. Hibernate now knows that the referenced target column is a natural key, and not the primary key, and manages the foreign key relationship accordingly.

If the target natural key is a composite key, use @JoinColumns instead as in the previous section. Fortunately, it’s often straightforward to clean up such a schema by refactoring foreign keys to reference primary keys—if you can make changes to the database that don’t disturb other applications sharing the data.

This completes our discussion of natural, composite, and foreign key–related problems you may have to deal with when you try to map a legacy schema. Let’s move on to another interesting special strategy: mapping basic or embedded properties of an entity to a secondary table.

9.3. Mapping properties to secondary tables

We’ve already shown the @SecondaryTable annotation in an inheritance mapping in section 6.5. It helped to break out properties of a particular subclass into a separate table. This generic functionality has more uses—but be aware that a properly designed system should have, simplified, more classes than tables.

Suppose that in a legacy schema, you aren’t keeping a user’s billing address information with the other user details in the USERS main entity table, but in a separate table. Figure 9.5 shows this schema. The user’s home address is stored in the columns STREET, ZIPCODE, and CITY of the USERS table. The user’s billing address is stored in the BILLING_ADDRESS table, which has the primary key column USER_ID, which is also a foreign key constraint referencing the ID primary key of the USERS table.

Figure 9.5. Breaking out the billing address data into a secondary table

To map this schema, declare the secondary table for the User entity and then how Hibernate should join it with @SecondaryTable:

Path: /model/src/main/java/org/jpwh/model/complexschemas/secondarytable/User.java

@Entity
@Table(name = "USERS")
@SecondaryTable(
    name = "BILLING_ADDRESS",
    pkJoinColumns = @PrimaryKeyJoinColumn(name = "USER_ID")
)
public class User {
<enter/>
    protected Address homeAddress;
<enter/>
    @AttributeOverrides({
        @AttributeOverride(name = "street",
            column = @Column(table = "BILLING_ADDRESS",
                             nullable = false)),
        @AttributeOverride(name = "zipcode",
            column = @Column(table = "BILLING_ADDRESS",
                             length = 5,
                             nullable = false)),
        @AttributeOverride(name = "city",
            column = @Column(table = "BILLING_ADDRESS",
                             nullable = false))
    })
    protected Address billingAddress;
<enter/>
    // ...
}

The User class has two properties of embedded type: homeAddress and billing-Address. The first is a regular embedded mapping, and the Address class is -@Embeddable.

Just as in section 5.2.3, you can use the @AttributeOverrides annotation to override the mapping of embedded properties. Then, @Column maps the individual properties to the BILLING_ADDRESS table, with its table option. Remember that an @AttributeOverride replaces all mapping information for a property: any annotations on the Address fields are ignored if you override. Therefore, you have to specify nullability and length again in the @Column override.

We’ve shown you a secondary table mapping example with an embeddable property. Of course, you could also break out simple basic properties like the username string in a secondary table. Keep in mind that reading and maintaining these mappings can be a problem, though; you should only map legacy unchangeable schemas with secondary tables.

9.4. Summary

• Focus on the database schema.
• You can add additional integrity rules to a Hibernate-generated database schema. You now know how to execute custom create, drop, and load SQL scripts.
• We discussed using SQL constraints: domain, columns, table, and database -constraints.
• We also covered using custom SQL data types, as well as check, unique, and foreign key constraints.
• You saw some common issues you have to resolve when dealing with legacy schemas, and specifically keys.
• You learned about several types of mapping: natural primary keys, composite primary keys, foreign keys in composite primary keys, foreign keys to composite primary keys, and foreign keys referencing non-primary keys.
• You saw how to move properties of an entity to a secondary table.