Archive for the ‘hibernate’ Category

Introduction

In this post I’m going to describe how to use hibernate id generator in order to produce portable solution for genrating prefix sequences with user-defined – part of an issue tracking system (very simple for the sake of brewity).

Requirements

As you might have guessed it – the requirement towards it are not that new and they resemble what we all know from other systems in the field. Each ticket is created within its own workspace and prefix which might be defined per workspace. Ticket number should start from 0 and increase. In case user decided to chanage prefix:

  • the old issues should keep it,
  • while new ones should be named after it
  • all numbering should not be influenced by prefix change

Solution idea

The entity hierarchy below reflects part of business with Client entity used as root for all the rest of nodes and having references to Projects (yes for simplicity of this post there’s nothing else :))) which you can see depicted in diagram #1.

Diagram #1

From technical point of view this dit look much like a sequence sequence where project-id would be used as primary key and increased value in second column would be used as ticket number.

 

Technical details

MySQL is the choice of RDBMS so it felt natural to use some of its built-in functonality and just wrap it around with Hibernate. So my idea was to use a composite primary-key with auto-increment column which would allow to set index per project and restart counter each project is inserted.

An DDL sql for such a table looks fairly simple:

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

And with simple inserts:

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

Produces:

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

Copied from mysql docs

This is exactly the behaviour I wanted so you might ask why not use it and stop wasting other’s time with this post. Well there’s a little detail hidden in the DDL statement above. Careful reader might have noticed that it requires you to use MyISAM engine/table type which among all other things is non-transactional. If you try to use it with InnoDB engine you end up with global auto-increment despite the id column.

Hibernate generators

From the very beginning all of this sql was supposed to be wrapped inside hibernate generator but after finding out that pure MySQL solution was out of the table I decided to analyse what exactly are generator and what they have to offer. This result in reading generator discussion after which I found out that hibernate team has almost solved my problem…

MultipleHiLoPerTableGenerator

If you’re like me – than you have probably never heard of  MultipleHiLoPerTableGenerator which can generate sequence per table and solves some issues related to:

  • creating table with dialect-specific ddl
  • creating initial sequence entry if non-existent
  • using locking on row-level for concurrent access
  • uses separate table for sequences which means that read-only queries are not affected
  • doing retry in case sequence was updated in-between
  • and last but not least – its portable

Everything comes with it’s price of course – this generator’s code is barely readable not to mention someone trying to debug what is actually happening.

IdGeneratorWithDynamicKey

So I decided to follow path showed by hibernate team and do some copy-pasting of their generator and start my own so here’s step-by-step:

  1. An artificial entity was created called TaskNumber.
  2. It contains embeddable id consiting of Project and number
  3. Embedded id is translated into database composite key.
  4. Composite key ensures that there are no two tickets with the same number in the same project.
  5. Id field is annotated with generator annotation.
  6. Generator takes care of creating table for storing sequences
  7. Before each insert of TaskNumber it increments per-project sequence
  8. Newly updated sequence is applied to TaskNumber and persisted

Summary

As one might expect the code is complex and hard to debug – the only positive side is that all the bumps have been handled already by hibernate team and it should produce less headaches in future when real concurrency and silmultaneous updates occur.

See it for yourself 🙂

Problem description

There is a part of your application that needs to display summaries of certain piece of data stored in data base and you need to specily  certain intervals for which summaries should be retrieved.

Requirements

I want to have an easy way of fetching summaries for some columns in certain table for specified interval.

Solution description

Plain SQL

The first solution that comes into play is glue some pieces of SQL together from sum and execute it – yes it’s fast, it works but it’s ugly.

Use HQL

Yes in this solution we’ve got some of the job perfomed by Hibernate – we’ve got our entity defined, there’s a bridge for each sql dilatect but… we still need to explicitly state columns and add calls to sum function in order to retrieve summaries.

Entity to Criteria mapping

But maybe we could somehow use the same Entity object that we have already defined for this specific table and instead of performing regular fetch, generate sql based on @Column definitions. That way we are able to use JavaBean property name as alias for sum and use result transformer to get the data back.

