Tag Archives: Java

Difference between join fetch Hsql and standard SQL request.


I stumbled upon a problem with a HSQL request recently(Hibernate SQL language). The application using a Hsql request was not retrieving the information we wanted and we did not know why.

The SQL request retrieving correctly the information

The tables from the database(simplified) :

        ID          NAME    
        122        NAME_X

        MY_ID     MY_NAME       VarA 
        231       NAME_Y        value_expected
        Null      NAME_X        value_expected

This SQL request executed from SQL developer would retrieve the expected outcome from the tables.

t.VarA = 'value_expected' and
a.NAME=t.MY_NAME and (a.ID=t.MY_ID or a.ID is null).

Outcome :

Null           NAME_Y

The different behavior with looking similar HSQL request

The HSQL request( used with entities) in the java code was supposed to give the same results as the previous sql request. Here is the code of the hsql request :

StringBuilder clause = new StringBuilder(
"from MyAlarm a join fetch a.mytable t"
+" where t.VarA = 'value_expected' and"
+"(a.ID=t.MY_ID or a.ID is null)");

The problem when using this hsql request is that some of the request is done through entity objects (from MyAlarm a join fetch a.mytabl). The join is done in the entity class of the Entity MyAlarm :

@Table(name = "MY_ALARM")
@Cache(usage = CacheConcurrencyStrategy.NONE)
public class MyAlarm implements Serializable {
    @Column(name = "NAME")
    private String Name;
    @Column(name = "ID")
    private String Id;

 @ManyToOne(targetEntity=MyTable.class, optional = true, fetch=FetchType.LAZY)
,nullable = true, insertable = false
, updatable = false),
,nullable = true
, insertable = false
, updatable = false)</b>

This following join from the entity will never retrieve the row of the table MY_TABLE where ID is null because there is no ID null in the table ALARM. Therefore this hsql request is not not equivalent to the SQL request above.

Explanation about join fetch

The documentation about join fetch : “14.3. Associations and joins”

A good explanation of the difference between join and join fetch here :

How did I find the solution ?

As i said previously unit testing is the key to find the resolution of coding problems. It enables you to reproduce problems faster and it adds a regression test to your product. More information about unit testing https://julienprog.wordpress.com/2015/03/15/the-power-of-unit-testing/

I reproduced the problem inside a unit test.It helped me a lot to understand the problem and how to solve it. To realise a unit test of hibernate request i use hsqldb,spring framework ,maven,etc..

The solution

I join the two tables “MyAlarm” and “MyTable” with the following conditions. The important part was to add (a.ID=t.MY_ID or a.ID is null). It is a condition necessary to respect the needs of the customer.

<b>select a from MyAlarm a, MyTable t</b> where 
t.VarA = 'value_expected' and
a.NAME=t.MY_NAME (a.ID=t.MY_ID or a.ID is null)

Mock HibernateTemplate with Mockito


Mockito is a useful Java library to create mock Objects for Java Unit Testing.

I used the framework Mockito in order to test objects difficult to test otherwise such as Struts method, Servlet, Hibernate , etc…

HibernateTemplate object to mock

This is the legacy code i wanted to test with Mockito. HibernateTemplate is a deprecated class but it is still used in this code.

Package com.dao.myclass;

import org.springframework.dao.DataAccessException;
import org.springframework.orm.hibernate4.support.HibernateDaoSupport;

import com.dao.ImyDAO;
import com.dao.exception.MyException;
import com.dao.myEntity;
import com.util.Logger;

public class MyHibernateImpl extends HibernateDaoSupport implements ImyDAO {

    public myEntity getMyEntity(String myNumber) throws MyException {
        try {
            if (myNumber == null) {
                        .error("myNumber : " + myNumber);
                return null;
            if (myNumber != null && myNumber.length() > 1) {
            myEntity thismyentity = (myEntity) getHibernateTemplate().get("com.dao.myEntity", myNumber);
            return thismyentity;
        } catch (final DataAccessException e) {
            throw new MyException("error.CodeMyEntity", e);


The line of code i had to difficulty to test without Mockito :

myEntity thismyentity = (myEntity) getHibernateTemplate().get("com.dao.myEntity", myNumber);

The code for myEntity :

@Table (name="MY_TABLE")
public class myEntity implements Serializable {

    private static final long serialVersionUID = 5447862515324364L;

    private String myNumber;

    @Column (name="MYCOLUMN")
    private String myColumn;

    public String getmyNumber() {
        return myNumber;
    public void setmyNumber(String myNumber) {
        this.myNumber = myNumber;
    public String getmyColumn() {
        return myColumn;
    public void setmyColumn(String myColumn) {
        this.myColumn = myColumn;

Mock the object HibernateTemplate

I need to mock the object HibernateTemplate in order to test this function. Here is my test class with Junit :

public class TestMyHibernateImpl {

    HibernateTemplate mockTemplate;

I used the annotation @Mock for the creation of a mock object of HibernateTemplate. To use @Mock it is necessary to initialise MockitoAnnotations :

    public void initMocks() {

This is how I test the function getMyEntity:

    public void testCasEntityReturned() throws MyException {

        myEntity entity = new myEntity();
        String myNumber = "S54564121SD”;
        String myColumnValue = "myColumn";

        (Mockito.doReturn(entity).when(mockTemplate)).get("com.dao.myEntity", myNumber);
<b>// I tell Mockito to return an entity when this code is used later in getMyEntity(..)</b>

        MyHibernateImpl myColumn = new MyHibernateImpl();
        myEntity thismyentity = myColumn.getMyEntity(myNumber);
        Assert.assertEquals(myNumber, thismyentity.getmyNumber());
        Assert.assertEquals(myColumnValue, thismyentity.getmyColumn());


Unit Testing of Jsp Custom Tag Before Version Spring 2.5

As it is described on this link, it is easier to mock Custom Tag with Spring 2.5 :http://blog.agilelogicsolutions.com/2011/02/unit-testing-jsp-custom-tag-using.html .

If for whatever reason you are stuck with a version of Spring before 2.5, you can still test Jsp Custom Tag.This is the class i would like to unit test :

public final class CustomTag extends javax.servlet.jsp.tagext.BodyTagSupport{

    public void writeMyTag() throws JspException {

              pageContext.getOut().write("<th> … text here <th>");              



My problem is : how to mock the object pageContext ? My solution is to use Mockito.

I did the following unit test and mock pageContext and jspWriter:

public class CustomTagTest {


PageContext pageContext;


JspWriter jspWriter;


public void setup(){




public void testwriteMyTag() throws JspException, IOException {

CustomTag tag = new CustomTag ();


tag.setPageContext(pageContext );




Here we go : i covered the custom tag code. However it does not test the output result of JspWriter.