• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Counting Matches

 
Patrick McDonogh
Ranch Hand
Posts: 89
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi there all,

I am trying to count the number of times a word is found in a database field using Hibernate.
For instance say i have a table called Person and a Person java class.

class Person {

String name;
String decsription;

etc,

}

The description of a person can be up to 500 characters.

I then run a query like "from Person p where p.description like '%chocolate%'".

Say i have 4 people in the database whose descriptions are as follows:

Person 1 description - I really love chocolate.

Person 2 description - chocolate is my favorite thing, especially dark chocolate.

Person 3 description - I like long walks.

Person 4 description - chocolate, chocolate, i absolutelly love chocolate


This query will then return people 1, 2 and 4 but not 3 as 3's description does not contain the word chocolate.

So what my question is, is that i want to create a counter for each row showing how many times chocolate occured in that persons description.
I then also want to order by this counter. For instance the counters for the 4 people would be the number of times chocolate appeared in their decsription.

So:

Person 1 - 1
Person 2 - 2
Person 3 - 0
Person 4 - 3

Does anyone know how to do this and how especially to do it with criteria and HQL.
If someone knows where an example or documentation, or what the technical term for this dynamic counting please could you let me know. Is this even possible using a relational database.

Cheers,

Patrick
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic