criteria

Hibernate criteria _ how to use criteria to return only one element of an object instead the entire object

Hello, I'm trying to get only the list of id of object bob for example instead of the list of bob. It's ok with a HQL request, but I would know if it's possible using criteria ? An example : final StringBuilder hql = new StringBuilder(); hql.append( "select bob.id from " ) .append( bob.class.getName() ).append( " bob " ) .appe...

Hibernate Query By Example and Projections

Hi all, To make it short: hibernate doesn't support projections and query by example? I found this post: The code is this: User usr = new User(); usr.setCity = 'TEST'; getCurrentSession().createCriteria(User.class) .setProjection( Projections.distinct( Projections.projectionList() .add( Projections.property("name"), "name") .add( Proj...

Hibernate -- load an object based on a member object's field

How can an object be loaded via Hibernate based on a field value of a member object? For example, suppose the following classes existed, with a one-to-one relationship between bar and foo: Foo { Long id; } Bar { Long id; Foo aMember; } How could one use Hibernate Criteria to load Bar if you only had the id of Foo? The fi...

Hibernate: Criteria vs. HQL

What are the pros and cons of using Criteria or HQL? The Criteria API is a nice object-oriented way to express queries in Hibernate, but sometimes Criteria Queries are more difficult to understand/build than HQL. When do you use Criteria and when HQL? What do you prefer in which use cases? Or is it just a matter of taste? ...

How to use MySQL functions in Propel

I want to select records that are 1 month old or newer. The query is: SELECT * FROM foobar WHERE created_at > DATE_SUB(curdate(), INTERVAL 1 MONTH) Using Propel in Symfony, I do: $c = new Criteria $c->add(FoobarPeer::CREATED_AT, "DATE_SUB(curdate(), INTERVAL 1 MONTH)", Criteria::GREATER_THAN); What Propel generates is: SELECT...

Querying ManyToMany relationship with Hibernate Criteria

I'm not sure how to describe this problem, so I think an example is the best way to ask my question: I have two tables with a manyToMany relationship: DriversLicence <-> LicenceClass LicenceClass is things like "Car", "Motorbike", and "Medium Rigid". Using Hibernate Criteria, how can I find all licences that have both "Car" and "Moto...

NHibernate 2.0.1 Criteria: IsNull restriction throwing exception

I've been utilizing NHibernate 2.0.1.4000 on all current .NET 3.5 SP1 projects and have had no problems with any other queries (utilizing either the Query or Criteria APIs), until some new business logic dictated the necessity of a new query in this particular project against the application's database that needs to retrieve records with...

How to get distinct results in hibernate with joins and row-based limiting (paging)?

I'm trying to implement paging using row-based limiting (for example: setFirstResult(5) and setMaxResults(10)) on a Hibernate Criteria query that has joins to other tables. Understandably, data is getting cut off randomly; and the reason for that is explained here. As a solution, the page suggests using a "second sql select" instead of...

Hibernate transform SQL to Criteria

Hello, I 'd like to use Criteria for my SQL query. I have 3 tables "home", "person" and a third table "liveIn" for correspondance between home and person. My sql query is "select home.id from home, person, liveIn where home.country = 'Japan' and person.id = '15' and liveIn.Homeid = home.id and liveIn.PersonId = person.id" A little hel...

How to a write a Criteria query with multiple joins involved

Hi, I'm trying to code the following HQL query using the Criteria API: var userList = _session .CreateQuery("select u from User u where u.Role.ID=3 and u.Customer.ID=:cID") .SetInt32("cID", 1) .List<User>(); (3 NHibernate objects : User(ID, Name, Role, Customer), Role(ID, Name) and Custo...

How do I limit the result set being pulled back by NHibernate's GetByCriteria?

I have an NHibernate Dao..lets call it MyClassDao for want of a better name. I am writing the following code. MyClassDao myDao = new MyClassDao(); var values = myDao.GetByCriteria(Restrictions.Eq("Status", someStatusValue)); I am using this in a Unit Test to pull back values from the database. However, it is taking over 30 seconds t...

How do IN () statements work in NHibernate? (Using Criteria)

I'm trying to create the equivalent of the below using NHibernate. I've done all the mappings using fluent and I can do the basic queries just fine but I have no idea how to do this. -**Product Table** Reference Title Subjects (Many to Many relationship) Price -**Subject table** SubjectID Name -**SubjectToProductMapping Table** Refere...

hibernate criteria - querying tables in n:m relationship

I'm trying to build a query with hibernate criteria for the following scenario: Two entities: Indicator and report (each with their own tables, classes etc.) an indicator can be used in zero to many reports a report uses zero to many indicators therefore, I have an intersection table to store the relationship the relationship is define...

Criteria question with NHibernate

I'm trying to use NHibernate for data access, and I have 2 simple entities that look like this: public class User : IEntity { public int ID { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public string Email { get; set; } public string Logon { get; set; } public string Password ...

How to set more than 2 Expression in Expression.Or

I want to create a query which has more than 3-4 Expression.Or ? But Expression.Or just let me to add two Expressions inside it. if (!string.IsNullOrEmpty(keyword)) query .Add(Expression.Or( Expression.Like("Name", keyword, MatchMode.Anywhere), ...

Getting DATEPART in HQL or Criteria ?

How can i get DATEPART of a time using HQL or Criteria ? I have googled it up and get some tips, but wasn't enough. If there is someone who has experienced it before, please let us know. Thanks in advance ...

Symfony Propel criteria

Hi, Is there any possible way to convert the MySQL object into criteria object? I tried this query "select p.disrepid, p.subject, p.body, c.disrepid as disrepid1, c.subject as subject1, c.body as body1 from discusreply as p, discusreply as c where p.distopid='.$this->id.' and (c.disrepid = p.parentid or c.parentid = p.distopid) order by...

How can I access to the properties of an object list returned by criteria query inside of itself query

Hello, I'm triying to access inside of the properties of an object inside of an criteria query. I have the follows: <list name="locationsNatural" schema="public" table="natural$locations" lazy="false"> <key column="locations_" /> <list-index column="locationsindex_" base="1"/> <many-to-many class="com.idom.dto.estr.Locatio...

Hibernate Criteria API - HAVING clause work arounds

I've written a query using Hibernate Criteria API to grab a summation of a particular value, now I need to be able to restrict the result to rows where that sum is greater or equal to a particular value. Normally I would use a HAVING clause in my SQL to do this, but the Criteria API doesn't seem to support that at this moment. In raw ...

NHibernate: can't successfully set lazy loading

I have a table Parent and a table Child. Child contains a foreign-key to the Parent table, creating a one-to-many relationship. Here is a part of my mapping that I define with fluent NHibernate: public class ParentMap : ClassMap<Parent> { public ParentMap() { WithTable("Parents"); Id(x => x.Id, "ParentID") ...