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).

Major new features in JPA 2
  • Schema generation and execution of custom SQL scripts during bootstrap is now standardized and can be configured on a persistence unit.
  • You can map and customize schema artifacts such as indexes and foreign key names with standard annotations.
  • You can map foreign keys/many-to-one associations in composite primary keys with @MapsId as “derived identity.”

9.1. Improving the database schema

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

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

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

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

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

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

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

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

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

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
    )
  );

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/>
    // ...
}

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/>
    // ...
}

9.2. Handling legacy keys

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

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