Avoiding SQL joins with java enums

3 min read >

Avoiding SQL joins with java enums

Engineering Insights & Enterprise solutions

Let’s say you have a Coffee object and 3 sizes for coffee (small, medium, and large). You can create a Coffee class like this

public class Coffee {
	private Long id;
	// add other propeties
	private CoffeeSize coffeeSize;


	// ... setters and getters
}

and the CoffeeSize class:

public class CoffeeSize {		
	private int id;
	private String name;
	private String i18nKey;
	
	// ... setters and getters
}

But every time you load a Coffee object you execute a SQL join to load the CoffeeSize.

Of course, you can create a CoffeeSize object and define some public static final CoffeeSize objects, but with java 1.5 you can do something like this :
create a java enum named CoffeeSize and persists just the CoffeeSize id.

package coffee;

import java.util.HashMap;
import java.util.Map;

public enum CoffeeSize {
	SMALL(1, "Small", "coffeeSize.small"),
	MEDIUM(1, "Medium", "coffeeSize.medium"),
	LARGE(1, "Large", "coffeeSize.large");
		
	private int id;
	private String name;
	private String i18nKey;
	
	private static Map<Integer, CoffeeSize> coffeeSizes = new HashMap<Integer, CoffeeSize>();
	
	static{
		CoffeeSize[] coffeeSizesArray = CoffeeSize.values();
		for (CoffeeSize coffeeSize : coffeeSizesArray) {
			coffeeSizes.put(coffeeSize.getId(), coffeeSize);
		}		
	}
	
	private CoffeeSize(int id, String name, String key) {
		this.id = id;
		this.name = name;
		i18nKey = key;
	}

	public int getId() {
		return id;
	}

	public String getName() {
		return name;
	}

	/**
	 * This is an i18n key defined in message.properties
	 * @return the i18n key
	 */
	public String getI18nKey() {
		return i18nKey;
	}
	
	/**
	 * For the id stored in database get the CoffeeSize object
	 * @param id the id stored in database
	 * @return the {@link CoffeeSize} object
	 */
	public static CoffeeSize getCoffeeSizeById(Integer id) {
		return CoffeeSize.coffeeSizes.get(id);
	}	
}

The Coffee object will look something like this (I used hibernate and ejb3 annotations):

package coffee;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Transient;

@Entity
@Table(name = "coffee")
public class Coffee {
	private Long id;
	// add other propeties
	private Integer coffeeSizeId;

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	public Integer getCoffeeSizeId() {
		return coffeeSizeId;
	}

	public void setCoffeeSizeId(Integer coffeeSizeId) {
		this.coffeeSizeId = coffeeSizeId;
	}

	@Transient
	public CoffeeSize getCoffeeSize(){
		return CoffeeSize.getCoffeeSizeById(coffeeSizeId);
	}
}

Look at the getCoffeeSize method: it is transient(is not persisted). The persisted fields are id and coffeeSizeId. You avoid a SQL join by putting private Integer coffeeSizeId instead of private CoffeeSize coffeeSize and creating a getCoffeeSize method that returns a CoffeeSize object. All the CoffeeSize objects are loaded in memory in the static map coffeeSizes.

But remember, this will work only if you don’t want to add a new size for coffee without recompiling and deploying your application.