I have seen quite a bit of discussion about how to address database relationships that involve a base table and an internationalized (I18N) table with JPA. The difficult issue with this relationship is that from a database perspective it is a typical One-to-Many, however from an application perspective it is a One-to-One with the locale determining which row from the I18N table is returned with the base table. This article describes one possible solution for addressing this problem.

From what I have been able to determine, there is no specific support in JPA to address this. There is a mechanism in OpenJPA but the locale is fixed so it can’t be changed dynamically at run time. With the lack of native JPA support for I18N, I decided to develop my own strategy for tackling the issue.

Given the two tables:

CREATE TABLE PRODUCT
(
	product_id integer NOT NULL PRIMARY KEY,
	sku integer NOT NULL
);

CREATE TABLE PRODUCT_I18N
(
	product_id integer NOT NULL ,
	locale char(5) NOT NULL ,
	description varchar(255),
	FOREIGN KEY(product_id) REFERENCES Product (product_id)
);

I created a Java model class called Product.java with the appropriate JPA annotations to map the variables to database columns.

@Entity
@Table(name="PRODUCT", uniqueConstraints = { })
public class Product implements Serializable {
	private static final long serialVersionUID = -5515140641967068961L;
	private Integer productId;
	private Integer sku;

	//Description of the product (I18N)
	private String locale;
	private String description;

	@Id
	@Column(name="PRODUCT_ID", unique=true, nullable=false, insertable=true, updatable=true)
	public Integer getProductId() {
		return this.productId;
	}

	public void setProductId(Integer productId) {
		this.productId = productId;
	}

	@Column(name="SKU", unique=true, nullable=false, insertable=true, updatable=true)
	public Integer getSku() {
		return this.sku;
	}

	public void setSku(Integer sku) {
		this.sku = sku;
	}

	@Transient
	public String getLocale() {
		return this.locale;
	}

	public void setLocale(String locale) {
		this.locale = locale;
	}

	@Transient
	public String getDescription() {
		return this.description;
	}

	public void setDescription(String description) {
		this.description = description;
	}
}

Note that the locale and description fields are marked as Transient. This is to prevent JPA from trying to use them when interacting with the database. These properties will be populated in the DAO. In my implementation there is also a ProductI18N class that maps the locale and description to the appropriate columns. I am not including an example because there is nothing out of the ordinary from a typical annotated model class.

The DAO class has some additional logic in each method to deal with the I18N values.

public class ProductDAO {
    @PersistenceContext
    private EntityManager entityManager;

    private ProductI18NDAO productI18NDAO;

    @Transactional
    public void persist(Product transientInstance) {
        try {
            entityManager.persist(transientInstance);
        } catch (RuntimeException re) {
            throw re;
        }

        // Save the localized description.
        ProductI18N productI18N = new ProductI18N(transientInstance.getProductId(),
        	transientInstance.getLocale(), transientInstance.getDescription());
        productI18NDAO.persist(productI18N);
    }

    @Transactional
    public void remove(Product persistentInstance) {
        // Remove all the localized descriptions.
        productI18NDAO.removeAllForProduct(persistentInstance.getProductId());

        try {
        	// The remove only works if the entity is first retrieved with getReference.
        	Product reference = entityManager.getReference(Product.class, persistentInstance.getProductId());
        	if (reference != null) {
                entityManager.remove(reference);
        	}
        } catch (RuntimeException re) {
            throw re;
        }
    }

    /**
     * The merge method will update an existing row.
     */
    @Transactional
    public Product merge(Product detachedInstance) {
        Product product;

        try {
        	product = entityManager.merge(detachedInstance);
        } catch (RuntimeException re) {
            log.error("merge: failed for " + detachedInstance.getProductName(), re);
            throw re;
        }

        // Update the localized description.
        ProductI18N productI18N = new ProductI18N(detachedInstance.getProductId(),
        	detachedInstance.getLocale(), detachedInstance.getProductDescription());
        productI18N = productI18NDAO.merge(productI18N);

        product.setLocale(productI18N.getProductI18NId().getLocale());
        product.setProductDescription(productI18N.getProductDescription());

        return product;
    }

    public Product findById(Integer productId, String locale) {
        Product product = null;

        try {
        	product = entityManager.find(Product.class, productId);
        } catch (NoResultException nre) {
        	log.warn("findById: did not find productI18N for id = " + productId);
        } catch (RuntimeException re) {
            throw re;
        }

        if (product != null) {
            // Retrieve the localized description.
	        ProductI18N productI18N = productI18NDAO.findById(productId, locale);
	        product.setLocale(productI18N.getProductI18NId().getLocale());
	        product.setProductDescription(productI18N.getProductDescription());
        }

        return product;
    }

	@Autowired
	public void setProductI18NDAO(ProductI18NDAO productI18NDAO) {
		this.productI18NDAO = productI18NDAO;
	}
}

There is nothing magical about how this works. The base properties are handled directly with JPA and the transient properties are handled in a separate call to the database through the I18N DAO. This is not particularly elegant but all the persistence code is encapsulated in the DAO so from an application perspective the model object will always maintain the correct description based on the locale. The ProductI18NDAO is fairly straight forward so I have not included an example of that, however there is one non-standard method, removeAllForProduct, that is unique to this implementation so an example of that is included here:

	@Transactional
	public void removeAllForProduct(Integer productId) {
		try {
			Query sqlCmd = entityManager.createQuery("delete from ProductI18N pi where productId = ?1");
			sqlCmd.setParameter(1, productId);
			sqlCmd.executeUpdate();
		} catch (RuntimeException re) {
			throw re;
		}
	}

This approach has proven very effective in addressing the I18N issue with JPA. The application has a clean interface to the model object, including the I18N data. If at some point there is an enhancement to JPA that allows this to be handled through an annotation it should only require minor changes to the DAO to take advantage of it.

Tags: I18N, java, jpa
Posted in: Development, Main