Hibernate SQLQuery with transient properties

Question

I need to make an hibernate SQLQuery with db2 and this query is returning me some fields which are calculated and have no relation with any columns in database.

The goal is setting the values of these sum() calculations from SQLQuery on three new transient fields in a Java Object which already existed.

The SQLQuery uses the syntax:

SELECT id as {entityObject.id},
           name as {entityObject.name},
           order as {entityObject.order},
           SUM(CASE
           WHEN pv.value_id = 1 
            AND pv.value=1 THEN 1 ELSE 0 END) AS {entityObject.someCount}

The problem is that Hibernate complains and says to need a column for someCount. It seems not to help declaring the java field as transient or even using the @Transient annotation from javax.persistence at the same time.

If I only declare in the hbm.xml mapping file:

<property name="id" type="java.lang.Integer" column="someColumn" />
<!-- Some more fields here -->

<!-- THE IMPORTANT ONE -->
<property name="someCount" type="java.lang.Integer"/>

Java Object:

public class EntityObject implements Serializable {

private static final long serialVersionUID = 1479579608940145961L;

private Integer id;
private String name;
private Integer order;

    // This is the one giving me hell. I've tried with @Transient also
private transient Integer someCount;

public Category() {
}

public Category(final String name, final Integer order) {
    this.name = name;
    this.order = order;
}

public Integer getOrder() {
    return this.order;
}

public void setOrder(final Integer order) {
    this.order = order;
}

public Integer getId() {
    return this.id;
}

public String getName() {
    return this.name;
}

public void setName(final String name) {
    this.name = name;
}


public Integer getSomeCount() {
    return someCount;
}

public void setSomeCount(final Integer count) {
    this.someCount = count;
}


}

It asks me for a column, and I have tried inserting a fake column and it does not work. The thing is that I want these 'count' fields only to be set from the SQLQuery and to be empty and null when coming from a regular Hibernate Query.

I have looked at the docs and googled, and it seems that you can declare a field transient by only not declaring it at the hibernate mapping file, but then it does not set it on the object with the "as {entityObject.someCount}" even when I have getters/setters declared.

Help please. Thanks very much in advance.

Hibernate doesn't 'see' transient fields. You will have to do the aggregation queries and put the results in the fields in your code after the query returns. — K.C.
– K.C., Commented Nov 22, 2013 at 7:38
Firstly, thank you. Secondly, ¿how do I get these values then if I can't map them directly from the SQLQuery to the object? — madtyn
– madtyn, Commented Nov 22, 2013 at 8:25

Alan Hay · Accepted Answer · 2013-11-22 09:15:22Z

2

The only option available that might do all this directly from the Database without having to issue additional queries is a Hibernate Formula property:

http://wiki.jrapid.com/w/Formula_(attribute_of_property)

<property name="someCount" formula="select count(*) from some_table where table_key = ?"/>

The ? placeholder will be populated automatically with the ID of the current instance.

answered Nov 22, 2013 at 9:15

Alan Hay

23.3k4 gold badges64 silver badges119 bronze badges

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

1 Comment

madtyn Over a year ago

Thanks, I thought on this but I need to do it in the sql query for efficiency as well as for company rules related to style and so on.

mvb13 · Accepted Answer · 2013-11-22 09:01:39Z

1

1 Create a POJO:

public class SumValue{
   private BigInteger myId; 
   private String myName;
   private BigInteger myOrder;
   private BigInteger mySum;
   ....
   getters and setters here
   ....
}

2 Minor changes in your query

SELECT id as "myId",
       name as "myName",
       order as "myOrder",
       SUM(CASE
       WHEN pv.value_id = 1 
        AND pv.value=1 THEN 1 ELSE 0 END) AS "mySum"

3 Execute native sql

     List<SumValue> jobStateViewList = (List<SumValue>)getSessionFactory().getCurrentSession()
                                   .createSQLQuery(yourQuery)
                                   .setResultTransformer(
                                        new AliasToBeanResultTransformer(SumValue.class)
                                   ).list();

edited Nov 22, 2013 at 9:01

answered Nov 22, 2013 at 8:31

mvb13

1,6043 gold badges20 silver badges33 bronze badges

10 Comments

madtyn Over a year ago

Mmmmmh, I'm afraid I didn't explain well myself. I'm editing the main question. But in advance I explain to you... There is an only Java object with three aggregate fields like the one I posted, and also as well another fields mapped as usual. Tell me if I'm wrong, but your solution only sets the value on a POJO containing only one value. I want all the retrieved values in the same object. I'm clarifying this just now by editing the initial post. Sorry about this.

mvb13 Over a year ago

I don't see any Java object in your post. From your sql query I understand that you need to transform sum value returned by sql into object. So, once again show me please the object you want to populate with data from sql query.

madtyn Over a year ago

I think now is complete. Sorry about the inconveniences.

mvb13 Over a year ago

I edited my post. I also have one question. Did you map EntityObject as Hibernate entity?

madtyn Over a year ago

Yes I did. I'm at some other taks with higher priority right now. I'm coming back to this later.

|

Collectives™ on Stack Overflow

Hibernate SQLQuery with transient properties

2 Answers 2

1 Comment

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related