Expression 1 « Core « JPA Q&A

1. Is there such thing CASE expression in JPQL?

Let say there is a table:

TableA:Field1, Field2, Field3
and associated JPA entity class
public class TableA{
  private Long id;

  private Long field2;


2. Storing conditional logic expressions/rules in a database

How can I store logical expressions using a RDBMS? I tag objects and would like to be able to build truth statements based on those tags. (These might be considered as ...

3. NHibernate: HQL equivalent of Criteria Expression.In()?

How would you express the following Criteria query in HQL?

var idArray = new int[] { 1, 2, 3, 4, 5 };

    .Add(Expression.In("Id", idArray)
I am ...

4. Expressions in hibernate criteria

Let's say I have a persistent class Item with a quantity field and a price field. Is there a way to build a Criteria that calculates the sum of quantity*price?

5. NHibernate HQL Query: Expressions in the Select clause

Is there any way to use expressions in the Select clause? E.g.: select u.Age/2 from User u I'm having this exception right now: NHibernate.QueryException: ',' expected in SELECT before:/ [select u.Age/2 from Business.Entities.User u]

6. Does anyone know how to translate LINQ Expression to NHibernate HQL statement?

Does anyone know of an existing solution to translate a LINQ Expression to HQL statement? Thanks in advance to all the good samaritans out there. P.S. We already use Linq to NHibernate. However, it ...

7. Nhibernate ICriteria and Using Lambda Expressions in queries

hi i am new in NHibernate and i am a little confused. Suppose we have a product table. Let the product table have 2 columns price1 and price2. then i may query mapped ...

8. Using Hibernate to 'order by' a column which having a expression(sum, max, ...)?

group by  CONTRACT_ID 
order by  sum(PO_SPEND) desc

9. Is there a library etc that walks joins in property path expression in a Hibernate Restriction property

It seems irritating that property paths that involve joins must be broken down into multiple criteria. This means that code building a HQL is considerably bloated especially when the path has ...

10. JPQL LIKE expression on enum

Can JPQL execute LIKE expressions against enums? If I have an entity Foo with an enum field bar I can execute the following in MySQL(bar is stored as a MySQL ...

11. HQL query: how to use "is empty" expression?

I am a beginner in NHibernate queries. And an easy task enters me into a stupor. Trying to get list of hotels filtering by cityIDs (where the hotels are located in) ...

12. how to use regular expression in EJB named query

I have query in mysql like SELECT COUNT(DISTINCT MOBILE) FROM TBLM_CUSTOMER WHERE MOBILE NOT REGEXP '^00*0$' AND LENGTH(MOBILE) >= 10; I am using EJB 3.0 and I want to make same query ...

13. hql conditional expressions in aggregate functions

Does HQL support conditional expressions in aggregate functions? I would like to do something like this

  count(o.something is null and o.otherthing = :other)
from objects o
But I ...

14. How to use regular expressions with Hibernate/Oracle

I'm trying to implement a web service which accepts a list of strings, each of which is a regular expression. These need to be compared against six columns of a ...

15. How to write Order by expression in JPQL

PostgreSQL and MySQL offers to write expression into ORDER BY clause in SQL query. It allows to sort items by some column but the special values are on the top. The ...

16. JPA 2.0, Criteria API, Subqueries, In Expressions

I have tried to written a query statement with a subquery and a in expression for many times. But I have never succeed. I always get the exception, " Syntax error near ...

17. Hibernate, Postgresql: Column "x" is of type oid but expression is of type byte

I have a strange problem regarding the hibernate mapping containing large objects (BLOB), when switching between different databases.

private byte[] binaryData;
The field above creates a byte array field in MySQL and in ...

18. How to break expression into more lines in Hibernate's import.sql?

In Hibernate there is possibility to add import.sql file in root of classpath, and the SQL expressions from this file are executed on database when Hibernate session is created. However, Hibernate ...

19. JPA2 critera query in-expression on FK issues n select statements, where n is #values

I have the same issue using hibernate against DB2 and MySQL. Here is a test:

        EntityManager em = emf.createEntityManager();

20. JPQL/HQL and JPA/Hibernate: boolean expression in select constructor expression not working (unexpected AST node: AND, NPE, HqlSqlWalker.setAlias)?

I have a JPQL statement to return a schedule of sports games:

SELECT NEW com.kawoolutions.bbstats.view.ScheduleGameLine(              ...

21. JPQL: boolean expression in CASE WHEN needs explicit comparison with TRUE/FALSE?

I had tried the following:

SELECT NEW com.kawoolutions.bbstats.view.ScheduleGameLine(                     ...

22. Hibernate: is there the way to use "like" expression in filters?

I use hibernate filters massively, but face the problem with using like expression together with '%' literals. So, I can use this:

@Filter(name = "filterOnTS", condition = "lower( like :carName)"),
but cannot this:
@Filter(name ...

23. Criteria API: same query, different resultset types (constructor expressions)

I have a method, which generates a JPA Criteria:

private CriteriaQuery<ResultSetType> createQuery (ReportFilter reportFilter) {
    List<Predicate> criteria = new ArrayList<Predicate>();
    CriteriaBuilder cb = em.getCriteriaBuilder();

24. JPA Criteria API select distinct on String Lob with Constructor Expression

I have a static method that creates a criteria query:

public static CriteriaQuery<ReportInfo> reportInfoQuery(EntityManager em){
    List<Predicate> criteria = new ArrayList<Predicate>();
    CriteriaBuilder cb = em.getCriteriaBuilder();

25. Accent in Regular Expression in Java

I'd like to use Hibernate Validator to validate some columns. The problem, as I understand, is that the \w marker in java doesn't accept letters with accents on them. Is there any ...

26. Case Expression in Hibernate Query Language

i am new to NHibernate and Hibernate Query Language, i have the following two tables:

table Categories(
Category_Id int primary key,
Cat_Type_Id int foreign key references Category_Types(Cat_Type_Id),
Category_Name varchar(20) not null,
Mode int not null /* ...

27. JPQL: Receiving a Collection in a Constructor Expression

I'm using JPQL and want to receive some normal parameters and a collection in a Constructor Expression to directly create the DTO-objects. But if the Collection is empty, I always get ...

28. JPQL: Problems receiving a Collection in a Multiple Select Expression

i'm using JPQL of JPA 2.0 and have just a simple Question. I have a Multiple Selection Expression and so i want to receive barely this:

Object[] objects = ... SELECT String, Collection<Integer>
I ...

29. Jpa QueryBuilder Multiple expressions in where clause not working

I am having an issue with creating queries with javax.persistence.criteria.CriteriaBuilder. I am using EclipseLink 2.1 and an Oracle 10g database. When building a query with multiple restrictions it will use only ...

30. NHibernate Execute SQL Query with case expression problem

i am trying to write the following sql query in HQL :

 select @Total_Budget = sum(Budget.Budget_Money * case when Categories.Mode = 0 then DATEDIFF(D,@From,@To) Else 1 end ) from Budget right ...

31. HQL Expression equivalent for data types

Background : I need to create a custom like extension method for linqtohiberante to do the below,

 result.Where(p => p.MyIntColumn.IsLike('%100%') );
so that would output a custom sql as follows.
select ...

32. jpa 2.0 criteria api expression for sql minus

How do you create a jpa Criteria expression that is equivalent to the sql minus statement example

  SELECT mod_code
  FROM SRS.Table1
  SELECT mod_code
  FROM SRS.Table2;

33. Retrieve all Entities that contains specific letters using criteria expression?

HEllo I have this scenario to search for specific user and the result that should appear is all users that contains the letters in the search text field ex. if I ...

34. How to order results by customer expression in Hibernate/HQL/JPQL

I have persistent entity Cats (id, name). I want to query all cats (using HQL/JPQL) that cats named 'Tom' were in top, i.e. order by custom expression name='Tom'. In Oracle I ...

35. help with expression for nHibernate linq provider extension

I'm working on a custom linq extension for nHibernate by extending the BaseHqlGeneratorForMethod. The technique is documented here: I've had success with implementing these for various types of operations, ...

36. Inline expressions in HQL

What is the most effective way to write this sql query in HQL

select uar.*, (a.default_user_asset_role_id is not null) as is_default  from User_asset_role uar
left outer join account a on a.default_user_asset_role_id = ...

37. How to create constant numeric value expression in QueryDSL?

I'd like to create query like this with QueryDSL

update WorkMessage w set w.totalPrice = 0.12 - w.totalCost;
I tried like this
    Expression<Float> priceExpr = Expressions.constant(0.12f);

    new ...

38. hibernate criteria like expression for Long

Hi, I am using Hibernate criteria for search purposes. I have an option to search the objects using its id.(primary key). This id search should be like search and not exact search. i.e If i give 1, then all objects having key start with 1 should be displayed. In my pojo this id is Long. (if i concat % to this ...

39. hibernate expression query week of the year

Hi there, I'm using JBoss 4.0.5 and it built in Hibernate 3. Into a POJO I have the following query, which should returns me all objects having the same district id and matching week of the year: @NamedQuery(name = "findShiftByWeekAndDistrict", query = "FROM Shift s WHERE week(s.week) = :weekInput AND district = :mrgreen: istrictInput") from the ejb, I call it using ...

40. Hibernate : Restrictions Vs Expression

44. discriminator-value with logical expression

Hi Is it possible to write discrimination-value with 'or' expression? I have one table witch I want to map in three class, for instance A,B,C. Class B and C extends A. The problem is that one row in table can be both B and C. I'd like to use subclass tag with discriminator-value attribute but I don't know how to write ...

45. Ordering by an expression with Criteria API?

you need to subclass Order: Code: public class SQLOrder extends Order { private static final long serialVersionUID = 1L; private final static String PROPERTY_NAME = "uselessAnyways"; private boolean ascending; private String sql; protected SQLOrder(String sql, boolean ascending) { ...

48. Multiple columns on a count expression

Hello there! I need to do a count using multiple columns but I get the following error: expecting CLOSE, found '(' I searched a lot about this issue and my conclusion is that hibernate does not support count(distinct t.col, t.col2) or something like that. The table I want to do this count has a composite id and once this is a ...

50. Expressions in JDBC Connect fields

Can we use an expression language to set parameters to JDBC at run time? In particular we want to make changes to the URL to add arguments, and to change the Username and Password values from the typical proxy user and password to an actual logged in user's username and password. We would want to get the values from the current ...

51. creating a select count(*) expression with JPA criteria

I need to build a select count(*) expression using JPA criteria. I know HQL supports this kind of query select country, count(*) from census c group by I don't know how to create a count(*) expression using the JPA CriteriaBuilder. The JPA builder has a count method Expression count(Expression x) Create an aggregate expression applying the count operation. However you ...

52. ORA-00936: missing expression with a Query - Hibernate 3.3.2

Hello, I have a stange error with this HBM query : Code:

53. Reverse Engineer regular table filter regular expression

After realizing reverse engineering table-filter does not support full regular expression, I modified my reveng.xml: My reverse engineering xml should get tables starting with name (AA_) (BB_) but excluding tables ends with (_ID), as such: Why do I keep getting duplicate table error? How many threads does it spawn? ...

54. Date object and constructor expressions

hi I was recently using constructor expressions in my hql queries and was supprised to see that selecting a field in an entity in this way of type java.sql.Date returned me an object of type java.util.Date. Have anybody else encountered this issue..? or can somebody explain me why this is the expected behaviour, if thats the case..? cheers Rasmus

55. how to use the hql expression "hour(date)"

56. MappingException: write expression must contain exactly one

I am using Hibernate read/write properties to decrypt and encrypt data while reading and writing. I am able to read the values but I am unable to write data into DB. My configuration would be something like below, followed by the exception stacktrace that is noticed Code: ...

57. Expression.isNull("field") returns all records

criteria = session.createCriteria(WorksheetHeader.class).add( Expression.eq(WORKSHEET_ID, new Integer(worksheetID))); criteria = criteria.createCriteria("products"); criteria = criteria.add(Expression.isNull(PRODUCT_ID));

58. Expressions and data types - can I just use Strings?

I'm trying to use the QueryByCriteria API to allow users to build dynamic queries. Basically, I'm presenting them with a drop-down that contains all the tables/column that they're able to search on. Then they're presented a drop-down of expressions (i.e. <, >, =, !=) and finally a text box for the value they want to search for. I noticed that when ...

59. problem with criteria / expression

I have a mapping many to one fairly standard I think a "titles" class with a genre property which is an instance of a genre class I can do this HQL successfully: "select titles.genre.genreid from Titles as titles" BUT I am using the criteria api to build dynamic queries, and when I attempt to add crit.add(Expression.eq("genre.genreid", x)); ...

60. problem

61. IN expression follow by a SELECT

Hye, I'm a newbie and have a trouble with the following query : List list = session.find("from Pelftvllotpn A where timestamp(A.ddlanpn,A.dhlanpn) in (select timestamp(X.ddlanpn,X.dhlanpn) from Pelftvllotpn X)"); An exception is thrown about the second timestamp : select timestamp(X.ddlanpn,X.dhlanpn) from Pelftvllotpn X. It is : "undefined alias: timestamp ..." However, the following query works : List list = session.find("from Pelftvllotpn A where ...

62. Using expressions in select

Hi, I have a table with three int fields (a,b,c) and want to get the sum of them in a select this way: select a+b+c as result from table It seems that you cannot use expressions in the select, but only in the where clause. Is there a possibility to do such a select? TIA, Thorsten

63. Create Expression for associations without createCriteria.

Hi, Is it possible to create an expression on attributes of an associated object by using just Expression and not createCriteria() for the association ? I'd like to keep the layer in my application that constructs the expressions free from the Criteria stuffm and just pass in a collection of Expressions for the query. If I use createCriteria, I have to ...

64. 'case' expression in SQL-statement

Hello! I'm using hibernate2.1 and firebirdsql (dialect=net.sf.hibernate.dialect.InterbaseDialect). I have problem for inheritance. I have 3 classes - Person and Organization inherits Subject (joined-subclass). For expression: session.load(Subject.class, new Integer(1)); , hibernate generates next SQL-query: select organiza1_.ID as ID0_, organiza1_.FULLNAME as FULLNAME2_0_, organiza1_.BRIEFNAME as BRIEFNAME2_0_, organiza1_.ORGPHONE as ORGPHONE2_0_, organiza1_.ORGMAILTO as ORGMAILTO2_0_, organiza1_.ID_PARENT as ID_PARENT2_0_, organiza2_.ID as ID1_, organiza2_.FULLNAME as FULLNAME2_1_, organiza2_.BRIEFNAME as BRIEFNAME2_1_, ...

65. negation of an expression

Hello, In my application a have a Project class with a collection of Persons. I want to present a list of Persons in the database which are not in this collection, so if I have 4 Persons in the database and I have a project which has person1 in it's collection, I want to show person2 till person4. Therefore, I've made ...

66. Expression missing a "NotEquals" method?

67. Many to Many expression in HQL

I have a many to many relationship between two tables A and B. I am trying to figure out how to write a HQL statement for the following scenario : Table a with attribute id and att1 Table b with attribute id and att2 The persisters for the two have a collection for the other ie a.b's and b.a's Now how ...

68. SybSQLException: Select expression results in more than one

Posted this bug report a week ago, but still no luck/responses. Anyone with ideas how to attack this one? I'm using Sybase ASE 12.5 and Hibernate 2.1.2 seems to have introduces a couple of annoying bugs. I went back to beta 6 last night and it work fined. Therefore, I'm pretty sure the following two items represent 2.1.2 bugs. Hope someone ...

69. What is difference between Expression and Criterion ?

Hi, I'm a hibernate beginner. I read a documented linked here. About Data Access Layer - "" -. And I found some deprecated(?) API Criteria.add(Expression) used until version 2.1beta-2. Criteria.add(Criterion) used from 2.1beta-3, but I couldn't find any description about this change, so compile error occurred. Have you ever been this problem ? I want to use "Data Access Layer..". Help ...

70. What is difference between Expression and Criterion ?

Hi, I'm a hibernate beginner. I read a documented linked here. About Data Access Layer - "" -. And I found some deprecated(?) API Criteria.add(Expression) used until version 2.1beta-2. Criteria.add(Criterion) used from 2.1beta-3, but I couldn't find any description about this change, so compile error occurred. Have you ever been this problem ? I want to use "Data Access Layer..". Help ...

71. What is difference between Expression and Criterion ?

Hi, I'm a hibernate beginner. I read a documented linked here. About Data Access Layer - "" -. And I found some deprecated(?) API Criteria.add(Expression) used until version 2.1beta-2. Criteria.add(Criterion) used from 2.1beta-3, but I couldn't find any description about this change, so compile error occurred. Have you ever been this problem ? I want to use "Data Access Layer..". Help ...

72. AnyOf in Expression

73. outer or full join must be followed by path expression

I read the posts here, and tried the documentation, so I'm feeling really dense. I just can't figure out a left join. This is how I would do it in pure SQL: select timeslot.queuesize, timeslotdelta.actualQueueSize from timeslot, linkdayplanlocation left join timeslotdelta on timeslotdelta.timestart = timeslot.timeslotstart and timeslotdelta.linkid = where linkdayplanlocation.dayplanid = timeslot.dayplanid and linkdayplanlocation.linkactivedate = '2004-04-10' and timeslot.timeslotstart = '1970-01-01 ...

75. how to use for a collection?

I'd like to know how to make a criterion for deciding whether my object is the element of a collection of a Hibernate object. E.g. A class has Collection props containing Property object. I'd like to know my Property q is the element of A.props. How can I ask it? thanks for help. Thomas

76. Criteria API suggestion (IN expression)

what i want to do is something similar to: q=s.createQuery("from Kitten k where k.parent in (:cats)"); q.setParameterList("cats", catsCollection); catsCollection contains Cat entities (not IDs!).. this works flawlessly and is great.. Now I'd like to do the same with the criteria API, but the InExpression class insists that I specify a property name.. I know its possible to do it like this: ...

77. Outer Join Error - path expression

78. Conditional Search using Criteria and Expression

79. Expression.ilike

80. Using with named paramaters

Hi, I've had a look and I beleive you can't use the, Object[]) with named parameters in named queries. If I could say in my named query: from User user where in (:emailList) And then substitue the 'emailList' named parameter with this code: String[] emails = { "", ""}; List results = session.getNamedQuery("getUsersByEmailAddress") .setIn("emailList", emails )) .list(); Or maybe ...

82. Expression methods

83. outer or full join must be followed by path expression ERROR

Hello. I've got a problem with outer join. There are 2 tables: TABLE_A id | NAME =========== 1 | Mark 2 | Kate 3 | Adolf 4 | Benito TABLE_B id | SPORT =========== 1 | Football 2 | Volleyball and there an application, where I can set what sport do they like (by checkboxes: I pick a name, and I ...

84. Outer or full join must be followed by path expression

Newbie Joined: Tue Jul 20, 2004 12:27 am Posts: 8 Hi, I get the following exception......i have removed the mappings from the Item.hbm.xml and Misc.hbm.xml files.......can somebody help me in fetching data using joins......code is shown below.....Application perfectly works without join relationship..... I am using MySql4.1, JBoss 3.2.3, Hibernate2.1.4 14:56:31,403 ERROR [STDERR] Hibernate Exceptionouter or full join must be followed by ...

85. Expressions in the SELECT clause

86. Node selection expressions

Hi guys. This is only tangentially related to Hibernate, hope it's okay in the Miscellaneous section. I'm finding myself in need of a little expression language for selecting nodes in my object graph. Since my object graph happens to be a tree, I was thinking that xpath might make a good choice. I might be able to write a custom object ...

87. Arithmetic expressions in aggregate functions

Full stack trace of any exception that occurs: net.sf.hibernate.QueryException: , expected in SELECT [ select sum(sq.maxToSend) - sum(sq.sent) from com.tunisiana.mytunisiana.model.SmsQuota as sq where = ? and current_date >= sq.startDate and current_date <= sq.endDate and sq.maxToSend > 0 and sq.sent < sq.maxToSend order by sq.priority desc ] at net.sf.hibernate.hql.SelectParser.token( at net.sf.hibernate.hql.ClauseParser.token( at net.sf.hibernate.hql.ClauseParser.end( at net.sf.hibernate.hql.PreprocessingParser.end( at net.sf.hibernate.hql.ParserHelper.parse( at net.sf.hibernate.hql.QueryTranslator.compile( at net.sf.hibernate.hql.QueryTranslator.compile( ...

88. outer or full join must be followed by path expression

Hibernate version: 2.3.1 Hi i have a doubt with a external query where i use a SQL string and the class Query , this is my function : public List obtenEjecutivosXGrupo(String[] parametros) throws HibernateException{ ResourceBundle bundle; bundle=ResourceBundle.getBundle("resources.GlobalExceptions"); if(parametros==null && parametros.length!=4 && parametros[0].trim().length()==0 && parametros[1].trim().length()==0 && parametros[2].trim().length()==0){ throw new IllegalArgumentException(bundle.getString("cat.obtenEjecutivosXGrupo.errParams")); } try{ String strQry=" SELECT, G.dsGrupo,, U.fgEstatus, P.paterno, P.materno, P.nombre,U.flCampania,C.dsCampania ...

90. Creating "or" expressions w/ Criteria API?

91. Oracle Functions in Criteria Expression

Hibernate version: 2.1 I am trying to do something in the Crieria API that works great in HQL Here is the HQL version: "from Product prod where prod.year = currentyear()" currentyear() is an Oracle function that returns the current year Now, I want to move this query into the Criteria API. Here's my attempt that doesn't work: .createCriteria(getSession(),Product.class) .add(Expression.eq("year","currentyear()")) .list(); This ...

92. Problem of using multiplication sign (*) in hql expression

hi, When i execute the following hql statement: Code: hql = "SELECT " + "a.employee.fname," + "AVG([b]a.scoreItemA * a.coefficient[/b])," + "SUM(a.sumItemB)," + "SUM(a.sumItemC) " + ...

93. outer or full join must be followed by path expression ?!

Session s=HibernateLocalUtil.currentSession(); Transaction t=s.beginTransaction(); String sql="select p.ProjectCode,a.Name from com.digitalchina.gx.common.bean.PubProStatus p left join com.digitalchina.gx.common.bean.PubApplyProinfo a where p.ProjectCode=a.ApplyCode"; List list=s.createQuery(sql).list(); ...

94. being ignored.

Hi, My database is mysql4.1.1, when I query it with just one 'like' expression, it works fine, eg: Criteria criteria = session.createCriteria(npd.hibernate.Plant.class); criteria.add("genus", plantForm.getGenus() +"%" )); list = criteria.list(); The sql that's generated is: Hibernate: select as id0_, as active0_, this.genus as genus0_, this.species as species0_ from plant this where this.genus like ? order by this.genus asc, this.species ...

95. does Expression eq has case insensitive verison

96. mathematical expressions in HQL select clause

of course, i've tried, it didn't run giving: aggregate function expected before ( in SELECT [select (1 / suggestion.numberOfAuthors) from ots.domain.Suggestion as suggestion] when i change the query into this select sum(1/suggestion.numberOfAuthors) from Suggestion as suggestion it worked. in fact this was what i wanted. i was just trying withot aggregate functions before it gets too complicated. now again there seems ...

97. Criteria API and the OR Expression

Hello, I'm using the Criteria API and i Need to produce such SQL from Foo f where a=? and (b=? or b=? or b=? or b=?) but i only managed to produce that from Foo f where a=? and (((b=? or b=?) or b=?) or b=?) Is it possible to limit the brackets ? I'm also obliged to limit the size ...

98. Add to documentation - 11.x Path Expression

99. How to query using a collection: and QBE

Hibernate version:2.1.8 Oracle 10g Java 1.5 I have a collection (Set) of objects and I want to query the db looking for matches. For example, I want some sql that looks like: select BOOK_ID, BOOK_NAME from BOOKS where BOOK_NAME in ("Atlas Shrugged", "Neuromancer"); Hibernate's Example (QBE) is perfect if I have a single Book object, but doesn't work if I have ...