The only portion that’s left here is adding support for omitting some properties – we don’t want to do automated retrieval of SUM’s for name or id 🙂

Source code

Solution consists of 2 static methods responsible for:

There’s also 3rd method but it’s totally optional – it prevents NPE for returned values of a specifc type and it uses cglib-jdk5 Enhancer, so you don’t need to do explicit casting 🙂

You can see full source code on github.

package pl.bedkowski.code.userType.EnumSetUserType;

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.EnumSet;
import java.util.List;
import java.util.Set;

import org.hibernate.HibernateException;
import org.hibernate.usertype.UserType;

public class EnumSetUserType<E extends Enum<E>> implements UserType {
	private Class<? extends EnumSet> clazz = null;
	private Class<E> typeClazz = null;
	protected EnumSetUserType(Class<E> c) {
		this.clazz = EnumSet.noneOf(c).getClass();
		typeClazz = c;
	}

	private static final int[] SQL_TYPES = {Types.VARCHAR};
	public int[] sqlTypes() {
		return SQL_TYPES;
	}

	public Class returnedClass() {
		return clazz;
	}

	public Object nullSafeGet(ResultSet resultSet, String[] names, Object owner) throws HibernateException, SQLException {
		String name = resultSet.getString(names[0]);
		EnumSet<E> result = null;
		if (!resultSet.wasNull()) {
			String[] values = name.split(",");
			List<E> enumList = new ArrayList<E>();
			for(String value : values) {
				enumList.add(Enum.valueOf(typeClazz, value));
			}
			result = EnumSet.copyOf(enumList);
		}
		return result;
	}

	public void nullSafeSet(PreparedStatement preparedStatement, Object value, int index) throws HibernateException, SQLException {
		if (null == value) {
			preparedStatement.setNull(index, Types.VARCHAR);
		} else {
			Set<E> values = (Set<E>)value;
			String sqlValue = "";
			if (!values.isEmpty()) {
				StringBuffer buf = new StringBuffer();
				for(E val : values) {
					buf.append(val.name()).append(",");
				}
				sqlValue = buf.substring(0, buf.length() - 1);
			}
			preparedStatement.setString(index, sqlValue);
		}
	}

	public Object deepCopy(Object value) throws HibernateException{
		return value;
	}

	public boolean isMutable() {
		return false;
	}

	public Object assemble(Serializable cached, Object owner) throws HibernateException {
		return cached;
	}

	public Serializable disassemble(Object value) throws HibernateException {
		return (Serializable)value;
	}

	public Object replace(Object original, Object target, Object owner) throws HibernateException {
		return original;
	}
	public int hashCode(Object x) throws HibernateException {
		return x.hashCode();
	}
	public boolean equals(Object x, Object y) throws HibernateException {
		if (x == y)
			return true;
		if (null == x || null == y)
			return false;
		return x.equals(y);
	}
}

Usage

public class User {
    public enum TYPE{admin,guest};
}

///

import pl.bedkowski.code.userType.EnumSetUserType;

public class UserType extends EnumSetUserType<User.TYPE> {
	public UserType() {
		super(User.TYPE.class);
	}
}

Ostatnio w projekcie powstała potrzeba odrzucenia części wyników asocjacji przed wysłaniem obiektu „w świat” 🙂

Zaczniemy od przeglądu możliwych rozwiązań:

  • najprostsze, które przychodzi do głowy to pobranie asocjacji get’erem, przejechanie się po wynikach i wybranie „właściwych”
  • można użyć złączenia, jednak problem pojawia się w przypadku OUTER JOIN’ów, bo Hibernate pobiera wszystkie elementy a dodanie warunku w wherze powoduje potraktowanie OUTER’a jak INNER’a i w przypadku braku wyników dostaniemy pusty zbiór wyników
  • można skorzystać z Hibernate’a 3.5, który dostał dodatkowy parametr do metody createCriteria, która to właśnie jest dodatkowym kryterium filtrującym dane
  • niestety dla tych „pechowców”, takich jak ja, którzy muszą używać starszych wersji jest też nadzieja 🙂

