Setting up query for specific field of Hibernate entity

Question

I use spring and hibernate. The following situation occured and I don't know if it's really possible to implement. Will appreciate any help.

For example, there is a hibernate entity

    @Entity
public class TestEntity {
    private String field1;
    private String field2;
    private String specField;

    @Column(name = "field1")
    public String getField1() {
        return field1;
    }

    @Column(name = "field2")
    public String getField2() {
        return field2;
    }

    public String getSpecField() {
        return (field1 != null ? field1 : field2);
    }
}

And I need the value for specField to be generated by SQL query and not by java code. Something like this

    @Entity
public class TestEntity {
    private String field1;
    private String field2;
    private String specField;

    @Column(name = "field1")
    public String getField1() {
        return field1;
    }

    @Column(name = "field2")
    public String getField2() {
        return field2;
    }

    @Query(value= "COALESCE(field1, field2)")
    public String getSpecField() {
        return specField;
    }
}

I was told that there should be ability to do so. But didn't find anything that approves this.

it's not actually important what exactly query does. I need that specField will be taken by some query and not by java code. Is it possible?

Thanks for help.

UPDATE Thanks to @premkumar, for advicing to use @Formula So now I have

@Entity
public class TestEntity {
    private String field1;
    private String field2;
    private String specField;

    @Column(name = "field1")
    public String getField1() {
        return field1;
    }

    @Column(name = "field2")
    public String getField2() {
        return field2;
    }

    @Formula("COALESCE(field2, field2)")
    public String getSpecField() {
        return (field1 != null ? field1 : field2);
    }
}

But app fails to start on bean initialization with NullPointerException at org.springframework.orm.hibernate3.LocalSessionFactoryBean.newSessionFactory

I tried also the following:

Put formula above "private String specField" - app started, but hibernate failed on could not find spec_field in database
Put @Formula and @Transient on getter - app started, no errors, but specField is always null. It looks like hibernate totally ignored it

Why don't you use @ PrePersist or @ PreUpdate to set the value in the field for that entity? — Rana_S, Dec 08 '15 at 18:33
did you mean specField should have value (field1 + ", "+ field2)? if not can you more clear — prem kumar, Dec 08 '15 at 18:34
@RossiRobinsion I actually didn't know about it. I will research it now — me1111, Dec 08 '15 at 18:35
@premkumar specField should be field1 if field2 is null or field2 if field1 is null — me1111, Dec 08 '15 at 18:36
Those are callback methods. E.G: http://www.javabeat.net/jpa-entity-listeners-callback-methods/ — Rana_S, Dec 08 '15 at 18:37
@RossiRobinsion thanks Rossi, I looked and unfortunately these don't fit my needs... It's important to generate specField exactly by SQL query — me1111, Dec 08 '15 at 18:39
@premkumar it's not actually important what exactly query does. I need that specField will be taken by some query and not by java code. Is it possible? — me1111, Dec 08 '15 at 18:55

score 1 · Answer 1 · answered Dec 08 '15 at 18:45

1

Why not:

public String getSpecField() {
   return MoreObjects.firstNonNull(field1,field2);
}

answered Dec 08 '15 at 18:45

Lucas Oliveira

3,357
1
16
20

It's important to generate specField exactly by SQL query and not by java code – me1111 Dec 08 '15 at 18:46
Coalesce is supported by JPA 2.0 API. try select COALESCE(field1,field2) as specField, from TestEntity – Lucas Oliveira Dec 08 '15 at 18:49
Not sure if I got you right. You mean I can put it as @NativeQuery for full entity? – me1111 Dec 08 '15 at 18:53
1

take a look at @SqlResultSetMapping from hibernate, it allows you to map the fields of your object to the columns retrieved by a native query – Lucas Oliveira Dec 08 '15 at 19:26

score 1 · Answer 2 · answered Dec 08 '15 at 18:49

1

You can leverage the JPA Callback methods:

Create a EntityListener:

public class MyEntityListener {

    @PrePersist
    @PreUpdate
    public void prePersist(Object object) {
        if(object instanceOf TestEntity ) {
            object.specField  = object.field1 != null ? object.field1 : object.field2
        }
    }
}

Annotate your class with @EntityListener:

@Entity
@EntityListeners(MyEntityListener .class)
public class TestEntity {
    private String field1;
    private String field2;
    private String specField;

    //default cons - getters and setters
}

answered Dec 08 '15 at 18:49

Rana_S

1,520
2
23
39

It's important to generate specField exactly by SQL query and not by java code – me1111 Dec 08 '15 at 18:54
You need to elaborate your definition of generate by SQL Query. – Rana_S Dec 08 '15 at 19:02
I mean that hibernate should get value for specField by executing SQL – me1111 Dec 09 '15 at 11:34

score 1 · Accepted Answer · answered Dec 08 '15 at 19:06

1

If you are using hibernate, try the following:

@Formula("COALESCE(field1, field2)")
public String getSpecField() {
    return specField;
}

Note:- As far as I know there is no alternative in JPA. Beware this will mixup hibernate and jpa annotations.

answered Dec 08 '15 at 19:06

prem kumar

5,641
3
24
36

Thanks! This matches perfectly my needs. But somehow adding this annotation ruins the project, and it won't start with NullPointerException at org.springframework.orm.hibernate3.LocalSessionFactoryBean.newSessionFactory. Maybe you have thoughts what this can be? – me1111 Dec 09 '15 at 09:09
@me1111 did this work for you? if yes then when is this exception happening? what is the case when you remove the annotation? Do you still get the exception? – prem kumar Dec 09 '15 at 09:22
It's failing on bean initalization and stacktrace leads to NullPointerException in LocalSessionFactoryBean.newSessionFactory. When removed annotation, app started ok, but hibernate failed with "no spec_field column in database" when fetching. I updated my post with details – me1111 Dec 09 '15 at 11:44
1

dont mixup field and getter annotations.. follow one style.. in your case keep it on getter for consistency.. remove transient annotation as hibernate will completely ignore that element... can you provide complete stack trace – prem kumar Dec 09 '15 at 12:06
I dont know how to put attachment to the post, so please see by link https://drive.google.com/file/d/0B5r-cLUnYrFSNlh4aUQwWkFtVnc/view?usp=sharing – me1111 Dec 09 '15 at 12:32
1

seems issue related to hiberate version. http://stackoverflow.com/questions/3331529/native-sql-query-for-an-hibernate-entity-using-formula-results-in-nullpointerex which version are you using? – prem kumar Dec 09 '15 at 12:45
It's 3.6.9.Final for hibernate-core – me1111 Dec 09 '15 at 13:02
Could this be that this is all because of missing alias for Entity? Can I set it for entity and use it in query? – me1111 Dec 09 '15 at 16:08

Setting up query for specific field of Hibernate entity

3 Answers3

Linked