If I were to define some function in the database (perhaps Postgres, or any other database): create or replace function isValidCookie(ckie); I would call it from SQL as: select * from cookietable c where isValidCookie(c.cookie); How can I call a custom function such as this from Hibernate? ...
I am trying to perform an hql query which returns a list of objects with distinct property value. Following is my pseudo code: string hql = @"select distinct m from Merchandise m where m.Serial is unique" I am using Castle ActiveRecord on top of NHibernate. I have spent half a day on this problem but couldn't find the c...
I am running into similar situation as this post. My pseudo code is string hql = "select ai from AreaInfo as ai where ai.PhoneSegment is substring of :InputPhoneNumber"; Using like wouldn't do the trick because what Like is doing is string hql = "select ai from AreaInfo as ai where :InputPhoneNumber is substring of ai.PhoneSegment"...
I have classes that look like this public class Agreement { public virtual string ArrgKey { get; set; } public virtual string SubjectCode { get; set; } // mapped as a HasMany to AgreementStateCountyRelation public virtual IList<AgreementStateCountyRelation> StateCounties { get; set; } } public class AgreementStateCount...
If I have a mapping like this: <class name="Library" table="Libraries"> ... <dynamic-component name="Annotations"> <property name="LibraryResolver.AlgorithmVersion" column="`LibraryResolver.AlgorithmVersion`" type="Int32" /> </dynamic-component> </class> How should I write HQL or Linq-to-NHibernate query for all libraries wh...
I have 2 domain classes with a many-to-many relationship in grails: decks and cards. The setup looks like this: class Deck { static hasMany = [cards: Card] } class Card { static hasMany = [decks: Deck] static belongsTo = Deck } After I delete a deck, I want to also delete all cards which no longer belong to a deck. The easiest way t...
Hi im a newbe to HQL and have a SQL expression I need converted but am unable to select the SQL statement is: select SenseDate as Time,SenseValue as Value from UserData where NetworkID = '23' and IODeviceID = '129' and SenseDate >= DateAdd("d",-1, GETDATE()) and SenseDate<=GETDATE() and can run this part in HQL without problems: from...
Trying to get all articles, with unique titles (distinct(title)), that have a body of "". List<Article> articles = (List<Article>) session.createQuery("select distinct a.title from Article a where body = :body") .setString("body", "") .list(); I am getting the error: main java.lang.ClassCastException: java.lang.String cannot be cast ...
I want to get a distinct result based on a property, but return the id in the select because I will be using it in a subquery. e.g. (List<Article>) session.createQuery("from Article a where a.id in (select distinct a2.title from article a2 where a2.body = :body"); setString("body", "") .list(); The key section is the subquery, I want...
I have a ClassA that has many ClassB elements: public abstract class ClassA: IEntity<ClassA> { public virtual IList<ClassB> ClassBList { get; protected set; } ... } Then ClassB references ClassC element: public class ClassB { public ClassC CEntity { get; private set; } public Percentage Percentage{ get; private ...
Hi, I have a paging method that uses the criteria API's SetMaxResults and SetFirstResult. Functionally, it works fine. What I'd like to do now is provide another method that retrieves the index of a given item within the set of all items, thus allowing me to calculate what page that item is in. I am able to calculate the index correct...
Is there a way of using SetMaxResult() on a sub query? Im writing a query to return all the order items belonging to the most recent order. So I need to limit the number of records on the sub query. The equivalent sql looks something like: SELECT i.* FROM tbl_Orders o JOIN tbl_OrderItems i on i.OrderId = o.Id WHERE o.Id in (SELECT TOP ...
Hi, short version: is there a HQL equivalent for session.CreateCriteria(string)? Long version: The mapping looks like this: <class name="MyClass" entity-name="MyClass"> ... </class> <class name="MyClass" entity-name="SomeEntityName"> ... </class> When I use session.CreateCriteria("SomeEntityName"), only objects stored...
For some reason I can never figure out how to do things via criteria api. I have a HQL: from Track track where size(track.trackTitles) > 1 Is it possible to convert it into a criteria query on Track class? If yes, how: what Restriction should I use? ...
Hi, all I'm a newbie to Hibernate, so please bear with me if this is obvious or something. I have an Item POJO which contains a Set<String> consisting of labels. The labels are contained on another Database table from the Item table, so I do a join to populate the pojo. I'm trying to run a simple example query from the book "Java Pers...
Hi Guys, I'm trying to get a report using Criteria and ProjectionList, and I'm pretty new using this through hibernate. So I have this model: private Long _userId; private Category _category; private Long _companyId; private Double _amount; private Date _date; And I building the query using this: public List sumPaymentsByU...
I have created 3 tables in my database and put data into them. The 3 tables all have foreign keys joining them together. Below are the table classes and there mappings. When I run the query listed at the end I get IList<> of the objects and they have the data from all 3 tables. However, my HQL query is only from the top most table. ...
I would like to perform the following query in HQL: select count(distinct year(foo.date)) from Foo foo However, this results in the following exception: org.hibernate.hql.ast.QuerySyntaxException: expecting CLOSE, found '(' near line 1, column 27 It seems that hibernate does not allow using functions as arguments to it...
HQL noob here. How do I re-write this HQL query without using the exists clause. In SQL I can just join VisitorProfileField table and Visitor table and add the conditions of the exists using an AND. But in plain HQL I am not able to get past some syntax violation I guess. Your help is much appreciated. "select distinct v from Visitor...
Hello, I'm starting to work with hibernate. I'm quite confused about HQL joins... they are really different from, say, mySQL joins... In fact if I have two tables like Parking and Auto I would do: select * from Parking left join on Parking.pid = Auto.parkingId or something the like. Instead in hibernate I will have a Parking class alr...