criteria

conditionally creating criteria nhibernate

Say I have a users table and a countries table, something like: tblUsers int UserId int UserCountry tblCountries int CountryId string CountryName and have the mappings etc appropriately organized for each. How would create a query to retrieve all users from a list of countries, if I had something like List EligibleCountries? Someth...

JPQL and SQL Criteria Api

JPA 1.0 specification is not defining the Criteria API which provides a programmatic approach for creating and modifying JPA Queries. Is there any API for building SQL and JPQL Queries like the way Hibernate Criteria Api is doing? ...

NHibernate query for object with child collection property

Hi, I need help with an nhibernate query. I would prefer to use Criteria API if possible, otherwise HQL is ok. I have an Employee object with an Account object property, the Account has a collection of Entry objects, and each Entry has an Amount property. (see class diagram). http://www.threeboxes.com.au/employeeQuery1.jpg I need a q...

howto compile Propel Criteria to SQL

Hi all! How can I compile Propel Criteria to clear SQL? I've tried $criteria->toString(); but this is not I expected. Also I've tried ModelPeer::doSelectStmt($criteria) but it returned raw sql (required parameters substitution) ...

how can I replace instanceof in this case?

I'm trying to compare compareCriteria. Simple ones like 'between' and 'inArray' or 'greaterThan'. I use polymorphism for these classes. One method they share from the compareCriteria interface is 'matchCompareCriteria'. What I'm trying to avoid is having each class check for the type of compareCriteria they should match against. Eg the ...

Parse a search string (into NHibernate Criterias )

I would like to implement an advanced search for my project. The search right now uses all the strings the user enters and makes one big disjunction with criteria API. This works fine, but now I would like to implement more features: AND, OR and brackets() I have got a hard time parsing the string - and building criterias from the stri...

nHibernate Criteria query with missing mapping

Hi, I'm trying to do the following thing: ICriteria criteriaSelect = session .CreateCriteria(typeof(Employees)) .CreateCriteria("Orders") ; var test = criteriaSelect.List(); With: public class Orders{ public virtual int OrderID { get; private set;} } public c...

(Detached)Criteria equivalent for HQL's 'index' function.

I have an IDictionary on an object which I'm loading with the following mapping: public class InternalFund : IInternalFund { public virtual IDictionary<DateTime, IValuation> Valuations { get; set; } } <class name="InternalFund"> <map name="Valuations"> <key> <column name="FundID" /> </key> <i...

Retrieve emebedded or component using Hibernate Criteria api

Hi! I have this class mapped as a entity, lets call it Person. Person has an embedded/component relation to Address. I am having trouble using a Criteria that would return Address objects. I have tried this: Criteria.createCriteria(Address.class) Which does not work. I guess I need to go through the entity but then I would need some...

NHibernate: Add criteria if param not null

I'm trying to retrieve a list of orders based on parameters specified by a user (basic search functionality). The user will enter either an orderId or a bunch of other params, those will get wrapped up into a message, and eventually make their way to the method below. My question is, how do I only look at the parameters that actually hav...

Hibernate Criteria - Exclude records with same id but different attribute values

Hi, I'm trying to find records where status = "a" for a Person but exclude records where the same person has another record with a status="b" SELECT * FROM Person WHERE STATUS = 'a' AND Person_id NOT IN (SELECT Person_id FROM Person WHERE STATUS = 'b' AND Person_id IS NOT NULL) Appreciate the help ...

Hibernate Criteria query - Class cast exception

Hello everyone, I am using Hibernate(3.2) Criteria for querying and its giving me exception when converting to a list. Please see my code and exception below: List<Summary> summaryList; Criteria criteria = session.createCriteria(Summary.class); session.beginTransaction(); summaryList = Criteria.setProjection( Pro...

Hibernate criteria query on different properties of different objects

Suppose I have classes like: class A { B getB(); C getC(); } class B { String getFoo(); } class C { int getBar(); } and I want to filter criteria on A, two filters on different subclass properties, like: Criteria criteriaA = session.createCriteria(A.class); Criteria criteriaB = criteriaA.createCriteria("b").add(Restrictions.eq(...

nhibernate cross table query optimization

I have a query I've written with NHibernate's Criteria functionality and I want to optimize it. The query joins 4 tables. The query works, but the generated SQL is returning all the columns for the 4 tables as opposed to just the information I want to return. I'm using SetResultTransformer on the query which shapes the returned data t...

Hibernate: Projection of a many-to-one in a Criteria

Hi all, I am playing around with the hibernate Criteria API for the first time recently. I was trying to do the equivalent of this HQL "select t.userTbl from Task t" userTbl property is a many-to-one from Task. The Task.userTbl relationship is lazy. So I came up with this Criteria criteria = session.createCriteria( Task.class, "t"...

Hibernate DetachedCriteria in FROM clause

I have 2 tables: orders: id items: id, orderId, total, flag I would like to make following query using Hibernate Criteria (DetachedCriteria): SELECT o.id, SUM(i1.total), SUM(i2.total) FROM orders o LEFT JOIN ( SELECT i.orderId ...

Hibernate Subquery Question

This should be a simple one I hope. I have an invoice and that invoice has a list of payments. Using the Criteria API I am trying to return a list of invoices and their payment total. So, in SQL I want something like this: SELECT i.*, (SELECT SUM(PMT_AMOUNT) FROM INVOICE_PAYMENTS p WHERE p.INVOICE = i.INVOICE) FROM INVOICES i I can'...

Criteria queries in EJB 3

Can I use Criteria queries with EJB3 entities? If so, how can I combine them with EntityManager? ...

[NHibernate] A correct way to load entities by Id list when Id is not mapped

I have the following code string idName = builder.IdentifierName; Disjunction disjunction = Restrictions.Disjunction(); IList ids = new ArrayList(entityInfos.Length); foreach (var entityInfo in entityInfos) { ids.Add(entityInfo .Id); } disjunction.Add(Restrictions.In(idName, ids)); criteria.Add(disjunction); criteria.List(); (I ha...

Use fewer columns on SQL query through Hibernate Projections on Entity with ManyToOne relation

I'm trying to build a smaller SQL, to avoid the "select * from A" that is being build by default for hibernate Criteria. If I use simple fields (no relation), through "Transformers", I have can manage to have this SQL: select description, weight from Dog; Hi, I have this Entity: @Entity public class Dog { Long id; String descr...