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:  Storybook with React

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!

Related Posts

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