Introduction

A client I’m working with has an email templating system that needs an upgrade! The current design utilizes a denormalized table that needed to grow a column every time a new unique token is needed. After a review of the offerings through JPA, I was happy to see that JPA had some support for java.util.Map through joins through a variety of the @MapKey annotation.

In this post, I’ll demonstrate the less frequently used methodology of applying and populating a Map of entities using a single table and a composite key.

Our Example

We first developed a migration script to vertically arranged template table into a normalized design that converted the oh-so-many key columns into a series of vertically-oriented rows with a single key column representing the one-off token name columns:

CREATE TABLE OLD_EMAIL_TEMPLATES (
	org_id      INT             NOT NULL,
	template_id INT             NOT NULL,
   	TEXT_TO     VARCHAR(256)        NULL,
   	TEXT_FROM   VARCHAR(256)        NULL,
   	TEXT_BODY   VARCHAR(256)        NULL,
   	TEXT_HEADER VARCHAR(256)        NULL,
   	TEXT_FOOTER VARCHAR(256)        NULL,
   	TEXT_CUSTOM VARCHAR(256)        NULL,
   	TEXT_CUST01 VARCHAR(256)        NULL,
   	TEXT_CUST02 VARCHAR(256)        NULL,
   	...
   	TEXT_CUST43 VARCHAR(256)        NULL,
);

With an improved data design, we can allow each customer a greater level of flexibility, eliminating a significant portion of technical debt, and accelerating the pace of future implementations. This is an example of that implementation:

CREATE TABLE template_entry 
(
	id          INT             NOT NULL,
	org_id      INT             NOT NULL,
	template_id INT             NOT NULL,
   	key         VARCHAR(50)     NOT NULL, 
     value       VARCHAR(255)     NULL
);

To satisfy a standard JPA entity implementation, a database view was needed to distinctly select the two categorizing columns of the new data model:

CREATE VIEW template_categories AS SELECT DISTINCT org_id,  template_id FROM  template_entry;

We assembled an Entity to express each Key/Value entry in the new data design:

@Entity(name="template_entry")
@IdClass(TemplateEntryId.class)
public class TemplateEntry implements Serializable {
	
	private static final long serialVersionUID = 1L;
	
	long id;
	
	@Id
	long orgId;

	@Id
	long templateId;

	private String key;
	
     private String value;     
}

We also needed an entity that consumes the view of the TEMPLATE_ENTRY composite key and joins the entries mapped by the KEY column:

@Entity
@Table(name = "template_categories") // NEEDED FOR VIEW CONSUMPTION
@IdClass(TemplateCategoryId.class)
public class TemplateCategory implements Serializable {
  private static final long serialVersionUID = 1L;
  @Id
  long orgId;

  @Id
  long templateId;

  @MapKey(name="key")  
  @OneToMany(targetEntity = TemplateEntry.class)
  @JoinColumns( {
    @JoinColumn(name="orgId", referencedColumnName="orgId"),
    @JoinColumn(name="templateId",referencedColumnName="templateId")
  })
  private Map<String, TemplateEntry> keyMap;
}

Conclusion

As my understanding of JPA continues to mature, I’ll hopefully learn to offer a more direct approach to mapping this kind of data. As I was hard-pressed to find working examples I felt many could benefit from a more concrete example to explore and hopefully improve upon. I hope you found this useful, and I look forward to hearing about your discoveries as well! Thanks for reading!

Also, feel free to clone my code and check it out!
https://github.com/in-the-keyhole/jpa-entity-map-examples

If you have any questions about this example, please comment or contact me through git!