Take a look at my book list at it-rezension.de. You may recognize the buttons for the different languages. Most books are described with German texts, but some have English description too (and there will be more soon).

The business rule is: Display text in choosen language. If there is no such text, then display the text in default language (wich is German).

The book object is nothing but a simple JPA entity.

package de.muellerbruehl.books.entities;

import de.muellerbruehl.books.enums.Language;
import java.io.Serializable;
import java.util.List;
import javax.persistence.*;

/**
  * This entity provides information about books like title,
  * publisher and especial information about its review.
  * @author mmueller
  */
@Entity
@Table(name = "Book")
public class Book implements Serializable {

       private static final long serialVersionUID = 1L;
       @Id
       @GeneratedValue(strategy = GenerationType.IDENTITY)
       @Column(name = "bookId")
       private Integer _id;
       @Column(name = "bookTitle")
       private String _title;
       @Column(name = "bookSubtitle")
       private String _subTitle;
       @Column(name = "bookAuthor")
       private String _author;
       @Column(name = "bookPublisher")
       private String _publisher;
       @Column(name = "bookYear")
       private Integer _year;
       @Column(name = "bookLanguage")
       @Enumerated(EnumType.STRING)
       private Language _language;
       @Column(name = "bookISBN")
       private String _isbn;
       @Column(name = "bookShorttext")
       private String _shorttext;
       @Column(name = "bookReference")
       private String _reference;
       @Column(name = "bookAdReference")
       private String _adReference;
       @OneToMany
       @JoinTable(name="mapBookCategory",
                       joinColumns=@JoinColumn(name="bcBookId"),
                       inverseJoinColumns=@JoinColumn(name="bcCategoryId"))
       private List<Category> _categories;
       @OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
       @JoinColumn(name = "BookId", referencedColumnName="bookId")
       private List<ReviewLink> _reviewLinks;
// getters/setters omited for brevity

As you may have recognized, a book is stored in a table called “Book” and some columns are mapped to the attributes. A book may be contained in one or more categories and optional there are links to reviews linked to a book. That’s what the two lists are for.

In the database, there is a second table which contains the translation if one exists. This table is build up with these attributes:

btId, btBookId, btLanguage, btShorttext, btReference, btAdReference

The prefix bt stands for “book translation”. BtId is an iditifier which is just an autogenerated number. Its a key with no special meaning. BookId refers to the book, whilst Language defines the language of text and reference. To diaplay text in a special language or even the default German one if a translation is missing, I do a left join from Book to BookTrans, e.g.

select isnull(btShortText, bookShortText) as ShortText
from Book
join BookTrans on bookId=btBookId
where bookId=123 and btLanguage='en'

This statement selects the book with id 123 and left joins its translation. If there is none, btShortText will be null. “isnull” is a T-SQL function to replace a null value by something else, here the text in default language.

As you can see, to retrieve a different language we cannot perform a simple JPA query to retrieve an entity. We need a join and then figure out whether to use the translated text or the default. I wanted to to this as simple as the mentioned SQL statement. Thus I decided to use a JPA native query (allthough other solutions exists). That’s using a native SQL statement which can be used by JPA. It is essential that the result of this query matches a Java class. This target has the same structure than the book entity. Why not use it? And here is my native query to retrieve all books by category using a target language if applicable:

public List<Book> getBooksByCategory(int catId, String languageCode) {
       String sqlQuery = "select bookId, bookTitle, bookSubtitle, bookAuthor, "
                       + "bookPublisher, bookYear, bookLanguage, bookISBN, "
                       + "isnull (btShortText, bookShorttext) as bookShorttext, "
                       + "isnull(btReference, bookReference) as bookReference, bookAdReference"
                       + " from book left join BookTrans on bookId = btBookId and btLanguage = ?1 "
                       + "join mapBookCategory on bookId = bcBookId and bcCategoryId = ?2 order by bookId desc;";
       return getEntityManager().createNativeQuery(sqlQuery, Book.class).
                       setParameter(1, languageCode).setParameter(2, catId).getResultList();
}

 

To web development content.