Maps and Entities and JPA, OH MY!

Ryan McCullough Development Technology, Programming Leave a Comment


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:

	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_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:

public class TemplateEntry implements Serializable {
	private static final long serialVersionUID = 1L;
	long id;
	long orgId;

	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:

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

  long templateId;

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


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!

See Also:  Got Formik? Yup!

Also, feel free to clone my code and check it out!

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

0 0 vote
Article Rating
Notify of
Inline Feedbacks
View all comments