Trying to use SQL Function in hibernate

Question

I am trying to call My-SQL function which is returning calculated value based on input parameters. I am using native queries for this purpose, I am able to get all fields except calculated field. I am always getting NULL as value for calculated field. Moreover If I execute same query in SQL Client, it gives proper result.

Test.class

        @NamedNativeQueries({
        @NamedNativeQuery(
        name = "getTestData", 
        query = "Select test_id, BillType, Description, Material, Netvalue1, "
              + "NetValue(billType, Netvalue1) as Sales_Rs "
              + "from test1",
        resultClass = Test.class
        )
    })

    @Entity
    @Table(name="TEST1")
    public class Test {

        private static final long serialVersionUID = 1L;

        @Id
        @GeneratedValue
        @Column(name="TEST_ID")
        private Integer test_id;

        @Column(name="Billtype")
        private String Billtype;

        @Column(name="Description")
        private String Description;

        @Column(name="Material")
        private String Material;

        @Column(name="Netvalue1")
        private Double Netvalue1;

        @Transient
        private Double Sales_Rs; 
// getters and setters 
.
.
.
};

Here is how I am calling native query:

@SuppressWarnings("unchecked")
    @Override
    public List<Test> getAllTestData() throws MyAppException {
        List<Test> result;

        try {
            Session session = sessionFactory.getCurrentSession();

            Query query = session.getNamedQuery("getTestData");

            result = query.list();

        } catch (DataAccessException e) {
            throw new MyAppException("Could not add a new record :", e);
        }

        return result;
    }

Here is Stored function:

CREATE FUNCTION NetValue(billType CHAR(30), Netvalue1 DOUBLE) RETURNS double
BEGIN
    Declare RetValue DOUBLE;

  SET RetValue =
  CASE billType 
    WHEN 'A' THEN 0.10 * Netvalue1
    WHEN 'B' THEN 0.05 * Netvalue1
    WHEN 'C' THEN 0.025 * Netvalue1
    ELSE Netvalue1
  END;

    RETURN RetValue;
END;

you are using Hibernate directly, not through JPA?

wero
– wero

2015-08-14 12:05:43 +00:00
Commented Aug 14, 2015 at 12:05 — wero
– wero, Commented Aug 14, 2015 at 12:05
I am using hibernate through JPA.

Shirish Bathe
– Shirish Bathe

2015-08-14 14:58:24 +00:00
Commented Aug 14, 2015 at 14:58 — Shirish Bathe
– Shirish Bathe, Commented Aug 14, 2015 at 14:58

wero · Accepted Answer · 2015-08-14 15:22:16Z

1

You declared field Double Sales_Rs as transient, therefore it is not handled by Hibernate.

See this thread for two solutions:

a) Use a Hibernate specific mapping to calculate Sales_RS by a formula which calls your database function:

@Formula("NetValue(billType, Netvalue1)")
private Double Sales_Rs;

b) use the JPA @Postload annotation to calculate Sales_RS after the object has been loaded:

@Transient
private Double Sales_Rs;

@PostLoad
private void onLoad() {
     Sales_Rs = ... //  same logic as in your db function
}

In both cases you can drop the native query and use simple hql:

@NamedQueries({
    @NamedQuery(
       name  = "getTestData",
       query = "from Test"
)})

answered Aug 14, 2015 at 15:22

wero

33.1k3 gold badges63 silver badges87 bronze badges

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

Collectives™ on Stack Overflow

Trying to use SQL Function in hibernate

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related