condition « Query « JPA Q&A

1. Is there any .net class for query conditions like DetachedCriteria in Hibernate, but not rely on Hibernate?

I'm writting a general DAO interface and facing a difficult in general Query operation. Query operation requires a set of query conditions. I'd like to give a interface like

IList<TEntity> Query(DetachedCriteria ...

2. Hibernate Criteria Query - nested condition

I can't figure out how to create a query like this with Hibernate Criteria synthax

select * from x where x.a = 'abc'  and (x.b = 'def' or x.b = 'ghi')
Do ...

3. Hibernate, how to count with condition

I am using oracle and hibernate for mapping. I want to count with a condition in count() function. my code is:

select count(case when st.averageMark < su.gradePass then 1 else ...

4. Use column of join in where condition

I do the fallowing HQL with NHibernate:

from Contact a where IsInternal = 0
this give me the fallowing sql (from NHProfiler):
select TOP ( 25 /* @p0 */ ) contact0_.Id    ...

5. Hibernate createQuery remove() in WHERE condition

let's say I have entity Person. When I try to use with hibernate createQuery, it remove ( ) in where condition. Example:

Query query = session.createQuery("FROM Person WHERE name=? OR (id=? AND active=?)");
query.setParameter(1, ...

6. Hibernate: Conditional query not evaluated correctly

I'm trying to get the following query to work in Hibernate:

SELECT m FROM MyEntity m WHERE m.owner = :user 

7. Build condition with date for jpa2 typesafe query

I have following query:

FROM Projekt p
WHERE p.bewilligungsdatum = to_date('01-07-2000', 'dd-mm-yyyy')
but i have problems to build the conditions. Here my code:
condition = criteriaBuilder.equal((Expression<String>) projekt.get(criterion), "to_date('" + projektSearchField + "', ...

8. Hibernate query building based on conditions

I have a form where user can select search criteria. The criterias are say:

Product Name: Input field
Name Option: Radio button group - begins with (default selected)/ is/ contains
Country: dropdown of country
Status: ...

9. Allow extra join conditions to be specified in HQL join clauses -

I have the following query where I want to add further restrictions to the join join clause. I am using the 'wtih' keyword as in written in the hibernate docs. However, I am always getting the following error a org.hibernate.hql.ast.QuerySyntaxError: unexpected token. If I take out 'with p.deleted is null' it works. from Category as c left join fetch c.member left ...

10. Hibernate Query with AND condition

11. Updating query with multiple joins in where condition

Hi, I wanted to update a table data based on multiple joins with different table keys in where condition. My query is as follows. Update table1 t1, table t2, table t3 set t1.col1=?, t1.col2=? where t1.col1=t2.col3 and t2.col3=t3.col4 I wanted to write the above query using Hibernate query language and update the table1 data based on the joins with t2 and ...

12. Swedish letters in like condition

How do I get hibernate to understand that I want to search on swedish letters?"name", "%") returns everything starting with a A or not only . The same goes for . For it returns everything starting with O and . If you cant see the letter because of some encoding problem, its a A with one dot above. ...

13. HQL query condition wrong column resolution

@Entity @Table(name = "template_queue") public class TemplateQueue { @Id @GeneratedValue(strategy= GenerationType.IDENTITY) private long id; @ManyToOne @JoinColumn(nullable = true) private Queue queue; @ManyToOne private Template template; ...

15. Extra condition clause on a left join

I have the following tables: Market ------------------- Market_ID MarketAbbrev DownLog ------------------- DownLog_ID Market_ID BroadcastDate There is a one-to-many relationship from market to downlog. Here is the SQL query I am trying to Hibernate: select m.* from market as m left join downlog as d on m.market_id = d.market_id and d.broadcastDate = ? where d.market_id is null; What ...

16. Why h2 ignore query condition in parent table in MSSQL?

Hibernate version :2.1rc1 mapping documents :

17. additional conditions in join clauses

I have a query that I found needs an additional condition in join clause. Is this kind of feature planned in Hibernate? Example: Code: select, dr.dueDate,,,, from Survey s right outer join s.dueReport dr *** with s.merchandiser = ? *** join dr.surveyDefinition sd join sd.contractor c, Merchandiser m join m.tasks t join t.depot d where s ...

18. additional conditions in join clauses

I have a query that I found needs an additional condition in join clause. Is this kind of feature planned in Hibernate? Example: Code: select, dr.dueDate,,,, from Survey s right outer join s.dueReport dr *** with s.merchandiser = ? *** join dr.surveyDefinition sd join sd.contractor c, Merchandiser m join m.tasks t join t.depot d where s ...

20. Criteria Query Question, ignore case for in condition

Hi, Using Criteria query api, how do I write a where condition, which will give me list of equipments for case-insensitive equipment names, I have available in a Collection. I understand that for case sensitive where clause, I can use the following ... I already have a list of equipment names in namesCollection converted to upper case. Criteria criteria = session.createCriteria(Equipment.class); ...

21. conditional select

Hello ! I am using Hibernate version:2.1.6 with MySQL 4.1.7. I have a table that has two columns that would represent a date: month and year. I want to select a row that's month and year are before the specified month and year, which means the year is <= than the specified one, and the month would also be <= than ...

22. Hibernate Filters Like Condition

23. Hibernate HQL left join and additional ON clause conditions

Hello, i have a question, how do i get the following statement done with HQL: SELECT ..... FROM Company company LEFT JOIN loginUsers l ON company.loginUserID = l.loginUserID AND l.loginUserActive=1 OR l.loginUserID=12 the "ON" statement is my problem, where i could have OR / AND or NOT connected statements. Hibernate creates its standard ON statement (i guess) which is something like ...

24. SQL order by condition

Hi We need an SQL like this: ======= select lpad(code, 5, "0") as paddedcode, * from cat_table order by paddedcode; OR select * from cat_table order by lpad(code, 5, "0"); ======= All it needs is an sql alias which is used in the order by field. Hibernate should still populate the object (cat in this example) as normal. Is this possible ...

26. Appending Or condition in the Where clause of the HIB SQL

Hi all, Hibernate version : 3.0.5 DBMS : Oraclce 9i Iam having the problem in using the criteria Queries, In my project Iam having three table in which one is master table and other two table has a relation ship with this master table parent --------------------- child1 one to one parent --------------------- child2 one to many consider parent = T_Quotation_mstr table ...

27. Many-to-any query IN condition

28. many-to-any IN condition query problem

29. SQL conditional select -> HQL help

30. How to create a query with multiple OR/AND conditions

What if i need to write a query that matches partial values and returns all the items. Equivalent sql should be select * from table where propName like '%value1% or propName like '%value2% or propName like '%value3% or propName like '%value4% I need to write a Hibernate criteria for the above, how can i do that?

31. if condition in hibernate query

Hi, I am a novice in Hibernate field. I have a SQL query like select distinct atd.docID, atd.Doctitle,col.collectionName, atm.ActionPerformed,atm.ActionDateTime,atm.UserID, (Case when atd.totalNoOfAccess = -1 then a.TotalNoOfAccess else atd.totalNoOfAccess end)TotalNoOfAccess, (Case when atd.DocID not in (Select DocID from TblDocMasterIdentification) then 'Deleted' else dmi.status end)CurrentStatus from TblAuditTrailMaster atm ,TblAuditTrailDoc atd, TblCollections col,tblDocInformation a,TblDocMasterIdentification dmi where atm.AuditID = atd.AuditID and atd.CollectionID = col.CollectionID and ...

32. Adding a condition in WHERE clause

Hi, Lets say my db have the following tables: TABLE-A TABLE-B Col1(PK) ColB1 Col2 ColB2 Col3 ColB3 Col4 Col5 In TABLE-A, Col1 is mapped to Generator Class in hbm file. In TABLE-B, my contents will be ColB1 ColB2 ColB3 ----------------------------------- 1 121 N 2 131 N 3 141 Y (Only one row will have 'Y') I want to map the TABLE-A ...

33. LIKE condition in HQL

34. HQL Query with condition at 4th level class association

Hi, I have a class structure like this public class Product { private productId; private ProductUse productUse .... } public class ProductUse { private Set useCategories = new HashSet //Collection of Category ..... } public class Category { private long categoryId; //primaryKey private CategoryHead categoryHead; .... } public class CategoryHead { private long categoryHeadId; //Primary key .... } I want to ...

35. how to write a query with and condition

36. How to set value in where condition of update query ?

Hi All, I tried one Hibernate update query like this. function() { Bean b = new Bean(); b.setName("Name"); b.setDesc("Desc"); b.setStatus("A"); b.setID(107); getHibernateTemplate().update(b); return null; } In this case, one update query is generated which is having its primary key i.e. Id of the table in where condition. Now I want name and status in where condition and on the basis of ...

37. conditional where clause: CASE WHEN or Criteria

39.  bug(?) : problem building a join condition

40. conditional clauses

I have a search form that allows a users to enter their name. depending on which part of their name they enter in that field i want to compare it against three different columns in the database. Eg Name Cameo Alhassan table columns cli_title, cli_forename, cli_surname, i want to do compare the entry in the search form against these fields so ...

41. Query with where condition