I na tym ostatnim elemencie będę się dzisiaj skupiał – otóż jak, poradzić sobie z tym problemem w elegancki sposób (bez konieczności zgłębiania wewnętrznych mechanizmów). Są to wspomniane już adnotacje @FiterDef/@Filter, a do opisu ich działania posłużę się przykładem prostego mapowania  dwóch klas – rodzica i dzieci, w relacji jeden-do-wielu, korzystając z adnotacji @OneToMany/@ManyToOne.

Zaczniemy od stworzenia klasy rodzica:


@Entity
@Table(name = "mock_object")
public class MockObject implements Serializable {

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	@Column(name = "id", updatable = false, nullable = false)
	public Long getId() {
		return id;
	}

	@OneToMany(mappedBy = "mockObject", cascade = CascadeType.ALL, fetch = FetchType.EAGER)
        public Set<MockSubObject> getSubObjects() {
		return subObjects;
	}
}

Ze względu na czytelność listing zawiera tylko niezbędne elementy – czyli encję MockObject, która odpowiada tabeli w bazie mock_object i zawiera pole id a także set sub objectów, którego definicja jest poniżej:

@Entity
@Table(name = "mock_sub_object")
public class MockSubObject implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id", updatable = false, nullable = false)
    public Long getId() {
	return id;
    }

    @Column(name="name", length=50)
    public String getName() {
	return name;
    }

    @ManyToOne(fetch = FetchType.EAGER)
    public MockObject getMockObject() {
	return mockObject;
    }

Żeby nie było tak całkiem nudno, dorzuciłem jeszcze jakąś nazwę 🙂

No i spróbujmy napisać do tego left join’a:


Criteria crit = session.prepareCriteria(MockObject.class)
   .createCriteria("subObjects", "su", Criteria.LEFT_JOIN)
   .add(Restrictions.eq("su.id", 1L);

List<MockObject> mo = crit.list();

Zapytanie sprecyzowane w ten sposób spowoduje, że zbiór wyników będzie pusty nawet mimo tego, że tabela mock_object nie jest pusta.

Można temu zaradzić w następujących krokach:

  1. Dodając definicję filtra do encji MockObject.
  2. Deklarując warunek dla kolekcji subObjects
  3. Włączając filtr i wstawiając parametr przed wywołaniem zapytania.
  4. Dodając DISTINCT_ROOT_ENTITY_TRANSFORMER

Do dzieła:


// definicja filtra

@FilterDef(name = "findByName", parameters = { @ParamDef(name = "name", type = "string") })
public class MockObject implements Serializable {

Jak widać filtr przyjmuje jeden parametr o nazwie name i typie string.

Teraz właściwy warunek:


@Filter(name = "findByName", condition = "(name = :name)")
 public Set<MockSubObject> getSubObjects() {

I reszta podczas tworzenia zapytania – które wraz z włączonym filtrem umożliwia filtrowanie danych po atrybucie name:

        Criteria crit = session.createCriteria(MockObject.class)
        	.add(Restrictions.eq("id", 1L))
        	.addOrder(Order.asc("id"));

        session.enableFilter("findByName").setParameter("name", "name1");

        crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

        List<MockObject> lMo = list(crit);

Dla pewności można sprawdzić jeszcze wygenerowane zapytanie:

-- Hibernate:
select this_.id as id0_1_, this_.version as version0_1_, subobjects2_.mockObject_id as mockObject3_3_, subobjects2_.id as id3_, subobjects2_.id as id1_0_, subobjects2_.mockObject_id as mockObject3_1_0_, subobjects2_.name as name1_0_ from mock_object this_ left outer join mock_sub_object subobjects2_ on this_.id=subobjects2_.mockObject_id and (subobjects2_.name = ?) where this_.id=? order by this_.id asc

Wygląda na to, że dodatkowy warunek został uwzględniony 🙂

Źródła standardowo na gicie.