Formula 1 « Core « JPA Q&A

15.Data Type
54.Sql Server
55.Stored Procedure
JPA Q&A » Core » Formula 1 

1. NHibernate property formula filter

I have a following class:

public virtual int Id { get; set; }
public virtual int Code { get; set; }
public virtual int Description { get; set; }
public virtual int Name { get; ...

2. hibernate property formula how to get more than 1 row?

In the hibernate mapping file I use property formula to get any value.

<property name="price" type="double" formula="(select SUM(amount) ...) />
But it allows to get only 1 row. How can I get lisf ...

3. Hibernate: Many-to-one using Formula

I hope someone can help me find an answer. I'm working with a legacy database and I can't change any of the preexisting tables, because other apps depend on them. I have three ...

4. hibernate formula with column

I have a table with a column that will be encrypted by a trigger after the record is inserted. When I read the column, I need to apply a decrypt function ...

5. Native SQL query for an Hibernate entity using @Formula results in NullPointerException

I've got an simple Hibernate entity for which I use the @Formula annotion:

private Long id;

private String name;

@Formula("(select count(f.*) from foo f where = id)")
private long bar;
When ...

6. Hibernate Criteria order by a specific state

Hi In the database, we have a PRSN_ADDRESS table which has many addresses. A user is shown these addresses in a grid. The requirement is show the addresses associated with this user's ...

7. Refresh Hibernate Formula

I have got two entities (example reduced as much as possible; each entity has got an id field):

public class A {
    @Column(nullable = false)
    private ...

8. Hibernate xml -> annonations described by formula

Is there any possibility to map following xml into annotations?


9. Many-to-one with formula in Hibernate

I have the following tables:

tableA: id[PK], name
tableB: id[PK], idTableA[FK], dateClosed
And my object model is the following:
class ObjectA {
    private Integer id:
    private String name;

10. Hibernate @Formula building query incorrectly

I'm adding a formula to a field:

@Formula(value = "(select count(*) from approvalGroup as a where a.isAccounting=true)")
But the query is failing because Hibernate is trying to make 'true' a field on my ...

11. Hibernate @Formula is not supporting query contains 'CAST() as int' function

Following is the one of property of ExecutionHistory class, which value is fetched from @Formula using JPA/Hibernate from exectution_history table,

@Formula("(SELECT SUM(dividend) || '/' || SUM(divisor) " +
"FROM (SELECT CAST(substr(sr.result, 1, position('/' ...

12. JPA & Hibernate: How to eager fetch a formula using HQL/JPQL (not Criteria API)

I have an entity with a lazy @Formula field:

private Integer commentCount;

@Formula("(SELECT count(  from Comment c where c.post_id = id)")
public Integer getCommentCount() { return commentCount; };
In one scenario I would like ...

13. Hibernate with JPA ignoring @Formula

I have a Formula defined as:

@Table(name = "MyEntity")
@org.hibernate.annotations.Table(appliesTo = "MyEntity")
public class MyEntity
    @Enumerated(value = javax.persistence.EnumType.STRING)
    @Formula(value = "select e.state from OTHER_ENTITY ...

14. Hibernate HQL filter by a property marked with @Formula

I want to filter out by a column that is marked as being a Formula. This is the mapping:

<class catalog="kinton" entity-name="myEntity" name="org.example.myEntity" table="virtualapp">
    <id name="idVirtualApp" type="java.lang.Integer">

15. hibernate @JoinColumnsOrFormulas throws Formula cannot be cast to Column exception

I see JoinColumnsOrFormulas is enabled since 3.5.1 as per


throws a org.hibernate.mapping.Formula cannot be cast to org.hibernate.mapping.Column, which version of hibernate and jar combinations supports ...

16. Hibernate - Orderding criteria by formula property

Say I have an entity MyEntity, and it has a formula-based property fmlaProp. Now say I create a criteria:


17. using hibernate formula

18. Hibernate column formula

19. Problems with using property in formula [hibernate]

I have many-to-many relationship: COMPANY * - * EMPLOYEE here is CompanyDTO (POJO): public class CompanyDTO implements Serializable{ private static final long serialVersionUID = 1L; private Long company_id; private String name; private EmployeeDTO employee; private String position; private Long employeeId; ............(getters-setters)............. } and also ompanyDTO.hbm.xml : ...

20. Does hibernate support annotation for formula join

Hi, I've never seen an example of using annotation for formula join. There's an example .hbm in hibernate distribution. Does any one know how to write it in hibernate annotation? Thanks. Master.hbm.xml Code: ...

21. Formula to retuen Object

(select * from Order o2 where = 128)

22. Formula error

How can I use the function 'interval' in a formula? Code: ----------------------------- package contact; import org.hibernate.SessionFactory; import org.hibernate.Session; import org.hibernate.Transaction; import org.hibernate.cfg.Configuration; public class PersonTest { public static void main(String[] args) { SessionFactory factory=new Configuration().configure().buildSessionFactory(); Session session=factory.openSession(); Transaction ...

24. Problems with using property in formula

I have many-to-many relationship: COMPANY * - * EMPLOYEE here is CompanyDTO (POJO): Code: public class CompanyDTO implements Serializable{ private static final long serialVersionUID = 1L; private Long company_id; private String name; private EmployeeDTO employee; private String position; ...

25. Associations using formula queries

I have the following mapping for classes A and B where I want to have an instance of B represented within A. Class A does not have the primary key of B directly, but wishes to derive it from the query: select b.primaryKey from B b where b.month=A.month and b.year=A.year When i use the formula tag I get the runtime error: ...

26. Q: Example usage of @Generated with @Formula

Hi, I am trying to generate a sequence ID unique by another column in the same table and am trying to do this with @Generated and @Formula, but somehow I am always getting null. Can any expert here please give me a hand and let me know if I am completely on the wrong track? Here's my code: @Column( nullable=false, insertable=true, ...

27. @Formula and enum values

Hello all, I need help because I want to do something and I don't know why, let me explain: I have an internal database procedure for calculating some stuff, and I want to retrieve the result in my application. This is done with the @Formula annotation: @Formula("(select my_function(param1, param2))") Integer myValue; This is working great. The problem is that we are ...

28. Criteria query with "formula" field used as ordering

Hi, What I am trying to do is retrieve some data using the Criteria method. However I want to order the results by a calculation on some of the fields in the row, a calculation that is never stored in the data objects. Eg, I do something like this right now Criteria c = super.getSession().createCriteria(Listing.class); if (search.getName() != null) c.add(Restrictions.ilike("name", "%" ...

29. how to uses @Formula annotation on method

30. @Formula is ignored

@Entity @XmlRootElement(name = "team") public class Team { private String nickname; private String city; @Formula(value = "CONCAT(city,\" \",nickname)") private String cityAndNickname; @Column public String getCity() { return city; } ...

31. Obtaining a custom type via formula (Bug?)

32. Formula for boolean types possible?

34. Formula using a filter parameter

I am using the following filter in my data object: @FilterDef(name="accountDecryption", parameters=@ParamDef(name="password", type="java.lang.String")) and using the below formula to decrypt(DB2) the encrypted account_number: @Formula( "decrypt_char(account_number,:accountDecryption.password)") and I am setting the parameter prior to the query being fired as such: template.getSession().enableFilter("accountDecryption").setParameter("password", "[password_Location]"); The filter parameter gets set properly, but the Formula does not populate with the actual value, the hql is as ...

35. Dynamically change subselect formula

Is there a possibility to dynamically change a formula (@Formula or ) that does a subselect? I'm asking because I've got some very dynamic columns in my classes, that perform a subselect using the formula-annotation. Is it possible to change the formula without the need for a restart of the session-factory? Best regards, Markus

36. Hibernate / MySQL cast as formula

For those interested, I have used some lateral thinking as a workaround The idea behind the issue was that I need a numeric sort of the employees for a screen display I am using the Hibernate Generic DAO framework for searching, so what I did in the end was allow it to do a custom sort of type "cast(employeeno as ...

37. many-to-one with formula problem

Hi! I want to store some meta data for my entity classes in the database. This meta data only depends on the entity class, not the entity itself and each entity should have a reference to the meta data object which describes the class of the entity. So I have a many-to-one association between a "MetaData" class and some "Entity" classes ...

39. Formula with paramters not working with direct queries

I created a formula with parameter accessing its value through a filter parameter like this: Code: @Formula("(label_id is null or") The CurrentLabel is a filter, and has a parameter named id. I set the filter's parameter after opening the session so the formula can get the value of it. When i created a test-case by quering the entity that has ...

40. How to set FetchMode of a @Formula in HQL/JPQL?

41. @Formula Hibernate return more than 1 row

42. Formula in Scalar Query (not in WHERE)

I realize there's an issue posted (HB-280) about using formula values in WHERE clauses, but this doesn't fall under that. I have the following query: select art.synopsis from News as art synopsis is a foruma property. I get: QueryException: unresolved property:synposis Now, if I load() the object instead of find(), I have no problem, and the formula works as it should. ...


This works great: Code:

44. Using formula

45. How to use ?

Regular Joined: Fri Sep 05, 2003 12:01 am Posts: 80 Location: Bogot hi! I have a many-to-one relationship (Proposal has many ProposalDetail)and it works great. I would like compute for a given Proposal the sum of certain fields in its ProposalDetail. I tried reading the hibernate doc but doesnt say much on formula properties... can someone help or give a suggestion? ...

49. does formula property have limitations?

I wonder if formula prop. have limitations about the number of the tables joined. ... ... .. In Oracle this query works, but with hibernate, it gives errors.

50. Using formula in order by clause

Hello, I am using hibernate 2.1.3, in jboss, with Informix. I have created a formula field to substract to date columns to find the number of days. This is how the propery is declared in the hbm: When the property is in the select column there is no problem, but when the ...

51. Modify Formula of property with API

52. formula with count(distinct ...) ?

I'm pretty new at hibernate, so I'm sure I'm just doing something wrong. I'm using hibernate 2.1.4 with postgres 7.4.1, and I'm trying to define a property in an object like: but hibernate is "translating" the 'distinct' part of the clause so that what is being generated is: select ... count(tablename.distinct tablename.event) ... which of ...

53. can i use formula for this?

how can I do something like this but as a formula? is it even possible? select avg(r.score) from Rating as r where r.ratee = ? i searched the docs but couldnt find anything too helpful with this i basically want to get back a list of Users along with the average rating but have them sorted by rating

54. formula property and wrong query generated in firebird db

I need to have a property in a class that is a sum of another property in a set. I've already seen a post here in the forum and basically copied the solution. But the query that I get is wrong. I am going to post the two hbm files here: ...

55. encryption : formula and read-write property

Hibernate version: 2.1.2 Is there a way to specify a formula for writing data? I realize the formula attribute of a property can be used for "computed" properties, but what about computing the value that will be inserted? A specific (and I think common) use case would be encrypting/decrypting functions. On read, I would like to use the db function decrypt(dbvalue). ...

56. many-to-one formula in Hibernate 2.1.6

Hi All I've a parent->child relation ClassA->ClassB. The foreign key on the B side is composed of a foreign key column (A_PID) and a second column which is also part of the primary key of B (SYSTEM_ID). This means, that I have to set the many-to-one to update="false" and insert="false". In order to get the foreign key column on the B ...

57. Formula and query.substitutions

58. Problem with formula

I did hacked a little and now it works. I have created new Dialect by extending SqlServerDialect, and I have registered 'as' and 'datetime' keywords as functions so Template.renderWhereStringTemplate() method does not prefix them with table alias. It is ugly but works :) However, I think that 'as' is standard sql keyword that should be in KEYWORDS map of net.sf.hibernate.sql.Template class. ...

59. formula on hql

Newbie Joined: Tue Mar 16, 2004 1:44 pm Posts: 17 Location: San Jose - Costa Rica Hibernate version: 2.1.6 Mapping documents: IDPAGO_SQ

60. Problem with formula definition in property

Hibernate version: 2.1.7c Mapping documents: I am trying to created a class witha derived property "billed" that indicates whether a particular object has been invoiced. Since Hibernate ...

61. column formula for update/insert

Hello, I am currently evaluating Hiberanate for my current project and have a question. My current application has a create and last update date for each of its tables and I would like to use a static formula for the insert and update. 1) DB Triggers - Would like to avoid. 2) Hibernate Interceptors - Would not work because it is ...

62. BUG when using formula (calculated property) and subselects

Neither of these is a bug. See my comment in the other thread. As for this one. MySQL is *definitely* smart enough to optimize it. After calculating the result of the subselect once, all the data will be cached in memory, and a second calculation will be incredibly efficient. With the new query parser, we *might* look into re-using aliases defined ...

63. Many-to-one formula


64. Formula property

I have two classes - Product and Sku. A product can have one or more skus. In product I need to add a property that gets calculated from data thats held by related Skus. For this I added a property called lowestPrice in Product that uses a formula. When I retreive a product I expected to have the lowestPrice calcuated using ...

65. Help with this formula

Regular Joined: Thu Feb 24, 2005 2:34 pm Posts: 80 Hibernate version:3.03 Please i have this formula Code: 20 ...

66. Problem using formula ....

Hi, I am having problem using formula... I have somthing like this.... where deptName is a field which belongs to EmployeeDTO and i have to get the deptName from the DepartmentDTO based on the empId...i am getting a problem like java.lang.ClassCastException :org.hibernate.mapping.Formula....why is it so?my deptName in EmployeeDTO and DepartmentDTO both are ...

67. Formula no longer works in Hibernate 3

I'm having a problem with formula. My code worked fine with Hibernate 2 but is broken with Hibernate 3. I don't understand why. I'm using mysql with the spatial functionnalities. I'm no longer able to load a geometry column. My mapping is the following : Code: and I have a setter setArea(String area) which parses ...

68. Relationships with criteria: formula in a many-to-many?

I have legacy data that uses the not uncommon practice of marking a 'deleted' field in each row as 'Y' if the row is deleted, 'N' if not. Rows are never actually deleted. This is causing me some trouble, as I can't figure out how to specify such criteria in the mapping. I know that I can do a Query or ...

69. Question about using formula

Need help with Hibernate? Read this first: ... AskForHelp Hibernate version: 2.1.6 Mapping documents: Full stack trace of any exception that occurs: 2005-06-23 16:33:57,318 WARN [net.sf.hibernate.util.JDBCExceptionReporter] SQL Error: 170, SQLState: 37000 2005-06-23 16:33:57,318 ERROR [net.sf.hibernate.util.JDBCExceptionReporter] Line 1: Incorrect syntax near '.'. ...

70. Set with formula column is it possibile?

....... ...

71. formula with parameter?

So, here is the problem... I would like to get the count of all permissions on an object for a given user to determine if it is updatable or not by that user. The count is to be retrieved when the object is, not with a separate query. Also, the whole thing should work with .scroll() , not just .list(), because ...

72. One-to-One formula challenge!

Hi, I think I have a real challenge here. I have 2 links to do between 3 database tables (2 one-to-one relations). My first table is AddressBook having a composite-key : AddressType and AddressKey. I want to link it with theses 2 next tables using a one-to-one relation: The second table is Composante having also a composite-key: Institution, Transit and ServiceCenter. ...

73. New keyword to not get parsed in , etc.

I've patched my hibernate3 to include "top" as a keyword, as I need it in a . Is that the right place to put it? I can't find any dialect-specific equivalent to I don't know what servers beyond SQLServer support top; I'd put the check in a SQLServer-specific place, if I knew where to do it. Is there anything specific ...

74. How do I do a simple reference table lookup (join? formula?)

I'm using the standard relational database design of using columns with either a integer or a char code of some sort which links to a reference table that has descriptions of the code. Basically, I want to display the meaningful name/description to the user but use the code behind the scenes (again, standard relational stuff). How do I map this simple ...

75. Is it possible to order by computed columns without formula?

Need help with Hibernate? Read this first: ... AskForHelp Hibernate version: 3.0.5 Mapping documents: ... Name and version of the database you are using: SQL Server 7.0 Hi all, I need to order records by computed column, in this case the column is 'totalBruto' which ...

78. UNION in Property Formula

I'm getting the folowing generated SQL from a formula. The only problem with it is that the UNION operator is getting a "this_" appended. I've already read that formula suports "SQL Expression", so i'm confused if UNION is not supported in this context, or if i'm doing something wrong? If it is not supported, is there any way to avoid de ...

79. How to use formula?

How to use formula? I use the attribute 'formula' to subselect the numbers of all rows in the 'property' tag, it runs smoothly. I also want to sum some two colomns values, but I don't know how to write the expression. The following is the mapping I wrote and the exception: Caused by: java.sql.SQLException: Unknown column 'jobs0_.minLvl' in 'field list'

80. property and formula

81. Need Formula to return constant string value

82. many-to-one formula usage

SELECT pk_column.impo_id from key_constraint primary_key ,key_constraint_column pk_concol ,column_definition pk_column WHERE primary_key.impo_id_reldf = impo_id AND ...

83. Formula problem

Hibernate version: 2.1.8 Mapping documents: Domain object: /** * The total price per stock of the invoice lines. */ private double totalPricePerStock; /** * @param totalPricePerStock The totalPricePerStock to set. */ public final void setTotalPricePerStock(final double totalPricePerStock) { this.totalPricePerStock = totalPricePerStock; } /** * name="totalPricePerStock" * formula="FAR_AANTAL*FAR_PRIJS_PER_STUK" type="double" * * @return Returns the totalPricePerStock. */ public final double getTotalPricePerStock() { ...

84. @formula annoation difficulties

85. Can't have formula property running

Full stack trace of any exception that occurs: org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.exception.ErrorCodeConverter.convert( at org.hibernate.exception.JDBCExceptionHelper.convert( at org.hibernate.loader.Loader.doList( at org.hibernate.loader.Loader.list( at org.hibernate.loader.criteria.CriteriaLoader.list( at org.hibernate.impl.SessionImpl.list( at org.hibernate.impl.CriteriaImpl.list( at org.apache.jsp.jsp.index_jsp._jspService(org.apache.jsp.jsp.index_jsp:121) at org.apache.jasper.runtime.HttpJspBase.service( at javax.servlet.http.HttpServlet.service( at org.apache.jasper.servlet.JspServletWrapper.service( at org.apache.jasper.servlet.JspServlet.serviceJspFile( at org.apache.jasper.servlet.JspServlet.service( at javax.servlet.http.HttpServlet.service( at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter( at org.apache.catalina.core.ApplicationFilterChain.doFilter( at org.apache.catalina.core.StandardWrapperValve.invoke( at org.apache.catalina.core.StandardContextValve.invoke( at org.apache.catalina.core.StandardHostValve.invoke( at org.apache.catalina.valves.ErrorReportValve.invoke( at org.apache.catalina.core.StandardEngineValve.invoke( at org.apache.catalina.connector.CoyoteAdapter.service( at org.apache.coyote.http11.Http11Processor.process( at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection( ...

86. Formula with standard MySQL function

87. invalid outer join generation on 'formula' with 3.1?

Newbie Joined: Wed Dec 28, 2005 8:13 am Posts: 2 Location: Recife, Brazil Hi all, I'm working on a project with a poor data-model, which was inherited. We cannot modify everything at once, so with Hibernate 3.0 we've successfully mapped tables to POJO's and things worked fine. I'm looking at upgrading to 3.1 in the future to benefit from a bug ...

89. formula question

Hello, all! I have entity with two foreign keys (FK) from Y, X. There is also entity B, also with FKs from Y,X. Database scema can't be changed. I need a connection from A to B based on a condition that X and Y are equial in both A and B. I mean, i need to have B in A ...

90. abt

91. issue with subselect in

92. formula help

Hibernate3.1: I have Table A and Table B. Fields in Table A ------------------ x - primary k y - foreign key z fields in Table B ------------------ y - primary key q r Tables A and B are joined by y and y. In the mapping file I want to specify a one-ton-one where class B resides inside class A. How ...

93. use hibernate formula with mySql 4.0

I am using MySql 4.0.23, and I want to use but this results in SQL select ..., ( SELECT (l.rows * l.cols) FROM landscape l where = ) as formula0_0_, ... from landscape this_ where ... but MySql 4.0 doesn't support subqueries. ...

94. Using Function in formula

95. error in Property formula

96. Using Formula with non "basic" type

Hibernate version: 3.1.2 Full stack trace of any exception that occurs: Caused by: org.hibernate.MappingException: Could not determine type for: de.innovalan.jpelas.domain.impl.User, for columns: [org.hibernate.mapping.Formula( (SELECT p.created_by FROM Post p, Thread t WHERE p.thread_id = thread_id ORDER BY p.created_at DESC LIMIT 1) )] at org.hibernate.mapping.SimpleValue.getType( at org.hibernate.mapping.SimpleValue.isValid( at org.hibernate.mapping.Property.isValid( at org.hibernate.mapping.PersistentClass.validate( ... In the Entity "Post" the property "createdBy" of type "User" is ...

97. Property formula (sum) and updates

98. Problem with the Formula Tag

Need help with Hibernate? Read this first: ... AskForHelp Hibernate version: 3.0 Mapping documents: