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
– Rana_S, Commented Dec 8, 2015 at 18:33
did you mean specField should have value (field1 + ", "+ field2)? if not can you more clear — prem kumar
– prem kumar, Commented Dec 8, 2015 at 18:34
@RossiRobinsion I actually didn't know about it. I will research it now — me1111
– me1111, Commented Dec 8, 2015 at 18:35
@premkumar specField should be field1 if field2 is null or field2 if field1 is null — me1111
– me1111, Commented Dec 8, 2015 at 18:36
Those are callback methods. E.G: javabeat.net/jpa-entity-listeners-callback-methods — Rana_S
– Rana_S, Commented Dec 8, 2015 at 18:37

prem kumar · Accepted Answer · 2015-12-08 19:06:14Z

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 8, 2015 at 19:06

prem kumar

5,8873 gold badges26 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

me1111 Over a year ago

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?

prem kumar Over a year ago

@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?

me1111 Over a year ago

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

prem kumar Over a year ago

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 Over a year ago

seems issue related to hiberate version. stackoverflow.com/questions/3331529/… which version are you using?

|

Lucas Oliveira · Accepted Answer · 2015-12-08 18:45:20Z

1

Why not:

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

answered Dec 8, 2015 at 18:45

Lucas Oliveira

3,5371 gold badge20 silver badges20 bronze badges

4 Comments

me1111 Over a year ago

It's important to generate specField exactly by SQL query and not by java code

Lucas Oliveira Over a year ago

Coalesce is supported by JPA 2.0 API. try select COALESCE(field1,field2) as specField, from TestEntity

me1111 Over a year ago

Not sure if I got you right. You mean I can put it as @NativeQuery for full entity?

Lucas Oliveira Over a year ago

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

Rana_S · Accepted Answer · 2015-12-08 18:49:17Z

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 8, 2015 at 18:49

Rana_S

1,5603 gold badges23 silver badges40 bronze badges

3 Comments

me1111 Over a year ago

It's important to generate specField exactly by SQL query and not by java code

Rana_S Over a year ago

You need to elaborate your definition of generate by SQL Query.

me1111 Over a year ago

I mean that hibernate should get value for specField by executing SQL

Collectives™ on Stack Overflow

Setting up query for specific field of Hibernate entity

3 Answers 3

8 Comments

4 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related